Downsampling¶

Storage review, Thursday. Finance flags the observability budget: the raw-readings disk has grown to 160 TB and is still climbing. Someone points out the dashboards only ever render a 1,200-pixel-wide chart — nobody looks at more than 1,200 points at once, no matter how many billions of rows sit behind it.

A. Buy more/cheaper disk. B. Shorten raw retention with nothing to replace it. C. Build tiered rollups (1 min / 1 hour / 1 day) and drop raw after days, not years. D. Compress harder at the same resolution.

Predict before you read on.

It's (C). 10 million devices × 1 sample / 30 s ≈ 3.3×10⁵ points/s ≈ 2.9×10¹⁰ / day ≈ 10¹³ / year, and at 16 bytes/point (time + id hash + value) that's already ~160 TB/year before indexes and replicas. You will aggregate. The only question is whether you aggregate once, on write, or every time someone opens Grafana.

Start with the situation¶

IoT {timestamp, device_id, sensor, value} plus observability events at hundreds of millions/day.

Queries:

Retention money: keep raw hot for 7 days, not 7 years.

Why the obvious approach breaks¶

Downsampling destroys information. Average of a minute hides a 5 s 100% CPU. p95 of a day cannot be recovered from 24 hourly p95s by averaging them. Late event time means a rollup you already closed is wrong unless you recompute.

If you downsample in the UI (Grafana “min interval”), 10 M devices still send 10 M series to the UI path. Pre-aggregation must happen per series / per device in the store.

Build the mental picture¶

A pyramid. Each layer is tumbling windows (windows) with fewer rows.

flowchart TB
    R["Raw 30s  — 7d hot"] --> M["1 min  — 30d"]
    M --> H["1 hour — 1y"]
    H --> D["1 day  — 5y"]

The query router picks the coarsest layer that still fills the pixels (and the statistics the tile needs).

Storage is dominated by the finest layer you keep × retention. Dropping raw after 7 days is the only lever that actually bends the year-cost curve.

Under the hood¶

Storage math (order of magnitude)¶

10 M devices, 1 sensor, 30 s raw:

Keeping raw 7 d + 1 min 30 d + 1 h 1 y + 1 d 5 y is tens of TB, not 160 TB × 5 years. Replicas and indexes still multiply — but the pyramid is why TSDBs quote “10× compression” and still need TTL.

Which statistics to keep¶

Merge law: avg of avgs is wrong unless weighted by count. Always store sum+count or avgState.

Continuous aggregates vs batch vs stream¶

Grafana “recording” in the browser is not a layer.

Query routing¶

-- pseudo: pick table by span
-- span < 6h  -> readings_raw
-- span < 40d -> readings_1m
-- else       -> readings_1h

Do this in the API or Grafana using different metrics/tables, not UNION ALL raw with 1h (the optimizer will not save you if you still touch raw).

TTL vs downsampling¶

TTL deletes. Downsampling copies a summary then deletes. TTL without a layer behind it is amnesia. A layer without TTL is a second full-price copy.

Put it to work¶

Timescale continuous aggregate:

CREATE MATERIALIZED VIEW readings_1h
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', ts) AS hour,
    device_id,
    sensor,
    avg(value) AS avg_v,
    max(value) AS max_v,
    min(value) AS min_v,
    count(*)   AS n,
    sum(value) AS sum_v
FROM readings
GROUP BY hour, device_id, sensor;

SELECT add_continuous_aggregate_policy('readings_1h',
    start_offset => INTERVAL '3 hours',
    end_offset   => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes');

SELECT add_retention_policy('readings', INTERVAL '7 days');
SELECT add_compression_policy('readings', INTERVAL '2 days');

end_offset 1 hour: do not close the bucket until event time has some slack for late IoT. If devices buffer 40 minutes, 1 hour is still tight — set from measured lag (time semantics).

ClickHouse rollup + TTL:

CREATE TABLE readings_raw (...)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(ts)
ORDER BY (device_id, sensor, ts)
TTL ts + INTERVAL 7 DAY;

CREATE TABLE readings_1m
(
    m DateTime,
    device_id String,
    sensor LowCardinality(String),
    avg_s AggregateFunction(avg, Float64),
    max_s AggregateFunction(max, Float64),
    min_s AggregateFunction(min, Float64),
    n_s   AggregateFunction(count)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(m)
ORDER BY (device_id, sensor, m)
TTL m + INTERVAL 90 DAY;

CREATE MATERIALIZED VIEW readings_1m_mv TO readings_1m AS
SELECT
    toStartOfMinute(ts) AS m,
    device_id,
    sensor,
    avgState(value) AS avg_s,
    maxState(value) AS max_s,
    minState(value) AS min_s,
    countState()    AS n_s
FROM readings_raw
GROUP BY m, device_id, sensor;

-- query
SELECT m, avgMerge(avg_s), maxMerge(max_s), countMerge(n_s)
FROM readings_1m
WHERE device_id = 'd-9' AND m >= now() - INTERVAL 14 DAY
GROUP BY m
ORDER BY m;

Prometheus: 15 d local, remote_write to VM/Thanos; recording rules for 5m rates; not 10 M device labels (cardinality).

Graphite-style CH: GraphiteMergeTree exists for drop policies; most teams still prefer explicit MVs they can query with SQL.

Where teams get caught¶

How it fails¶

What happened next¶

It was C. Nobody renders more than 1,200 points, so storing a year of raw 30-second samples means keeping 10¹³ points to draw charts that discard essentially all of them.

Tiered rollups — 1 minute, 1 hour, 1 day — with raw kept for days rather than years cut the 160 TB by orders of magnitude and made the dashboards faster, because a year-long chart now reads daily rollups instead of aggregating billions of raw points on every page load.

The aggregation was never optional. The only choice was whether to do it once on write or every time someone opens Grafana, and the raw-retention number is where that choice is actually made: it is the window in which an incident can still be investigated at full resolution.

Check your understanding¶

10 M devices, 30 s temperature, 3× replication, 24 bytes/point on disk after compression (all-in). Budget: 40 TB usable for this pipeline.

Propose raw / 1 min / 1 h retentions that fit, which stats live in 1 min, and what the 1-year exec chart reads. Show the arithmetic.

Reference¶

Behaviour at the next orders of magnitude, the trade-offs, the alternatives, and what to check when inheriting someone else's version of this — kept here rather than in the walkthrough above.

How to investigate¶

Storage:

-- ClickHouse
SELECT
    table,
    formatReadableSize(sum(bytes_on_disk)) AS disk,
    sum(rows) AS rows
FROM system.parts
WHERE active AND table LIKE 'readings%'
GROUP BY table;

SELECT partition, min(min_date), max(max_date), count()
FROM system.parts
WHERE table = 'readings_raw' AND active
GROUP BY partition
ORDER BY partition;

If readings_raw has partitions older than TTL, TTL is mis-set (ttl_only_drop_parts, merge not running, column vs table TTL).

Query:

-- should be ~14d of minutes, not 14d of 30s
SELECT count() FROM readings_1m
WHERE device_id = 'd-9' AND m > now() - INTERVAL 14 DAY;

Timescale: timescaledb_information.jobs / job stats; chunk exclusion in EXPLAIN.

Prom: retention flag vs disk; remote_write lag.

Scale 10× / 100× / 1000׶

The pyramid dimensions can coarsen too: not only time, but identity (device → customer → fleet). That is downsampling of cardinality, not only of time.

Trade-offs¶

Alternatives¶

Apply¶

Write the table:

tile → max span → layer → stats → retention.

Compute TB. If finance would not sign it, you do not have a retention policy; you have a hope.

At work the smell is a 14-day dashboard that scans 30 s raw for 10 M series, or a 13-month disk that is 99% raw.