CS Fundamentals Trainer · Guides
Time-series and high-write systems
Designing for a steady stream of small writes: batching and COPY, delta-of-delta and XOR compression, expiring data by dropping partitions, label cardinality, rollups and why percentiles cannot be averaged.
33 min read
Metrics, logs, clicks, sensor readings, financial ticks, game events: some systems receive a huge, steady stream of small writes and read them mostly in aggregate, by time range. They have a different shape from the usual "web application with a database": the data is append-mostly (nobody edits last Tuesday's CPU reading), the rows are tiny, the write rate is high and constant, old data matters less every day, and the queries are things like "the 99th percentile of latency per service over the last hour". General-purpose habits (one INSERT per event, DELETE to expire old rows, one metric per user id) break down quickly under this load. This guide measures what goes wrong and what works, on a real PostgreSQL server and with a model of the compression that time-series databases use. The scripts are in the appendix.
It builds on storage engines (why appending is cheap), observability (the main use of time-series data) and replication and partitioning.
1. What makes the workload different
| A typical application database | A time-series workload | |
|---|---|---|
| Writes | random: update this user, insert that order | append at the current time, thousands to millions per second |
| Updates and deletes of old rows | common | almost never; old data is dropped in bulk by age |
| Reads | by key, small | by time range and aggregate over many rows |
| Value of data | stays | falls with age (recent: precise; old: summarised) |
| Row | many columns, varied | a timestamp, a series identity (name + labels) and a number |
Each of these points to a design decision: batch the writes, partition by time, compress the regularity, keep old data in summarised form, and be careful with the number of distinct series.
Diagram: The path of a metric from the application to a dashboard
2. Writes: batch, or pay for every commit
The first lesson is about how you write. I wrote the same 50,000 samples (timestamp, series id, value) into one PostgreSQL table four ways:
| Method | Time | Rows per second | Relative |
|---|---|---|---|
one INSERT per transaction (autocommit) | 16.7 s | 3,000 | 1 |
one transaction containing 50,000 single-row INSERTs | 3.3 s | 15,000 | 5 times |
1,000 rows per INSERT statement | 0.40 s | 125,000 | 42 times |
COPY | 0.12 s | 420,000 | 140 times |
The same data and the same database, and a factor of 140. Where does it go?
- Commits. Every transaction must be made durable, which means forcing the write-ahead log (the WAL, a sequential log of changes; see storage engines) to disk and waiting for it. One commit per row means one disk flush per row. Putting 50,000 rows in one transaction does that once. (Postgres can also group the commits of concurrent clients into one flush, and
synchronous_commit = offtrades a small window of possible loss for speed; neither is a substitute for batching.) - Round trips and parsing. Each statement costs a network round trip, parsing and planning. A statement with 1,000 rows does that once for 1,000 rows.
COPYskips most of the per-statement machinery and streams rows in bulk, which is why it is the standard way to load data.
So the ingest path of such a system is built around batches: agents collect samples for a second or so and send them together, a queue absorbs bursts, and workers write large batches with COPY or a bulk API. This also gives backpressure: when the database is slow, the queue grows (visibly) instead of the application timing out (resilience patterns). Make writes idempotent (a sample is identified by series and timestamp, so a retried batch overwrites or is ignored rather than duplicating, see idempotency), because at-least-once delivery is the realistic guarantee.
Indexes also cost on every write. A time-series table usually needs one index on (series, time) or just time, not five; every extra index slows the ingest and makes the table bigger.
3. Compressing a time series
Time-series data is unusually compressible because it is regular: timestamps arrive at fixed intervals, and consecutive values are close to each other. The Gorilla paper (Facebook, 2015) described two simple tricks that are now used, in variants, by Prometheus, InfluxDB, TimescaleDB and others:
- Delta-of-delta for timestamps. Store the difference between consecutive timestamps (the delta), and then the difference between consecutive deltas. For a sample every 10 seconds, the delta is always 10, so the delta-of-delta is 0, which takes one bit.
- XOR for values. The IEEE floating-point representation of two nearby values shares its leading bits (sign, exponent and top of the mantissa). XOR the new value with the previous one: if they are identical the result is all zeros (one bit); otherwise only the "meaningful" middle bits need to be stored.
I counted the bits that this encoding needs on 100,000 synthetic samples (compared with 16 bytes = 128 bits for a raw timestamp and value):
| Series | Bits per sample | Compression |
|---|---|---|
| a gauge that mostly does not change | 2.0 | 64 times |
| a counter increasing by one each time | 16.4 | 7.8 times |
| a sensor, smooth, one decimal | 28.6 | 4.5 times |
| the same with timestamps jittering by a second | 22.2 | 5.8 times |
| random noise at full floating-point precision | 54.8 | 2.3 times |
The pattern is the point. Regular timestamps cost almost nothing, unchanging values cost almost nothing, and noise cannot be compressed (the last row stores most of the bits of every value). Two practical consequences: align collection to a fixed interval (jittered timestamps cost about six times more bits for the timestamp alone), and do not record more precision than you need, since rounding a sensor to one decimal turned 54.8 bits into 28.6. Facebook reported averaging 1.37 bytes per sample on its production data.
Column-oriented storage helps in the same way: storing all the timestamps together, and all the values of a series together, makes each block uniform, which compresses well and makes aggregation scans read only the columns they need. That is the design of ClickHouse and of the compression in TimescaleDB.
4. Deleting old data: partition by time
Time-series data is kept for a retention period (30 days of raw data, say) and then removed. In Postgres the obvious way is DELETE FROM samples WHERE ts < now() - interval '30 days'. It works, and it is expensive. I built a table of 5,000,000 rows covering 10 days, once as a plain table and once partitioned into one partition per day, and removed the oldest two days (1,000,000 rows):
DELETE from a plain table | DROP two daily partitions | |
|---|---|---|
| time | 0.8 to 1.3 s (two runs) | 54 ms |
| WAL written | 103 MB | 2.4 MB |
| table size afterwards | 356 MB (unchanged) | 285 MB (space freed at once) |
| extra work later | VACUUM FULL: 3.6 s and an exclusive lock, to reach 285 MB | none |
A DELETE marks each row dead, writes every deletion to the WAL (which is also what replicas receive), and leaves the space in the file for later reuse by an ordinary VACUUM; the table does not shrink on disk until a rewrite. Dropping a partition is deleting a file: it takes milliseconds, writes almost nothing and returns the disk space at once, whatever the number of rows. The partition also helps reads. For a query on the last hour, the plan scanned only the partition for the last day (partition pruning), in 3.3 ms, and the other nine partitions were not touched. And the recent partitions stay small, so their indexes fit in memory, which keeps the hot writes fast (memory hierarchy).
The cost is operational: partitions have to be created ahead of time (automate it, with a few days of margin so that a failed job does not make inserts fail), and queries and unique constraints should include the partition key. Extensions such as TimescaleDB automate this ("hypertables" and "chunks"), and databases built for the purpose (Prometheus blocks, InfluxDB shards, ClickHouse partitions) work the same way: expiry is the deletion of whole time-ranges, never of single rows.
5. Cardinality: the number of series is the real size
In a time-series database a series is a unique combination of a metric name and label values: http_requests{service="checkout", status="500", endpoint="/pay"}. Storage and memory depend less on the number of samples than on the number of distinct series, because each series has an index entry and an in-memory buffer for its recent samples. The count is the product of the label values:
| Labels | Series | Memory at an assumed 3 KB per series |
|---|---|---|
| service (20) × status code (5) | 100 | negligible |
| × endpoint (50) | 5,000 | 15 MB |
| × instance (30) | 150,000 | 440 MB |
| × user id (10,000 users) | 1,500,000,000 | about 4 TB |
Adding one label with many values turned a comfortable system into an impossible one. The rule is: labels must have a small, bounded set of values. Never use user ids, session ids, email addresses, full URLs with ids in them, or request ids as labels (use the route template /users/{id}, not /users/48213). High-cardinality questions ("what did this user do?") belong in logs or traces (observability), which are designed to be searched by arbitrary fields. Monitor the number of active series itself and alert when it jumps; a deploy that adds an unbounded label is a classic cause of a monitoring outage.
5.1 A cardinality incident, step by step
Suppose a server has 8 GiB reserved for series, at the 3 KiB per series used above: room for about 2.8 million series (cardinality.py). It holds 150,000 series today (the 20 × 5 × 50 × 30 product from the table). A deploy adds a customer_id label to the request counter, "so support can see a customer's errors". The service handles 60,000 requests a minute from 400,000 distinct customers, with a few busy ones and a long tail of rare ones, and I simulated an hour (the shape of the traffic is my assumption):
| Minutes after the deploy | Series | Memory | Share of the limit |
|---|---|---|---|
| 1 | 208,727 | 0.6 GiB | 7% |
| 10 | 677,471 | 1.9 GiB | 24% |
| 20 | 1,147,770 | 3.3 GiB | 41% |
| 40 | 2,023,523 | 5.8 GiB | 72% |
| 60 | 2,851,356 | 8.2 GiB | 102% |
The limit is crossed at minute 59. Nothing looks wrong for most of the hour, because the series count grows smoothly and the per-sample rate is unchanged; the growth is also not linear, because busy customers reuse series while the long tail creates new ones continuously, so it does not level off in time to help. Which of three outcomes follows depends on what you configured before the deploy:
| Policy | What happens at the limit | Blast radius |
|---|---|---|
| A. No limit | the server runs out of memory and is killed; on restart it replays its write-ahead log (minutes, during which it ingests nothing), and then grows again | every metric of every team: dashboards are blank and alerts cannot fire, exactly when an incident is likely |
| B. A limit per metric (or per tenant) | samples for new series of that metric are rejected, with a counter you can alert on; existing series, and every other metric, keep working | one metric loses the customers who first appeared after the limit |
| C. Drop or bound the label at ingestion (a relabelling rule, or a code review that rejects unbounded labels) | the series count stays at 150,000 | none; per-customer questions move to logs and traces, which are built for them |
The operational lessons: alert on the rate of growth of active series (not only on memory), put a limit on every tenant and metric, check label values in review ("can this have more than a few hundred values?"), keep the previous deploy easy to roll back, and note that removing the label does not free the memory at once, since old series stay in memory until their retention or head block expires. The memory figure (3 KiB) is the guide's working assumption: measure your own system, because it varies several-fold with the labels, the engine and its version. I did not run Prometheus or another real server for this guide; what is shown is arithmetic and a seeded simulation of the number of distinct series.
6. Summarise old data: rollups
Nobody needs a ten-second resolution for a year ago. Downsampling replaces old raw samples with aggregates over larger windows. For one series sampled every 10 seconds, a year of raw data is 3,153,600 points; 1-minute rollups keep 525,600 (6 times fewer), and hourly rollups 8,760 (360 times fewer). A typical policy is raw data for 2 weeks, 1-minute rollups for 3 months, hourly rollups for 2 years, each tier dropped by partition.
What you keep in a rollup matters, because you cannot recover what you did not store. Keep count, sum, minimum and maximum, so that the mean can be recomputed (sum / count, correctly weighted across windows), and extremes are not hidden. For latency you have a problem, shown next.
Percentiles cannot be averaged
Suppose instance A served 9,000 requests, all in 50 ms, and instance B served 1,000 requests of which 20% took 900 ms. Their 99th percentiles are 50 ms and 900 ms. The average of those two is 475 ms, which is no real latency at all. The true 99th percentile of all 10,000 requests is 900 ms (2% of requests were slow), and the mean of all requests is 67 ms, which hides them. Percentiles, unlike sums and counts, do not combine. The solutions are histograms: store, for each series, how many requests fell into each latency bucket (counts can be summed across instances and across time), and compute percentiles from the merged histogram at query time. The buckets must be chosen near the values you care about (your latency objective), because the answer is only as exact as the bucket width. Sketches such as t-digest and DDSketch give a more precise result in less space.
7. Time itself is awkward
- Late and out-of-order data: a mobile device sends yesterday's readings when it reconnects; a queue replays an hour of data after an outage. Decide how late a sample may arrive and still be accepted, and keep the write path able to insert into older (but still open) partitions. Rollups for a window must be recomputed or finalised only after the lateness limit passes. Distinguish event time (when it happened) from ingest time (when you received it).
- Clocks differ between machines (distributed systems); store timestamps in UTC, with a sufficient resolution, and use server-side time when device clocks cannot be trusted.
- Duplicates from retries: make the key
(series, timestamp)unique or accept an overwrite. - Gaps are information: a missing sample (the agent died) is different from a zero. Alert on missing data separately from alerting on values.
- Time zones and calendar windows ("per day" for a user) are a presentation concern; aggregate in UTC in the database and convert at the edge.
7.1 Late data, corrections and backfills in detail
"Decide how late a sample may arrive" hides several separate decisions. I simulated six hours of one series (one sample a minute) whose samples arrive with a realistic spread: 90% within 10 seconds, 8% within 5 minutes, and 2% from devices that reconnect up to two hours later. Rollups are 5-minute sums. The simulation is deterministic (late.py) and uses only the Python standard library; the shares are my assumption, not a measurement of any system.
1. A rollup that is sealed on a schedule loses the late samples. The policy "seal a window at its end plus an allowed lateness L, and drop anything later":
Allowed lateness L | Samples dropped (of 360) | Sum missing from the rollups | A rollup is final after |
|---|---|---|---|
| 0 (seal at once) | 17 | 4.96% | 0 s |
| 1 minute | 14 | 4.07% | 60 s |
| 10 minutes | 5 | 1.54% | 10 min |
| 2 hours | 0 | 0.00% | 2 hours |
Every row is a trade: a small L gives timely dashboards and silently wrong totals, and a large L is exact but means that the last two hours are "provisional". The alternative is a recompute policy (the last row of the output): a late sample re-aggregates the window it belongs to, nothing is lost, and the cost is that published rollups change (17 rewrites here) and every consumer must tolerate numbers that move. State the policy per metric. Counters used for billing need the exact answer, and a CPU graph for the last hour does not. Always keep the event time and the ingest time, so that you can tell afterwards what was known when. And count the drops: a metric such as late_samples_dropped_total turns a silent loss into something you can alert on.
2. Corrections to historical samples need the raw data. A faulty sensor reported 90 where the true value was 14. If the raw samples still exist, you fix the sample and recompute the rollup. If retention has already deleted them (raw data for 2 days, rollups for 90 is a common policy), you can only adjust the rollup by the difference. The simulation shows what survives:
| Rollup field | After correcting 90 to 14 with only the rollup | True value |
|---|---|---|
| count | 6 | 6 |
| sum | 72.0 (adjustable by the delta) | 72.0 |
| mean | 12.0 (derived from sum and count) | 12.0 |
| maximum | 90.0 (wrong) | 14 |
Sums, counts and means can be repaired by a delta, but a minimum or maximum cannot, because you no longer know what the next-largest sample was; histograms cannot be repaired without the original values either. So a correction window is another retention rule: decide how long after the fact a correction is accepted, keep raw data at least that long, and record every correction as its own event (old value, new value, who, why) instead of overwriting history silently. For regulated data, see the audit-trail section in the event sourcing guide.
3. A backfill must be idempotent. Loading history (a migration, an outage replay, a new device's backlog) will sometimes be run twice, after a timeout or a partial failure. Writing an additive rollup (sum = sum + x) twice doubled it (200 where 100 is true), while a write keyed by (series, window) that replaces the value (INSERT ... ON CONFLICT DO UPDATE) stayed at 100. Key backfills by series and window, make each run replace instead of add, and load oldest-first or by whole windows so that a half-finished run can be repeated.
4. Backfill and retention fight each other. If retention keeps 30 days and you load 45 days of history, 15,000 of the 45,000 rows (a third, in the example) are removed by the next retention run, whatever you meant; with partitions per day the old partitions are created and dropped again, or the insert fails because no partition exists. Decide first whether history older than the retention period is wanted at all: if it is, load it straight into the rollup tiers that are kept (not the raw table); if it is not, reject it at the door, with a clear error. A backfill that writes a large amount of raw data also competes with live ingest for the same disks and the same write-ahead log (see section 2), so run it in the background with a rate limit, and not in the same partition as the live writes if you can avoid it.
5. Rollups and retention have to be sequenced. Dropping a raw partition before its rollup is final loses data for good, and the lateness limit from point 1 is a lower bound on how long raw data must be kept. The order is: raw data arrives, the window is sealed (or recomputed until the correction window closes), the rollup is written and checked, and only then is the raw partition dropped. Make the drop job refuse to run if the rollup for that range is missing, and keep one extra partition of margin.
8. Push or pull, and the choice of database
Metrics reach the store in two ways. In the pull model (Prometheus) the monitoring server scrapes each target's /metrics endpoint on a schedule: it knows which targets exist, a down target is itself a signal, and targets cannot flood it. In the push model (StatsD, OpenTelemetry exporters, agents) clients send data, which suits short-lived jobs and devices behind firewalls, but needs a buffer and flow control.
| Choice | Fits |
|---|---|
| Prometheus (or a compatible long-term store such as Thanos, Mimir or VictoriaMetrics) | infrastructure and application metrics, with the alerting ecosystem |
| PostgreSQL with time partitioning, or TimescaleDB | moderate volume, when you want SQL and joins with your other data; the experiment above shows what to get right |
| ClickHouse and other column stores | very high volume, analytical queries over events and logs |
| InfluxDB and similar | purpose-built time-series databases with retention and rollup policies |
| A managed cloud service | when you would rather not operate it |
| A log or stream (Kafka) in front | to buffer, replay and feed several consumers; it is the buffer in the diagram |
For many systems the right start is the simplest: batched writes into time-partitioned Postgres. Move to a specialised store when the series count, ingest rate or retention outgrows one machine, and do not scale it in a way that ignores the reasons in this guide (sharding a time-series table by time puts every new write on one shard; shard by series, or by series and time).
9. Common mistakes
- One row, one commit; or one network call per sample.
DELETEfor expiry on a large table, then wondering why the disk is full and the replicas lag.- Unbounded label values (user ids, URLs with ids) and no alert on the number of series.
- Averaging percentiles, or monitoring only the mean.
- Raw data at full resolution forever, or rollups that discard the maximum.
- Not creating the next partitions ahead of time.
- Several indexes on a table that receives millions of rows per hour.
- No plan for late data, duplicates and missing samples.
- Treating dropped samples as zeros.
- Putting the time-series workload on the same database instance as the transactional application, so that a metrics flood slows the checkout.
10. Things to mention in an interview
- The workload: append-mostly, bulk expiry by age, range reads and aggregates.
- Batching and group commit; why
COPYand multi-row inserts are 40 to 140 times faster than commit-per-row (I measured 3,000 against 420,000 rows per second). - Queues for buffering and backpressure; idempotent writes for retries.
- Delta-of-delta and XOR compression, and why regularity compresses (2 to 64 times in the model).
- Time partitioning: dropping a partition against
DELETE(milliseconds against a second plus vacuum), partition pruning, creating partitions ahead. - Cardinality as a product of label values; bounded labels; where high-cardinality data belongs.
- Downsampling tiers and what to keep (count, sum, min, max); histograms because percentiles do not average.
- Late data, event time against ingest time, push against pull.
Quick recap
- A time-series workload is append-mostly and read in aggregate by time, so batch the writes (the same 50,000 rows took 16.7 s one commit at a time and 0.12 s with
COPY). - Compress the regularity: delta-of-delta timestamps and XOR values cost 2 to 55 bits per sample in the model, against 128 raw.
- Expire by dropping time partitions (54 ms, 2.4 MB of WAL, space returned) rather than
DELETE(about a second, 103 MB of WAL, no space returned until a rewrite). - Watch cardinality: the series count is the product of the labels' values, and one user-id label can turn thousands of series into billions.
- Downsample old data keeping count, sum, minimum and maximum, use histograms for latency because percentiles cannot be averaged, and plan for late, duplicate and missing data.
Appendix: the experiment code
Three files. ingest.sh and retention.sql need a scratch PostgreSQL 16 server (use a throw-away database, never a production one): the first writes the same 50,000 samples by four methods, the second builds a 5,000,000-row table with and without daily partitions and removes the oldest two days both ways. gorilla.py uses only the Python standard library; it counts the bits that the encodings of the Gorilla paper would need on synthetic series (my reconstruction of the published bit layouts, not the code of any real database), and performs the cardinality, rollup and percentile arithmetic. The Postgres timings are single runs on a small two-core virtual machine with default settings (fsync on, 128 MB of shared buffers) and vary between runs; the ratios between the methods are what matter. The memory-per-series figure (3 KB) is an assumption for the sake of the arithmetic; real systems differ by a factor of a few.
ingest.sh: four ways to write 50,000 rows to Postgres
#!/bin/bash
# Postgres ingest: the same 50,000 metric samples written four ways. Needs a scratch Postgres and python3.
# PSQL="psql -h <socket dir> -p <port> -d <database> -X -q"
PSQL=${PSQL:-psql}
N=50000
python3 - <<PY
import random
r = random.Random(7)
rows = [(f"2026-10-01 00:{(i//600)%60:02d}:{(i//10)%60:02d}.{(i%10)*100:03d}+00", i % 50, round(40 + r.random()*20, 2)) for i in range($N)]
open("one.sql", "w").write("".join(f"INSERT INTO samples VALUES ('{t}', {s}, {v});\n" for t, s, v in rows))
open("one_tx.sql", "w").write("BEGIN;\n" + "".join(f"INSERT INTO samples VALUES ('{t}', {s}, {v});\n" for t, s, v in rows) + "COMMIT;\n")
open("multi.sql", "w").write("".join("INSERT INTO samples VALUES " + ",".join(f"('{t}', {s}, {v})" for t, s, v in rows[k:k+1000]) + ";\n" for k in range(0, $N, 1000)))
open("copy.csv", "w").write("".join(f"{t},{s},{v}\n" for t, s, v in rows))
PY
reset() { $PSQL -c "DROP TABLE IF EXISTS samples; CREATE TABLE samples (ts timestamptz NOT NULL, series int NOT NULL, value double precision NOT NULL);"; }
timeit() { # label command...
label=$1; shift; reset; s=$(date +%s.%N); "$@" >/dev/null; e=$(date +%s.%N)
awk -v l="$label" -v s=$s -v e=$e -v n=$N 'BEGIN{printf "%-44s %7.2f s %9.0f rows/s\n", l, e-s, n/(e-s)}'
}
timeit "one INSERT per transaction (autocommit)" $PSQL -f one.sql
timeit "one transaction, one INSERT per row" $PSQL -f one_tx.sql
timeit "1,000 rows per INSERT statement" $PSQL -f multi.sql
timeit "COPY" $PSQL -c "\\copy samples FROM 'copy.csv' CSV"
retention.sql: DELETE against dropping a partition
-- Retention: delete old data with DELETE, or by dropping a partition. 5,000,000 samples over 10 days.
\timing on
DROP TABLE IF EXISTS plain, part CASCADE;
CREATE TABLE plain (ts timestamptz NOT NULL, series int NOT NULL, value double precision NOT NULL);
CREATE TABLE part (ts timestamptz NOT NULL, series int NOT NULL, value double precision NOT NULL) PARTITION BY RANGE (ts);
SELECT format('CREATE TABLE part_d%s PARTITION OF part FOR VALUES FROM (%L) TO (%L)', d, ('2026-10-01'::date + d), ('2026-10-01'::date + d + 1)) FROM generate_series(0, 9) d \gexec
INSERT INTO plain SELECT '2026-10-01'::timestamptz + (i * interval '0.1728 seconds'), i % 100, random() FROM generate_series(0, 4999999) i;
INSERT INTO part SELECT * FROM plain;
CREATE INDEX ON plain (ts); CREATE INDEX ON part (ts);
VACUUM ANALYZE plain; VACUUM ANALYZE part;
\timing off
SELECT 'rows', count(*), pg_size_pretty(pg_total_relation_size('plain')) AS plain_size, pg_size_pretty((SELECT sum(pg_total_relation_size(inhrelid)) FROM pg_inherits WHERE inhparent = 'part'::regclass)::bigint) AS partitioned_size FROM plain;
SELECT pg_current_wal_lsn() AS lsn0 \gset
\echo --- remove the oldest 2 days (1,000,000 rows) with DELETE
\timing on
DELETE FROM plain WHERE ts < '2026-10-03';
\timing off
SELECT pg_size_pretty(pg_current_wal_lsn() - :'lsn0'::pg_lsn) AS wal_written_by_delete;
SELECT pg_current_wal_lsn() AS lsn0 \gset
SELECT 'plain after DELETE, before VACUUM', pg_size_pretty(pg_total_relation_size('plain')), (SELECT count(*) FROM plain) AS rows_left;
\echo --- the same with DROP of two partitions
\timing on
DROP TABLE part_d0; DROP TABLE part_d1;
\timing off
SELECT pg_size_pretty(pg_current_wal_lsn() - :'lsn0'::pg_lsn) AS wal_written_by_drop;
SELECT 'partitioned after DROP', pg_size_pretty((SELECT sum(pg_total_relation_size(inhrelid)) FROM pg_inherits WHERE inhparent = 'part'::regclass)::bigint), (SELECT count(*) FROM part) AS rows_left;
\echo --- VACUUM FULL is needed to give the deleted space back to the operating system
\timing on
VACUUM FULL plain;
\timing off
SELECT 'plain after VACUUM FULL', pg_size_pretty(pg_total_relation_size('plain'));
\echo --- a query for the last hour: partition pruning
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY ON) SELECT count(*) FROM part WHERE ts >= '2026-10-10 22:00' AND ts < '2026-10-10 23:00';
gorilla.py: compression, cardinality, rollups and percentiles
"""Gorilla-style compression of a time series: delta-of-delta timestamps and XOR-ed floats.
Counts the bits each encoding needs (the bit counts follow the Gorilla paper, VLDB 2015)."""
import math, random, struct
def bits_ts(ts):
"""Timestamps: the first is stored whole (64 bits); then the delta of the deltas is stored in 1 to 36 bits."""
total, prev_delta = 64 + 14, None
for a, b in zip(ts, ts[1:]):
delta = b - a
if prev_delta is None: total += 14; prev_delta = delta; continue # first delta: 14 bits
dod = delta - prev_delta; prev_delta = delta
if dod == 0: total += 1
elif -63 <= dod <= 64: total += 2 + 7
elif -255 <= dod <= 256: total += 3 + 9
elif -2047 <= dod <= 2048: total += 4 + 12
else: total += 4 + 32
return total
def f2i(x): return struct.unpack(">Q", struct.pack(">d", x))[0]
def bits_values(vs):
"""Values: XOR with the previous value. Identical -> 1 bit; else a control bit plus the meaningful middle bits."""
total, prev, lead, trail = 64, f2i(vs[0]), None, None
for v in vs[1:]:
x = f2i(v) ^ prev; prev = f2i(v)
if x == 0: total += 1; continue
l = min(64 - x.bit_length(), 31); t = (x & -x).bit_length() - 1
if lead is not None and l >= lead and t >= trail: total += 2 + (64 - lead - trail) # reuse the previous window
else: total += 2 + 5 + 6 + (64 - l - t); lead, trail = l, t # new window: header + bits
return total
def report(name, ts, vs):
n = len(ts); raw = n * 16 * 8
bt, bv = bits_ts(ts), bits_values(vs)
print(f"{name:<38} timestamps {bt/n:5.1f} bits values {bv/n:5.1f} bits total {(bt+bv)/n:5.1f} bits per sample ({raw/(bt+bv):4.1f}x smaller than 16 bytes)")
r = random.Random(3); n = 100_000
regular = [1_700_000_000 + 10 * i for i in range(n)] # a scrape every 10 s, on time
jitter = [1_700_000_000 + 10 * i + r.choice((-1, 0, 0, 0, 1)) for i in range(n)] # +/- 1 s of jitter
print("--- timestamps and values together, 100,000 samples")
report("counter: +1 each step", regular, [float(i) for i in range(n)])
report("gauge that mostly does not change", regular, [round(50 + (i // 500) % 3, 0) for i in range(n)])
report("gauge: smooth sensor (1 decimal)", regular, [round(20 + 3 * math.sin(i / 2000) + r.gauss(0, .05), 1) for i in range(n)])
report("gauge: full-precision noise", regular, [20 + r.gauss(0, 1) for _ in range(n)])
report("counter with jittery timestamps", jitter, [float(i) for i in range(n)])
print("--- how many series? (cardinality is the product of the label values)")
for name, dims in (("service x status code", (20, 5)), ("... x endpoint", (20, 5, 50)), ("... x instance", (20, 5, 50, 30)), ("... x user id (10,000 users)", (20, 5, 50, 30, 10_000))):
s = math.prod(dims); print(f"{name:<32} {s:>14,} series ~{s * 3 / 1024:>12,.0f} MB of index and head memory at 3 KB per series")
print("--- rollups: points kept for one series sampled every 10 s for a year")
samples = 365 * 24 * 360
for name, factor in (("raw samples, 10 s", 1), ("1-minute rollups", 6), ("1-hour rollups", 360)):
print(f"{name:<20} {samples // factor:>10,} points ({factor}x fewer)")
print("--- percentiles cannot be averaged")
def pct(xs, p): xs = sorted(xs); return xs[min(len(xs) - 1, math.ceil(p / 100 * len(xs)) - 1)]
a = [50] * 9000 # instance A: 9,000 requests, all fast (50 ms)
b = [50] * 800 + [900] * 200 # instance B: 1,000 requests, 20% slow (900 ms)
print(f"p99 of A: {pct(a, 99)} ms p99 of B: {pct(b, 99)} ms average of the two p99s: {(pct(a, 99) + pct(b, 99)) / 2:.0f} ms")
print(f"p99 of all 10,000 requests together: {pct(a + b, 99)} ms (average of the two p99s is wrong whenever the instances differ)")
print(f"mean latency of all requests: {sum(a + b) / len(a + b):.0f} ms (the mean hides the slow 2%)")
Late data, corrections and backfills: late.py
Python standard library only; deterministic.
"""Late data, corrections, backfills and retention against 5-minute rollups. stdlib only, deterministic."""
import random, sqlite3, math
random.seed(7)
W = 300 # rollup window: 5 minutes
HOURS = 6
N = HOURS * 60 # one sample per minute, value = 10 + (t % 7)
def delay(): # seconds between event time and arrival
r = random.random()
if r < 0.90: return random.uniform(0, 10)
if r < 0.98: return random.uniform(10, 300)
return random.uniform(300, 7200) # a device that reconnects later
events = [] # (event_time, arrival_time, value)
for m in range(N):
t = m * 60
events.append((t, t + delay(), 10 + (m % 7)))
events.sort(key=lambda e: e[1])
true_sum = {}
for t, _, v in events: true_sum[t // W] = true_sum.get(t // W, 0) + v
true_total = sum(true_sum.values())
def run(policy, L):
"""policy 'finalize': a window is sealed at window_end + L; later samples are dropped.
policy 'recompute': a window is re-aggregated whenever a late sample lands in it."""
sums, count, dropped, rewrites, sealed = {}, {}, 0, 0, set()
ready_at = {}
for t, a, v in events:
w = t // W
seal_time = (w + 1) * W + L
if policy == 'finalize':
if a > seal_time: dropped += 1; continue
sums[w] = sums.get(w, 0) + v; count[w] = count.get(w, 0) + 1
ready_at[w] = seal_time
else:
if w in sums: rewrites += 1 if a > (w + 1) * W else 0
sums[w] = sums.get(w, 0) + v; count[w] = count.get(w, 0) + 1
tot = sum(sums.values())
miss = 100 * (true_total - tot) / true_total
lag = (max(0, L) if policy == 'finalize' else 0)
return dropped, miss, rewrites, lag
print("samples:", N, "late(>5 min):", sum(1 for e in events if e[1] - e[0] > 300))
print("policy,L_seconds,dropped,missing_sum_pct,rewrites_of_published_windows,min_delay_before_rollup_is_final_s")
for L in (0, 60, 600, 7200):
d, m, r, lag = run('finalize', L); print(f"finalize,{L},{d},{m:.2f},{r},{lag}")
d, m, r, lag = run('recompute', 0); print(f"recompute,-,{d},{m:.2f},{r},0 (never final)")
# --- corrections: raw is kept 2 days, rollups 90. Correct one sample after the raw data is gone.
con = sqlite3.connect(":memory:")
con.execute("create table roll(w int primary key, n int, s real, mn real, mx real)")
vals = [10, 12, 11, 13, 90, 12] # 90 is a faulty reading
con.execute("insert into roll values(0,?,?,?,?)", (len(vals), sum(vals), min(vals), max(vals)))
# correction arrives: the 90 should have been 14. Raw samples were deleted by retention.
old, new = 90, 14
con.execute("update roll set s=s-?+? where w=0", (old, new)) # sum and count can be adjusted by the delta
print("\nafter a delta correction without raw data:", con.execute("select n,s,mn,mx, s/n from roll").fetchone(), "(true max is 14, mean 12.0)")
# --- backfill: re-running a load without an idempotent key
con.execute("create table naive(w int, s real)")
con.execute("create table keyed(w int primary key, s real)")
for _ in range(2): # the job is run twice, e.g. after a timeout
con.execute("insert into naive values(0, 100)")
con.execute("insert into keyed values(0,100) on conflict(w) do update set s=excluded.s")
print("backfill run twice: add-only rollup =", con.execute("select sum(s) from naive").fetchone()[0],
"; upsert by window =", con.execute("select s from keyed").fetchone()[0], "(true 100)")
# --- backfill against retention: 45 days of history loaded into a store with 30-day retention
days_back, keep = 45, 30
rows_per_day = 1000
kept = rows_per_day * keep
print(f"\nbackfill of {days_back} days with {keep}-day retention: {rows_per_day*days_back} rows written, "
f"{rows_per_day*(days_back-keep)} ({100*(days_back-keep)/days_back:.0f}%) removed by the next retention run")
A cardinality incident: cardinality.py
Python standard library only; takes about six seconds.
"""A deploy adds a customer_id label. Model of what happens to a server with a series limit.
Assumptions are stated in the output; this is arithmetic and a seeded simulation, not a measurement of Prometheus."""
import random
random.seed(3)
BYTES_PER_SERIES = 3 * 1024 # the guide's working figure
RAM_FOR_SERIES = 8 * 1024**3 # 8 GiB reserved for series on the server
LIMIT = RAM_FOR_SERIES // BYTES_PER_SERIES
BASE = 150_000 # existing series (20 services x 5 statuses x 50 endpoints x 30 instances)
CUSTOMERS = 400_000 # distinct customers seen over a day
STATUS, ENDPOINTS_PER_METRIC, INSTANCES = 5, 50, 30
REQ_PER_MIN = 60_000
# each request picks a customer from a Zipf-like popularity; the new series key is (customer, status, endpoint) per instance
def zipf_pick():
return int(CUSTOMERS ** random.random()) # heavy tail: a few busy customers, a long tail
seen = set(); series = BASE; t_hit = None; per_min = []
for minute in range(1, 61):
for _ in range(REQ_PER_MIN):
key = (zipf_pick(), random.randrange(STATUS), random.randrange(ENDPOINTS_PER_METRIC), random.randrange(INSTANCES))
if key not in seen: seen.add(key)
new = len(seen)
series = BASE + new
per_min.append(series)
if t_hit is None and series * BYTES_PER_SERIES >= RAM_FOR_SERIES: t_hit = minute
print(f"series limit for {RAM_FOR_SERIES//1024**3} GiB at {BYTES_PER_SERIES//1024} KiB/series: {LIMIT:,}")
for m in (1, 5, 10, 20, 40, 60):
s = per_min[m-1]; print(f"minute {m:>2}: {s:>10,} series, {s*BYTES_PER_SERIES/1024**3:5.1f} GiB, {100*s/LIMIT:5.0f}% of the limit")
print("limit first reached at minute:", t_hit)
# What each policy does once the limit is reached (counts of samples/s affected, simple accounting)
total_samples_per_s = BASE / 10 # one sample per series every 10 s
print(f"\nhealthy ingest: {total_samples_per_s:,.0f} samples/s across {BASE:,} series")
print("A no limit: process is OOM-killed -> ALL series stop; restart replays the write-ahead log (minutes); dashboards and alerts are blind")
print("B per-metric limit: new series of http_requests rejected; the", f"{BASE-1:,}", "other series keep working; an alert fires on 'series rejected'")
print("C drop label at ingest (relabel customer_id away): series count stays", f"{BASE:,}", "; the per-customer question moves to logs/traces")
Sources
- Tuomas Pelkonen et al., Gorilla: A Fast, Scalable, In-Memory Time Series Database, Proceedings of the VLDB Endowment 8(12), 2015 (delta-of-delta timestamps and XOR-compressed values).
- Fabian Reinartz, Writing a Time Series Database from Scratch (2017), and the Prometheus documentation: storage, data model, histograms and summaries and instrumentation practices (label cardinality).
- PostgreSQL documentation: Populating a database (COPY, batching), COPY, Table partitioning, Routine vacuuming and Asynchronous commit.
- Timescale, TimescaleDB documentation (hypertables, compression, continuous aggregates); ClickHouse, documentation on MergeTree tables; InfluxData, InfluxDB documentation.
- Martin Kleppmann, Designing Data-Intensive Applications (O'Reilly, 2017), chapter 3 (log-structured storage, column storage) and chapter 11 (stream processing, handling time).
- Gil Tene, How NOT to Measure Latency (talk) on percentiles and coordinated omission; the OpenTelemetry metrics data model.
- Betsy Beyer et al., Site Reliability Engineering (O'Reilly, 2016), chapter 6 "Monitoring Distributed Systems".
Practise this topic: Databases