Managing Data Lifecycles in ClickHouse with Time‑to‑Live (TTL)

Why TTL Is a Game‑Changer for ClickHouse

When you ingest millions of events per day, the storage cost can balloon quickly. ClickHouse’s Time‑to‑Live (TTL) feature lets you automate data expiration, keeping only the most recent data in the hot tier and moving older rows to cheaper storage or deleting them altogether. Unlike manual cleanup scripts, TTL works inside the MergeTree engine, so it scales with your data volume.

TL;DR – The Core Syntax

TTL can be set per column or per table. The simplest form deletes rows that are older than a certain age:

CREATE TABLE events (
    event_date Date,
    user_id UInt64,
    action String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id)
TTL event_date TO INTERVAL 30 DAY DELETE;

Key points:

• MergeTree (or any MergeTree variant) is required.
• TTL is evaluated against the column value (here event_date).
• Actions supported: DELETE, MOVE TO PARTITION, UPDATE.
• TTL is enforced by background merges; deletions are not instant.

1. Building a Table With a 30‑Day TTL

Below is a step‑by‑step guide you can run on any ClickHouse instance running version 24.6 or newer. Replace placeholders with your values.

# 1. Create a table with a 30‑day delete TTL
CREATE TABLE IF NOT EXISTS analytics.events (
    event_date Date,
    user_id UInt64,
    action String,
    created_at DateTime DEFAULT now()
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id)
TTL event_date TO INTERVAL 30 DAY DELETE;

# 2. Insert sample data (some rows older than 30 days)
INSERT INTO analytics.events (event_date, user_id, action) VALUES
    ('2025-06-01', 123, 'click'),
    ('2025-08-15', 456, 'view'),
    ('2025-09-20', 789, 'purchase');

After inserting, run:

SELECT * FROM analytics.events;

to confirm that all rows are visible.

2.1. Verifying TTL Marks for Deletion

TTL rows are flagged in system.parts. Each part has a is_mark flag that becomes true when the part contains rows that should be deleted.

SELECT name, is_mark, is_temporary, partition
FROM system.parts
WHERE database = 'analytics' AND table = 'events';

Look for parts where is_mark = 1. Those parts will be removed during the next merge cycle.

2.2. Waiting for the Merge

Background merges happen automatically. If you want to force a merge for quick testing, you can run:

SYSTEM SYNC MERGES analytics.events;

After the merge finishes, re‑query system.parts and the table. Rows older than 30 days should now be gone.

3. Combining TTL With Logical Partitioning

Partitioning confines deletions to small, self‑contained parts, reducing the cost of merges. In the example above, PARTITION BY toYYYYMM(event_date) creates one part per month. If you set a 30‑day TTL, only the last month’s part will be kept; older monthly parts will be marked for deletion.

When you use MOVE TO PARTITION instead of DELETE, ClickHouse can relocate the part to a different partition or even a different table, enabling tiered storage strategies.

4. A Complex Lifecycle Policy Example

Suppose you want to keep the last 30 days of data in the main table, archive 30–60 days elsewhere, and delete anything older than 60 days. You can chain TTL actions:

CREATE TABLE analytics.events (
    event_date Date,
    user_id UInt64,
    action String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id)
TTL event_date TO INTERVAL 30 DAY DELETE,
    event_date TO INTERVAL 60 DAY MOVE TO PARTITION toYYYYMM(event_date);

In this policy:

• Rows older than 30 days are deleted during the next merge.
• Rows between 30 and 60 days are moved to the partition that matches their event_date (effectively archiving them).
• Rows older than 60 days are never touched because the earlier actions already removed them.

Monitor the move with system.tiered_parts:

SELECT * FROM system.tiered_parts
WHERE database = 'analytics' AND table = 'events';

5. Trade‑Offs and Limitations

• Non‑instant deletions: TTL marks parts for deletion, but the actual removal occurs during the next merge. If you need immediate cleanup, use SYSTEM SYNC MERGES or run a manual OPTIMIZE command.
• TTL only works on MergeTree‑based engines. Attempting to add TTL to a Memory or Log table will throw a syntax error.
• Large parts can still be expensive to delete. Partitioning mitigates this but requires careful design.
• TTL expressions cannot reference non‑partitioned columns that are not part of the primary key, limiting some use cases.
• The DELETE action removes rows permanently; there is no built‑in rollback.

6. Practical Checklist Before Deploying TTL

1. Confirm your engine is MergeTree or a variant (e.g., ReplacingMergeTree).
2. Define a partition key that aligns with your TTL horizon.
3. Test the TTL on a staging environment: insert data older than the TTL, run SYSTEM SYNC MERGES, and verify deletion.
4. Set up monitoring: query system.parts or system.tiered_parts regularly to ensure parts are being marked and removed.
5. Consider using MOVE TO PARTITION if you have a cheaper storage tier or need to keep an archive.
6. Document the TTL policy in your data‑management playbook.

7. Closing Thoughts

TTL in ClickHouse is a powerful, built‑in way to enforce data lifecycles without external cron jobs or manual scripts. By combining TTL with logical partitioning and the optional MOVE TO PARTITION action, you can keep your hot storage lean, archive older data cost‑effectively, and reduce the operational overhead of data cleanup. Just remember that TTL is a background process, so plan for eventual consistency and monitor the merge state to keep the system healthy.