Skip to main content
Create a policy that automatically refreshes a . 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

NameTypeDefaultRequiredDescription
continuous_aggregateREGCLASS-✔The to add the policy for
start_offsetINTERVAL 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 .
end_offsetINTERVAL 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 .
schedule_intervalINTERVAL24 hours✔Interval between refresh executions in wall-clock time.
initial_startTIMESTAMPTZNULL✔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_existsBOOLEANfalse-Set to true to issue a notice instead of an error if the job already exists.
timezoneTEXTNULL-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_dataBOOLEANNULL-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_batchINTEGER1-Number of buckets to be refreshed by a batch. This value is multiplied by the CAgg bucket width to determine the size of the batch range. Default value is 1, single batch execution. Values of less than 0 are not allowed.
max_batches_per_executionINTEGER0-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_firstBOOLEANTRUE-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 :
  • For s with TIMESTAMP, TIMESTAMPTZ, and DATE time columns, set the offset as an INTERVAL type.
  • For s 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 , as long as the start_offset and end_offset does not overlap with another policy on the same .
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 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

ColumnTypeDescription
job_idINTEGER background job ID created to implement this policy