Setting Up ClickHouse TTL Policies to Expire Old Data Automatically

The problem TTL solves

Time-series and event tables in ClickHouse grow without bound unless someone deletes old rows. Manual ALTER TABLE ... DELETE jobs are expensive mutations, and cron-driven cleanup scripts tend to break silently. TTL (time-to-live) expressions on MergeTree-family tables let the server expire, move, or recompress data during its normal background merge cycle — no external scheduler required.

The useful takeaway: a TTL clause is declarative, but its execution is tied to background merges, so expiration is eventual rather than precise. Plan for that, and verify with system tables instead of assuming the deadline was met.

Prerequisites

• A table on a MergeTree-family engine (MergeTree, ReplicatedMergeTree, ReplacingMergeTree, etc.). TTL is not supported on Log or Memory engines.
• A Date, DateTime, or similar column the TTL expression can reference.
• Sufficient privileges: the user issuing the ALTER needs ALTER TTL (granted via ALTER TABLE in most default setups).
• If you plan to move data to cheaper storage instead of deleting it, a storage policy with multiple volumes must already exist in the server configuration.

Defining a TTL at table creation

The simplest form deletes rows once a timestamp column passes an age threshold:

CREATE TABLE events
(
    event_time DateTime,
    user_id UInt64,
    payload String
)
ENGINE = MergeTree
ORDER BY (event_time, user_id)
TTL event_time + INTERVAL 30 DAY;

Run this on the ClickHouse node (via clickhouse-client or your SQL console) as a user with CREATE TABLE rights. Rows become eligible for deletion 30 days after their event_time. ClickHouse removes them when the affected part is next merged — not at the exact second the interval elapses.

Adding or changing TTL on an existing table

Use ALTER TABLE ... MODIFY TTL:

ALTER TABLE events
MODIFY TTL event_time + INTERVAL 30 DAY;

This changes table metadata, so it is a real state change. The new expression applies to parts as they merge going forward; already-merged parts are re-evaluated on subsequent merges. If you need the TTL applied to existing data promptly rather than waiting for organic merges, you can force it:

ALTER TABLE events MATERIALIZE TTL;

Risk: MATERIALIZE TTL on a large table triggers heavy background I/O as parts are rewritten. Run it during a low-traffic window and watch system.merges and disk utilization while it progresses.

Moving data instead of deleting it

TTL can also relocate parts between storage volumes, which is the common pattern for hot/warm/cold tiering:

ALTER TABLE events
MODIFY TTL event_time + INTERVAL 7 DAY TO VOLUME 'warm',
          event_time + INTERVAL 90 DAY DELETE;

This requires a storage policy defining a warm volume (for example, on cheaper disk or object storage) configured in storage_configuration. Multiple TTL rules can coexist; ClickHouse applies whichever rule's condition is met at merge time.

Verifying the TTL is in place and working

Do not assume expiration happened on schedule. Check three things:

1. The expression is registered

SELECT name, engine_full
FROM system.tables
WHERE name = 'events';

The engine_full column includes the active TTL clause. Confirm it parses and matches what you intended.

2. Parts are actually being cleaned

SELECT partition, name, rows, min_date, max_date
FROM system.parts
WHERE table = 'events' AND active
ORDER BY min_date;

After a merge cycle, parts whose data is entirely past the TTL threshold should disappear, and the oldest remaining min_date should roughly track your retention window. A row-count comparison on an expected-expired range is a good sanity check:

SELECT count() FROM events
WHERE event_time < now() - INTERVAL 30 DAY;

This may legitimately return non-zero if merges haven't reached those parts yet — that is expected behavior, not necessarily a fault.

3. Merges and TTL work are progressing

Check system.merges for active merge activity and the server log for TTL-related errors. If parts are never merging (for example, because the table receives no new inserts and merges are idle), TTL deletion stalls too. You can nudge it with OPTIMIZE TABLE events FINAL, but be aware OPTIMIZE ... FINAL rewrites parts and is expensive on large tables.

Limitations and gotchas

• Timing is not guaranteed. TTL executes during background merges. Under heavy merge load, or on a quiet table with few merges, deletion can lag the threshold by hours or days. Do not use TTL as a hard compliance boundary without verification.
• Non-deterministic expressions are dangerous. TTL expressions should be deterministic functions of the row's columns. Expressions depending on volatile state can behave inconsistently, especially across replicas.
• Replica convergence. On ReplicatedMergeTree, TTL results propagate through replication, but replicas merge on their own schedules, so replicas can temporarily disagree on which expired rows remain.
• ALTER and MATERIALIZE cost I/O. Changing TTL on a big table and materializing it generates significant disk and network activity; schedule accordingly.

Recovery if TTL misfires

Because TTL deletion is destructive, keep a rollback path:

• Wrong expression, caught early: issue ALTER TABLE events MODIFY TTL ... with the corrected expression immediately. Rows whose parts have not yet merged are unaffected.
• Data already deleted: TTL deletion is not recoverable from within ClickHouse. Restore from a backup (for example, clickhouse-backup snapshots or native BACKUP TABLE output) or re-sync from a replica that still holds the parts. On replicated setups, detaching a lagging replica before it processes the deletion can preserve a copy — but this is an emergency measure, not a strategy.
• Pause before damage spreads: if you suspect a bad TTL, remove it (ALTER TABLE events REMOVE TTL) before further merges run.

The practical habit: after any TTL change, watch system.parts and a boundary row-count query for one or two merge cycles before considering the job done.