add_continuous_aggregate_policy() Community

Create a policy that automatically refreshes a continuous aggregate. When the policy runs it refreshes the continuous aggregate in the refresh window range given by the start_offset and end_offset relative the current time when the policy executes. Since a refresh only materializes full buckets, and the policy execution time likely won’t align with the end of a bucket, the policy’s refresh window must be at least two buckets in size to ensure that at least one bucket is refreshed when the policy runs. Note that the manual refresh function refresh_continuous_aggregate allows specifying a window of only one bucket, since it is possible to manually align its refresh window with bucket boundaries.

Required Arguments

NameDescription
continuous_aggregate(REGCLASS) The continuous aggregate to add the policy for.
start_offset(INTERVAL or integer) Start of the refresh window as an interval relative to the time when the policy is executed
end_offset(INTERVAL or integer) End of the refresh window as an interval relative to the time when the policy is executed
schedule_interval(INTERVAL) Interval between refresh executions in wall-clock time.

The start_offset should be greater than end_offset. The start_offset and end_offset parameters should be specified differently depending on the type of the time column of the hypertable:

  • For hypertables with TIMESTAMP, TIMESTAMPTZ, and DATE time columns: the offset should be an INTERVAL type
  • For hypertables with integer-based timestamps: the offset should be an integer type.

Optional Arguments

NameDescription
if_not_exists(BOOLEAN) Set to true to avoid throwing an error if the continuous aggregate policy already exists. A notice is issued instead. Defaults to false.

Returns

ColumnDescription
job_id(INTEGER) TimescaleDB background job id created to implement this policy

Errors

An error will be given if:

  • The start_offset or end_offset specify a range less than two buckets in size.
  • The type of start_offset or end_offset is not compatible with the time type of the continuous aggregate.

Sample Usage

Add a policy that refreshes the last month once an hour, excluding the latest hour from the aggregate:

  1. SELECT add_continuous_aggregate_policy('conditions_summary',
  2.     start_offset => INTERVAL '1 month',
  3.     end_offset => INTERVAL '1 hour',
  4.     schedule_interval => INTERVAL '1 hour');

Note that, for performance reasons, it is recommended to exclude buckets that still see lots of writes.