Time to live (TTL) in ClickHouse
Mark Needham
Time to Live (TTL) in ClickHouse is one of those features that looks simple on the surface but gives you surprising control over your data lifecycle. Whether you need to automatically delete old records, roll up historical data into aggregates, or anonymize sensitive fields after a certain period, TTL has you covered.
In this tutorial, Mark Needham walks through practical examples using a web server access logs dataset with 10 million records. You'll see how TTL works with different merge strategies, how to apply conditions to only expire specific rows, and how to use TTL for data aggregation while preserving your metrics. The video also covers column-level TTL for selectively anonymizing fields like IP addresses and user agents.
- Row-based TTL for automatic data deletion after a specified time period
- Conditional TTL to expire only rows matching specific criteria (e.g., status code 200)
- TTL with GROUP BY for rolling up detailed records into daily aggregates
- Column-level TTL for anonymizing sensitive fields while keeping the rest of the row
- Understanding TTL merge behavior and how to trigger it manually with OPTIMIZE TABLE
Scaling ClickHouse to petabytes of logs at OpenAI
How ClickHouse helps Anthropic scale observability
How Capital One cut infrastructure costs by 50%
Engineering leaders at Capital One share how they cut infrastructure costs by 50% and reduced average dashboard load time from 5+ to under 500ms with ClickHouse Cloud.
