Tiered Retention with Continuous Aggregate Downsampling

Storing five years of raw IoT telemetry is ruinously expensive, but throwing that history away destroys the long-range trends analysts actually query — the fix is to roll raw readings up into progressively coarser aggregates and give each resolution its own retention horizon, so the raw chunks can be dropped early while hourly and daily rollups survive for years at a fraction of the cost. This page turns that trade-off into arithmetic: profile the query horizon at each resolution, size the storage per tier, and prove the raw retention window is wide enough that every aggregate materializes its buckets before the underlying chunks are dropped.

# Input Profiling: What to Measure per Tier

Tiered retention only works if every query horizon is served by some surviving tier. Before choosing drop boundaries, profile how far back each resolution is actually read and what it costs to keep. Gather the following for the fleet you are sizing:

• Query horizon per resolution — how far back dashboards and reports read raw, hourly, and daily data. A typical split: raw for live troubleshooting (days), hourly for capacity and SLA reporting (months), daily for year-over-year trends (years).
• Storage budget per tier — the uncompressed bytes you are willing to hold at each resolution, which caps the drop boundary you can afford.
• Aggregate refresh lag — the gap between a raw row arriving and the continuous aggregate materializing its bucket, governed by the refresh policy scheduling you attach to each view.
• Series cardinality — the number of distinct (device, metric) groups, because aggregate storage scales with cardinality, not with raw row rate.

The single most important relationship to capture is between the raw retention window and each aggregate’s refresh reach. A downsampling tier reads raw chunks every time it refreshes; if retention drops those chunks before the refresh runs, the aggregate materializes empty buckets and the history is gone for good. That constraint is the safety rule the calculation below enforces.

# Calculation: Storage per Tier and the Retention Safety Rule

Storage per tier. Raw storage is the ingest rate multiplied by row width and the retention window:

$$S_{raw} = R_{rows/s} \times 86400 \times b_{raw} \times D_{raw}$$

An aggregate tier stores one row per series per bucket, so its footprint depends on cardinality $G$ and bucket width $w$ (in seconds), not on the raw rate:

$$S_{agg} = \frac{G \times 86400}{w} \times b_{agg} \times D_{tier}$$

where $b_{agg}$ is the bytes per aggregate row (bucket timestamp, grouping keys, and the stored measures such as sum, count, min, max) and $D_{tier}$ is that tier’s retention in days. Because $w$ grows by orders of magnitude from raw to hourly to daily, each coarser tier costs dramatically less per day of history retained.

The safety rule. For every aggregate built directly on the raw hypertable, the raw retention window must strictly exceed that aggregate’s refresh start_offset, plus a margin for scheduler lag:

$$D_{raw} > t_{start\_offset} + t_{lag\_margin}$$

The start_offset is how far back each refresh reaches. If drop_after on the raw table is smaller, the refresh window overlaps chunks that retention has already dropped, and those buckets never materialize. Keep the margin generous — at least several times the refresh schedule_interval — so a paused or backed-up scheduler cannot silently breach the boundary.

Ordering. Retention and refresh run as independent background jobs, so you cannot rely on execution order — you rely on the window separation above. Attach the retention policy to the raw table with a drop_after far larger than the refresh reach, and let the refresh policy run often enough that materialization always finishes long before any chunk ages into the drop window.

Attach a retention policy to the raw hypertable and to each aggregate view, each with its own drop_after:

-- Tier 0: raw telemetry, kept 30 days.
SELECT add_retention_policy('sensor_readings',
    drop_after => INTERVAL '30 days', if_not_exists => TRUE);

-- Tier 1: hourly rollup downsampled from raw.
CREATE MATERIALIZED VIEW sensor_readings_1h
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       device_id, metric_type,
       sum(value) AS sum_value,   -- keep sum + count so avg re-aggregates correctly
       count(*)   AS n,
       min(value) AS min_value,
       max(value) AS max_value
FROM sensor_readings
GROUP BY 1, 2, 3
WITH NO DATA;

-- Refresh reach (3h) is far smaller than the raw drop_after (30d): safe.
SELECT add_continuous_aggregate_policy('sensor_readings_1h',
    start_offset      => INTERVAL '3 hours',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes');

-- Retention on the aggregate itself drops the materialization hypertable's chunks.
SELECT add_retention_policy('sensor_readings_1h',
    drop_after => INTERVAL '1 year', if_not_exists => TRUE);

The daily tier is downsampled from the hourly rollup rather than from raw — a continuous aggregate defined over another continuous aggregate. Its safety rule points one level up: the daily view’s start_offset must stay inside the hourly tier’s one-year window. This cascade is covered in depth in hierarchical hourly, daily, and weekly rollups.

-- Tier 2: daily rollup downsampled from the hourly tier (aggregate on aggregate).
CREATE MATERIALIZED VIEW sensor_readings_1d
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', bucket) AS bucket,
       device_id, metric_type,
       sum(sum_value) AS sum_value,   -- re-aggregate sums and counts, not averages
       sum(n)         AS n,
       min(min_value) AS min_value,
       max(max_value) AS max_value
FROM sensor_readings_1h
GROUP BY 1, 2, 3
WITH NO DATA;

SELECT add_continuous_aggregate_policy('sensor_readings_1d',
    start_offset      => INTERVAL '3 days',
    end_offset        => INTERVAL '1 day',
    schedule_interval => INTERVAL '6 hours');

SELECT add_retention_policy('sensor_readings_1d',
    drop_after => INTERVAL '5 years', if_not_exists => TRUE);

# Worked Example

Take the same fleet used elsewhere on this site: 50,000 devices, each emitting one reading every 20 seconds, so the raw hypertable ingests 2,500 rows/sec at 180 bytes per uncompressed row. Each device reports 4 metric types, giving a series cardinality of $G = 200{,}000$. Aggregate rows carry a bucket timestamp, two grouping keys, and four measures, averaging 80 bytes.

Raw tier, 30-day retention:

$$S_{raw} = 2500 \times 86400 \times 180 \times 30 \approx 1.17 \times 10^{12} \text{ bytes} \approx 1.17 \text{ TB}$$

Hourly tier, 1-year retention ($w = 3600$):

$$S_{1h} = \frac{200000 \times 86400}{3600} \times 80 \times 365 \approx 1.40 \times 10^{11} \text{ bytes} \approx 140 \text{ GB}$$

Daily tier, 5-year retention ($w = 86400$):

$$S_{1d} = \frac{200000 \times 86400}{86400} \times 80 \times 1825 \approx 2.92 \times 10^{10} \text{ bytes} \approx 29 \text{ GB}$$

The whole five-year archive costs about 170 GB across the two rollups. Holding five years of raw data instead would run to roughly $38.9 \text{ GB/day} \times 1825 \approx 71 \text{ TB}$ — over 400× more. The safety check is trivially satisfied: the raw drop_after of 30 days dwarfs the hourly start_offset of 3 hours, and the hourly drop_after of 1 year dwarfs the daily start_offset of 3 days. Compressing the raw and hourly chunks with a chunk compression schedule before they are dropped shrinks the live footprint further, typically by 8–15× on high-frequency telemetry.

# Edge Cases & When to Deviate

• Dropping raw before it materializes. The failure mode this whole design guards against: if drop_after on raw is smaller than the aggregate’s start_offset (or the scheduler is paused long enough to breach the margin), the next refresh reads chunks that no longer exist and writes empty buckets. There is no recovery — the raw source is gone. Always keep raw retention several times larger than the widest start_offset of any aggregate built on it.
• Retention on real-time aggregates. With materialized_only = false, a query unions the materialized rollup with a live scan of raw for the region newer than the materialization watermark. That live scan only ever touches recent data, never the 30-day drop boundary, so real-time mode is safe for downsampling — but keep the tiers materialized_only = true if you want reads over old ranges to be provably independent of raw chunks. The trade-offs are laid out in the parent guide on refresh policy design and scheduling.
• Cascade to a weekly tier. Extending to a weekly rollup means adding a fourth aggregate on top of the daily one, and the safety rule chains again: the weekly start_offset must stay inside the daily tier’s five-year window. Re-aggregate sum and count at every level and derive the average as sum_value / n in a plain view — averaging an average silently corrupts the result when bucket populations differ.
• Uneven bucket populations. If devices report at different rates, a straight avg(avg) weights every group equally regardless of sample count. Carrying sum and count through all tiers, as the DDL above does, keeps the arithmetic exact no matter how you re-roll.

# Verification

Confirm each tier’s oldest surviving data lines up with its horizon, and that a retention policy is actually attached at every level:

-- Oldest retained timestamp per tier should track its drop_after window.
SELECT 'raw' AS tier, min(time)   AS oldest FROM sensor_readings
UNION ALL
SELECT '1h',          min(bucket)         FROM sensor_readings_1h
UNION ALL
SELECT '1d',          min(bucket)         FROM sensor_readings_1d;

-- Every tier must have its own retention job with the expected drop_after.
SELECT j.hypertable_name,
       (j.config ->> 'drop_after') AS drop_after,
       js.last_run_status,
       js.last_successful_finish
FROM timescaledb_information.jobs j
LEFT JOIN timescaledb_information.job_stats js USING (job_id)
WHERE j.proc_name = 'policy_retention'
ORDER BY j.hypertable_name;

The first query’s oldest values should sit just inside 30 days, 1 year, and 5 years respectively; a raw oldest far younger than 30 days means retention is dropping chunks early, while an hourly oldest older than 1 year means its retention job is stalled. Cross-check the refresh side too — if the hourly aggregate’s watermark lags, tighten its schedule_interval before the gap approaches the raw drop boundary:

-- Materialization watermark per aggregate: how fresh is each tier?
SELECT view_name,
       to_timestamp(
         _timescaledb_internal.to_seconds(
           _timescaledb_internal.cagg_watermark(mat_hypertable_id))
       ) AS materialized_through
FROM timescaledb_information.continuous_aggregates;

As long as each tier’s watermark stays comfortably ahead of the drop boundary of the tier feeding it, the downsampling pipeline is losing no history — and the five-year archive keeps costing gigabytes instead of terabytes.

# Related

• TTL Policy Mapping & Enforcement — the parent guide on mapping lifecycle windows to drop policies
• Hierarchical Continuous Aggregates: Hourly, Daily, Weekly Rollups — building the aggregate-on-aggregate cascade this design drops from
• Chunk Compression Scheduling & Automation — compressing each tier before its retention window closes
• Refresh Policy Design & Scheduling — sizing the start_offset and schedule_interval the safety rule depends on
• Data Retention, Compression & Lifecycle Automation — the full lifecycle guide this page sits under

← Back to TTL Policy Mapping & Enforcement