add_policies()

Add refresh, compression, and data retention policies to a continuous aggregate in one step. The added compression and retention policies apply to the continuous aggregate, not to the original hypertable.

timescaledb_experimental.add_policies(
     relation REGCLASS,
     if_not_exists BOOL = false,
     refresh_start_offset "any" = NULL,
     refresh_end_offset "any" = NULL,
     compress_after "any" = NULL,
     drop_after "any" = NULL
) RETURNS BOOL

Copy

Warning

Experimental features could have bugs. They might not be backwards compatible, and could be removed in future releases. Use these features at your own risk, and do not use any experimental features in production.

Note

add_policies() does not allow the schedule_interval for the continuous aggregate to be set, instead using a default value of 1 hour.

If you would like to set this add your policies manually (see add_continuous_aggregate_policy).

For arguments that could be either an INTERVAL or an INTEGER, use an INTERVAL if your time bucket is based on timestamps. Use an INTEGER if your time bucket is based on integers.

Returns true if successful.

Given a continuous aggregate named example_continuous_aggregate, add three policies to it:

1. Regularly refresh the continuous aggregate to materialize data between 1 day and 2 days old.
2. Compress data in the continuous aggregate after 20 days.
3. Drop data in the continuous aggregate after 1 year.

SELECT timescaledb_experimental.add_policies(
    'example_continuous_aggregate',
    refresh_start_offset => '1 day'::interval,
    refresh_end_offset => '2 day'::interval,
    compress_after => '20 days'::interval,
    drop_after => '1 year'::interval
);

Copy