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.
Why this piece earns its place
The fair version of the cheaper option isn't a naive table — it's a relational store partitioned by time, with a block-range index over the timestamp instead of a B-tree. That genuinely fixes two of the four complaints: the index stops bloating, because a block-range index over already-ordered data is tiny, and the write amplification goes with it, because there is no per-row B-tree left to maintain — retention gets cheaper too, since expiring old data becomes dropping a whole partition rather than a delete storm. For a modest fleet that is the right call, and I'd rather operate it than a TSDB I have to learn. Where it stops is the two things partitioning can't touch: the per-row overhead and the tag columns repeated on every single point, and the row-major layout that reads the whole row to aggregate one column. Those are properties of the storage format, not of how you slice it. So the honest trigger for a purpose-built engine isn't the word "metrics" — it's the point where bytes per point and scan width, rather than query planning, are what's actually hurting.
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.
Why this piece earns its place
This model quietly assumes a series identity never changes, but the tag set is chosen by whoever emits the metric, and they will change it. Add a version tag to an existing metric and you haven't annotated a series, you've minted a new one: the old series stops, a new one starts, and anything keyed to the whole identity breaks at the deploy. For the length of a rolling deploy both exist, so a naive sum across them double-counts while a naive rate sees a series that just ended. The defense is to treat the tag set as a schema. Queries should aggregate with an explicit group-by over the tags they actually care about, rather than pinning a full identity, so an added tag passes through invisibly. Then review tag changes the way you'd review a column, and where a rename is unavoidable, emit both series for at least a dashboard window instead of cutting over in one deploy.
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.
Why this piece earns its place
Both encodings are bets on regularity, and the block size is where you settle them. Delta-of-delta collapses to a bit only while intervals stay near-fixed — let scrapes jitter, or an agent batch and replay, and the timestamp column stops being nearly free. XOR makes the same bet on values: a counter ticking up in small steps or a flat gauge packs beautifully, but a genuinely noisy full-precision float shares almost no bits with its predecessor and can cost more than the raw 8 bytes. So ~1–2 bytes a point is a fleet average, not a per-series guarantee, and bytes-per-point is worth tracking per metric, because the metrics that blow it are usually the mismodelled ones. Block size is the dial on top of that. These are chained bit streams, so you cannot seek into one — a read decodes from the start of the block. Long blocks amortise headers and compress better; short blocks let a narrow query decode less and let retention expire at a finer grain.
- ~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.
Why this piece earns its place
What this buys in throughput it pays for in memory, and the bill is sized by the wrong variable. The in-memory head holds an open block for every active series at once, so RAM tracks the number of series being written, not the points per second — a fleet that doubles its scrape rate barely moves it, and one that doubles its tag combinations doubles it. Flushing is the other edge, and it's a cliff rather than a trickle: compressing and writing a whole block spikes CPU and disk in one instant, and while it runs the old head is still resident alongside what's being written, so peak memory is the number to size against, not the steady state. Two cheap defenses: stagger flush boundaries across shards so they never land together, and keep the flush interval short enough that WAL replay after a crash stays inside an acceptable restart time — the log only truncates once a flush completes, so a long interval is a long, quiet recovery.
- 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.)
Why this piece earns its place
Hashing the identity assumes the map from series to shard never moves, and it moves the first time you add a node. A plain modulo over the node count relocates nearly every series, and relocating a series here means copying its history, precisely while the cluster is short of capacity — which is why you were adding a node. So fix a large shard count up front and make the shard, not the series, the unit that moves: hash into many virtual shards, assign shards to nodes, and rebalancing becomes handing over whole shards. Time-blocking then offers a second, cheaper option you already have: cut over at a block boundary, so new blocks land on the new owner while old blocks stay put and simply age out under retention. Nothing is copied at all — the cost is that a query for that series reads from two owners until the old blocks expire.
- 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.
Why this piece earns its place
Picking the rollup intervals is the easy half. What you store in each bucket is where long-range queries are quietly decided, because a rollup is lossy on purpose and you learn which question you gave up much later. Min, max, sum and count are safe, because they re-aggregate: an hour composes from its minutes, and an average is recovered as sum over count. An average alone does not compose — averaging averages weights the buckets wrongly unless you kept the counts. A percentile composes from nothing at all: a p99 over 6 hours cannot be derived from stored per-minute p99s, so if you promise percentiles at long range you must roll up a sketch rather than a number, and pay its size in every bucket. Counters have the same trap a level down — a rate has to be computed before the rollup, because the coarse series can no longer see the resets. Decide all of this when you define the rollup, since the raw points that would let you change your mind are exactly what retention deletes.
- 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.
Why this piece earns its place
"Bounded" has to mean bounded across the retention window, not bounded right now, and that distinction is what actually bites. A pod name, a container id, a build version, an autoscaling instance id — each is a small set at any instant, a handful of values that look obviously safe on a dashboard, and every deploy retires that set and mints a fresh one. The live series count stays flat while the total the index must carry climbs with deploy frequency, so the teams that ship most often break it first, and the wall gets hit by a tag nobody would have flagged as unbounded. The number worth alerting on is therefore new series per hour, not active series: the rate is what predicts the wall, while the total only tells you that you already hit it. The usual fix is to aggregate the churning dimension away at ingest — keep the deployment, drop the pod — and to give churn-heavy metrics shorter retention than the stable ones.
- 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.
Why this piece earns its place
The look-back window is the knob here, and it fails quietly in both directions. Too short, and a lagging agent's backlog is refused on arrival: the series has a hole, the chart draws a flat line across it, and nothing in the picture distinguishes dropped data from a steady value. So count and expose rejected-as-too-late per writer — the silent drop is the failure, not the drop. Too long, and the cost lands on everything upstream: the block covering that range must stay open, pinning memory and deferring compression, and any bucket the rollup jobs already aggregated is now wrong, so a late point either forces a recompute of that bucket or is accepted into raw data the coarse series will never reflect. That second outcome is the nastier one, because raw and rolled-up answers to the same question start disagreeing. Set the window from the measured lateness of your agents, keep it short, and give genuine backfill its own path rather than widening the window for everyone.
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.