Skip to main content
Old API since v2.18.0. Superseded by add_columnstore_policy(). However, compression APIs are still supported, you do not need to migrate to the hypercore APIs. Allows you to set a policy by which the system compresses a automatically in the background after it reaches a given age. Compression policies can only be created on s or s that already have compression enabled. To set timescaledb.compress and other configuration parameters for s, use the ALTER TABLE command. To enable compression on s, use the ALTER MATERIALIZED VIEW command. To view the policies that you set or the policies that already exist, see informational views.

​
Samples

Add a policy to compress s older than 60 days on the cpu . 
SELECT add_compression_policy('cpu', compress_after => INTERVAL '60d');
Add a policy to compress s created 3 months before on the ‘cpu’ . 
SELECT add_compression_policy('cpu', compress_created_before => INTERVAL '3 months');
Note above that when compress_after is used then the time data range present in the partitioning time column is used to select the target s. Whereas, when compress_created_before is used then the s which were created 3 months ago are selected. Add a compress s policy to a with an integer-based time column: 
SELECT add_compression_policy('table_with_bigint_time', BIGINT '600000');
Add a policy to compress s of a called cpu_weekly, that are older than eight weeks: 
SELECT add_compression_policy('cpu_weekly', INTERVAL '8 weeks');

​
Arguments

The syntax is: 
SELECT add_compression_policy(
    hypertable = '<hypertable_name>',
    compress_after = <interval>,
    if_not_exists = true | false,
    schedule_interval = <interval>,
    initial_start = <timestamptz>,
    timezone = '<timezone>',
    compress_created_before = <interval>
);
NameTypeDefaultRequiredDescription
hypertableREGCLASS-✔Name of the or
compress_afterINTERVAL or INTEGER-✔The age after which the policy job compresses s. compress_after is calculated relative to the current time, so s containing data older than now - compress_after::interval are compressed. This argument is mutually exclusive with compress_created_before.
compress_created_beforeINTERVALNULL-s with creation time older than this cut-off point are compressed. The cut-off point is computed as now() - compress_created_before. Defaults to NULL. Not supported for s yet. This argument is mutually exclusive with compress_after.
schedule_intervalINTERVAL12 hours for s with chunk_interval >= 1 day, chunk_interval / 2 for others-The interval between the finish time of the last execution and the next start.
initial_startTIMESTAMPTZNULL-Time the policy is first run. Defaults to NULL. If omitted, then the schedule interval is the interval from the finish time of the last execution to the next start. If provided, it serves as the origin with respect to which the next_start is calculated
timezoneTEXTNULL-A valid time zone. If initial_start is also specified, subsequent executions of the compression policy are aligned on its initial start. However, daylight savings time (DST) changes may shift this alignment. Set to a valid time zone if this is an issue you want to mitigate. If omitted, UTC bucketing is performed.
if_not_existsBOOLEANfalse-Setting to true causes the command to fail with a warning instead of an error if a compression policy already exists on the .
The compress_after parameter should be specified differently depending on the type of the time column of the or :
  • For s with TIMESTAMP, TIMESTAMPTZ, and DATE time columns: the time interval should be an INTERVAL type.
  • For s with integer-based timestamps: the time interval should be an integer type (this requires the integer_now_func to be set).