TimescaleDB Continuous Aggregates: Real-Time vs Materialized-Only

Source: Dev.to

What is a Continuous Aggregate?

A continuous aggregate 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.

Refresh Policy

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'
);

How the three parameters interact

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.

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 (default)

When you query a real‑time CAGG, TimescaleDB transparently combines two data sources:

1. Pre‑computed results from the materialization hypertable for the historical range.
2. Live aggregation 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).

Trade‑off

Materialized‑Only Mode

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.

Benefits

• Queries are faster and more predictable; no live aggregation path, no variable performance depending on how much unmaterialized data has accumulated.
• 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.

Comparison Table

When to Use Which Mode

• Real‑Time Mode – Use when consumers expect up‑to‑the‑minute data and can tolerate slightly higher latency on the recent window. Ideal for operational dashboards and alerting.
• Materialized‑Only Mode – Use when freshness requirements are relaxed and query consistency matters more. Ideal for billing calculations, daily reports, and analytics pipelines that run on a schedule and do not benefit from the live aggregation overhead.

The Silent Failure Mode

The silent failure mode of continuous aggregates is staleness. In materialized‑only mode, the CAGG returns results that are stale by the size of the end_offset plus the schedule_interval. Be sure to choose the mode that matches your application’s freshness requirements.

-- 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 the staleness exceeds your end_offset plus schedule_interval by a significant margin, the refresh policy is not running properly. Inspect timescaledb_information.job_stats for the refresh job’s last_successful_finish and total_failures.

Note:
The end_offset in your refresh policy is not a bug or mis‑configuration. It is a deliberate design choice that prevents wasted re‑computation of partially‑filled buckets.

• Real‑time mode fills the gap transparently, ensuring up‑to‑date results.
• Materialized‑only mode is useful when speed matters more than freshness.

Recommendation

1. Start with real‑time mode.
2. Switch to materialized‑only only after you have identified specific queries or use cases where the live aggregation overhead is unnecessary.

The toggle between modes is instant and reversible. Knowing which mode each of your continuous aggregates is running—and why—keeps your aggregated data trustworthy.