add_compaction_policy()

Tech preview 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. Setting the hypertable timescaledb.direct_compress option to true in CREATE TABLE or ALTER TABLE will generate the policy immediately.

Direct compress is in 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.

Why compaction suits append-only workloads

Append-only workloads, such as IoT telemetry or observability metrics, mostly add new rows and almost never update or delete existing ones. Direct compress with compaction is designed for exactly this pattern.

As rows get ingested, direct compress builds batches per segmentby and writes the batches immediately to the columnstore. Transactions should have at least 10 tuples to batch. Below that, a batch isn't worth the overhead of compressing: it adds batch-level bookkeeping without enough rows to benefit from, which hurts both query performance and the compression ratio. Even at 50 rows, though, a batch is still well short of the roughly 1000 rows per segmentby value that the columnstore is optimized for, so freshly written batches are usually smaller than ideal.

For example, a low-cardinality segmentby, like a handful of regions or a couple of thousands of sites, lets each INSERT or COPY contribute many rows to the same batch. A high-cardinality segmentby, like a device_id with millions of distinct values, spreads the same INSERT or COPY across many segment values instead.

In an append-only workload, that pattern repeats with every incoming batch of writes: each one leaves behind another small, unordered batch for the same segmentby value in the same chunk, overlapping the batches already written by previous inserts. Because nothing updates those older rows, it's safe to merge these overlapping batches together as soon as a chunk stops receiving new writes. There's no concurrent write to conflict with. That's what this policy automates: it finds unordered chunks and consolidates their overlapping batches into fewer, larger ones, closer to that 1000-row target, which restores chunk order for queries and improves the compression ratio.

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');

• Tune the policy for an append-only, high-cardinality workload:

  A hypertable ingesting per-device metrics, segmented by a high-cardinality device_id, accumulates many small, overlapping batches as direct compress writes each incoming batch straight to the columnstore. Since the workload never modifies existing rows, a short inactive_for is enough to safely compact each chunk as soon as it stops receiving new writes.

  tsdb.direct_compress creates the compaction policy automatically, so tune the job it created with alter_job rather than adding a second one:

  CREATE TABLE metrics (
     time      TIMESTAMPTZ NOT NULL,
     device_id TEXT,
     value     DOUBLE PRECISION
  ) WITH (
     tsdb.hypertable,
     tsdb.partition_column = 'time',
     tsdb.segmentby = 'device_id',
     tsdb.direct_compress
  );
  
  -- Find the job ID of the compaction policy direct compress created:
  SELECT job_id FROM timescaledb_information.jobs
  WHERE proc_name = 'policy_compaction' AND hypertable_name = 'metrics';
  
  -- Run it more often, and only touch chunks that have gone quiet:
  SELECT alter_job(job_id,
     schedule_interval => INTERVAL '1 minute',
     config_merge => '{"inactive_for": "5 minutes"}')
  FROM timescaledb_information.jobs
  WHERE proc_name = 'policy_compaction' AND hypertable_name = 'metrics';

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