Continuous Aggregates and Downsampling Retention Policies

Raw telemetry at one sample per second per tag turns into billions of rows within months on a mid-size plant, and almost none of those rows are ever queried individually once a shift is closed out — dashboards and OEE reports want minute- or hour-level rollups, not raw ticks. This page is the storage-tiering companion to time-series database sync: how to define TimescaleDB continuous aggregates that pre-compute rollups incrementally, keep them fresh with a refresh policy, serve near-real-time queries without waiting for the next refresh, and retire raw rows on a budget that matches what the retention actually needs to answer.

Continuous aggregate definition #Permalink to this section

A TimescaleDB continuous aggregate is a materialized view over a hypertable that Timescale refreshes incrementally — only the time buckets touched by new or changed data are recomputed, not the whole view — using time_bucket() to define the rollup grain. For OEE, the natural first rollup is state-duration and value-summary per tag per minute, because most downstream reporting windows (shift, hour, day) compose cleanly from a one-minute grain.

-- Base hypertable (see time-series-database-sync for the full write contract)
CREATE TABLE telemetry (
    canonical_id text        NOT NULL,
    ts           timestamptz NOT NULL,
    value        double precision,
    quality      text        NOT NULL DEFAULT 'GOOD'
);
SELECT create_hypertable('telemetry', by_range('ts', INTERVAL '1 day'));

-- One-minute continuous aggregate: avg/min/max plus a count for coverage checks
CREATE MATERIALIZED VIEW telemetry_1m
WITH (timescaledb.continuous) AS
SELECT
    canonical_id,
    time_bucket('1 minute', ts) AS bucket,
    avg(value)                  AS avg_value,
    min(value)                  AS min_value,
    max(value)                  AS max_value,
    count(*) FILTER (WHERE quality = 'GOOD') AS good_samples,
    count(*)                    AS total_samples
FROM telemetry
GROUP BY canonical_id, bucket
WITH NO DATA;  -- populate via the refresh policy, not an initial full scan

The good_samples/total_samples pair is deliberate: a rollup that silently averages over BAD-quality readings poisons every OEE calculation built on top of it, so carrying sample-quality coverage into the aggregate itself lets OEE formula validation reject a bucket with insufficient good-quality coverage rather than trusting a number computed on garbage.

A second, coarser aggregate can be built on top of the first-level aggregate rather than the raw hypertable, which keeps the incremental refresh cost bounded as more rollup levels are added:

CREATE MATERIALIZED VIEW telemetry_1h
WITH (timescaledb.continuous) AS
SELECT
    canonical_id,
    time_bucket('1 hour', bucket) AS bucket,
    avg(avg_value)                AS avg_value,
    min(min_value)                AS min_value,
    max(max_value)                AS max_value,
    sum(good_samples)             AS good_samples,
    sum(total_samples)            AS total_samples
FROM telemetry_1m
GROUP BY canonical_id, time_bucket('1 hour', bucket)
WITH NO DATA;

Refresh and real-time aggregation policies #Permalink to this section

A continuous aggregate is only as useful as its freshness. Timescale’s add_continuous_aggregate_policy schedules incremental refreshes on a rolling window, and the start_offset/end_offset/schedule_interval triplet controls the trade-off between refresh cost and staleness: too tight a window and every refresh reprocesses buckets that have not changed; too loose and dashboards lag behind the floor.

-- Refresh the trailing 3 hours of the 1-minute cagg every 5 minutes, but never
-- touch the most recent 1 minute (still filling in from live ingest).
SELECT add_continuous_aggregate_policy('telemetry_1m',
    start_offset      => INTERVAL '3 hours',
    end_offset        => INTERVAL '1 minute',
    schedule_interval  => INTERVAL '5 minutes');

-- Hourly rollup refreshes less often; it is downstream of the 1-minute cagg.
SELECT add_continuous_aggregate_policy('telemetry_1h',
    start_offset      => INTERVAL '2 days',
    end_offset        => INTERVAL '1 hour',
    schedule_interval  => INTERVAL '30 minutes');

The end_offset gap exists because the current, still-filling bucket is incomplete by definition; refreshing it repeatedly wastes work and can show a partial average as if it were final. For queries that genuinely need the last few seconds — a live shift dashboard — Timescale’s real-time aggregation feature (enabled by default) transparently unions the materialized, refreshed rows with a live query over the raw hypertable for the still-open window, so a query against telemetry_1m returns a correct answer for the current minute even before the next scheduled refresh has run:

-- Real-time aggregation stitches materialized history with the live tail
SELECT bucket, avg_value, good_samples, total_samples
FROM telemetry_1m
WHERE canonical_id = 'LINE_03.CNC_07.SPINDLE_SPEED'
  AND bucket >= now() - INTERVAL '15 minutes'
ORDER BY bucket;

Disabling real-time aggregation (timescaledb.materialized_only = true) trades that always-fresh guarantee for lower query cost on high-traffic dashboards that can tolerate a few minutes of lag — a reasonable trade for a plant-wide summary board but the wrong default for a line-side operator display feeding shift-boundary logic decisions in real time.

Tiered retention and compression budgets #Permalink to this section

Retention should be set per tier, not globally: raw resolution has a short, expensive shelf life; coarse rollups are cheap enough to keep for years. Pair each tier’s retention policy with a compression policy on the raw hypertable so data is compressed well before it is dropped, not deleted while still uncompressed and expensive.

-- Compress raw chunks older than 2 days (still within the 7-day raw retention)
ALTER TABLE telemetry SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'canonical_id',
    timescaledb.compress_orderby   = 'ts DESC'
);
SELECT add_compression_policy('telemetry', INTERVAL '2 days');

-- Retention: raw kept 7 days, 1-minute rollup 90 days, 1-hour rollup 5 years
SELECT add_retention_policy('telemetry',   INTERVAL '7 days');
SELECT add_retention_policy('telemetry_1m', INTERVAL '90 days');
SELECT add_retention_policy('telemetry_1h', INTERVAL '5 years');

Sizing each window against what the business actually queries keeps the budget honest: raw resolution is needed for root-cause investigation of a specific shift, which is rarely requested more than a week or two after the fact; minute-level rollups serve trend dashboards and the reconciliation work in reconciling OEE with MES production counts for a quarter; hour-level rollups are what a five-year capacity-planning or year-over-year OEE trend report needs, and at that grain the row count per tag is small enough that five years costs less storage than a single week of raw telemetry. This is the same engine comparison made in TimescaleDB vs InfluxDB vs QuestDB for IIoT telemetry — every engine needs this same tiering discipline, only the SQL syntax to express it changes.

Watermarking matters for correctness as much as retention matters for cost. Timescale tracks an internal invalidation threshold per continuous aggregate so that late-arriving data — a buffered payload replayed after an edge outage, or a batch that missed the live window entirely — still triggers a re-refresh of the buckets it lands in, as long as it arrives inside the policy’s start_offset window. Data that arrives older than that window silently fails to update the materialized rollup even though the raw hypertable itself accepts it, which is the single most common cause of a rollup that looks final but quietly disagrees with the raw data underneath it. Widen start_offset for lines with a known worst-case replay delay, and alert on the gap between now() and the aggregate’s actual invalidation watermark (timescaledb_information.continuous_aggregates exposes the refresh state) so a stuck refresh job is caught before a quarter of stale minute-buckets reaches a report.

Gotchas & anti-patterns #Permalink to this section

• Refreshing the still-open bucket. Omitting end_offset (or setting it to zero) causes the policy to repeatedly materialize an incomplete final bucket, which then looks “final” to any consumer that does not know to distrust the newest row.
• Dropping raw data before it is compressed. A retention policy that fires before the compression policy has run on the same chunks deletes uncompressed rows the compression step was about to shrink — order the policies so compression always runs well inside the raw retention window.
• Building every rollup from raw instead of chaining. Computing the 1-hour aggregate directly from raw telemetry instead of from the 1-minute aggregate multiplies refresh cost with every additional rollup level; chain aggregates so each level reads the one below it.
• No quality-coverage column in the aggregate. An avg() with no visibility into how many samples were BAD or missing silently produces a plausible-looking number from an incomplete bucket; carry good_samples/total_samples (or an explicit coverage ratio) into every rollup.
• Ignoring real-time aggregation cost on high-cardinality dashboards. Real-time aggregation is correct by default but re-scans the live tail on every query; disable it (materialized_only = true) for wide fan-out dashboards that can tolerate a short refresh lag.

Quick reference #Permalink to this section

Related #Permalink to this section

• Time-Series Database Sync — the write contract these aggregates are built on top of
• TimescaleDB vs InfluxDB vs QuestDB for IIoT Telemetry — the engine-selection decision this tiering strategy assumes
• OEE Formula Validation — the consumer of rollup quality-coverage data
• Reconciling OEE with MES Production Counts — a concrete downstream use of the minute-level rollup tier