ClickHouse TTL: Auto‑Purge Old Data Without Housekeeping Scripts

Problem: Manual Expiration is a Maintenance Nightmare

In high‑velocity ClickHouse deployments, data that is older than a few days or weeks can accumulate quickly. Without a cleanup routine, storage costs rise and query performance degrades as the engine scans more rows. Traditional solutions involve cron jobs that run OPTIMIZE TABLE … FINAL or delete statements, but they add operational overhead and risk inconsistent retention across shards.

Thesis: Use ClickHouse’s Built‑In TTL to Let the Engine Clean Itself

ClickHouse’s TTL clause is a declarative, background‑driven mechanism that automatically removes rows older than a specified interval. It integrates with the MergeTree engine’s compaction pipeline, so the table stays queryable while deletions happen asynchronously.

How TTL Works in MergeTree

• Definition: ALTER TABLE table MODIFY TTL column + INTERVAL n unit;
• Scope: TTL applies to whole parts (the on‑disk segments created during inserts). If any row in a part satisfies the TTL expression, the entire part is marked for deletion.
• Background Thread: The engine schedules compaction threads that scan parts, evaluate the TTL expression, and drop expired parts without blocking reads.
• Asynchronicity: Deletion can lag behind the TTL boundary if the system is under heavy load. Monitoring system.parts or system.metrics is essential to confirm that parts are being purged.

Concrete Example: Retain 7‑Day Logs

# 1. Create a MergeTree table partitioned by day
CREATE TABLE logs (
    id UInt64,
    ts DateTime,
    msg String
) ENGINE = MergeTree()
ORDER BY id
PARTITION BY toYYYYMMDD(ts);

# 2. Insert sample rows – one old, one current
INSERT INTO logs VALUES
    (1, now() - INTERVAL 10 DAY, 'old log'),
    (2, now(), 'current log');

# 3. Add a 7‑day TTL on the timestamp column
ALTER TABLE logs MODIFY TTL ts + INTERVAL 7 DAY;

# 4. (Optional) Force a compaction to see the effect immediately
SYSTEM FLUSH LOGS;

# 5. Verify the old row is gone after TTL expiry
SELECT * FROM logs;

After the 7‑day window passes, the part containing the row with ts = now() - INTERVAL 10 DAY will be marked for deletion. The row will disappear once the compaction thread processes the part. You can confirm this by querying system.parts for a part with is_marked_for_delete = 1.

Trade‑Offs and Limitations

• Write Overhead: Each insert must evaluate the TTL expression to decide if the part should be flagged. On tables with millions of writes per second, a very short TTL can become a bottleneck.
• Simple Predicate Only: TTL can only reference a single column and a straightforward expression. Complex retention logic (e.g., "keep 10 rows per user") is not supported.
• Shard Independence: In a distributed setup, each shard evaluates TTL locally. All shards must have identical TTL policies to avoid data drift.
• No Rollback: Once a part is scheduled for deletion, it cannot be resurrected. If you need to preserve specific rows, move them to a separate table or add an exclusion flag before applying TTL.

Practical Checklist for Production Use

1. Decide on a retention window that matches business needs (e.g., 30 days for logs, 90 days for analytics).
2. Partition by a daily or monthly key to reduce the amount of data scanned during compaction.
3. Apply TTL to a dedicated timestamp column; avoid using the primary key if it’s not a timestamp.
4. Monitor system.metrics for MergeTreeDeletes and MergeTreeDeletesTime to detect lag.
5. Adjust max_threads or merge_max_size if deletions start to impact insert latency.
6. For critical data, create a “protected” copy before enabling TTL, and test the deletion behavior in a staging environment.

Conclusion: Let ClickHouse Do the Heavy Lifting

TTL in ClickHouse turns data retention from a manual chore into a declarative, background process that scales with your workload. By combining TTL with thoughtful partitioning and monitoring, you can keep storage lean, queries fast, and operations simple. The next time you face a data‑bloat headache, consider adding a TTL clause and let the engine clean up for you.