system-design

Time-Series Databases

Data where the timestamp is the primary axis. It arrives constantly, is never updated, is queried by range, and becomes worthless with age — which is a specific enough profile to deserve its own kind of database.

Prerequisites: Storage Engines, NoSQL Modeling Time to read: ~18 minutes

The problem

You’re storing server metrics. 10,000 servers, 100 metrics each, every 10 seconds.

📐 The arithmetic:

10,000 servers × 100 metrics ÷ 10 s = 100,000 writes/second
Per year: 3.15 × 10¹² data points

At ~50 bytes per row (timestamp, metric name, value, tags) in Postgres:
  157 TB/year — before indexes

And the access pattern is peculiar:

Writes are append-only and relentless. Almost never an update or a delete of a single row.
Recent data is queried constantly; data from six months ago, almost never.
Queries are always time ranges plus aggregation — “average CPU per host, last 6 hours, in 1-minute buckets.”
Old data becomes worthless, or is only needed at a coarser resolution.

🚨 A general-purpose database is a poor fit for all four properties. A B-tree index on a constantly-appended timestamp column, 157 TB of storage, and GROUP BY time_bucket over billions of rows is a losing combination. Hence a specialized category.

What makes a time-series database different

1. Time-partitioned storage

Data is automatically split into chunks by time — a partition per day, per week, per month.

Why this matters more than it sounds:

A query for “last 6 hours” touches one chunk and ignores everything else. The planner eliminates 99.9% of the data before reading anything.
Dropping old data is a DROP TABLE, not a DELETE. Instant, no vacuum, no tombstones, no index churn. This alone is transformative — deleting a billion rows from a normal table is a multi-hour operation that bloats everything.
Recent chunks stay in memory; old chunks live on cheap storage.

2. Extreme compression

Time-series data compresses spectacularly because consecutive values are similar.

Delta encoding: store the difference, not the value.

Timestamps:  1700000000, 1700000010, 1700000020, 1700000030
Deltas:      1700000000, +10, +10, +10
Delta-of-delta: 1700000000, 10, 0, 0        ← almost free

Gorilla / XOR compression (from Facebook’s Gorilla paper) applies the same idea to float values: consecutive CPU readings of 45.2, 45.3, 45.1 share most of their bits, so XOR them and store only the differing bits.

📐 Result: 10–20× compression is routine, and it’s why 157 TB becomes ~10 TB. Combined with columnar layout, some systems report 90%+ reduction.

3. Downsampling and retention policies

Automatic, declarative aging of data:

Raw (10 s resolution)      → keep 7 days
1-minute averages          → keep 30 days
1-hour averages            → keep 1 year
1-day averages             → keep forever

📐 This is the real storage win. You keep a decade of history for the cost of a few days of raw data, because nobody debugging an incident from 2023 needs 10-second granularity.

4. Time-aware query functions

-- TimescaleDB
SELECT time_bucket('5 minutes', ts) AS bucket,
       host,
       avg(cpu) AS avg_cpu,
       max(cpu) AS max_cpu
FROM metrics
WHERE ts > now() - interval '6 hours'
GROUP BY bucket, host
ORDER BY bucket;

Plus built-ins that are painful in plain SQL: gap-filling (what if a host reported nothing for a minute?), interpolation, last-observation-carried-forward, moving averages, and rate calculation.

The systems

System	Character
TimescaleDB	A Postgres extension. Full SQL, joins with relational data, ACID. The easiest adoption if you’re already on Postgres — often the right answer.
Prometheus	Pull-based metrics + alerting. The de-facto standard for infrastructure monitoring. Local storage only; not a long-term store.
InfluxDB	Purpose-built TSDB. Its own query languages (InfluxQL/Flux). Strong for IoT and metrics.
VictoriaMetrics	Prometheus-compatible, much more efficient, scales horizontally. Increasingly the long-term-storage answer for Prometheus.
ClickHouse	Columnar OLAP that’s excellent at time series. Enormous scale, sub-second aggregations.
Cassandra with TWCS	Time-window compaction strategy makes an LSM store behave well for time series. Whole SSTables expire at once.
Amazon Timestream / Google Cloud Monitoring	Managed. Serverless, automatic tiering, no ops.

🎙️ The pragmatic answer, often overlooked: “If we’re already running Postgres, I’d use TimescaleDB before adopting a separate system. We keep SQL, we can join metrics against our relational data, and we don’t add a datastore to operate. I’d move to something like ClickHouse or VictoriaMetrics if the volume genuinely exceeds what it handles.”

Data modeling for time series

Tags vs fields

The universal model:

measurement:  cpu_usage
tags:         {host: "web-01", region: "eu-west", env: "prod"}    ← indexed, low cardinality
fields:       {value: 45.2, load_avg: 1.3}                         ← not indexed, the data
timestamp:    2026-07-22T10:00:00Z

Tags are indexed dimensions you filter and group by. Fields are the measurements.

🚨 Cardinality is the thing that kills time-series databases

Cardinality = the number of unique tag combinations. Every combination becomes a separate stored series.

10,000 hosts × 20 metrics × 5 regions          = 1,000,000 series      ✅ fine

Now someone adds user_id as a tag:

10,000 hosts × 20 metrics × 1,000,000 users    = 2 × 10¹¹ series       💥

This is the most common way people destroy a time-series database, and it happens innocently — someone adds a tag for request_id, session_id, email, or a full URL path. Memory usage explodes, the index no longer fits, and the system falls over.

Rules:

Never use unbounded values as tags — IDs, emails, URLs, trace IDs, timestamps.
High-cardinality data belongs in fields (unindexed) or in a log/trace store, not as a metric dimension.
Monitor series count as a first-class metric. Alert on growth.

🎙️ “I’d keep user_id out of the metric tags — that’s unbounded cardinality and it would blow up the series count. If we need per-user analysis, that belongs in the event/log pipeline, queried in ClickHouse.”

This distinction — metrics are for aggregates over bounded dimensions; logs and traces are for high-cardinality individual events — is a genuinely useful thing to articulate. → Three Pillars of Observability

Wide vs narrow schemas

Narrow (one row per metric):        Wide (one row per timestamp):
ts, host, "cpu", 45.2               ts, host, cpu, memory, disk
ts, host, "memory", 78.1

Narrow is flexible (add metrics without schema change) but stores the timestamp and host repeatedly. Wide is more compact and faster for queries needing several metrics together, but requires a schema change to add a metric. Most systems use narrow; ClickHouse users often use wide for efficiency.

Pull vs push

A genuine architectural fork, and Prometheus made the unusual choice:

Push (StatsD, InfluxDB, most agents) — the application sends metrics to the server. ✅ Works through firewalls and NAT; suits short-lived jobs and serverless. ❌ The server can be overwhelmed by a misbehaving client; you don’t know if a silent client is dead or just quiet.

Pull (Prometheus) — the server scrapes an HTTP endpoint on each target. ✅ The scrape itself is a health check — a failed scrape means the target is down, which is genuinely useful. The server controls its own load. Targets are discovered automatically via service discovery. ❌ Needs network reachability to every target; awkward for short-lived jobs (hence Prometheus’s Pushgateway) and for clients behind NAT.

🚨 “The scrape doubles as liveness detection” is the strongest argument for pull, and it’s a nice detail to bring up.

Where it shows up in system design

Time-series data appears more often than people notice:

Domain	Data
Infrastructure monitoring	CPU, memory, request rate, error rate, latency histograms
Application metrics	Business KPIs, feature usage, conversion funnels
IoT	Sensor readings, device telemetry, GPS tracks
Finance	Tick data, OHLC candles, order book snapshots
Product analytics	Events, sessions, funnels
Real-time ML	Feature values over time, model prediction distributions

🚨 The ride-hailing connection: driver location history is time-series data (device, time, lat/lng) even though current position lives in Redis. That split — current state in memory, history in a TSDB — is the standard pattern. → Geospatial Indexing

⚖️ Trade-offs

Decision	Gain	Cost
TSDB over a general database	10–20× compression, fast range queries, automatic retention	Another system; poor at non-temporal queries
TimescaleDB (Postgres extension)	Keep SQL, joins, ACID, one system	Lower ceiling than purpose-built at extreme scale
Aggressive downsampling	Keep years of history cheaply	Lose granularity — you can’t recover it later
High tag cardinality	Rich filtering	💥 Memory blow-up; the classic failure
Pull model	Scrape = health check; server controls load	Needs reachability; awkward for ephemeral jobs
Push model	Works anywhere; suits short jobs	Server can be swamped; silent failures are invisible

In the real world

Facebook’s Gorilla paper (2015) introduced the delta-of-delta and XOR float compression that most modern TSDBs use. It reported a ~12× reduction, which is what made in-memory time-series storage practical at scale. Worth reading — it’s short and the ideas are elegant.
Prometheus’s deliberate design limits are instructive: it stores locally, doesn’t cluster, and isn’t intended for long-term retention or 100% accuracy. That focus is why it’s reliable — during an outage, your monitoring must not depend on the infrastructure that’s failing.
Cardinality explosions are common enough to be a recognized incident genre. The usual trigger is adding a seemingly harmless label — a URL path with IDs in it, a customer identifier, a container ID that changes on every deploy.

🚨 Interview traps

Storing metrics in your main relational database without acknowledging the volume and retention problem.
Not mentioning retention and downsampling. “We’ll keep everything” is 157 TB/year.
Ignoring cardinality. If your design tags metrics by user or request ID, that’s a blow-up.
Confusing metrics with logs. Metrics are aggregates over bounded dimensions; logs are high-cardinality individual events. Different systems.
Forgetting that old data can be dropped by partition, which is the main operational advantage.
Adding a new TSDB when TimescaleDB would do, if you’re already on Postgres.

🎙️ Soundbites

“At 100,000 writes per second with a year of retention, that’s over 150 TB in a general-purpose database. A time-series store gets 10–20× compression and lets us drop old partitions instantly rather than running a DELETE over billions of rows.”
“I’d downsample: raw for 7 days, 1-minute rollups for 30, hourly for a year. Nobody debugging a two-year-old incident needs 10-second granularity.”
“Keep user_id out of the metric tags — that’s unbounded cardinality and it will take the system down. Per-user analysis belongs in the event pipeline.”
“If we’re already running Postgres, TimescaleDB before a separate system — we keep SQL and joins against relational data, and we don’t add a datastore to operate.”
“Prometheus pulls rather than pushes, which means a failed scrape is itself a liveness signal — that’s a real advantage for monitoring.”

🛠️ Try it

1. Compare storage directly. In Postgres, create a plain table and a TimescaleDB hypertable with the same schema. Insert 10 million rows of realistic metric data into each. Then:

SELECT pg_size_pretty(pg_total_relation_size('metrics_plain'));
SELECT pg_size_pretty(hypertable_size('metrics_hyper'));

Enable compression on the hypertable and measure again. The ratio is the argument.

2. Compare query speed. Run the same “average per 5-minute bucket over the last 6 hours” query against both. Then over the last 30 days. The gap widens dramatically with range size, because chunk exclusion means the hypertable never reads the irrelevant data.

3. Cause a cardinality explosion. In Prometheus or InfluxDB, emit a metric with a request_id label. Generate 100,000 requests. Watch memory usage climb and query performance collapse. Then check the series count. This is a five-minute exercise that will make you permanently careful about labels.

4. Set up retention. Configure a retention policy that drops chunks older than 7 days, and a continuous aggregate that rolls up to 1-minute averages. Watch the raw chunks disappear while the aggregates persist.

Check yourself

1. What makes time-series data different enough to need its own database?

Four properties together: writes are append-only, relentless, and never updated (so LSM/columnar designs fit and B-tree in-place updates don't); queries are always time ranges with aggregation (so time-partitioned storage lets the planner skip nearly all data); consecutive values are highly similar (so delta and XOR compression achieve 10–20×, which general databases don't attempt); and data loses value with age (so automatic downsampling and partition-level deletion are essential — dropping a partition is instant, whereas `DELETE` over a billion rows is a multi-hour, bloat-inducing operation). No single property is decisive; the combination is.

2. What is cardinality, and why is it dangerous?

Cardinality is the number of unique tag/label combinations — every combination becomes a separately stored and indexed series. 10,000 hosts × 20 metrics × 5 regions is a million series, which is fine. Add an unbounded tag like `user_id` or `request_id` and you get hundreds of billions of series: the index no longer fits in memory, ingestion slows, queries time out, and the system falls over. It's the most common way people destroy a TSDB, and it usually happens innocently — someone adds a label for a URL path containing IDs, or a container ID that changes every deploy. High-cardinality data belongs in logs or traces, not metric labels.

3. How does downsampling change your storage requirements?

Dramatically, and it's the main lever. Raw 10-second data for a year is enormous; the same data at 1-minute resolution is 6× smaller, at 1-hour it's 360× smaller, at 1-day 8,640× smaller. A typical policy — raw for 7 days, 1-minute for 30 days, 1-hour for a year, 1-day forever — keeps a decade of usable history for roughly the cost of a week of raw data. The trade-off is irreversible: you cannot recover granularity you discarded, so choose the raw retention window to cover realistic incident investigation (usually days to weeks).

4. Why does Prometheus pull metrics rather than having applications push them?

Several reasons, but the strongest is that **the scrape doubles as a health check** — if Prometheus can't reach a target's endpoint, that target is down, and you learn it immediately without any separate liveness mechanism. Beyond that: the server controls its own ingestion rate, so a misbehaving client can't overwhelm it; targets are discovered automatically from service discovery, so new instances are monitored without configuration; and you can scrape any target manually with `curl` for debugging. The costs are needing network reachability to every target and awkwardness with short-lived jobs (which is why the Pushgateway exists as an escape hatch).

5. When would you use TimescaleDB rather than a purpose-built TSDB?

When you're already running Postgres and the volume is within its capability — which covers a lot of systems. You keep full SQL (including window functions and CTEs), you can **join metrics against relational data** (correlate error rates with customer tier, or sensor readings with device metadata), you get ACID transactions, and you avoid adding a datastore to operate, back up, and monitor. You'd move to ClickHouse, VictoriaMetrics, or a managed TSDB when ingestion volume exceeds what a Postgres node handles, when you need horizontal scaling, or when you need Prometheus-ecosystem compatibility specifically.