ClickHouse MergeTree TTL: Automate Data Expiration and Tiered Storage

The Problem: Manual Data Retention Is Fragile

Teams running ClickHouse often rely on external cron jobs or application logic to delete old rows or move them to cheaper disks. Those jobs drift, fail silently, or require coordination across clusters. ClickHouse's MergeTree engine solves this with built-in TTL (Time-To-Live) clauses that execute during background merges, eliminating external dependencies.

Useful Takeaway

Define TTL at table creation or via ALTER TABLE to automatically delete or relocate data based on a DateTime column. TTL runs asynchronously during merges, so expired rows may remain visible until the next merge; always filter queries with a time predicate for consistent results.

Worked Configuration: Delete and Tiered Storage

The following example creates an events table that deletes rows after 90 days and moves rows older than 30 days to a colder disk. Run this on any ClickHouse node with CREATE TABLE privilege (typically default or a role with CREATE on the database).

CREATE TABLE events (
    ts DateTime,
    id UInt64,
    payload String
) ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (ts, id)
TTL ts + INTERVAL 30 DAY TO DISK 'cold_storage',
    ts + INTERVAL 90 DAY DELETE;

Key points:

• Partitioning: PARTITION BY toYYYYMM(ts) aligns partitions with monthly boundaries. When a whole partition expires, ClickHouse drops it instantly instead of rewriting.
• TTL expressions: Each clause references the ts column. The first moves data to a disk named cold_storage after 30 days; the second deletes after 90 days.
• Storage policy prerequisite: The disk cold_storage must be defined in config.xml under <storage_configuration><disks> and referenced by a policy. Without it, merges will fail with "Disk not found" errors.

Downsampling with TTL GROUP BY

For long-term analytics, raw rows can be aggregated into rollups. The following clause replaces raw rows older than one day with hourly aggregates:

TTL ts + INTERVAL 1 DAY GROUP BY toStartOfHour(ts)
    SET cnt = count(), sum_val = sum(value);

After the merge, the part contains one row per hour with columns ts (hour start), cnt, and sum_val. Downstream consumers must handle the schema change — original columns like payload disappear.

Limits and Common Mistakes

Engine Compatibility

TTL works only with MergeTree family engines (ReplicatedMergeTree, SummingMergeTree, AggregatingMergeTree, etc.). It is not supported for Log, Memory, or external table engines.

Partition Alignment

Using a partition key that does not match the TTL granularity (e.g., partitioning by id while TTL uses ts) forces ClickHouse to rewrite entire partitions on every merge, generating high I/O. Always partition by a time function like toYYYYMM(ts) or toStartOfDay(ts).

Asynchronous Execution

TTL merges are background, non-deterministic tasks. Do not rely on exact expiration timing for compliance or billing. Queries may return expired rows until the next merge. Mitigate by adding WHERE ts > now() - INTERVAL 90 DAY to every query.

ALTER TABLE MODIFY TTL Rewrites Data

Changing TTL via ALTER TABLE events MODIFY TTL ... rewrites all existing parts to apply the new rules. Schedule during a maintenance window; the operation is I/O intensive and can saturate disks.

Tiered Storage Requires Pre‑Configured Disks

TTL ... TO DISK or TO VOLUME fails if the target disk/volume is not mounted and declared in config.xml. Verify with SELECT * FROM system.disks before adding TTL clauses.

Monitoring TTL Progress

Observe merge activity and per-part TTL state using system tables. Run these queries on any node with SELECT privilege on system database.

-- Active TTL merges
SELECT * FROM system.merges WHERE is_ttl = 1;

-- Per-part TTL information
SELECT table, partition, name, ttl_infos FROM system.parts
WHERE database = 'default' AND table = 'events' AND ttl_infos != '';

ttl_infos shows the expression, target disk, and estimated execution time for each part. Stuck merges appear with is_ttl=1 for extended periods.

Practical Verification Steps

1. Confirm ClickHouse version supports TTL (since 19.12, tiered storage since 20.8):

   SELECT version();

2. Create a test table with a short TTL, insert rows with old timestamps, force a merge, and verify deletion:

   CREATE TABLE test_ttl (ts DateTime, val UInt32) ENGINE = MergeTree
   PARTITION BY toYYYYMM(ts) ORDER BY ts
   TTL ts + INTERVAL 1 MINUTE DELETE;
   
   INSERT INTO test_ttl VALUES (now() - INTERVAL 2 MINUTE, 1), (now(), 2);
   -- Wait for background merge or force:
   OPTIMIZE TABLE test_ttl FINAL;
   SELECT count() FROM test_ttl; -- Expect 1 row (the recent one)

3. For tiered storage, check system.disks and system.parts after a merge to confirm parts moved to cold_storage.
4. For downsampling, insert raw events, wait for merge, then SELECT * FROM test_ttl to confirm aggregated rows replace raw data.

Summary Checklist

• Partition by time function matching TTL granularity.
• Define all target disks/volumes in config.xml before using TO DISK.
• Filter queries with explicit time windows; do not trust TTL for immediate removal.
• Monitor system.merges and system.parts.ttl_infos to catch stalled merges.
• Test TTL changes on a staging cluster before applying to production.