Automatically expire old data in ClickHouse MergeTree tables using column-level and table-level TTL clauses, including how to force materialization.

Documented steps

1. Add column TTL when creating the table to expire individual column values (replaced with the type's default, not the whole row) after an interval: CREATE TABLE example1 (timestamp DateTime, x UInt32 TTL timestamp + INTERVAL 1 MONTH, y String TTL timestamp + INTERVAL 1 DAY, z String) ENGINE = MergeTree ORDER BY tuple();
2. Add table-level TTL to delete whole rows once expired: CREATE TABLE customers (timestamp DateTime, name String, balance Int32, address String) ENGINE = MergeTree ORDER BY timestamp TTL timestamp + INTERVAL 12 HOUR;
3. Optionally scope row deletion with WHERE conditions, and use multiple TTL expressions: TTL time + INTERVAL 1 MONTH DELETE WHERE event != 'error', time + INTERVAL 6 MONTH DELETE WHERE event = 'error';
4. Add or change TTL on an existing table with ALTER TABLE ... MODIFY COLUMN <col> <type> TTL <expr>, or ALTER TABLE ... MODIFY TTL <expr> for table-level rules; then force the new rule to apply to already-existing parts with ALTER TABLE customers MATERIALIZE TTL;
5. Understand that TTL deletion/rollup is not immediate — it only happens during background merges. Two settings control how often it is retried in the absence of a normal merge: merge_with_ttl_timeout (default 14400s/4h) for delete TTL, and merge_with_recompression_ttl_timeout (default 14400s/4h) for recompression TTL.
6. To force expiration sooner (not recommended for routine use), run OPTIMIZE TABLE example1 FINAL, which triggers an unscheduled merge that reoptimizes even a single-part table.
7. For efficient bulk drops, partition your table so the partition key aligns with the TTL time field (e.g. PARTITION BY toYYYYMMDD(date_field) for day/week TTLs, or toYYYYMM(date_field) for month/year TTLs) — ClickHouse can then drop entire expired partitions instead of rewriting parts to remove individual rows; this behavior is governed by the ttl_only_drop_parts MergeTree setting (default 0).
8. Reference: https://clickhouse.com/docs/guides/developer/ttl