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.

Predict before you read on: (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, or (D) compress harder at the same resolution?

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¶

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.

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.