Time-series downsampling and rollups: an interactive simulator of what averages hide

Monitoring systems collect enormous numbers of points: a million series sampled every second is 86 billion points a day. Nobody needs last year’s data to the second, so time-series databases roll up old data into coarser buckets, a value per minute or per hour, and keep each resolution for a different length of time. The catch is what each bucket keeps: an average quietly erases the short spikes that monitoring exists to catch.

The playground stores two hours of a latency metric with a 20-second incident. Change the resolution and what each bucket keeps, and watch the incident.

What averages hide

The incident pushed latency from about 130 ms to about 3 seconds for 20 seconds. In 1-minute averages its worst minute shows 1.1 seconds; in 5-minute averages, 320 ms; in 15-minute averages, 191 ms, which looks like a slightly busy quarter-hour. Keeping the maximum of each bucket alongside the average preserves the 3-second peak at every resolution, for one more number per bucket. Production rollups usually keep several: count, sum, min, max, and for latency a histogram.

Percentiles need special care. The 99th percentile of an hour is not the average of sixty per-minute 99th percentiles, and there is no way to compute it from them. Store a mergeable summary instead: fixed-bucket histograms, as Prometheus does, or sketches such as t-digest or DDSketch, which can be combined across time buckets and across hosts and still answer percentile queries within a known error.

Tiered retention

Storage scales with points. With Gorilla-style compression, about 1.37 bytes a point, a million series at 1-second resolution take 3.6 TB a month; at 1-minute resolution, 59 GB. A tiered policy keeps raw data for 2 days for debugging, 1-minute rollups for 30 days for dashboards, and hourly rollups for 2 years for capacity planning: about 320 GB in all, against 86 TB to keep two years at full resolution. Queries pick the finest resolution that covers their range, so a year-long graph reads a few thousand hourly points, not 31 million.

A rollup with retention in SQL

-- TimescaleDB: a 1-minute rollup kept up to date as raw data arrives
CREATE MATERIALIZED VIEW latency_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts) AS bucket, service,
       avg(latency_ms)  AS avg_ms,
       max(latency_ms)  AS max_ms,
       count(*)         AS n,
       percentile_agg(latency_ms) AS pct   -- a mergeable sketch, not a p99 number
FROM requests GROUP BY bucket, service;

-- keep raw data 2 days, 1-minute rollups 30 days
SELECT add_retention_policy('requests', INTERVAL '2 days');
SELECT add_retention_policy('latency_1m', INTERVAL '30 days');

In a system design interview

For a metrics system, describe ingestion, rollups and queries together: “Agents push samples every 10 seconds; ingesters write them into time-partitioned, compressed blocks. A background job rolls closed blocks up into 1-minute and 1-hour buckets with count, sum, min, max and a DDSketch per series. Raw data lives 7 days, minute data 90 days, hourly data 2 years, and the query layer picks the resolution from the time range. Alerts always run on raw data.” Expect the follow-ups: high-cardinality labels, late-arriving samples, and how many series one node can hold in memory.

Read the guide to time-series data or observability, see tail latency for why the spikes matter and burn rate alerts for alerting on them, or watch a strong candidate design a metrics system.

Questions people ask