add_continuous_aggregate_policy()

Since 1.7.0

Create a policy that automatically refreshes a continuous aggregate. To view the policies that you set or the policies that already exist, see informational views.

Samples

Add a policy that refreshes the last month once an hour, excluding the latest hour from the aggregate. For performance reasons, we recommend that you exclude buckets that see lots of writes:

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

Arguments

The syntax is:

SELECT add_continuous_aggregate_policy(
    continuous_aggregate = '<view_name>',
    start_offset = <interval>,
    end_offset = <interval>,
    schedule_interval = <interval>,
    if_not_exists = true | false,
    initial_start = <timestamptz>,
    timezone = '<timezone>',
    include_tiered_data = true | false,
    buckets_per_batch = <integer>,
    max_batches_per_execution = <integer>,
    refresh_newest_first = true | false
);

Name	Type	Default	Required	Description
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. NULL is equivalent to MIN(timestamp) of the hypertable.
end_offset	INTERVAL or integer	-	✔	End of the refresh window as an interval relative to the time when the policy is executed. NULL is equivalent to MAX(timestamp) of the hypertable.
schedule_interval	INTERVAL	24 hours	✔	Interval between refresh executions in wall-clock time.
initial_start	TIMESTAMPTZ	NULL	-	Time the policy is first run. Defaults to NULL. If omitted, then the schedule interval is the interval between the finish time of the last execution and the next start. If provided, it serves as the origin with respect to which the next_start is calculated
if_not_exists	BOOLEAN	false	-	Set to true to issue a notice instead of an error if the job already exists.
timezone	TEXT	NULL	-	A valid time zone. If you specify initial_start, subsequent executions of the refresh policy are aligned on initial_start. However, daylight savings time (DST) changes may shift this alignment. If this is an issue you want to mitigate, set timezone to a valid time zone. Default is NULL, UTC bucketing is performed.
include_tiered_data	BOOLEAN	NULL	-	Enable/disable reading tiered data. This setting helps override the current settings for thetimescaledb.enable_tiered_reads GUC. The default is NULL i.e we use the current setting for timescaledb.enable_tiered_reads GUC
buckets_per_batch	INTEGER	10	-	Number of buckets to be refreshed by a batch. This value is multiplied by the continuous aggregate bucket width to determine the size of the batch range. Default value is 10. Values of less than 0 are not allowed.
max_batches_per_execution	INTEGER	0	-	Limit the maximum number of batches to run when a policy executes. If some batches remain, they are processed the next time the policy runs. Default value is 0, for an unlimted number of batches. Values of less than 0 are not allowed.
refresh_newest_first	BOOLEAN	TRUE	-	Control the order of incremental refreshes. Set to TRUE to refresh from the newest data to the oldest. Set to FALSE for oldest to newest. The default is TRUE.

The start_offset should be greater than end_offset.

You must specify the start_offset and end_offset parameters differently, depending on the type of the time column of the hypertable:

• For hypertables with TIMESTAMP, TIMESTAMPTZ, and DATE time columns, set the offset as an INTERVAL type.
• For hypertables with integer-based timestamps, set the offset as an INTEGER type.

While setting end_offset to NULL is possible, it is not recommended. To include the data between end_offset and the current time in queries, enable real-time aggregation.

You can add concurrent refresh policies on each continuous aggregate, as long as the start_offset and end_offset does not overlap with another policy on the same continuous aggregate.

Setting buckets_per_batch greater than zero means that the refresh window is split in batches of bucket width * buckets per batch. For example, a given continuous aggregate with bucket width of 1 day and buckets_per_batch of 10 has a batch size of 10 days to process the refresh. Because each batch is an individual transaction, executing a policy in batches make the data visible for the users before the entire job is executed. Batches are processed from the most recent data to the oldest.

Returns

Column	Type	Description
job_id	INTEGER	TimescaleDB background job ID created to implement this policy