add_compaction_policy()

Since 2.29.0

Create a job that automatically compacts unordered chunks in the columnstore. Compaction combines overlapping batches within a chunk so queries no longer need an extra sort step to restore order.

Note

You usually don't need to add this policy yourself. Direct compress creates it for you, and it is the main reason chunks end up unordered. Use add_compaction_policy when you want to tune the schedule or bound how much work each run does. Direct compress is a tech preview, so this policy is too.

The policy only processes chunks that are fully in the columnstore and marked as unordered. It skips partial chunks, which the columnstore policy handles, and frozen chunks. If your hypertable doesn't use direct compress, it is unlikely to have unordered chunks and this policy has nothing to do.

To view the policies that you set or the policies that already exist, see informational views.

Samples

• Add a compaction policy with the default 5 minute schedule:

  SELECT add_compaction_policy('metrics');

• Run less often, bound the work per run, and skip chunks still being written to:

  SELECT add_compaction_policy('metrics',
     schedule_interval => INTERVAL '15 minutes',
     max_chunks => 10,
     max_batches => 500,
     inactive_for => INTERVAL '30 minutes');

Arguments

The syntax is:

SELECT add_compaction_policy(
    hypertable = '<hypertable_name>',
    if_not_exists = true | false,
    schedule_interval = <interval>,
    initial_start = <timestamptz>,
    timezone = '<timezone>',
    max_chunks = <integer>,
    max_batches = <integer>,
    inactive_for = <interval>
);

Name	Type	Default	Required	Description
hypertable	REGCLASS	-	✔	Name of the hypertable to run this job on.
if_not_exists	BOOLEAN	false	✖	Set to true so this job fails with a warning rather than an error if a compaction policy already exists on hypertable.
schedule_interval	INTERVAL	5 minutes	✖	Set the interval between the finish time of the last execution of this policy and the next start. When direct compress creates this policy, it uses 1 minute instead.
initial_start	TIMESTAMPTZ	NULL	✖	Set the time this job is first run.
timezone	TEXT	NULL	✖	Set to a valid time zone to mitigate DST shifting. If initial_start is set, subsequent executions of this policy are aligned on initial_start.
max_chunks	INTEGER	NULL	✖	Set the maximum number of chunks to process in a single run, including chunks that fail. Leave unset to process every eligible chunk.
max_batches	INTEGER	NULL	✖	Set the maximum number of batches to combine in each chunk. Leave unset for no limit.
inactive_for	INTERVAL	NULL	✖	Only compact chunks that have not been written to for this interval. Leave unset to compact every eligible chunk regardless of when it was last written to.

Returns

Column	Type	Description
job_id	INTEGER	TimescaleDB background job ID created to implement this policy