Continuous aggregates overview

TimescaleDB reference for calculating continuous aggregates on your data. Includes SQL functions and views related to creating, altering, and dropping continuous aggregates

In modern applications, data usually grows very quickly. This means that aggregating it into useful summaries can become very slow. If you are collecting data very frequently, you might want to aggregate your data into minutes or hours instead. For example, if an IoT device takes temperature readings every second, you might want to find the average temperature for each hour. Every time you run this query, the database needs to scan the entire table and recalculate the average. TimescaleDB makes aggregating data lightning fast, accurate, and easy with continuous aggregates.

Continuous aggregates in TimescaleDB are a kind of hypertable that is refreshed automatically in the background as new data is added, or old data is modified. Changes to your dataset are tracked, and the hypertable behind the continuous aggregate is automatically updated in the background.

Continuous aggregates have a much lower maintenance burden than regular Postgres materialized views, because the whole view is not created from scratch on each refresh. This means that you can get on with working your data instead of maintaining your database.

Because continuous aggregates are based on hypertables, you can query them in exactly the same way as your other tables. This includes continuous aggregates in the rowstore, compressed into the columnstore, or tiered to object storage. You can even create continuous aggregates on top of your continuous aggregates, for an even more fine-tuned aggregation.

Real-time aggregation enables you to combine pre-aggregated data from the materialized view with the most recent raw data. This gives you up-to-date results on every query.

For more information about using continuous aggregates, see the documentation in Use continuous aggregates.

Samples

Create a continuous aggregate

Create a continuous aggregate that calculates hourly average temperature:

CREATE MATERIALIZED VIEW conditions_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  location,
  AVG(temperature) AS avg_temp,
  MAX(temperature) AS max_temp,
  MIN(temperature) AS min_temp
FROM conditions
GROUP BY bucket, location;

Add a refresh policy

Automatically refresh the continuous aggregate to keep it up to date:

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

Manually refresh a continuous aggregate

Refresh a specific time range in the continuous aggregate:

CALL refresh_continuous_aggregate('conditions_hourly',
  '2024-01-01', '2024-02-01');

Query a continuous aggregate

Query the continuous aggregate just like a regular table:

SELECT bucket, location, avg_temp
FROM conditions_hourly
WHERE bucket >= NOW() - INTERVAL '7 days'
  AND location = 'office'
ORDER BY bucket DESC;

Available functions

Create and modify continuous aggregates

• CREATE MATERIALIZED VIEW (Continuous Aggregate): create a continuous aggregate on a hypertable or another continuous aggregate
• ALTER MATERIALIZED VIEW (Continuous Aggregate): change an existing continuous aggregate
• DROP MATERIALIZED VIEW (Continuous Aggregate): drop a continuous aggregate view
• cagg_migrate(): migrate a continuous aggregate from the old format to the new format introduced in TimescaleDB 2.7

Refresh continuous aggregates

• refresh_continuous_aggregate(): manually refresh a continuous aggregate

Manage policies

• add_continuous_aggregate_policy(): add policy to schedule automatic refresh of a

continuous aggregate

• remove_continuous_aggregate_policy(): remove a refresh policy from a continuous aggregate

Experimental policy management

• add_policies(): add refresh, compression, and data retention policies on a continuous aggregate
• alter_policies(): alter refresh, compression, or data retention policies on a continuous aggregate
• remove_policies(): remove refresh, compression, or data retention policies from a continuous aggregate
• remove_all_policies(): remove all policies from a continuous aggregate
• show_policies(): show all policies that are currently set on a continuous aggregate