Using ClickHouse TTL Expressions to Automate Time‑Series Data Expiration

The problem with unbounded time‑series data

ClickHouse tables that store event logs, metrics, or sensor readings grow as new rows arrive. Without a strategy, storage expands indefinitely and older data competes with fresh queries for cache space. Traditional approaches require manual partition dropping or external cron jobs, both of which become fragile as cluster size increases.

How ClickHouse TTL expressions work

TTL (time‑to‑live) is a table‑level or column‑level directive that ClickHouse evaluates during merges of a MergeTree table. When the expression evaluates to true for a part, that part is marked for expiration. The expression can reference any column, so retention logic is not limited to a single timestamp column.

TTL processing is version‑sensitive. Early ClickHouse releases applied TTL only at merge time and lacked per‑partition overrides. Recent major versions introduced the ability to adjust TTL priority per partition and to control whether expired parts are dropped immediately or kept for compaction (ttl_only_drop_parts setting).

Key terms

• MergeTree – ClickHouse’s primary table engine for ordered, partitioned data.
• Part – A immutable data segment that merges with other parts.
• Merge – The background operation that combines parts, applies TTL checks, and frees expired data.

Concrete example: expire data older than 30 days

Suppose you have a table with a DateTime column named event_time. The following CREATE TABLE statement sets a rolling 30‑day retention window:

CREATE TABLE metrics (
    event_time      DateTime CODEC(Delta, ZSTD),
    value           Float64 CODEC(ZSTD)
) ENGINE = MergeTree()
PARTITION BY toDate(event_time)
ORDER BY (event_time, value)
TTL event_time + INTERVAL 30 DAY;

The expression event_time + INTERVAL 30 DAY computes a deadline that moves forward as new rows arrive. During the next merge, any part whose maximum event_time is older than that deadline is marked expired. Because partitions are aligned to day boundaries, entire old partitions disappear in a single merge step, which is far more efficient than row‑by‑row deletion.

Applying TTL to an existing table

ALTER TABLE metrics
MODIFY TTL event_time + INTERVAL 30 DAY;

Always verify the expression is parsed correctly before enabling actual removal:

SHOW CREATE TABLE metrics;

The output includes the TTL clause, confirming that ClickHouse accepted the expression.

Verification: checking part expiration

After inserting rows and allowing a merge to run, query the system tables to see the effect:

SELECT table, active, name, min_time, max_time
FROM system.parts
WHERE table = 'metrics'
  AND active = 1
ORDER BY min_time;

Rows inserted more than 30 days ago will eventually have active = 0 or disappear from the query result after the merge completes. The name column identifies each part; min_time/max_time show the range of event_time values within that part.

You can also inspect the TTL column on the tables metadata:

SELECT ttl FROM system.tables WHERE database = 'your_db' AND table = 'metrics';

This returns the parsed expression, helping you confirm that no syntax errors were introduced.

Finally, watch merge activity:

SELECT event_name, start_time, duration_ms
FROM system.merges
WHERE table = 'metrics'
ORDER BY start_time DESC
LIMIT 10;

A sustained increase in merge frequency after activating TTL indicates that data expiration is active.

Trade‑offs and practical limits

• Merge pressure: Aggressive TTL expressions on high‑throughput inserts can increase merge frequency and I/O. If inserts outpace the merge scheduler, parts may accumulate temporarily, giving a false impression that TTL isn’t working.
• Part explosion: Expressions that do not align with the partition key can create many small parts, slowing down queries. Aligning the partition expression (e.g., PARTITION BY toDate(event_time)) with the TTL deadline helps keep part count manageable.
• Delete‑mode cost: Using TTL in delete mode rewrites parts to remove individual rows, which is I/O‑expensive. The default behavior marks parts for expiration during merges; row removal is then handled as part of the merge rewrite. If you need immediate row deletion, test carefully and consider the ttl_hard_timeout setting.
• Version upgrades: Major ClickHouse upgrades may change TTL priority rules and evaluation timing. After upgrading, re‑run the verification queries and adjust the expression if needed.

Closing actionable steps

1. Define a DateTime column that captures the event time.
2. Add a TTL clause using an expression such as event_time + INTERVAL 30 DAY (or a more complex conditional expression if needed).
3. Run SHOW CREATE TABLE to confirm the expression parsed without errors.
4. Insert a few test rows older than the desired retention window.
5. Wait for the next merge (or trigger one with SYSTEM DROP PARTITION) and query system.parts to verify that old parts are marked inactive.
6. If the behavior matches expectations, keep the TTL as‑is. If you need immediate row removal, experiment with delete mode on a test table and monitor merge I/O.
7. Schedule periodic checks of system.merges and system.parts to ensure the retention policy stays aligned with workload patterns.

TTL expressions give ClickHouse a code‑first way to keep time‑series storage lean without external housekeeping. Start with a simple rolling‑window expression, verify part status through the system tables, and adjust the expression or partition strategy as your data volume and query patterns evolve.