--- title: add_compaction_policy() | Tiger Data Docs description: Set a policy to automatically compact unordered chunks in the columnstore --- Tech preview [2.29.0](https://github.com/timescale/timescaledb/releases/tag/2.29.0) Create a [job](/reference/timescaledb/jobs-automation/add_job/index.md) 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`](/reference/timescaledb/hypertables/create_table#arguments/index.md) option to `true` in `CREATE TABLE` or `ALTER TABLE` will generate the policy immediately. [Direct compress](/build/data-management/write-data/insert#improve-performance-with-direct-compress/index.md) 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](/reference/timescaledb/hypercore/add_columnstore_policy/index.md) handles, and frozen chunks. If your hypertable doesn't use [direct compress](/build/data-management/write-data/insert#improve-performance-with-direct-compress/index.md), 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](/reference/timescaledb/informational-views/jobs/index.md). ## 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](/build/data-management/write-data/insert#improve-performance-with-direct-compress/index.md) 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`](/reference/timescaledb/jobs-automation/alter_job/index.md) 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 = '', if_not_exists = true | false, schedule_interval = , initial_start = , timezone = '', max_chunks = , max_batches = , inactive_for = ); ``` | Name | Type | Default | Required | Description | | ------------------- | ----------- | ----------- | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `hypertable` | REGCLASS | - | ✔ | Name of the hypertable to run this [job](/reference/timescaledb/jobs-automation/add_job/index.md) 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 |