TimescaleDB Continuous Aggregates: Real-Time vs Materialized-Only

Every time-series dashboard eventually hits the same wall: the aggregation query that was fast at 1 million rows takes seconds at 100 million rows, and your users are staring at a loading spinner. Pre-computing aggregations is the obvious solution, but maintaining materialized views manually is brittle -- you end up with stale data, missed refreshes, and a growing pile of custom refresh scripts.

TimescaleDB continuous aggregates solve this by automating the materialization lifecycle. But they come with a subtlety that catches most people: the refresh policy intentionally leaves a gap in the materialized data, and there are two different modes for how that gap is handled. Choosing the wrong mode -- or not understanding which one you are running -- leads to dashboards that silently show stale data.

What Continuous Aggregates Are

A continuous aggregate (CAGG) is a materialized view backed by its own hidden hypertable. TimescaleDB stores pre-computed aggregate results in this materialization hypertable, so queries read compact summary rows instead of scanning millions of raw records.

CREATE MATERIALIZED VIEW hourly_device_metrics
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', event_timestamp_utc) AS bucket_hour_utc,
    device_id,
    AVG(metric_value) AS avg_metric_value,
    MAX(metric_value) AS max_metric_value,
    COUNT(*) AS event_count
FROM sensor_events
GROUP BY bucket_hour_utc, device_id;

Creating the CAGG defines its structure but does not populate it. The materialization hypertable remains empty until you add a refresh policy or run a manual refresh.

The Refresh Policy: Three Parameters That Control Everything

The refresh policy tells TimescaleDB to periodically re-compute aggregates for a sliding time window:

SELECT add_continuous_aggregate_policy('hourly_device_metrics',
    start_offset  => INTERVAL '3 hours',
    end_offset    => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes');

These three parameters interact in ways that are not immediately obvious:

• start_offset -- how far back from now() the refresh window begins. With 3 hours, the policy re-materializes data starting from 3 hours ago.
• end_offset -- how far back from now() the refresh window ends. With 1 hour, data newer than 1 hour ago is never materialized by the policy.
• schedule_interval -- how often the policy runs.

The end_offset is the crucial parameter. It creates a deliberate gap: a window of the most recent data that the policy intentionally skips. This is not a bug. The most recent time buckets are still accumulating data. Materializing a partially-filled bucket and then re-materializing it minutes later is wasted computation. The end_offset prevents this churn.

The practical consequence: with the configuration above, materialized data is always at least 1 hour old, and could be up to 1.5 hours old (the end_offset plus one schedule_interval cycle).

Real-Time Mode: Automatic Gap-Filling

By default, TimescaleDB creates continuous aggregates in real-time mode. When you query a real-time CAGG, it transparently combines two data sources:

1. Pre-computed results from the materialization hypertable for the historical range
2. A live aggregation query against the source hypertable for any data newer than the materialization watermark

The two result sets are unioned automatically. Your application sees a single, complete result set as if all data had been materialized.

You can see this in the query plan: an Append node with two children -- one scan of the materialization hypertable (fast) and one scan of the source hypertable (slower, because it runs the full aggregation on raw data).

The tradeoff is straightforward. The historical portion is fast because it reads pre-computed rows. The recent portion is slower because it aggregates raw data on the fly. For a dashboard querying the last 24 hours, the first 23 hours come from materialization and the last hour comes from the live path. The larger the end_offset or the longer since the last refresh, the more data flows through the slower live path.

Materialized-Only Mode: Predictable Performance

You can disable the real-time union entirely:

ALTER MATERIALIZED VIEW hourly_device_metrics
SET (timescaledb.materialized_only = true);

In materialized-only mode, the CAGG returns only pre-computed data. Queries never touch the source hypertable. The most recent data (everything within the end_offset window) simply does not appear.

This makes queries faster and more predictable. There is no live aggregation path, no variable performance depending on how much unmaterialized data has accumulated. The query plan shows a single scan of the materialization hypertable.

You can toggle between modes at any time without data loss:

-- Switch to materialized-only (faster queries, stale recent window)
ALTER MATERIALIZED VIEW hourly_device_metrics
SET (timescaledb.materialized_only = true);

-- Switch back to real-time (slower recent window, complete data)
ALTER MATERIALIZED VIEW hourly_device_metrics
SET (timescaledb.materialized_only = false);

No data is dropped or re-computed. The toggle only changes whether the query executor appends the live union.

When to Use Which Mode

Aspect	Real-Time (default)	Materialized-Only
Data freshness	Current (up to now())	Stale by end_offset + schedule_interval
Recent query performance	Slower (live aggregation)	Fast (materialization only)
Historical query performance	Same	Same
Touches source hypertable	Yes, for unmaterialized range	Never
Best for	Dashboards, alerts, operational monitoring	Reports, billing, analytics, batch pipelines

Use real-time mode when consumers expect up-to-the-minute data and can tolerate slightly higher latency on the recent window. This is the right default for operational dashboards and alerting.

Use materialized-only mode when freshness requirements are relaxed and query consistency matters more. Billing calculations, daily reports, and analytics pipelines that run on a schedule do not benefit from the live aggregation overhead.

Monitoring Staleness

The silent failure mode of continuous aggregates is staleness. The CAGG returns results, but they are hours or days behind because the refresh policy stalled, was never configured, or is running but the end_offset is larger than expected.

To check the materialization watermark and detect staleness:

SELECT
    view_name,
    materialization_hypertable_name,
    (SELECT max(bucket_hour_utc) FROM hourly_device_metrics) AS latest_materialized,
    now() - (SELECT max(bucket_hour_utc) FROM hourly_device_metrics) AS staleness
FROM timescaledb_information.continuous_aggregates
WHERE view_name = 'hourly_device_metrics';

If staleness exceeds your end_offset plus schedule_interval by a significant margin, the refresh policy is not running properly. Check timescaledb_information.job_stats for the refresh job's last_successful_finish and total_failures.

The Key Takeaway

The end_offset in your refresh policy is not a bug or misconfiguration. It is a deliberate design choice that prevents wasted re-computation of partially-filled buckets. Real-time mode exists to cover this gap transparently. Materialized-only mode exists for when speed matters more than freshness.

Start with real-time mode. Switch to materialized-only when you identify specific queries or use cases where the live aggregation overhead is unnecessary. The toggle is instant and reversible. Knowing which mode each of your continuous aggregates is running -- and why -- is what keeps your aggregated data trustworthy.