Allows you to set a policy by which the system will compress a chunk automatically in the background after it reaches a given age.

Note that compression policies can only be created on hypertables that already have compression enabled, e.g., via the ALTER TABLE command to set timescaledb.compress and other configuration parameters.

Required Arguments

hypertableREGCLASSName of the hypertable
compress_afterINTERVAL or INTEGERThe age after which the policy job will compress chunks

The compress_after parameter 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 time interval should be an INTERVAL type.
  • For hypertables with integer-based timestamps: the time interval should be an integer type (this requires the integer_now_func to be set).

Optional Arguments

if_not_existsBOOLEANSetting to true will cause the command to fail with a warning instead of an error if a compression policy already exists on the hypertable. Defaults to false.

Sample Usage

Add a policy to compress chunks older than 60 days on the 'cpu' hypertable.

SELECT add_compression_policy('cpu', INTERVAL '60d');

Add a compress chunks policy to a hypertable with an integer-based time column:

SELECT add_compression_policy('table_with_bigint_time', BIGINT '600000');