How TimescaleDB Continuous Aggregates Reduce a 424ms Aggregate Query to 0.4ms — Cagg-on-Cagg Hierarchical Design and Automatic Compression Scheduling

When I first started working with PostgreSQL for IoT sensor data, I struggled quite a bit because aggregate queries doing full scans over millions of rows made dashboards sluggish. I tried index tuning, partitioning, and everything else, but the fundamental solution turned out to be the simple idea of "pre-compute and store." TimescaleDB's Continuous Aggregates (CAgg) handles exactly that idea automatically within PostgreSQL.

A CAgg is an auto-refreshing Materialized View that pre-computes aggregations in the form of GROUP BY time_bucket(...). When designed well, the perceptible difference is significant — there are reported cases of 424ms → 0.433ms (approximately 979×) response time improvement on identical aggregate queries (Dev.to, 2024).

In this article, we'll walk through real code covering the Cagg-on-Cagg (hierarchical continuous aggregates) design pattern of stacking CAggs on top of CAggs, how to coordinate auto-refresh schedules with Refresh Policies, and chunk compression policies that automatically convert old chunks to columnar storage. The patterns apply equally to IoT sensors, financial tick data, and DevOps metrics.

Core Concepts

Continuous Aggregates: Auto-Refreshing Materialized Views

You might wonder, "Can't I just attach a cron job to a regular Materialized View?" — but CAggs differ in a few important ways.

• Incremental refresh: Instead of recomputing everything, only the time buckets where changes occurred are recomputed.
• Real-time blending: In the default materialized_only = false mode, even the latest intervals not yet refreshed are transparently merged with real-time computed values and returned.
• Batched incremental execution: In TimescaleDB 2.28 and above, even large Refresh windows are processed in bucket-sized batches by default (buckets_per_batch defaults to 10), reducing lock contention and exposing processed intervals immediately.

The basic creation syntax is as follows.

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', ts) AS bucket,
  device_id,
  avg(value) AS avg_val,
  max(value) AS max_val,
  min(value) AS min_val
FROM metrics
GROUP BY 1, 2
WITH NO DATA;

Creating with WITH NO DATA builds only the view definition without any initial data. You can then populate it by registering a Refresh Policy or manually calling CALL refresh_continuous_aggregate(...).

Cagg-on-Cagg: Hierarchical Rollup Structure

You can stack CAggs on top of other CAggs. Aggregate raw data into an hourly CAgg, then layer a daily CAgg on top of that, then a monthly CAgg on top of that. Each higher-level CAgg uses the materialization hypertable of the lower CAgg as its source, not the raw hypertable.

flowchart TD
    A[Raw Hypertable] --> B[Hourly CAgg]
    B --> C[Daily CAgg]
    C --> D[Monthly CAgg]
    A -.->|Auto-compressed after 7 days| E[Compressed Raw Chunks]
    B -.->|Auto-compressed after 30 days| F[Compressed Hourly Chunks]

The advantage of this structure is that when running a daily aggregate query, you only need to read thousands of rows from the hourly CAgg rather than hundreds of millions of rows of raw data. With each layer, the number of rows each level handles decreases exponentially.

There is one constraint to verify early in the design phase. If the lower CAgg contains a JOIN, the upper CAgg cannot use a JOIN. There are also some limitations on supported aggregate functions, so reviewing these at schema design time will save refactoring costs later.

Refresh Policy: Designing Auto-Refresh Schedules

This is a background auto-refresh job registered with add_continuous_aggregate_policy(). Three parameters are key.

The reason end_offset exists is to avoid storing an incompletely settled aggregate value, since the most recent bucket may still be receiving data. It's generally good practice to set end_offset equal to or slightly larger than the bucket size.

Understanding Refresh ordering dependencies in Cagg-on-Cagg is important. TimescaleDB does not automatically guarantee the execution order of two policies. If the daily CAgg policy runs at its scheduled time while the hourly CAgg policy is still running or slightly delayed, the latest bucket will be rolled up with missing data. The practical mitigation is not to align schedule_interval cycles — it is to set the upper CAgg's start_offset large enough to give the lower CAgg time to finish its refresh.

The diagram below shows the correct execution order and the problem that occurs when the order is reversed, side by side.

flowchart TD
    subgraph correct["Recommended Execution Order"]
        A1[Refresh Hourly CAgg] --> A2[Refresh Daily CAgg] --> A3[Refresh Monthly CAgg]
    end
    subgraph risk["When Upper CAgg Runs First"]
        B1[Daily CAgg Runs Early] --> B2[Latest Data from Lower Layer Missing] --> B3[Incomplete Aggregate Stored]
    end

For a practical configuration example: set the hourly CAgg to schedule_interval = '1 hour', end_offset = '1 hour', and the daily CAgg to schedule_interval = '1 day', start_offset = '3 days' with generous headroom. The more generous the start_offset, the safer the coverage even if the lower CAgg finishes later than expected.

Chunk Compression Policy

TimescaleDB's chunk compression automatically converts old chunks from row-based (Row Store) → columnar (Column Store). Compression ratios of 90–98% have been reported in some cases, with a publicly documented production example of 150GB → 15GB (90% reduction) (Dev.to, 2024).

Compression configuration is done in two steps.

-- Set compression options
ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id',
  timescaledb.compress_orderby   = 'ts DESC'
);
 
-- Auto-compress chunks older than 7 days
SELECT add_compression_policy('metrics', compress_after => INTERVAL '7 days');

compress_segmentby specifies columns frequently used in query filters. When set correctly, segments can be located quickly by that column even within compressed chunks. It is safer to set compress_after to at least twice the chunk interval, because if a hot chunk that is still receiving data falls within the compression target, performance can actually degrade.

Compression policies can also be applied to CAggs themselves, significantly reducing long-term storage costs. Additionally, Hypercore (the hybrid row-columnar storage engine introduced in TimescaleDB 2.18) provides an integrated approach that rapidly ingests new data into a Row Store and automatically converts chunks exceeding the configured age to Column Store. Cases achieving 98% compression ratio have been reported, making it a worthwhile option to evaluate for new installations (per the official blog).

Practical Application

IoT Sensor Monitoring: Full Multi-Layer Aggregate Setup

Assume tens of thousands of sensors sending thousands of records per second. Here is the complete flow for setting up a Cagg-on-Cagg from hourly → daily, with compression policies attached to each layer.

-- Create raw hypertable
CREATE TABLE metrics (
  ts        TIMESTAMPTZ NOT NULL,
  device_id TEXT,
  value     DOUBLE PRECISION
);
 
SELECT create_hypertable('metrics', 'ts',
  chunk_time_interval => INTERVAL '1 day');
 
-- [Step 1] Hourly CAgg
-- Store sum_val alongside to enable accurate weighted average calculation in upper layers
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', ts) AS bucket,
  device_id,
  avg(value) AS avg_val,
  max(value) AS max_val,
  min(value) AS min_val,
  sum(value) AS sum_val,
  count(*)   AS cnt
FROM metrics
GROUP BY 1, 2
WITH NO DATA;
 
SELECT add_continuous_aggregate_policy('metrics_hourly',
  start_offset      => INTERVAL '3 hours',
  end_offset        => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');
 
-- [Step 2] Daily CAgg-on-Cagg (referencing the lower CAgg as source)
-- Use sum_val/cnt instead of avg(avg_val) for a mathematically accurate weighted average
CREATE MATERIALIZED VIEW metrics_daily
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', bucket) AS day_bucket,
  device_id,
  sum(sum_val) / sum(cnt) AS avg_val,
  max(max_val)            AS max_val,
  min(min_val)            AS min_val,
  sum(sum_val)            AS sum_val,
  sum(cnt)                AS cnt
FROM metrics_hourly   -- Reference the hourly CAgg, not the raw table
GROUP BY 1, 2
WITH NO DATA;
 
-- Set start_offset generously to 3 days to allow time for
-- the hourly CAgg refresh to complete
SELECT add_continuous_aggregate_policy('metrics_daily',
  start_offset      => INTERVAL '3 days',
  end_offset        => INTERVAL '1 day',
  schedule_interval => INTERVAL '1 day');
 
-- Compress raw table
ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id',
  timescaledb.compress_orderby   = 'ts DESC'
);
SELECT add_compression_policy('metrics',
  compress_after => INTERVAL '7 days');
 
-- Compress hourly CAgg (after 30 days)
ALTER MATERIALIZED VIEW metrics_hourly SET (timescaledb.compress = true);
SELECT add_compression_policy('metrics_hourly',
  compress_after => INTERVAL '30 days');

The reason for using the sum(sum_val) / sum(cnt) pattern is that a simple avg(avg_val) is mathematically inaccurate when bucket row counts differ. By passing sum_val and cnt up to the higher CAgg as well, you can compute an accurate average in the same way when adding a monthly rollup layer later.

Financial OHLCV Aggregation: From Tick Data to Candlestick Charts

Taking cryptocurrency or stock tick data as an example, a common pattern is to pre-compute OHLCV in a 1-minute CAgg and stack 1-hour candles on top. Applying this structure is known to reduce aggregate query responses by hundreds to thousands of times.

CREATE TABLE ticks (
  ts     TIMESTAMPTZ NOT NULL,
  symbol TEXT,
  price  NUMERIC(18, 8),
  volume NUMERIC(18, 8)
);
 
SELECT create_hypertable('ticks', 'ts',
  chunk_time_interval => INTERVAL '1 day');
 
-- 1-minute OHLCV CAgg
CREATE MATERIALIZED VIEW ohlcv_1m
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 minute', ts) AS bucket,
  symbol,
  first(price, ts) AS open,
  max(price)       AS high,
  min(price)       AS low,
  last(price, ts)  AS close,
  sum(volume)      AS volume
FROM ticks
GROUP BY 1, 2
WITH NO DATA;
 
SELECT add_continuous_aggregate_policy('ohlcv_1m',
  start_offset      => INTERVAL '10 minutes',
  end_offset        => INTERVAL '1 minute',
  schedule_interval => INTERVAL '1 minute');
 
-- 1-hour OHLCV CAgg-on-Cagg
CREATE MATERIALIZED VIEW ohlcv_1h
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', bucket) AS bucket,
  symbol,
  first(open, bucket)  AS open,
  max(high)            AS high,
  min(low)             AS low,
  last(close, bucket)  AS close,
  sum(volume)          AS volume
FROM ohlcv_1m
GROUP BY 1, 2
WITH NO DATA;
 
SELECT add_continuous_aggregate_policy('ohlcv_1h',
  start_offset      => INTERVAL '3 hours',
  end_offset        => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

first() and last() are time-order-based aggregate functions provided by TimescaleDB. When reusing them in Cagg-on-Cagg, you must explicitly specify the bucket column as the sort key, as in first(open, bucket).

Managing the Full Data Lifecycle

Combining with a Retention Policy allows you to independently automate the lifecycle of both the raw data path and the CAgg path.

flowchart TD
    subgraph raw["Raw Data Path"]
        R1[Ingest New Data] --> R2[Keep Raw Chunks Within 7 Days]
        R2 -->|Auto-compressed after 7 days| R3[Compressed Chunks]
        R3 -->|Deleted after 90 days| R4[Deleted]
    end
    subgraph cagg["CAgg Path"]
        C1[Hourly CAgg Auto-Refresh] --> C2[Compress CAgg After 30 Days]
        C2 -->|Deleted after 1 year| C3[Deleted]
    end
    R1 -->|Triggers incremental auto-refresh| C1

-- Retain raw data for 90 days
SELECT add_retention_policy('metrics',
  drop_after => INTERVAL '90 days');
 
-- Retain hourly CAgg for 1 year
SELECT add_retention_policy('metrics_hourly',
  drop_after => INTERVAL '1 year');
 
-- Daily CAgg retained long-term (no policy set)

One thing to be careful about: when you manually increase start_offset to re-aggregate history, if that range overlaps with raw chunks already deleted by the retention policy, aggregate values for that interval may be corrupted with 0 or NULL. Keeping start_offset short as in this example avoids the problem, but if long-range re-aggregation is needed, verify the range of deleted chunks first.

Initial Backfill

If you create with WITH NO DATA and only register a Refresh Policy, historical data before the policy's start_offset will not be automatically populated. If you need historical data, you must backfill manually.

There are two important points to note here.

First, passing NULL as the start point will full-scan from the hypertable's first chunk. If there is a lot of data, this can take hours, so it is better to specify the actual time range you need.

Second, in a Cagg-on-Cagg structure, you must complete the lower CAgg backfill before running the upper CAgg backfill.

-- 1. Backfill the lower CAgg first
CALL refresh_continuous_aggregate(
  'metrics_hourly',
  NOW() - INTERVAL '1 year',
  NOW()
);
 
-- 2. Run the upper CAgg only after the lower one finishes
CALL refresh_continuous_aggregate(
  'metrics_daily',
  NOW() - INTERVAL '1 year',
  NOW()
);

Monitoring Refresh Jobs

If you set it up and move on trusting automation, you may find out later — too late — that refreshes have been silently failing. It's good practice to regularly check the views below.

-- Check recent job execution results
SELECT job_id, hypertable_name, last_run_status, last_run_duration
FROM timescaledb_information.job_stats
ORDER BY last_run_started_at DESC;
 
-- Check registered policy list
SELECT *
FROM timescaledb_information.jobs
WHERE proc_name IN (
  'policy_refresh_continuous_aggregate',
  'policy_compression'
);

If there are rows where last_run_status is Failed, investigate the cause immediately.

Pros and Cons Analysis

Advantages

Disadvantages and Caveats

Common Mistakes in Practice

Setting compress_after too short. If the chunk interval is 1 day and you set compress_after = '1 day', chunks still being written may get compressed. It's good to set at least twice the chunk interval, and ideally add enough buffer to account for late write latency.

Setting the upper CAgg's start_offset too short. If both the hourly and daily CAggs are configured too tightly, the daily CAgg policy may run while the hourly CAgg is still processing. The key is to set the upper CAgg's start_offset large enough to allow time for the lower CAgg refresh to complete.

Forgetting the initial backfill. Creating with WITH NO DATA and only registering a Refresh Policy will not automatically populate historical data before the policy's coverage window (before start_offset). When there is a hierarchy, you must complete the lower CAgg backfill first, then run the upper CAgg backfill to get correct rollup values.

Closing Thoughts

Introducing even a single CAgg can give you the experience of aggregate queries running hundreds of times faster. Adding a Cagg-on-Cagg hierarchy lets you handle long-term trend queries quickly as well, and attaching a compression policy can be expected to reduce storage costs by 90% or more.

Key takeaways:

• CAgg incrementally recomputes only changed buckets, and unrefreshed intervals are transparently blended with real-time computation.
• In Cagg-on-Cagg, TimescaleDB does not automatically guarantee Refresh order. Setting the upper CAgg's start_offset large enough to allow the lower refresh to complete is the correct mitigation.
• Use the sum(sum_val) / sum(cnt) pattern instead of avg(avg_val) for weighted averages, and pass sum_val and cnt through every layer to maintain rollup accuracy.
• Set compress_after to at least twice the chunk interval and leave enough headroom to ensure hot chunks are not compressed.
• Having a monitoring routine that regularly checks timescaledb_information.job_stats for Refresh and compression job success will make operations significantly more stable.

3 Starting Points:

1. Pick the most frequently run aggregate query on your existing hypertable, create an hourly CAgg, and compare the response time — the difference is worth seeing.
2. Once the hourly CAgg is stable, add a daily Cagg-on-Cagg and connect it to your long-term trend dashboard. Apply the sum_val and cnt passthrough pattern at this step too.
3. Register a compress_after policy and check pg_size_pretty(pg_total_relation_size('metrics')) a week later — you'll be able to see the compression effect firsthand.

References

Domain note: The references below include both tigerdata.com and timescale.com. Both domains are official documentation sites for the same service, and either can be used to access the same content.

• TimescaleDB GitHub Releases & CHANGELOG
• Official Docs — Continuous Aggregates Overview
• Official Docs — Refresh Policies
• Official Docs — add_compression_policy()
• Official Docs — Hypercore
• Incremental CAgg Refresh Policy PR #7790
• TimescaleDB Compression: From 150GB to 15GB (Dev.to, 2024)
• Real-Time Analytics for Time Series — Medium/Timescale
• How Cloudflare Uses TimescaleDB
• Storing 100k Events/sec with TimescaleDB — Medium
• TimescaleDB Hypertables, Continuous Aggregates & Compression Production Guide
• Hypercore: Hybrid Row-Columnar Storage Engine (Official Blog)
• TimescaleDB Compression Guide — DEV Community