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. makes aggregating data lightning fast, accurate, and easy with s.

s in are a kind of 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 behind the is automatically updated in the background.

s have a much lower maintenance burden than regular 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 s are based on s, you can query them in exactly the same way as your other tables. This includes s in the , compressed into the , or tiered to object storage. You can even create s on top of your s, 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 s, see the documentation in Use products.

Samples

Create a

Create a 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 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

Refresh a specific time range in the :

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

Query a

Query the 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 s

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

Refresh s

• refresh_continuous_aggregate(): manually refresh a

Manage policies

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

• remove_continuous_aggregate_policy(): remove a refresh policy from a

Experimental policy management

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