Real-Time Aggregates vs Manual Refresh Tradeoffs

Deciding whether a continuous aggregate should stitch its materialized rollup to a live scan of the raw tail (materialized_only = false) or serve only pre-computed buckets between scheduled refreshes is a direct trade of query-time cost against data freshness. Real-time aggregates hide refresh lag by unioning the materialized region with a fresh scan of everything newer than the refresh watermark, while a materialized-only view answers from the rollup alone and is only ever as current as its last successful refresh. This page profiles the four inputs that decide the trade, quantifies both paths, and shows how to verify which plan you actually got. It builds on the choices introduced in the parent guide on incremental vs full refresh strategies.

# Input Profiling: What Decides the Trade

The choice is not aesthetic; it falls out of four measurable quantities. Capture each from a representative production window before you flip the flag either way:

• Query latency budget — the p95 response time the dashboard or API contract allows for the aggregate query. A 200 ms tile budget behaves very differently from a 5 s report budget.
• Freshness SLA — how stale an answer is allowed to be. “Within the last minute” forces a live tail; “yesterday’s totals” tolerates a daily refresh with no real-time union at all.
• Raw tail size per bucket — how many raw rows accumulate between the refresh watermark and now(). This is the volume the union must scan and aggregate on every query, and it is the single biggest driver of real-time cost.
• Refresh cadence — how often the refresh policy advances the watermark. A short cadence shrinks both the staleness gap and the raw tail; a long cadence widens both.

The interaction is what matters. Freshness SLA and refresh cadence together bound how stale a materialized-only view can be; raw tail size and query budget together bound how expensive a real-time union can get. When the cadence already satisfies the SLA, the union buys nothing but cost. When it cannot, the union is the mechanism that closes the gap at read time.

# Comparing the Two Paths

Both behaviours come from one flag set at creation time or altered later. With materialized_only = false, the view definition is expanded at plan time into a union of the materialized hypertable and a real-time time_bucket() aggregation over the raw hypertable beyond the watermark. With materialized_only = true, the planner reads only the materialized hypertable.

-- Real-time aggregate: rollup UNION live tail on every query.
CREATE MATERIALIZED VIEW dashboard_1h
WITH (timescaledb.continuous, timescaledb.materialized_only = false) AS
SELECT
    time_bucket('1 hour', ts) AS bucket,
    device_id,
    avg(value)  AS avg_value,
    max(value)  AS max_value,
    count(*)    AS sample_count
FROM sensor_readings
GROUP BY bucket, device_id
WITH NO DATA;

-- Flip to materialized-only later without recreating the view.
ALTER MATERIALIZED VIEW dashboard_1h
    SET (timescaledb.materialized_only = true);

The two paths trade along five dimensions:

A useful mental model is that the real-time query cost is the sum of two scans:

$$C_{query} \approx C_{materialized} + C_{tail}, \qquad C_{tail} \approx N_{tail} \times c_{row}$$

where $C_{materialized}$ is the cost of scanning the pre-computed buckets, $N_{tail}$ is the number of raw rows accumulated since the watermark, and $c_{row}$ is the per-row cost of the live time_bucket() aggregation. Because $C_{materialized}$ is roughly constant for a fixed time range, all of the variability between the two paths lives in $C_{tail}$. The materialized-only path simply drops that second term, and $N_{tail}$ grows linearly with the time since the last refresh:

$$N_{tail} \approx R_{rows/s} \times (t_{now} - t_{watermark})$$

so a long refresh cadence inflates $C_{tail}$ on every single query until the next refresh resets it to near zero.

# Worked Example: A 1-Hour Dashboard on an Hourly Refresh

Take an operational dashboard that plots avg_value per device in 1-hour buckets over the trailing 24 hours, backed by a continuous aggregate whose refresh policy runs hourly. The underlying sensor_readings hypertable ingests 2,500 rows/sec across the fleet.

Staleness under materialized-only. With a 1-hour cadence and a typical end_offset of one bucket, the watermark trails now() by up to two hours in the worst case (the unrefreshed current bucket plus the offset). A dashboard reading materialized_only = true would therefore show a flat line for the most recent hour or two — unacceptable if operators watch it to catch anomalies as they happen.

Union cost under real-time. Immediately after a refresh the raw tail is nearly empty, so $C_{tail} \approx 0$. Just before the next refresh, the tail has accumulated roughly a full hour of ingest:

$$N_{tail} \approx 2500 \times 3600 = 9{,}000{,}000 \text{ rows}$$

Every dashboard load in that final pre-refresh minute triggers a live time_bucket() over those ~9 million raw rows, on top of the ~24 materialized rows per device it reads from the rollup. That is the concrete shape of the trade: the real-time path pays up to a nine-million-row scan to erase a two-hour staleness gap, and the cost sawtooths — near zero right after each refresh, peaking right before the next.

Two levers move the peak. Shortening the cadence to every 5 minutes cuts the worst-case tail to $2500 \times 300 = 750{,}000$ rows, a 12× reduction in $C_{tail}$, at the price of more frequent refresh jobs. Alternatively, if the SLA actually tolerates hour-old data, materialized_only = true removes $C_{tail}$ entirely and the dashboard reads only the rollup:

-- Cadence that keeps the real-time tail small: refresh every 5 minutes,
-- covering the last 2 hours so the watermark stays close to now().
SELECT add_continuous_aggregate_policy('dashboard_1h',
    start_offset      => INTERVAL '2 hours',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '5 minutes');

# Edge Cases & When to Deviate

• Large raw tail makes the union expensive. If ingest is high or the cadence is long, $N_{tail}$ can dwarf the rollup and every query degrades. Shorten the refresh cadence first; only keep the real-time union if the freshness SLA genuinely requires sub-cadence currency. When the tail routinely reaches millions of rows, this becomes a case for incremental refresh performance tuning on large datasets.
• Compressed raw tail. Real-time aggregates scan the raw hypertable, including compressed chunks. If the compression boundary falls inside the tail window, the union decompresses batches at query time — cheap on columnar reads but far from free. Keep the compression policy’s threshold safely older than the refresh watermark so the tail scanned by the union stays uncompressed.
• Late-arriving data. A real-time union sees late rows the instant they land, but only for buckets beyond the watermark; late data that falls before the watermark is invisible until an invalidation-triggered refresh reprocesses that region. If late data is common, do not lean on the real-time path to mask it — pair it with the diagnostics in troubleshooting stale continuous aggregates in production.
• Mixing per tier. The flag is per view, so you can serve the same rollup two ways: a materialized_only = false view for the live operations screen and a materialized_only = true view (or the same view queried with a time filter that excludes the tail) for heavy analytical exports that must not pay the union cost on every run.
• Refresh contention. If refreshes queue behind other jobs, the watermark stalls and the real-time tail grows unbounded, silently inflating query cost. Watch job scheduling through asynchronous execution and queue management so a backed-up refresh does not turn a cheap union into a full-tail scan.

# Verification

Confirm which path the planner actually chose. A real-time aggregate produces an Append/Merge of two branches — the materialized hypertable and a live aggregation over the raw hypertable — while a materialized-only view scans only the rollup:

-- Real-time view: the plan contains BOTH the materialized hypertable
-- and a GroupAggregate over the raw hypertable (the live tail).
EXPLAIN (ANALYZE, COSTS ON)
SELECT bucket, avg_value
FROM dashboard_1h
WHERE bucket > now() - INTERVAL '24 hours'
  AND device_id = 'a1b2c3d4-...';

-- Look for a shape like:
--  Append
--    ->  ... Scan on _materialized_hypertable_*   (rollup branch)
--    ->  GroupAggregate                            (real-time tail branch)
--          ->  ... Scan on _hyper_*                (raw sensor_readings)

After ALTER ... SET (materialized_only = true), the same EXPLAIN collapses to a single scan of _materialized_hypertable_* with no GroupAggregate over the raw table — proof the tail is no longer read. You can also confirm the flag and inspect how far the watermark trails now():

-- Confirm the flag currently in effect for the view.
SELECT view_name, materialized_only, finalized
FROM timescaledb_information.continuous_aggregates
WHERE view_name = 'dashboard_1h';

-- How stale is the materialized region? Compare the watermark to now().
SELECT to_timestamp(
    _timescaledb_internal.to_unix_microseconds(
        _timescaledb_internal.cagg_watermark(mat_hypertable_id)) / 1e6
) AS watermark
FROM _timescaledb_catalog.continuous_agg
WHERE user_view_name = 'dashboard_1h';

If the watermark trails now() by more than one refresh cadence, refreshes are falling behind and a real-time view is quietly scanning an ever-larger tail — the signal to shorten the cadence, fix the refresh queue, or accept materialized-only reads for that view.

# Related

• Incremental vs Full Refresh Strategies — the parent guide on how the refresh watermark advances
• Refresh Policy Design & Scheduling — choosing the cadence that bounds staleness and tail size
• Troubleshooting Stale Continuous Aggregates in Production — diagnosing watermarks that stop advancing
• Asynchronous Execution & Queue Management — keeping refresh jobs from backing up behind the queue

← Back to Incremental vs Full Refresh Strategies

Up to Continuous Aggregate Creation & Refresh Management