Hierarchical Continuous Aggregates: Hourly, Daily, Weekly Rollups

Stacking a daily rollup directly on top of an hourly one — and a weekly on top of that — lets each coarser summary re-aggregate a few thousand pre-computed rows instead of rescanning billions of raw readings, but only if every tier refreshes in child-before-parent order. This page shows how to build a cascading 1-hour → 1-day → 1-week aggregate stack where each level is a continuous aggregate defined ON the level beneath it, size the refresh policy for each tier so lower tiers materialize first, and verify that watermarks advance and row counts reconcile end to end.

# Input Profiling: Resolutions, Ratios, and Cadence

Before writing any DDL, decide what each tier is for and what feeds it. A hierarchical stack is only worth building when the read paths genuinely need multiple resolutions — a real-time operational dashboard reading the hourly tier, a daily report reading the daily tier, a quarter-over-quarter trend reading the weekly tier. If every query lands on one resolution, a single continuous aggregate on top of the raw hypertable, following the standard materialized view architecture and syntax, is simpler and cheaper.

Profile three properties per tier:

• Query resolution — the coarsest bucket a given consumer ever reads at. This fixes the tier’s time_bucket width.
• Bucket ratio to the parent below — how many child buckets fit in one parent bucket. This is the per-level row-reduction factor and must be an integer for time_bucket alignment to stay exact.
• Refresh cadence and lag — how often each tier’s policy runs and how far behind real time its end_offset holds. A parent’s cadence should be no faster than its child’s, and its end_offset should lag at least one child bucket behind the child’s own end_offset.

For a per-second IoT fleet, a typical profile looks like this:

The end_offset column is the alignment lever: the daily tier holds its leading edge one full day behind now, which guarantees the 24 hourly buckets it needs are already materialized by the time it runs.

# Calculating Row Reduction Across Tiers

Each tier shrinks its input by the bucket ratio between it and the tier below. If $N_0$ is the raw row count for one series over a fixed window and $r_i$ is the integer bucket ratio at level $i$, the materialized row count at tier $k$ is:

$$N_k = \frac{N_0}{\displaystyle\prod_{i=1}^{k} r_i}$$

The denominator is cumulative, so the compounding is what makes the stack pay off: the weekly tier re-aggregates $N_2$ daily rows rather than $N_0$ raw rows. Crucially, tier $k$’s refresh only ever scans $N_{k-1}$ rows — the count materialized by the tier directly below it — never the raw table. The work done at each level is proportional to the child’s size, not the raw size:

$$W_k \propto N_{k-1} = \frac{N_0}{\displaystyle\prod_{i=1}^{k-1} r_i}$$

For the per-second profile above, the cumulative reduction from raw to weekly is $3600 \times 24 \times 7 = 604{,}800$, exactly the number of seconds in a week — so one weekly bucket per series condenses 604,800 raw readings, and the weekly refresh touches only the 7 daily rows that feed it.

To keep averages correct across levels, never take an average of averages. Carry a running count and sum at every tier and divide at query time; min, max, and additive count/sum all roll up losslessly, whereas avg does not unless every child bucket carries identical weight.

# Building the Cascade

Cascading continuous aggregates — a continuous aggregate defined on top of another — require TimescaleDB 2.9 or later. Confirm the version first, then create each tier in order, because a parent cannot be created before the child it reads exists.

-- Version gate: cagg-on-cagg is unavailable before 2.9.
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';

-- Raw hypertable (per-second telemetry).
CREATE TABLE sensor_readings (
    time      TIMESTAMPTZ NOT NULL,
    device_id UUID        NOT NULL,
    value     DOUBLE PRECISION
);
SELECT create_hypertable('sensor_readings', 'time',
                         chunk_time_interval => INTERVAL '1 day');

Tier 1 buckets the raw hypertable by the hour. Because this tier feeds another aggregate, set materialized_only = true: a parent reads its child’s materialized hypertable, so real-time rows on the child would never reach it anyway, and disabling the real-time union keeps the intermediate view’s semantics unambiguous.

-- Tier 1: hourly, built directly on the raw hypertable.
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous, timescaledb.materialized_only = true) AS
SELECT
    device_id,
    time_bucket('1 hour', time) AS bucket,
    count(*)   AS n,          -- carry count + sum so avg rolls up exactly
    sum(value) AS sum_value,
    min(value) AS min_value,
    max(value) AS max_value
FROM sensor_readings
GROUP BY device_id, time_bucket('1 hour', time)
WITH NO DATA;

Tier 2 buckets the hourly cagg’s bucket column — not the raw time — by the day, summing the carried n and sum_value. This is the hierarchical step: the FROM clause references sensor_hourly, another continuous aggregate.

-- Tier 2: daily, built on the hourly cagg.
CREATE MATERIALIZED VIEW sensor_daily
WITH (timescaledb.continuous, timescaledb.materialized_only = true) AS
SELECT
    device_id,
    time_bucket('1 day', bucket) AS bucket,
    sum(n)         AS n,
    sum(sum_value) AS sum_value,
    min(min_value) AS min_value,
    max(max_value) AS max_value
FROM sensor_hourly
GROUP BY device_id, time_bucket('1 day', bucket)
WITH NO DATA;

Tier 3 buckets the daily cagg by the week. This is the tier consumers query directly for long-range trends, so it can safely leave materialized_only at its default and serve real-time results:

-- Tier 3: weekly, built on the daily cagg (top of the stack).
CREATE MATERIALIZED VIEW sensor_weekly
WITH (timescaledb.continuous) AS
SELECT
    device_id,
    time_bucket('1 week', bucket) AS bucket,
    sum(n)         AS n,
    sum(sum_value) AS sum_value,
    min(min_value) AS min_value,
    max(max_value) AS max_value
FROM sensor_daily
GROUP BY device_id, time_bucket('1 week', bucket)
WITH NO DATA;

Now attach one refresh policy per tier, ordered so children materialize ahead of parents. The end_offset of each parent lags one full bucket behind its child, and the schedule cadence coarsens as you climb — the pattern generalizes the same policy mechanics covered when setting up automatic refresh policies for 5-minute intervals:

-- Tier 1 refreshes most often and leads the others.
SELECT add_continuous_aggregate_policy('sensor_hourly',
    start_offset      => INTERVAL '3 hours',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes');

-- Tier 2 lags one day behind now, so its 24 hourly buckets are already materialized.
SELECT add_continuous_aggregate_policy('sensor_daily',
    start_offset      => INTERVAL '3 days',
    end_offset        => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 hour');

-- Tier 3 lags one week; it reads only the 7 daily rows that feed each bucket.
SELECT add_continuous_aggregate_policy('sensor_weekly',
    start_offset      => INTERVAL '3 weeks',
    end_offset        => INTERVAL '1 week',
    schedule_interval => INTERVAL '6 hours');

The staggered end_offset values are what enforce child-before-parent correctness without any explicit job dependency: because the daily tier never refreshes closer than one day to now, and the hourly tier keeps its watermark within an hour of now, the hourly data the daily tier reads is always present before the daily job runs.

# Worked Example: Per-Second Fleet Over One Week

Take 10,000 devices each emitting one reading per second. Over a full week the raw hypertable holds:

$$N_0 = 10{,}000 \times 604{,}800 = 6.048 \times 10^{9}\ \text{rows}$$

Applying the reduction formula tier by tier, with $r_1 = 3600$, $r_2 = 24$, $r_3 = 7$:

$$N_1 = \frac{6.048\times10^{9}}{3600} = 1{,}680{,}000,\quad N_2 = \frac{N_1}{24} = 70{,}000,\quad N_3 = \frac{N_2}{7} = 10{,}000$$

So the four tiers materialize 6.048 billion → 1.68 million → 70,000 → 10,000 rows. The weekly refresh scans 70,000 daily rows, not 6 billion raw rows — a 86,400× reduction in per-refresh work compared with rebuilding the weekly view straight from the raw table. Each tier’s n column carries the count forward, so sum(n) at any level equals the raw row count for the same window — the property the verification step below leans on. Query-time averages come from the carried sums:

-- Read the weekly tier: reconstruct avg from the carried sum and count.
SELECT device_id, bucket,
       sum_value / n AS avg_value,   -- exact, not an average of averages
       min_value, max_value, n
FROM sensor_weekly
WHERE bucket >= now() - INTERVAL '12 weeks'
ORDER BY device_id, bucket;

# Edge Cases & When to Deviate

• Refreshing a parent before its child. If a manual refresh_continuous_aggregate runs on sensor_daily for a window whose hourly buckets are not yet materialized, the daily tier records incomplete sums for those buckets and will not revisit them until that range is invalidated again. Always refresh bottom-up; when backfilling historical data manually, call refresh on the hourly tier first, then daily, then weekly, for the same range.
• Version below 2.9. Cascading continuous aggregates are unsupported before TimescaleDB 2.9 — the CREATE MATERIALIZED VIEW ... FROM <cagg> statement fails outright. On older versions, build each resolution independently on the raw hypertable and accept the redundant scans.
• Real-time aggregation on an intermediate tier. Leaving materialized_only = false on the hourly or daily tier does not make its parent fresher: a parent always reads its child’s materialized region up to the child’s watermark, never the real-time union. Enable real-time only on the top tier you query directly, and keep intermediate tiers materialized_only = true to avoid confusion.
• Gapfill is not allowed in the definition. time_bucket_gapfill() cannot appear in any continuous aggregate query — hierarchical or not — because it is a query-time windowing function. Use plain time_bucket at every tier and apply gap-filling in read queries against the finished weekly view.
• Non-integer bucket ratios. Rolling a 1-hour tier up to a 1-week tier directly (ratio 168) is fine, but mixing calendar-irregular widths — months on top of weeks — breaks exact alignment because weeks do not divide months evenly. Keep each ratio a clean integer, or route the irregular tier off the raw table instead.
• Retention interaction. Dropping raw chunks does not invalidate already-materialized aggregate rows, which is exactly what makes this stack the backbone of tiered retention with continuous aggregate downsampling — short raw retention, long rollup retention.

# Verification

Confirm two things after the policies have run: every tier’s watermark has advanced into recent time, and the carried counts reconcile across levels. First, read each tier’s watermark — the timestamp up to which it is materialized:

-- Watermark per tier. Function schema is _timescaledb_functions on 2.12+,
-- _timescaledb_internal on earlier 2.x releases.
SELECT
    ca.view_name,
    _timescaledb_functions.to_timestamp(
        _timescaledb_functions.cagg_watermark(h.id)
    ) AS materialized_through
FROM timescaledb_information.continuous_aggregates ca
JOIN _timescaledb_catalog.hypertable h
    ON h.table_name = ca.materialization_hypertable_name
WHERE ca.view_name IN ('sensor_hourly', 'sensor_daily', 'sensor_weekly')
ORDER BY materialized_through;

A healthy stack shows the hourly watermark closest to now, then daily, then weekly — the exact left-to-right ordering of the cascade. If a parent’s watermark sits ahead of its child’s, a policy is misordered or a manual refresh jumped a tier.

Second, reconcile the counts. Because every tier carries n, summing n over the same fully-materialized window must yield the raw row count at every level:

-- Pick a window that is fully materialized in all tiers (older than the
-- weekly end_offset) so no tier is missing its leading buckets.
WITH win AS (
    SELECT date_trunc('week', now() - INTERVAL '3 weeks') AS lo,
           date_trunc('week', now() - INTERVAL '2 weeks') AS hi
)
SELECT
    (SELECT count(*)  FROM sensor_readings, win
       WHERE time >= win.lo AND time < win.hi)                       AS raw_rows,
    (SELECT sum(n)::bigint FROM sensor_hourly, win
       WHERE bucket >= win.lo AND bucket < win.hi)                   AS hourly_n,
    (SELECT sum(n)::bigint FROM sensor_daily, win
       WHERE bucket >= win.lo AND bucket < win.hi)                   AS daily_n,
    (SELECT sum(n)::bigint FROM sensor_weekly, win
       WHERE bucket >= win.lo AND bucket < win.hi)                   AS weekly_n;

All four columns should be equal. A shortfall at the daily or weekly level means those tiers materialized before their children finished — refresh bottom-up for the affected range and re-run the check. If reconciliation holds and watermarks descend left to right, the cascade is sharing work correctly and every resolution is consistent with the raw data beneath it.

# Related

• Setting Up Automatic Refresh Policies for 5-Minute Intervals — the policy start_offset/end_offset mechanics each tier reuses
• Materialized View Architecture & Syntax — the base continuous aggregate model each tier builds on
• Creating Continuous Aggregates with time_bucket_gapfill — why gapfill belongs in read queries, not the definition
• Tiered Retention with Continuous Aggregate Downsampling — pairing short raw retention with long-lived rollups
• Up to Continuous Aggregate Creation & Refresh Management — the full lifecycle these rollups sit inside

← Back to Refresh Policy Design & Scheduling