Continuous Aggregates vs timescaledb_toolkit Hyperfunctions

Deciding how to serve an advanced rollup — a time-weighted average, a percentile, a delta counter — comes down to whether you pre-materialize the answer with a continuous aggregate or compute it on the fly with a timescaledb_toolkit hyperfunction, and this page shows how to profile the read pattern, compare the two mechanisms dimension by dimension, and combine them by storing a hyperfunction’s partial state inside the aggregate so rollup() can re-aggregate it later. The choice is not exclusive: the strongest production pattern materializes a partial aggregate once and finishes the computation cheaply at query time.

# Input Profiling: What to Measure Before Choosing

The right mechanism follows directly from three properties of your read workload. Establish them before writing any DDL, because retrofitting the wrong choice means dropping and rebuilding a materialized view.

• Query type — is the aggregate a simple decomposable statistic (avg, min, max, sum, count) or an advanced one that needs a hyperfunction (time_weight for time-weighted averages, percentile_agg or tdigest for approximate percentiles, stats_agg for combined mean/variance/skew, counter_agg for resetting counters)?
• Query frequency — how often is the rollup read, and by how many concurrent dashboards or alert rules? A number served thousands of times per minute justifies pre-materialization; an ad-hoc analyst query run twice a day does not.
• Freshness tolerance — must the answer include the last few seconds of raw data, or is a bucket that lags the refresh policy by one interval acceptable? Real-time aggregation and refresh cadence set the floor here, a tradeoff explored in incremental vs full refresh strategies.

A useful rule of thumb: pre-materialize when the same bucketed answer is read far more often than the underlying data changes, and compute on the fly when the query shape is exploratory or the freshness window is tighter than any refresh policy can guarantee. The read-to-write ratio is the deciding number:

$$R = \frac{\text{reads per bucket}}{\text{writes per bucket}}$$

When $R \gg 1$ the materialization cost is amortized across many cheap reads and a continuous aggregate wins; when $R \lesssim 1$ you are paying to store an answer almost nobody reads, and an on-the-fly hyperfunction over raw chunks is the leaner choice.

# Comparison: Continuous Aggregate vs Hyperfunction vs Combined

Each mechanism occupies a different point on the storage-versus-freshness-versus-latency surface. The combined column is the reason the two are not mutually exclusive — a continuous aggregate can store a hyperfunction’s intermediate aggregate rather than a finished scalar, giving you cheap reads without losing the advanced statistic.

A plain continuous aggregate storing a simple average is the baseline case:

-- Plain decomposable aggregate: cheap to materialize, cheap to read.
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
    device_id,
    time_bucket('1 hour', time) AS bucket,
    avg(value)                  AS avg_value,
    count(*)                    AS n
FROM sensor_readings
GROUP BY device_id, bucket
WITH NO DATA;

Computing an advanced statistic on the fly needs a hyperfunction over the raw hypertable. A time-weighted average and an approximate 95th percentile look like this:

-- On the fly: no materialization, always fresh, full scan cost each call.
SELECT
    device_id,
    time_bucket('1 hour', time)                          AS bucket,
    average(time_weight('Linear', time, value))          AS twa,
    approx_percentile(0.95, percentile_agg(value))       AS p95
FROM sensor_readings
WHERE time > now() - INTERVAL '6 hours'
GROUP BY device_id, bucket;

The powerful middle path materializes the hyperfunction’s partial aggregate — the TimeWeightSummary, StatsSummary, or percentile digest — inside a continuous aggregate, and finishes the computation at read time. Because these summaries are designed to be re-aggregated, rollup() merges hourly partials into daily or weekly ones without touching raw data:

-- Combined: store the intermediate partial, not a finished scalar.
CREATE MATERIALIZED VIEW sensor_hourly_adv
WITH (timescaledb.continuous) AS
SELECT
    device_id,
    time_bucket('1 hour', time)      AS bucket,
    time_weight('Linear', time, value) AS tw,     -- partial: TimeWeightSummary
    stats_agg(value)                 AS stats,     -- partial: StatsSummary
    percentile_agg(value)            AS pct        -- partial: percentile digest
FROM sensor_readings
GROUP BY device_id, bucket
WITH NO DATA;

-- Read: finish the stored partials, and roll several hours into one answer.
SELECT
    device_id,
    average(rollup(tw))                  AS twa,        -- weighted correctly
    stddev(rollup(stats))                AS stddev,
    approx_percentile(0.95, rollup(pct)) AS p95
FROM sensor_hourly_adv
WHERE bucket > now() - INTERVAL '7 days'
GROUP BY device_id;

The distinction that makes this work: average(time_weight(...)) in the plain query returns a double precision, but time_weight(...) alone returns a TimeWeightSummary — a small serialized state object. Storing the summary keeps the door open for rollup(); storing the scalar closes it, because you cannot correctly weight-average a set of already-averaged numbers.

# Worked Example: Time-Weighted Average of a Gauge

Consider a fleet of 8,000 pressure sensors reporting an irregular gauge value — readings arrive whenever the value changes by more than a threshold, so a plain avg(value) is biased toward chatty periods and undercounts long stable stretches. The correct metric is a time-weighted average, and there are two ways to serve it.

On the fly, straight over the raw hypertable, always fresh but paying a full six-hour scan on every dashboard refresh:

SELECT
    device_id,
    average(time_weight('Linear', time, value)) AS twa_6h
FROM gauge_readings
WHERE time > now() - INTERVAL '6 hours'
GROUP BY device_id;

Pre-materialized as a partial, so the same dashboard reads a small stored table and the summary rolls up to any coarser window:

CREATE MATERIALIZED VIEW gauge_twa_hourly
WITH (timescaledb.continuous) AS
SELECT
    device_id,
    time_bucket('1 hour', time)        AS bucket,
    time_weight('Linear', time, value) AS tw
FROM gauge_readings
GROUP BY device_id, bucket
WITH NO DATA;

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

-- Daily time-weighted average, computed from hourly partials, no raw scan.
SELECT
    device_id,
    average(rollup(tw)) AS twa_daily
FROM gauge_twa_hourly
WHERE bucket >= date_trunc('day', now())
GROUP BY device_id;

For 8,000 devices at, say, an average of two readings per minute, the raw six-hour scan touches roughly 5.8 million rows every time a dashboard loads. The materialized version reads at most a few thousand hourly summary rows for the same window — the read-to-write ratio $R$ is high because dozens of operators watch the same gauges, so the partial-in-a-cagg path is decisively cheaper while preserving the exact weighting. Getting the bucketing right for gappy gauges pairs naturally with gap-filling continuous aggregates via time_bucket_gapfill, which carries the last observation across buckets with no readings.

# Edge Cases & When to Deviate

• Non-decomposable aggregates cannot live in a continuous aggregate. The materialization engine requires an aggregate to be parallelizable and to expose a combinable partial. A bare percentile_cont (the ordered-set SQL aggregate) is neither, so it is rejected in the view definition — you must use the toolkit’s percentile_agg/tdigest, which do expose a rollup-able digest, or compute the percentile on the fly.
• rollup() is only valid on partials, not finished values. If the view stored average(time_weight(...)) as a scalar, no amount of rollup() recovers the correct coarser answer; re-materialize the view to store the summary object instead.
• Toolkit extension availability. Hyperfunctions require the timescaledb_toolkit extension to be installed and in the search path (CREATE EXTENSION timescaledb_toolkit;). Managed platforms usually bundle it, but a self-hosted cluster or a minimal image may not — check before you design around it, because a view that references a missing function will fail to create.
• Approximate versus exact. percentile_agg and tdigest return approximate percentiles; the digest trades a bounded error for constant-size, rollup-able state. When a billing or compliance path needs an exact percentile, fall back to an on-the-fly percentile_cont over raw data and accept the scan cost, because there is no exact percentile summary to materialize.
• Wide GROUP BY cardinality. Storing a summary object per device per bucket is larger than storing a scalar; at very high device counts, confirm the materialized view’s own footprint still beats re-scanning raw, and lengthen the bucket if it does not.

# Verification

After building a partial-based continuous aggregate, confirm that the materialized summary rolls up to exactly the value an on-the-fly computation would produce over the same window. Any drift means the view is storing finished scalars or a stale bucket:

-- Materialized path: finish the stored hourly partials for a device/day.
WITH materialized AS (
    SELECT average(rollup(tw)) AS twa
    FROM gauge_twa_hourly
    WHERE device_id = 'dev-42'
      AND bucket >= now() - INTERVAL '1 day'
      AND bucket <  now()
),
-- On-the-fly path: same window, straight over raw rows.
on_the_fly AS (
    SELECT average(time_weight('Linear', time, value)) AS twa
    FROM gauge_readings
    WHERE device_id = 'dev-42'
      AND time >= now() - INTERVAL '1 day'
      AND time <  now()
)
SELECT
    materialized.twa      AS twa_materialized,
    on_the_fly.twa        AS twa_raw,
    abs(materialized.twa - on_the_fly.twa) AS delta
FROM materialized, on_the_fly;

A delta at floating-point noise level confirms the partial re-aggregates correctly; a large delta points at a boundary mismatch, an unrefreshed bucket, or a scalar stored where a summary belonged. Confirm the aggregate is actually materialized and current with the catalog:

-- Is the view real-time, and when did it last refresh?
SELECT view_name, materialized_only, finalized
FROM timescaledb_information.continuous_aggregates
WHERE view_name = 'gauge_twa_hourly';

-- Refresh job health for the view's policy.
SELECT j.hypertable_name, js.last_run_status, js.last_successful_finish
FROM timescaledb_information.jobs j
JOIN timescaledb_information.job_stats js USING (job_id)
WHERE j.proc_name = 'policy_refresh_continuous_aggregate';

If materialized_only is false the view unions the stored partials with a live computation over the raw tail, giving you the pre-materialized cheap read and last-moment freshness — the combined pattern at its most complete.

# Related

• Materialized View Architecture & Syntax — the DDL foundations for the views compared here
• Creating Continuous Aggregates with time_bucket_gapfill — carrying values across empty buckets for gappy gauges
• Incremental vs Full Refresh Strategies — how refresh cadence sets the freshness floor for either approach
• Continuous Aggregate Creation & Refresh Management — the full lifecycle of building and refreshing materialized rollups

← Back to Materialized View Architecture & Syntax