Create a continuous aggregate

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 have more than one continuous aggregate on a single hypertable.

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.

Create a continuous aggregate

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.

Creating a continuous aggregate

  1. At the psqlprompt, create the materialized view:

    1. CREATE MATERIALIZED VIEW conditions_summary_daily
    3. WITH (timescaledb.continuous) AS
    5. SELECT device,
    7.    time_bucket(INTERVAL '1 day', time) AS bucket,
    9.    AVG(temperature),
    11.    MAX(temperature),
    13.    MIN(temperature)
    15. FROM conditions
    17. GROUP BY device, bucket;

  2. Create a policy to refresh the view every hour:

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

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

Choosing an appropriate bucket interval

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.

When the continuous aggregate is materialized, the materialization table stores partials, which are then used to calculate the result of the query. This means a certain amount of processing capacity is required for any query, and the amount required becomes greater as the interval gets smaller. Because of this, if you have very small intervals, it can be more efficient to run the aggregate query on the raw data in the hypertable. We recommend that you test both methods to determine what is best for your data set and desired bucket interval.

You can read more about the time bucket function in our API Guide. If you want to use time_bucket_gapfill, you need to run it in the SELECT statement on the continuous aggregate view, you can not run it in the continuous aggregate directly.

Using 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 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.

Creating a continuous aggregate with the WITH NO DATA option

  1. At the psql prompt, create the view:

    1. CREATE MATERIALIZED VIEW cagg_rides_view
    3. WITH (timescaledb.continuous) AS
    5. SELECT vendor_id,
    7. time_bucket('1h', pickup_datetime) AS hour,
    9.   count(*) total_rides,
    11.   avg(fare_amount) avg_fare,
    13.   max(trip_distance) as max_trip_distance,
    15.   min(trip_distance) as min_trip_distance
    17. FROM rides
    19. GROUP BY vendor_id, time_bucket('1h', pickup_datetime)
    21. WITH NO DATA;

  2. Manually refresh the view:

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

  3. Add the policy:

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

Query continuous aggregates

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, joins, 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.

Querying a continuous aggregate

  1. At the psql prompt, query the continuous aggregate view called conditions_summary_hourly for the average, minimum, and maximum temperatures for the first quarter of 2021 recorded by device 5:

    1. SELECT *
    3.   FROM conditions_summary_hourly
    5.   WHERE device = 5
    7.   AND bucket >= '2020-01-01'
    9.   AND bucket < '2020-04-01';

  2. Alternatively, query the continuous aggregate view called conditions_summary_hourly for the top 20 largest metric spreads in that quarter:

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

Use continuous aggregates with window functions

Continuous aggregates don’t currently support window functions. You can work around this by:

  1. Creating a continuous aggregate for the other parts of your query, then
  2. Using the window function on your continuous aggregate at query time

For example, say you have a hypertable named example with a time column and a value column. You bucket your data by time and calculate the delta between time buckets using the lag window function:

  2. WITH t AS (
  4.   SELECT
  6.     time_bucket('10 minutes', time) as bucket,
  8.     first(value, time) as value
  10.   FROM example GROUP BY bucket
  12. )
  14. SELECT
  16.   bucket,
  18.   value - lag(value, 1) OVER (ORDER BY bucket) delta
  20.   FROM t;

You can’t create a continuous aggregate using this query, because it contains the lag function. But you can create a continuous aggregate by excluding the lag function:

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

Then, at query time, calculate the delta by using lag on your continuous aggregate:

  2. SELECT
  4.   bucket,
  6.   value - lag(value, 1) OVER (ORDER BY bucket) AS delta
  8. FROM example_aggregate;

This speeds up your query by calculating the aggregation ahead of time. The delta still needs to be calculated at query time.