How does TTL work in ClickHouse for automatic data lifecycle management?

Think of a grocery store's shelf management system. Fresh produce (hot data) sits on premium refrigerated shelves (SSD), items approaching expiration get moved to discount racks (HDD cold storage), and expired items are automatically removed from shelves entirely (deletion). This tiered lifecycle happens automatically based on the expiration date stamped on each item, without manual intervention.

ClickHouse TTL enables automatic data lifecycle management without external jobs or manual partition drops. You define TTL rules at table creation or alter them later, and ClickHouse enforces them during background merges. Row-level TTL deletes entire rows when a timestamp column exceeds the specified age. Column-level TTL resets specific columns to their default values (useful for GDPR compliance where you keep aggregate data but erase PII after 30 days). Move TTL migrates data between storage policies (tiers) based on age.

Internally, TTL rules are evaluated during part merges. When ClickHouse merges parts, it checks each row's TTL expression. If the row has expired, it is excluded from the merged output (effectively deleted). For column TTL, the column value is replaced with its default. For move TTL, the entire part is written to a different disk/volume defined in the storage policy. The materialize_ttl_after_modify setting controls whether altering a TTL triggers immediate re-evaluation or waits for natural merges. TTL operations are tracked in system.part_log.

At production scale, TTL is the standard mechanism for multi-tier storage architectures. A typical setup defines a hot tier on NVMe SSDs for the last 7 days, a warm tier on standard SSDs for 7-90 days, and a cold tier on HDD or S3 for 90-365 days. After 365 days, row TTL deletes the data entirely. This reduces storage costs by 70-80% compared to keeping all data on premium storage while maintaining query access to historical data (albeit slower for cold queries).

The non-obvious gotcha is that TTL enforcement is eventual, not real-time. Expired rows remain queryable until a merge processes their part. You cannot guarantee that a row is deleted exactly at expiration time. For compliance requirements (GDPR right-to-be-forgotten), you may need to combine TTL with explicit ALTER TABLE DELETE WHERE mutations for immediate (though async) deletion. Also, TTL moves to cold storage can cause query performance degradation if dashboards query across time ranges spanning multiple tiers without users realizing the latency difference.