add_compression_policy.md

api_name	add_compression_policy()
excerpt	Add policy to schedule automatic compression of chunks
topics	compression jobs
keywords	compression policies
tags	scheduled jobs background jobs automation framework
api	license type community function
products	cloud mst self_hosted

import Deprecated2180 from "versionContent/_partials/_deprecated_2_18_0.mdx";

add_compression_policy()

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 chunk automatically in the background after it reaches a given age.

Compression policies can only be created on hypertables or continuous aggregates that already have compression enabled. To set timescaledb.compress and other configuration parameters for hypertables, use the ALTER TABLE command. To enable compression on continuous aggregates, 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 chunks older than 60 days on the cpu hypertable.

SELECT add_compression_policy('cpu', compress_after => INTERVAL '60d');

Add a policy to compress chunks created 3 months before on the 'cpu' hypertable.

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 chunks. Whereas, when compress_created_before is used then the chunks which were created 3 months ago are selected.

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

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

Add a policy to compress chunks of a continuous aggregate called cpu_weekly, that are older than eight weeks:

SELECT add_compression_policy('cpu_weekly', INTERVAL '8 weeks');

Required arguments

Name	Type	Description
hypertable	REGCLASS	Name of the hypertable or continuous aggregate
compress_after	INTERVAL or INTEGER	The age after which the policy job compresses chunks. compress_after is calculated relative to the current time, so chunks containing data older than now - {compress_after}::interval are compressed. This argument is mutually exclusive with compress_created_before.
compress_created_before	INTERVAL	Chunks 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 continuous aggregates yet. This argument is mutually exclusive with compress_after.

The compress_after parameter should be specified differently depending on the type of the time column of the hypertable or continuous aggregate:

• 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

Name	Type	Description
schedule_interval	INTERVAL	The interval between the finish time of the last execution and the next start. Defaults to 12 hours for hyper tables with a chunk_interval >= 1 day and chunk_interval / 2 for all other hypertables.
initial_start	TIMESTAMPTZ	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
timezone	TEXT	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. Defaults to NULL.
if_not_exists	BOOLEAN	Setting to true causes the command to fail with a warning instead of an error if a compression policy already exists on the hypertable. Defaults to false.