Time-series data
Storing and querying time-series data such as metrics, sensor readings, prices and events: write patterns, time-partitioning, compression, downsampling and retention, and choosing between time-series databases, wide-column stores and OLAP engines.
Reading is half of it. See this used in a real interview: walk through Design a Metrics and Monitoring System →
Metrics, IoT sensor readings, stock ticks, location pings and click counts all have the same shape: a value (or a few) attached to a timestamp and an identity, arriving constantly, almost never updated, and queried by time range. That shape allows storage and query techniques that general-purpose databases do not use, and designs that ignore them end up paying ten times too much.
What makes time-series different
- Append-mostly: data arrives in time order and old points are almost never changed.
- Very high write rates: millions of points a second is common.
- Queries by range and aggregate: "average CPU per host over the last hour in 1-minute buckets", not "fetch point 12345".
- Value decays with age: last hour at full resolution, last year in daily summaries.
- Identity by labels: a series is a metric name plus tags (
host=web-17, region=eu), and the number of distinct series (cardinality) is a key cost.
Data model
Two common shapes:
- Narrow: one row per (series, timestamp, value). Flexible; the norm in metrics systems.
- Wide: one row per (entity, timestamp) with many columns (a sensor’s temperature, humidity and pressure together). Efficient when values are always written and read together.
Either way, the series identity (name plus tags) is stored once in an index and referred to by id, so each point is just (series id, time, value).
Partition by time
Almost every time-series store partitions data into time chunks (an hour, a day), often combined with a hash of the series:
- Writes go to the newest chunk, which stays hot in memory.
- Range queries touch only the chunks they cover.
- Retention is cheap: dropping old data is deleting whole chunks, not running a huge
DELETE.
In a wide-column store this is the classic time-bucketed partition key: (sensor_id, day) as the partition, timestamp as the clustering column, so one partition never grows without bound. See Design Slack for the same pattern applied to messages.
Compression
Consecutive points are similar, so time-series compresses extremely well:
- Timestamps: delta-of-delta encoding; regular intervals compress to about one bit per point.
- Values: XOR with the previous float (Gorilla), or delta plus run-length encoding for integers and counters.
- Columnar layout: values of one series stored together.
Facebook’s Gorilla reported about 1.37 bytes per point, against 16 raw. That ratio is what makes a year of metrics affordable. See Design a Metrics and Monitoring System.
Downsampling and retention
Keep full resolution only as long as it is useful, and roll up beyond that:
| Age | Resolution | What is stored |
|---|---|---|
| 0–15 days | raw (10 s) | every point |
| 15–90 days | 5 minutes | min, max, sum, count |
| 90 days–2 years | 1 hour | min, max, sum, count |
Store min, max, sum and count, not just averages: averages hide spikes and cannot be re-aggregated correctly. For latency, store histogram buckets so percentiles can be recomputed. Rollups can be computed by background jobs over closed chunks, or continuously by the database (continuous aggregates in TimescaleDB, materialised views in ClickHouse).
Choosing a store
| Option | Fits | Watch out for |
|---|---|---|
| Purpose-built TSDB (Prometheus, InfluxDB, VictoriaMetrics) | metrics and monitoring with label queries | high-cardinality labels |
| TimescaleDB (Postgres extension) | time-series plus relational joins and SQL | write throughput below dedicated engines at the very top end |
| Wide-column store (Cassandra, Bigtable, DynamoDB) with time-bucketed keys | huge write rates, simple per-entity range reads | aggregations must be done elsewhere |
| Columnar OLAP (ClickHouse, Druid) | events with many dimensions, fast ad-hoc aggregates | row-level updates |
Rule of thumb: metrics and alerting → a TSDB; per-device or per-user histories with simple reads → wide-column; analytics over event dimensions → OLAP. See OLTP versus OLAP.
Late, out-of-order and duplicate data
Devices go offline and send batches later; networks reorder. Decide:
- How late a point may arrive and still be accepted (an out-of-order window).
- Whether rollups for a closed window are recomputed when late data arrives.
- How duplicates are handled: idempotent writes keyed by (series, timestamp) make retries safe.
Stream processors handle this with event time, watermarks and allowed lateness. See batch and stream processing.
Cardinality
Every distinct label combination is a series with its own index entry and memory. Labels with unbounded values (user id, request id) multiply series into the millions and are the most common way to break a time-series system. Keep labels bounded; put per-request detail in logs or traces.
Sizing example
50,000 devices × 10 metrics × one point every 10 s = 50,000 points a second. At ~1.5 bytes per point compressed: 50,000 × 1.5 × 86,400 ≈ 6.5 GB a day, about 2.4 TB a year at full resolution, before replication. With 15 days of raw data and hourly rollups after that, storage is a small fraction of that.
Checklist
- Series identity and label cardinality.
- Time-partitioned storage, and retention by dropping chunks.
- Compression appropriate to the values.
- Downsampling tiers with min, max, sum and count.
- Store choice by query shape: TSDB, wide-column or OLAP.
- Late and duplicate data handling.