Automating Data Expiration in ClickHouse with TTL on MergeTree Tables

Desired outcome

Automatically remove or move rows that are older than a defined age, reducing storage costs and keeping query performance high on recent data without manual cleanup scripts.

Prerequisites

• A table using a MergeTree-family engine (e.g., MergeTree, ReplicatedMergeTree, AggregatingMergeTree).
• A column that can express the age of data (typically a DateTime or Date).
• Sufficient disk space for background merges and the ability to run ALTER TABLE (requires the ALTER privilege).
• A safety net: either a replica (for Replicated* tables) or a recent backup/shadow table.
• Understanding that TTL actions are asynchronous; disk space is not freed immediately.

Procedure

1. Define or add TTL

   If creating a new table, include the TTL clause in CREATE TABLE. To add TTL to an existing table, use ALTER TABLE … MODIFY TTL.

   -- Example: table storing web logs with a timestamp column
   CREATE TABLE IF NOT EXISTS default.web_logs (
       event_time DateTime,
       user_id UInt64,
       url String,
       status UInt16
   ) ENGINE = MergeTree()
   PARTITION BY toYYYYMM(event_time)
   ORDER BY (user_id, event_time)
   TTL event_time < now() - INTERVAL 30 DAY TO DELETE;
   
   -- To add TTL to an existing table:
   ALTER TABLE default.web_logs MODIFY TTL event_time < now() - INTERVAL 30 DAY TO DELETE;

   Run this on the ClickHouse client (clickhouse-client) or via HTTP interface with a user that has ALTER rights on the target database.

2. Materialize the TTL change

   TTL rules are evaluated during background merges. To force immediate evaluation (useful for validation), run OPTIMIZE TABLE … FINAL.

   OPTIMIZE TABLE default.web_logs FINAL;

   This command merges all parts and applies the TTL deletion mutation. It requires sufficient I/O bandwidth; on a busy cluster consider running during low‑traffic windows.

3. Monitor progress

   Check system tables to confirm that the mutation completed and that parts are marked for deletion.

   -- See mutation status
   SELECT mutation_id, command, is_done, latest_failed_part, num_parts
   FROM system.mutations
   WHERE table = 'web_logs' AND database = 'default';
   
   -- Observe parts and their active/deleted rows
   SELECT partition, active, rows, marks, bytes
   FROM system.parts
   WHERE table = 'web_logs' AND database = 'default'
   ORDER BY partition;

   The is_done column should be 1 for the TTL mutation. In system.parts, look for a decrease in rows or bytes for partitions older than the TTL threshold.

Expected checks

• Mutation completion: Query system.mutations as shown; ensure is_done = 1 and no latest_failed_part.
• Row count reduction: Compare SELECT count(*) FROM default.web_logs; before and after the optimize.
• Disk usage trend: Use SELECT sum(bytes) FROM system.parts WHERE table = 'web_logs' AND database = 'default'; to verify a downward trend over time.
• Partition health: Confirm that partitions older than the TTL boundary show zero active rows (or are absent) while newer partitions retain data.

Recovery options

• Disable TTL: If you need to stop further expirations, run ALTER TABLE default.web_logs MODIFY TTL TO; (empty TTL disables the rule). Existing deletions remain; new rows will not be auto‑removed.
• Restore from replica: For Replicated* tables, the replica retains the original data until its own TTL runs. You can detach the affected partition on the primary and attach it from the replica using ALTER TABLE … DETACH PARTITION and ALTER TABLE … ATTACH PARTITION.
• Replay from source: If the table is populated from an external source (e.g., Kafka, batch load), you can re‑ingest the missing rows after fixing the TTL expression or temporarily disabling it.

Limitations and practical tips

• TTL deletion is asynchronous; expect a lag between the moment a row becomes eligible and when its space is reclaimed. Monitor system.parts to gauge the lag.
• Changing the partition key after TTL is in place requires a table rebuild (e.g., using EXCHANGE PARTITIONS or a shadow table). Plan for downtime or use a zero‑copy swap.
• Background merges triggered by TTL can increase I/O and query latency on busy clusters. Consider adjusting max_bytes_to_merge_at_max_space_in_pool or scheduling OPTIMIZE during off‑peak hours.
• Always test TTL on a non‑production clone first; verify that the expression behaves as expected with SELECT … WHERE event_time < now() - INTERVAL 30 DAY.