Refresh continuous aggregates

Set up automatic refresh policies or refresh your aggregates manually to ensure you have the latest aggregation data

s can have a range of different refresh policies. In addition to refreshing the automatically using a policy, you can also refresh it manually.

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 .

Change the refresh policy

s require a policy for automatic refreshing. You can adjust this to suit different use cases. For example, you can have the and the stay in sync, even when data is removed from the . Alternatively, you could keep source data in the even after it is removed from the .

You can change the way your is refreshed by calling add_continuous_aggregate_policy.

Among others, add_continuous_aggregate_policy takes the following arguments:

• start_offset: the start of the refresh window relative to when the policy runs
• end_offset: the end of the refresh window relative to when the policy runs
• schedule_interval: the refresh interval in minutes or hours. Defaults to 24 hours.

Note the following:

• If you set the start_offset or end_offset to NULL, the range is open-ended and extends to the beginning or end of time.

• If you set end_offset within the current time bucket, this bucket is excluded from materialization. This is done for the following reasons:

  • The current bucket is incomplete and can’t be refreshed.
  • The current bucket gets a lot of writes in the timestamp order, and its aggregate becomes outdated very quickly. Excluding it improves performance.

  To include the latest raw data in queries, enable real-time aggregation.

See the API reference for the full list of required and optional arguments and use examples.

The policy in the following example ensures that all data in the is up to date with the , except for data written within the last hour of wall-clock time. The policy also does not refresh the last time bucket of the .

Since the policy in this example runs once every hour (schedule_interval) while also excluding data within the most recent hour (end_offset), it takes up to 2 hours for data written to the to be reflected in the . Backfills, which are usually outside the most recent hour of data, will be visible after up to 1 hour depending on when the policy last ran when the data was written.

Because it has an open-ended start_offset parameter, any data that is removed from the table, for example with a DELETE or with drop_chunks, is also removed from the view. This means that the always reflects the data in the underlying .

To change a refresh policy to use a NULL start_offset:

1. Connect to your

   In open an SQL editor. You can also connect to your using psql.

2. Create a refresh policy

   Create a new policy on conditions_summary_hourly that keeps the up to date, and runs every hour:

   SELECT add_continuous_aggregate_policy('conditions_summary_hourly',
     start_offset => NULL,
     end_offset => INTERVAL '1 h',
     schedule_interval => INTERVAL '1 h');
   

   If you want to keep data in the even if it is removed from the underlying , you can set the start_offset to match the data retention policy on the source . For example, if you have a retention policy that removes data older than one month, set start_offset to one month or less. This sets your policy so that it does not refresh the dropped data.

3. Keep data after removal from hypertable

   Create a new policy on conditions_summary_hourly that keeps data removed from the in the , and runs every hour:

   SELECT add_continuous_aggregate_policy('conditions_summary_hourly',
     start_offset => INTERVAL '1 month',
     end_offset => INTERVAL '1 h',
     schedule_interval => INTERVAL '1 h');
   

It is important to consider your data retention policies when you’re setting up policies. If the policy window covers data that is removed by the data retention policy, the data will be removed when the aggregates for those buckets are refreshed. For example, if you have a data retention policy that removes all data older than two weeks, the policy will only have data for the last two weeks.

Add concurrent refresh policies

You can add concurrent refresh policies on each , as long as their start and end offsets don’t overlap. For example, to backfill data into older chunks you set up one policy that refreshes recent data, and another that refreshes backfilled data.

The first policy in this example keeps the up to date with data that was inserted in the past day. Any data that was inserted or updated for previous days is refreshed by the second policy.

1. Connect to your

   In open an SQL editor. You can also connect to your using psql.

2. Create a refresh policy for recent data

   Create a new policy on conditions_summary_daily to refresh the with recently inserted data which runs hourly:

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

3. Create a concurrent policy for backfilled data

   Create a concurrent policy on conditions_summary_daily to refresh the with backfilled data:

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

Manually refresh a continuous aggregate

If you need to manually refresh a , you can use the refresh_continuous_aggregate command. This recomputes the data within the window that has changed in the underlying since the last refresh. Therefore, if only a few buckets need updating, the refresh runs quickly.

If you have recently dropped data from a with a , calling refresh_continuous_aggregate on a region containing dropped chunks recalculates the aggregate without the dropped data. See drop data for more information.

The refresh_continuous_aggregate command takes three arguments:

• The name of the view to refresh
• The timestamp of the beginning of the refresh window
• The timestamp of the end of the refresh window

Only buckets that are wholly within the specified range are refreshed. For example, if you specify 2021-05-01', '2021-06-01 the only buckets that are refreshed are those up to but not including 2021-06-01. It is possible to specify NULL in a manual refresh to get an open-ended range, but we do not recommend using it, because you could inadvertently materialize a large amount of data, slow down your performance, and have unintended consequences on other policies like data retention.

To manually refresh a , use the refresh_continuous_aggregate command:

CALL refresh_continuous_aggregate('example', '2021-05-01', '2021-06-01');

Follow the logic used by automated refresh policies and avoid refreshing time buckets that are likely to have a lot of writes. This means that you should generally not refresh the latest incomplete time bucket. To include the latest raw data in your queries, use real-time aggregation instead.