Create continuous aggregates

Creating a continuous aggregate is a two-step process. You need to create the view first, then enable a policy to keep the view refreshed. You can create the view on a hypertable, or on top of another continuous aggregate. You can have more than one continuous aggregate on each source table or view.

Continuous aggregates require a time_bucket on the time partitioning column of the hypertable.

By default, views are automatically refreshed. You can adjust this by setting the WITH NO DATA option. Additionally, the view can not be a security barrier view.

Continuous aggregates use hypertables in the background, which means that they also use chunk time intervals. By default, the continuous aggregate's chunk time interval is 10 times what the original hypertable's chunk time interval is. For example, if the original hypertable's chunk time interval is 7 days, the continuous aggregates that are on top of it have a 70 day chunk time interval.

In this example, we are using a hypertable called conditions, and creating a continuous aggregate view for daily weather data. The GROUP BY clause must include a time_bucket expression which uses time dimension column of the hypertable. Additionally, all functions and their arguments included in SELECT, GROUP BY, and HAVING clauses must be immutable.

You can use most PostgreSQL aggregate functions in continuous aggregations. To see what PostgreSQL features are supported, check the function support table.

Continuous aggregates require a time_bucket on the time partitioning column of the hypertable. The time bucket allows you to define a time interval, instead of having to use specific timestamps. For example, you can define a time bucket as five minutes, or one day.

You can't use time_bucket_gapfill directly in a continuous aggregate. This is because you need access to previous data to determine the gapfill content, which isn't yet available when you create the continuous aggregate. You can work around this by creating the continuous aggregate using time_bucket, then querying the continuous aggregate using time_bucket_gapfill.

By default, when you create a view for the first time, it is populated with data. This is so that the aggregates can be computed across the entire hypertable. If you don't want this to happen, for example if the table is very large, or if new data is being continuously added, you can control the order in which the data is refreshed. You can do this by adding a manual refresh with your continuous aggregate policy using the WITH NO DATA option.

The WITH NO DATA option allows the continuous aggregate to be created instantly, so you don't have to wait for the data to be aggregated. Data begins to populate only when the policy begins to run. This means that only data newer than the start_offset time begins to populate the continuous aggregate. If you have historical data that is older than the start_offset interval, you need to manually refresh the history up to the current start_offset to allow real-time queries to run efficiently.

In Timescale 2.10 and later, with PostgreSQL 12 or later, you can create a continuous aggregate with a query that also includes a JOIN. For example:

CREATE MATERIALIZED VIEW conditions_summary_daily_3
WITH (timescaledb.continuous) AS
SELECT time_bucket(INTERVAL '1 day', day) AS bucket,
   AVG(temperature),
   MAX(temperature),
   MIN(temperature),
   name
FROM devices JOIN conditions USING (device_id)
GROUP BY name, bucket;

Copy

Note

For more information about creating a continuous aggregate with a JOIN, including some additional restrictions, see the about continuous aggregates section.

When you have created a continuous aggregate and set a refresh policy, you can query the view with a SELECT query. You can only specify a single hypertable in the FROM clause. Including more hypertables, tables, views, or subqueries in your SELECT query is not supported. Additionally, make sure that the hypertable you are querying does not have row-level-security policies enabled.

Mutable functions have experimental supported in the continuous aggregate query definition. Mutable functions are enabled by default. However, if you use them in a materialized query a warning is returned.

When using non-immutable functions you have to ensure these functions produce consistent results across continuous aggregate refresh runs. For example, if a function depends on the current time zone you have to ensure all your continuous aggregate refreshes run with a consistent setting for this.

Window functions have experimental supported in the continuous aggregate query definition. Window functions are disabled by default. To enable them, set timescaledb.enable_cagg_window_functions to true.

To use a window function in a continuous aggregate:

1. Create a simple table with to store a value at a specific time:

   CREATE TABLE example (
     time       TIMESTAMPZ        NOT NULL,
     value      TEXT              NOT NULL,
   );

   Copy

2. Enable window functions.

   As window functions are experimental, in order to create continuous aggregates with window functions. you have to enable_cagg_window_functions.

   SET timescaledb.enable_cagg_window_functions TO TRUE;

   Copy

3. Bucket your data by time and calculate the delta between time buckets using the lag window function:

   Window functions must stay within the time bucket. Any query that tries to look beyond the current time bucket will produce incorrect results around the refresh boundaries.

   CREATE MATERIALIZED VIEW example_aggregate
     WITH (timescaledb.continuous) AS
       SELECT
         time_bucket('1d', time),
         customer_id,
         sum(amount) AS amount,
         sum(amount) - LAG(sum(amount),1,NULL) OVER (PARTITION BY time_bucket('1d', time) ORDER BY sum(amount) DESC) AS amount_diff,
         ROW_NUMBER() OVER (PARTITION BY time_bucket('1d', time) ORDER BY sum(amount) DESC)
       FROM sales GROUP BY 1,2;

   Copy

   Window functions that partition by time_bucket should be safe even with LAG()/LEAD()

For TimescaleDB v2.19.3 and below, continuous aggregates do not support window functions. To work around this:

1. Create a simple table with to store a value at a specific time:

   CREATE TABLE example (
     time       TIMESTAMPZ        NOT NULL,
     value      TEXT              NOT NULL,
   );

   Copy

2. Create a continuous aggregate that does not use a window function:

   CREATE MATERIALIZED VIEW example_aggregate
     WITH (timescaledb.continuous) AS
       SELECT
         time_bucket('10 minutes', time) AS bucket,
         first(value, time) AS value
       FROM example GROUP BY bucket;

   Copy

3. Use the lag window function on your continuous aggregate at query time:

   This speeds up your query by calculating the aggregation ahead of time. The delta is calculated at query time.

   SELECT
     bucket,
     value - lag(value, 1) OVER (ORDER BY bucket) AS delta
   FROM example_aggregate;

   Copy