Automatically route queries to continuous aggregates

Since 2.27.0

Warning

Experimental features could have bugs. They might not be backwards compatible, and could be removed in future releases. Use these features at your own risk, and do not use any experimental features in production.

When you enable timescaledb.enable_cagg_rewrites, TimescaleDB can transparently rewrite a query against a hypertable to read from a matching continuous aggregate instead. If a continuous aggregate aggregates exactly the columns and time buckets the query asks for, TimescaleDB replaces the hypertable scan with a continuous aggregate scan, which is much faster on large datasets.

How continuous aggregate query rewrites work

When you query a hypertable with SELECT … GROUP BY time_bucket(…), TimescaleDB looks for a continuous aggregate on that hypertable whose definition matches the query’s grouping columns, time_bucket width, and aggregates. If one matches, the planner replaces the hypertable scan with a scan of the continuous aggregate‘s materialization — the rewrite is invisible to the application.

The match must be exact. TimescaleDB does not reaggregate (for example, it does not roll up 1-hour continuous aggregate buckets into 2-hour query buckets).

Use continuous aggregate query rewrites

Turn the rewriting on:

SET timescaledb.enable_cagg_rewrites = on;

A second setting controls diagnostics:

SET timescaledb.cagg_rewrites_debug_info = on;

With cagg_rewrites_debug_info on, TimescaleDB prints whether each query is eligible for rewriting and, if not, why. Set this on while enable_cagg_rewrites is off to inspect which queries would be rewritten without changing plans.

Limitations

• Exact match required. The query must aggregate the same columns with the same time_bucket width as the target continuous aggregate. TimescaleDB does not roll up smaller buckets into larger ones to match a query.
• Real-time continuous aggregates only. A continuous aggregate with the default timescaledb.materialized_only = true is not eligible. Set materialized_only = false on the continuous aggregate you want the rewriter to use.
• PostgreSQL 16 or later. Rewrites are not supported on PostgreSQL 15.