Setup continuous aggregates

Create, refresh, and query continuous aggregates in TimescaleDB

Creating a is a two-step process: create the view, then add a refresh policy to keep it updated. You can create the view on a , or on top of another . You can have more than one on each source table or view.

s require a time_bucket on the time partitioning column of the . By default, views are automatically refreshed. You can adjust this by setting the WITH NO DATA option. Additionally, the view cannot be a security barrier view.

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

Prerequisites

To follow the steps on this page:

• Create a target with Real-time analytics enabled.

  You need your connection details. This procedure also works for .

Create a continuous aggregate

In this example, we create a for daily weather data from a called conditions. The GROUP BY clause must include a time_bucket expression using the time dimension column. All functions and their arguments in SELECT, GROUP BY, and HAVING clauses must be immutable.

1. Create the materialized view

   CREATE MATERIALIZED VIEW conditions_summary_daily
   WITH (timescaledb.continuous) AS
   SELECT device,
      time_bucket(INTERVAL '1 day', time) AS bucket,
      AVG(temperature),
      MAX(temperature),
      MIN(temperature)
   FROM conditions
   GROUP BY device, bucket;
   

   To create a within a transaction block, use the WITH NO DATA option.

   To improve performance, set timescaledb.invalidate_using = 'wal' ( 2.22.0+).

2. Create a refresh policy

   SELECT add_continuous_aggregate_policy('conditions_summary_daily',
     start_offset => INTERVAL '1 month',
     end_offset => INTERVAL '1 day',
     schedule_interval => INTERVAL '1 hour');
   

   For more information, see add_continuous_aggregate_policy.

Choose a time bucket interval

s require a time_bucket on the time partitioning column of the . 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 . This is because you need access to previous data to determine the gapfill content, which isn’t yet available when you create the . You can work around this by creating the using time_bucket, then querying the using time_bucket_gapfill.

Create with the WITH NO DATA option

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 . 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 policy using the WITH NO DATA option.

The WITH NO DATA option allows the 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 . 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.

CREATE MATERIALIZED VIEW cagg_rides_view
WITH (timescaledb.continuous) AS
SELECT vendor_id,
  time_bucket('1h', pickup_datetime) AS hour,
  count(*) total_rides,
  avg(fare_amount) avg_fare
FROM rides
GROUP BY vendor_id, time_bucket('1h', pickup_datetime)
WITH NO DATA;

Then manually refresh historical data using refresh_continuous_aggregate and add the policy:

CALL refresh_continuous_aggregate('cagg_rides_view', NULL, localtimestamp - INTERVAL '1 week');

SELECT add_continuous_aggregate_policy('cagg_rides_view',
  start_offset => INTERVAL '1 week',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '30 minutes');

Create a continuous aggregate with JOINs

In 2.10+ with v12+, create s with JOIN clauses:

CREATE MATERIALIZED VIEW conditions_summary_daily
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;

See s with JOINs for restrictions.

Use continuous aggregates with mutable functions

Early access

Mutable functions have experimental support in the 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 refreshes run with a consistent setting for this.

Use continuous aggregates with window functions

Early access

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

Support is experimental, there is a risk of data inconsistency. For example, in backfill scenarios, buckets could be missed.

Create a window function

To use a window function in a :

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

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

2. Enable window functions

   As window functions are experimental, in order to create s with window functions, you have to enable enable_cagg_window_functions.

   SET timescaledb.enable_cagg_window_functions TO TRUE;
   

3. Use time buckets

   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;
   

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

Window function workaround

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

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

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

2. Create a 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;
   

3. Use the lag window function on your 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;
   

Query continuous aggregates

Query s with standard SELECT queries:

SELECT *
  FROM conditions_summary_hourly
  WHERE device = 5
  AND bucket >= '2020-01-01'
  AND bucket < '2020-04-01';

Or find the top 20 largest metric spreads:

SELECT *
  FROM conditions_summary_hourly
  WHERE max - min > 1800
  AND bucket >= '2020-01-01' AND bucket < '2020-04-01'
  ORDER BY bucket DESC, device DESC LIMIT 20;