The whole design, in writing
Learn system design by building a time-series database like InfluxDB, Prometheus TSDB or TimescaleDB step by step. An interactive guide covering why a normal SQL table melts under metrics, the series = metric + tags data model, append-only columnar storage with delta and XOR compression, an LSM write path with a WAL, sharding by series, downsampling and retention for cheap long-range reads, the cardinality problem and inverted index, and the unhappy paths (late data, hot series).
Every step of the build above, written out: the problem each piece solves, the option that was taken and the ones that were not, the numbers, and how it fails in production.
The big idea
What is a time-series database?
Every server, container, sensor and app emits a constant stream of timestamped numbers: CPU every second, requests per second, temperature, price ticks. You need to ingest millions of points per second, keep them for months, and answer "p99 latency for the EU region over the last 6 hours" in milliseconds.
A time-series database is built for exactly this shape: append-mostly writes of (series, timestamp, value), and reads that are time-range aggregations. By specializing — columnar layout, aggressive compression, time partitioning, and downsampling — it does what a general SQL table simply can't at this volume.
What the new pieces do
- Agents · Dashboardsclient
- Thousands of exporters/agents streaming metrics in, and dashboards/alerts querying time ranges out. Write-heavy, read-bursty.
Step 1 · Why not just SQL?
A normal table melts
The obvious approach: one big table, a row per data point (metric, tags, timestamp, value), indexed on time. Insert every point, query with WHERE time BETWEEN …. At a million points a second, why does this fall apart?
A row-per-point SQL table at 1M points/sec. What breaks?
Indexes make it worse: every insert updates a B-tree, and the index itself becomes enormous. Row storage also can't compress the highly-regular data well. It breaks on write throughput, storage size and range-scan speed at once.
It's not "a little" — it's write amplification from B-tree index maintenance, huge uncompressed row storage, and range scans reading vast amounts of unneeded columns. All three are fundamental to the row model here.
B-tree inserts thrash on every point, the time index balloons, row storage compresses the regular data poorly, and a range scan drags whole rows off disk. The workload wants append-only, columnar, compressed, time-partitioned storage — a different engine.
A general row-store fights this workload. Per-point B-tree index maintenance causes write amplification; the time index bloats; row storage compresses the highly-regular numbers poorly; and range scans drag entire rows off disk. Time-series data is append-only, enormously repetitive, and queried by range — it wants a purpose-built engine.
Step 2 · The shape of the data
Series = metric + tags
Before storing anything, model the data. "CPU usage" isn't one stream — it's one per host, per core, per data center. How do you identify the millions of distinct streams without a table per one?
How do you identify the millions of distinct metric streams?
Millions of tables is unmanageable and can't be queried across. The standard model is a single logical space of series identified by a metric name plus a set of key/value tags.
cpu_usage{host="a",core="0"} is one series; each series is an ordered stream of (timestamp, value) points. Tags let you slice and aggregate ("all cores on host a", "all hosts in eu") without separate tables.
JSON per point is huge and unqueryable at scale. The compact model is a typed metric+tags identity with numeric points, which compresses and indexes beautifully.
Model a series as a metric name + a set of key/value tags (labels): http_requests{method="GET", region="eu"}. Each series is an ordered stream of (timestamp, value) points. Tags are what let you slice and aggregate across series — "sum over all regions", "p99 for method=GET" — without a table per stream.
What the new pieces do
- Ingestgateway
- The write endpoint. Accepts a firehose of (series, timestamp, value) points and lands them durably at very high throughput.
Step 3 · Store it small
Columnar + compression
A point is basically a timestamp and a number. Stored naively that's ~16 bytes each; at a million per second that's terabytes a day. But the data is astonishingly regular. How do you shrink it 10× or more?
Points are timestamp+value at huge volume, but very regular. How do you store them small?
Compressing rows helps a little but mixes timestamps and values and different series together, killing the ratio. Storing each column separately lets you exploit the specific regularity of timestamps and of values.
At terabytes/day the storage (and the scan cost) is the problem, not a shortage of disks. Specialized encodings cut it 10×+, which also makes reads faster — you can't buy your way out of the scan cost.
Store timestamps and values in separate columns per time block. Timestamps arrive at near-fixed intervals, so delta-of-delta encoding shrinks them to bits; consecutive float values barely change, so XOR-ing them leaves mostly zeros to pack. This is the Gorilla/Prometheus approach — often ~1–2 bytes per point.
Store data columnar, in time-partitioned blocks, and compress each column with encodings tuned to it. Timestamps arrive at near-regular intervals, so delta-of-delta encoding reduces them to a few bits. Consecutive float values change little, so XOR-ing adjacent values leaves leading/trailing zeros you can pack. Facebook's Gorilla approach gets from ~16 bytes to often ~1–2 bytes per point — and columnar scans read only the needed column.
- ~16→~1.5bytes per point
- delta²timestamps
- XORvalues
What the new pieces do
- Columnar Shard Adata
- Compressed, time-partitioned columns of timestamps and values. Reads scan only the blocks in range.
Back of the envelope
- columnar, time-partitioned blocks
- scan only the range + column
- delta-of-delta timestamps
- regular intervals → bits
- XOR-compressed floats
- small changes → mostly zeros
Step 4 · Swallow the firehose
The LSM write path
Compressed columnar blocks are great for reading, but you can't rewrite an immutable, compressed block on every incoming point. Yet points arrive by the million per second. How do you ingest fast and keep data durable and readable?
You can't rewrite compressed blocks per point, but points flood in. How do you ingest?
Rewriting a compressed, immutable block per point is impossibly slow. The LSM pattern buffers writes in memory and flushes them in batches to new immutable blocks instead.
New points append to a fast in-memory structure, made durable by a write-ahead log. Periodically the buffer is flushed and compressed into a new immutable columnar block. Reads merge the memtable with on-disk blocks. This is the LSM-tree pattern.
Blocking writers stalls the firehose and risks data loss. You accept every point immediately into memory (durably, via the WAL) and flush asynchronously — never make the producer wait.
Use an LSM-style path. New points append to an in-memory memtable (fast, sorted by time), made durable by a write-ahead log so a crash loses nothing. Periodically the memtable is flushed and compressed into a new immutable columnar block on disk. Reads merge the in-memory head with the on-disk blocks. Ingest stays sequential and cheap; storage stays compressed and immutable.
- memtablein-RAM head
- WALcrash-safe
- flush→ immutable block
What the new pieces do
- WAL + Memtablecontrol
- New points buffer in memory (fast) with a write-ahead log for durability, then flush to immutable columnar blocks.
Step 5 · Beyond one node
Shard by series
One node's memory and disk can't hold or ingest every series in a large fleet. You need to spread both the write load and the stored data across many nodes. What's the partition key?
One node can't ingest/store everything. How do you partition?
Partitioning only by time means all current writes hammer a single "now" node (a hotspot), while older nodes sit idle. You want writes spread across nodes at all times.
Scattering a single series's points across all nodes destroys locality — a range query for one series would gather from everywhere. Keep each series together and spread whole series across nodes.
Hashing the series identity spreads write load evenly across nodes and keeps each series's points together for fast range scans. Within a node, data is still chunked into time blocks so old blocks can be dropped or moved cheaply.
Partition by series — hash the metric+tags identity to a shard — so write load spreads evenly across nodes and each series's points stay together for fast range scans. Within each shard, keep data in time blocks so expiring or moving old data is just dropping whole blocks. (Replicate shards for durability, as any distributed store must.)
- hash(series)even write spread
- time blockscheap retention
- replicateddurable shards
What the new pieces do
- Columnar Shard Bdata
- Another shard. Series are partitioned across shards so ingest and storage scale horizontally.
Step 6 · Cheap long-range reads
Downsampling & retention
A dashboard asking for "CPU over the last year" at 1-second resolution would scan billions of raw points to draw a few hundred pixels. And keeping every raw point forever is ruinously expensive. How do you make long ranges fast and storage bounded?
A "last year" query would scan billions of raw points. How do you make it fast + bounded?
Reading billions of points to draw a chart is slow and expensive, and repeats the work on every refresh. Pre-aggregate into coarser resolutions so a long-range query reads a tiny rolled-up series.
Background jobs roll raw points up into 1-min, 1-hour, 1-day aggregates; a long-range query reads the coarse series and scans orders of magnitude fewer points. Retention policies then drop or archive old raw data so storage stays bounded.
Caching a rendered chart doesn't help arbitrary new ranges or queries and goes stale. The durable fix is pre-aggregated rollups plus retention, which speed up all long-range queries and cap storage.
Downsample: background rollup jobs pre-aggregate raw points into coarser resolutions (1-minute, 1-hour, 1-day) storing min/max/avg/count. A long-range query reads the coarse series and touches orders of magnitude fewer points. Pair this with retention: keep raw data for days, downsampled data for months/years, and drop or archive the rest — bounding storage while preserving history.
- rollups1m / 1h / 1d
- retentionraw days · rolled years
- 1000×fewer points scanned
What the new pieces do
- Query Enginegateway
- Resolves which series match a query's tags, scans the right time blocks, and aggregates (rate, avg, p99) over the range.
- Rollup / Compactservice
- Background jobs that pre-aggregate raw points into coarser resolutions and compact/expire old blocks.
- Cold / Downsampledcold
- Older, downsampled data on cheaper storage. Long-range queries hit this small rolled-up series, not raw points.
Step 7 · The cardinality wall
Tags, cardinality & the index
Queries filter by tags ("region=eu, status=500"), which needs a fast tag→series lookup. But there's a trap: put a high-cardinality value in a tag — a user ID, a request ID, a timestamp — and you create a new series per value, exploding memory and the index. This is the number-one way to kill a TSDB.
Build an inverted index mapping each tag key=value to the set of series ids that carry it, so a query intersects a few posting lists to find matching series instantly. Then defend cardinality: tags must be bounded sets (region, status, method) — never unbounded ids. Enforce limits, drop or reject offending series, and put truly high-cardinality identifiers in the value or in logs, not in tags.
- inverted indextags → series ids
- bounded tagsno user/request ids
- #seriesthe scaling limit
What the new pieces do
- Series Indexservice
- An inverted index mapping tag values (region="eu") to the series ids that match — how queries find series fast.
Step 8 · The sharp edges
Late data, hot series & compaction
Reality intrudes: points arrive out of order or late (a lagging agent), one series gets hammered (a hot series), and immutable blocks pile up needing housekeeping.
Handle out-of-order/late writes with a bounded look-back window that can still land in the current block (or a dedicated catch-up path), rejecting points too old to matter. Spread hot series by good shard hashing and cache their recent head. Run background compaction to merge small blocks into larger ones and apply rollups/retention. And guard writers with rate limits and cardinality caps so one misbehaving exporter can't take the cluster down.
You did it
You just designed a time-series database.
Everything you assembled, in order
- A row-per-point SQL table melts on write amplification, index bloat, poor compression and slow range scans.
- Model data as series (metric + bounded tags) with ordered (timestamp, value) points.
- Columnar storage + delta-of-delta timestamps + XOR values shrink ~16 bytes to ~1–2 per point.
- An LSM write path (memtable + WAL → immutable compressed blocks) swallows the firehose durably.
- Shard by series for even write spread + locality; time-block within a node for cheap retention.
- Downsampling/rollups + retention make long-range reads fast and storage bounded.
- An inverted index makes tag queries fast; bounded cardinality is the real scaling limit.