MANUFACTURING
All Case Studies
10 min

Implementing an Efficient, Scalable Time Series Database Architecture

200,000
rows per second
200,000 rows ingested per second with minimal CPU resources (3-4 vCPUs)
10:1
compression ratio
Compression ratio achieved: 6 TB of storage holds about 60 TB of actual data.
3-4 vCPUs
CPU resources
Time series architecture built on PostgreSQL with TimescaleDB, optimized for partitioning and self-management.
Context

The customer needed a time series database. The requirements called for a system that could handle partitioning, manage time series partitions automatically and, above all, compress data to reduce its overall volume.

Problems

The main challenge was to find a solution that balanced performance and cost. Managed cloud services were available, such as the cloud version of TimescaleDB, which was considered too expensive, and others that lacked the timescaledb extension, such as Amazon RDS for PostgreSQL.

Highly efficient use of storage and compute is the key aspect of this case study. After Miriade's engagement, the database now holds about 6 TB of compressed data which, with a compression ratio of roughly 10:1, corresponds to an actual data volume of 60 TB. Despite this volume, the system delivers very high write performance, inserting 200,000 rows per second with only 3-4 vCPUs.

Solution

To optimize costs while maintaining high performance, the team chose Amazon EC2 instances (split into test, QA, and production environments) running PostgreSQL with TimescaleDB.

The engagement covered the entire engineering chain:

  • Deployment and tuning: we set up replication and tuned TimescaleDB, which was recently upgraded from PostgreSQL 15 to 17.
  • Ingestion and reads: incoming data is ingested at high speed, while serverless functions read the data and export it to long-term storage.
  • Backup: we implemented a hybrid strategy based on consistent snapshots and pgBackRest. To further reduce future costs, the team is evaluating removing pgBackRest and relying on snapshots and the standby alone, and possibly downsizing the replica if read volumes remain low.
Technologies
  • PostgreSQL with TimescaleDB - Time series database chosen for its ability to manage partitioning automatically and compress data, significantly reducing the storage footprint.
  • Amazon EC2 - Virtual server infrastructure hosting the database, preferred over managed services for its lower cost.
  • Apache Flink - Data processing engine that feeds the database, capable of inserting 200,000 rows per second.
  • AWS Lambda - Serverless compute service used to run read queries and populate the storage buckets.
  • Amazon S3 and Amazon Athena - Storage and analytics services that receive and query the data extracted from the main database.
  • pgBackRest - Backup management tool, used alongside the consistent snapshot strategy.