TTL in ClickHouse: data lifecycle management

TTL in ClickHouse: Automatic Data Lifecycle Management

1. Why TTL is Needed – The "Forgot to Delete" Problem

Let's go back to our online casino. You store all player bets. After a month, the table weighs 500 GB. After a year, 5 TB. Disks fill up, queries slow down, and old data is needed less and less. The business owner says: "Players only look at the last 30 days of bets, and for reports we only need aggregated totals for older periods."

You could write a script that deletes old partitions once a day (as we learned in the partitioning article). But that requires an external scheduler (cron), a separate script, monitoring its execution, and error handling.

TTL (Time-To-Live) solves this problem at the database level. It is a built-in ClickHouse mechanism that automatically:

• deletes old rows,
• moves them to cheaper disks (HDD, S3),
• aggregates old data (collapses detail into summaries),
• anonymizes personal data (GDPR).

All of this happens in the background, without your involvement, on a schedule you define in SQL.

Real-life analogy: TTL is like renting a warehouse. You agree with the owner: "Goods that have been sitting for more than 30 days, move to the far cheap section. If they've been sitting for more than a year, throw them out." The owner keeps track of the deadlines and does the work; you don't need to remind them every time.

2. Row-Level TTL – Deleting Old Records

The simplest option: rows live for a certain time, then are deleted.

CREATE TABLE with TTL

-- Create a bets table where rows live for 90 days
CREATE TABLE bets
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 90 DAY;   -- 90 days after created_at, the row is deleted

What's happening here:

• TTL created_at + INTERVAL 90 DAY – for each row, the expiration date is calculated: created_at + 90 days. As soon as the current date (today()) exceeds this date, the row is marked for deletion.
• A background process (usually once a day) goes through granules and deletes rows whose TTL has expired.
• Deletion happens at the part level – ClickHouse rewrites the part without the deleted rows.

ALTER TABLE – Add or Change TTL

The key feature: TTL can be added to an existing table without recreating it.

-- Add TTL to an existing table
ALTER TABLE bets MODIFY TTL created_at + INTERVAL 90 DAY;

-- Change the lifetime from 90 to 180 days
ALTER TABLE bets MODIFY TTL created_at + INTERVAL 180 DAY;

-- Remove TTL (data will be stored forever)
ALTER TABLE bets REMOVE TTL;

Why is this important? Because in real life, storage requirements change. At first, you thought you needed to store everything forever. Then you found out backups eat up space and analysts don't need old data. With MODIFY TTL, you change the rule with a single line.

What happens if you add TTL to a table with 10 billion rows? Nothing terrible. ClickHouse won't rewrite data instantly. It will simply start applying TTL in the background, gradually. During the next merge of parts, old rows will be excluded.

3. TTL with Disk Relocation (Tiered Storage)

Sometimes it's a shame to delete data, but storing it on fast expensive SSDs is costly. Solution: move old data to slow cheap HDDs (or cloud storage S3).

First, configure disks in the ClickHouse config (config.xml):

<storage_configuration>
    <disks>
        <ssd>
            <path>/mnt/ssd/clickhouse/</path>
        </ssd>
        <hdd>
            <path>/mnt/hdd/clickhouse/</path>
        </hdd>
    </disks>
    <policies>
        <hot_to_cold>
            <volumes>
                <hot>
                    <disk>ssd</disk>
                </hot>
                <cold>
                    <disk>hdd</disk>
                </cold>
            </volumes>
        </hot_to_cold>
    </policies>
</storage_configuration>

Now create a table with TTL relocation:

CREATE TABLE bets_tiered
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 30 DAY TO DISK 'hdd';   -- After 30 days, moves to HDD

What happens: Rows live on the fast SSD for the first 30 days (when they are most often needed in reports). After 30 days, ClickHouse moves the data parts to HDD in the background. When querying data for older periods, it will be available but slightly slower.

You can combine – first move, then delete:

-- 30 days on SSD, then on HDD until 90 days, then delete
CREATE TABLE bets_multi_ttl
(
    user_id UInt64,
    amount Decimal(18,2),
    created_at DateTime
)
ENGINE = MergeTree()
ORDER BY user_id
TTL
    created_at + INTERVAL 30 DAY TO DISK 'hdd',
    created_at + INTERVAL 90 DAY DELETE;

Analogy: A hotel: the first 30 days you live in a suite (fast, expensive). Then you are moved to a standard room (slower, cheaper). And after 90 days you are evicted. All automatic.

4. TTL with Relocation to S3

ClickHouse can work with cloud storage (Amazon S3, MinIO, Google Cloud Storage). You can configure a storage_policy with S3 and move old data to the cloud, where storage costs pennies.

Configuration (simplified):

<storage_configuration>
    <disks>
        <s3>
            <type>s3</type>
            <endpoint>https://s3.amazonaws.com/mybucket/clickhouse/</endpoint>
            <access_key_id>AKIAIOSFODNN7EXAMPLE</access_key_id>
            <secret_access_key>wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY</secret_access_key>
        </s3>
    </disks>
    <policies>
        <s3_policy>
            <volumes>
                <hot>
                    <disk>default</disk>   <!-- local SSD -->
                </hot>
                <cold_s3>
                    <disk>s3</disk>        <!-- cloud -->
                </cold_s3>
            </volumes>
        </s3_policy>
    </policies>
</storage_configuration>

Table with TTL to S3:

CREATE TABLE bets_s3
(
    user_id UInt64,
    amount Decimal(18,2),
    created_at DateTime
)
ENGINE = MergeTree()
ORDER BY created_at
TTL created_at + INTERVAL 90 DAY TO VOLUME 'cold_s3';   -- after 90 days in S3

Why this is cool: You pay pennies per gigabyte per month for S3. Data remains available for analytics (though slower than from local disk). And you don't have to worry about running out of server space – S3 is infinite.

5. TTL for Aggregation (The Most Powerful Feature)

This is my favorite pattern. Instead of deleting old detailed data, you collapse it into aggregates. For example, bets older than 7 days are not needed at the per-second level, but daily and per-user totals are needed.

-- Table with detailed bets
CREATE TABLE bets_detailed
(
    user_id     UInt64,
    bet_id      String,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 7 DAY
    GROUP BY toDate(created_at) AS day, user_id
    SET total_bets = sum(amount),           -- sum of all bets for the day
        bet_count = count()                 -- number of bets for the day
    DELETE WHERE day < now() - INTERVAL 90 DAY;   -- after 90 days, delete aggregates too

Breaking it down:

• TTL created_at + INTERVAL 7 DAY – 7 days after the row (detailed bet) is created, it ceases to be detailed.
• GROUP BY toDate(created_at) AS day, user_id – rows are grouped by day and user. Instead of thousands of detailed bets per day for one user, there will be one aggregate row.
• SET total_bets = sum(amount), bet_count = count() – in the new aggregated row, fields are filled with aggregate functions.
• DELETE WHERE day < now() - INTERVAL 90 DAY – aggregates older than 90 days are finally deleted.

What happens in practice:

1. Day 0–7: Data is stored in detailed form. You can analyze every bet.
2. Day 7–90: Detailed bets are collapsed into one row per (user_id, day) with fields total_bets and bet_count. Space usage decreases by 10–100 times.
3. Day 90+: Even aggregates are deleted, only backups remain.

Analogy: You keep a diary with hourly entries. After a week, you rewrite the hourly entries into daily summaries (totals). And after three months, you throw away even the daily summaries, keeping only monthly reports.

6. TTL for Specific Columns (GDPR Anonymization)

By law (GDPR in Europe, personal data), you are required to store personal information (email, IP address) no longer than a certain period. But anonymous analytics (bet amount, number of games) can be stored forever.

TTL can be applied not to the entire row, but to individual columns.

-- Table with personal data
CREATE TABLE user_events
(
    user_id     UInt64,
    email       String,           -- personal column
    ip_address  String,           -- personal column
    event_type  String,
    event_value UInt64,
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL
    email + INTERVAL 1 YEAR,      -- after one year, email is nullified
    ip_address + INTERVAL 1 YEAR, -- after one year, IP is nullified
    created_at + INTERVAL 10 YEAR; -- after 10 years, the entire row is deleted

What happens: One year after the row is created, the email and ip_address columns are replaced with default values (empty string for String, 0 for numbers). The data remains useful for analytics (you know there was some user, but not who exactly). After 10 years, the entire row is deleted.

Why this is important for GDPR: You automatically comply with the law without manual scripts. An auditor can come, look at your TTL rules, and verify that personal data is not stored longer than allowed.

7. Configuring the Background TTL Process

TTL does not trigger instantly after expiration. ClickHouse runs a background process that:

• checks granules,
• applies TTL rules,
• rewrites parts without outdated data or with modified columns.

Configuration parameters (in config.xml or via SET):

-- Interval between TTL process runs (default 1 day)
ALTER SYSTEM MODIFY SETTING merge_with_ttl_timeout = 86400;  -- in seconds

-- For testing, you can make it more frequent
SET merge_with_ttl_timeout = 3600;   -- every hour

Why shouldn't TTL be too frequent? Because applying TTL is a data part rewrite operation that loads CPU and disks. Once a day is fine. Every hour might interfere if you have terabytes of data.

How to check that TTL is working:

-- See which parts have active TTL
SELECT 
    partition,
    name,
    rows,
    modification_time,
    has_ttl_info   -- 1 = TTL has been applied to this part
FROM system.parts
WHERE table = 'bets' AND active = 1;

8. MATERIALIZE TTL – Forced Application

Sometimes you need TTL to apply right now, not wait for the background process. For example:

• You just added TTL to a huge table and want to immediately clean up old data.
• You are testing TTL rules and don't want to wait a day.

-- Force apply TTL for the entire table
ALTER TABLE bets MATERIALIZE TTL;

-- Only for a specific partition (faster)
ALTER TABLE bets MATERIALIZE TTL IN PARTITION '202501';

What happens: ClickHouse scans all parts of the table (or partition) and immediately applies all TTL rules. This can take minutes or hours on large tables. Don't do this during peak hours.

When to use: At night, during maintenance, or before creating a backup to avoid backing up data that is already dead.

9. Real Use Case: Casino with GDPR and Aggregates

Now let's put it all together. Imagine a complete schema for storing events in an online casino.

-- Raw events (each bet, each game)
CREATE TABLE raw_events
(
    user_id         UInt64,
    session_id      String,
    ip_address      String,           -- GDPR-sensitive
    event_type      String,           -- 'bet', 'win', 'login'
    event_value     Int64,
    created_at      DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL
    -- Detailed events stored for 30 days
    created_at + INTERVAL 30 DAY DELETE,
    -- But IP address is removed after 14 days (GDPR)
    ip_address + INTERVAL 14 DAY,
    -- Aggregation for old data: after 30 days, collapse into daily summaries
    created_at + INTERVAL 30 DAY
        GROUP BY toDate(created_at) AS day, user_id
        SET total_bets = sumIf(event_value, event_type = 'bet'),
            total_wins = sumIf(event_value, event_type = 'win'),
            sessions_count = countDistinct(session_id)
        DELETE WHERE day < now() - INTERVAL 2 YEAR;   -- aggregates stored for 2 years

What we achieved:

• 0–14 days: Full information, including IP addresses. Can investigate incidents, detect multi-accounting.
• 15–30 days: IP addresses are already nullified (anonymized), but detailed events still exist. Can analyze user behavior without geolocation.
• 31 days – 2 years: Detailed events are deleted. Instead, aggregated rows per (day, user). Space usage is 100 times less. Dashboards work fast.
• Older than 2 years: Everything is deleted. Only backups on S3 (if you make them).

How to read aggregated data after TTL:

-- Now the table is mixed: detailed rows (first 30 days) and aggregates (up to 2 years)
-- You still write a normal aggregation query; it works on both types of rows
SELECT 
    toDate(created_at) AS day,
    user_id,
    sum(event_value) AS total
FROM raw_events
WHERE created_at >= today() - 45
GROUP BY day, user_id;

ClickHouse itself figures out that for fresh data it sums detailed rows, for old data it uses precomputed aggregates. Magic.

10. When NOT to Use TTL and Alternatives

When TTL is not suitable:

• Data is updated frequently. TTL triggers on insertion, not on the last update. If you modify a row via INSERT with cancellation (CollapsingMergeTree), TTL will count from the original created_at. Solution: use an updated_at column in the TTL expression.

• Need precise time control. The TTL process is background and imprecise. If you need to guarantee deletion exactly at midnight – it won't work. The delay can be up to several hours.

• Very large tables with rare merges. TTL is applied during part merges. If the table merges rarely (e.g., due to merge_with_ttl_timeout settings), old data may linger longer.

Alternatives to TTL:

Approach	When to Use	Pros	Cons
Partitioning + DROP PARTITION	Data flows continuously, deletion by calendar	Instant deletion, no overhead	Requires external scheduler, no flexibility (only by partitions)
TTL DELETE	Data may arrive with delay, deletion by row age	Built-in, flexible, no external scripts needed	Imprecise deletion time, merge load
TTL TO DISK	Need to keep data but on cheap storage	Storage cost savings, transparent to queries	Requires storage_policy configuration
TTL GROUP BY	Detailed data not needed, only aggregates	Drastic space reduction (100x+)	Loss of detail, debugging complexity

Combine with Partitioning for Best Results

Best practice: use partitioning + TTL together.

CREATE TABLE bets_optimized
(
    user_id UInt64,
    amount Decimal(18,2),
    created_at DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)           -- partitions by month
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 30 DAY DELETE;    -- TTL by 30 days

Why this is good:

• Partitions help quickly delete entire months (if TTL lags).
• TTL cleans inside partitions more flexibly (by row age, not by calendar).

What's Next

Now you have all the tools for automatic data management in ClickHouse. Next topics:

• Advanced storage policies – how to set up automatic movement from SSD → HDD → S3 with different TTL at each stage.
• Monitoring TTL – how to use system.query_log to track how much data is being deleted and how fast.
• TTL + Materialized Views – how to automatically aggregate old data into a separate table without mixing with detailed data.

Summary: TTL in ClickHouse is a Swiss Army knife for data lifecycle management. It can delete, move, aggregate, and anonymize. Use TTL ... DELETE for cleanup, TTL ... TO DISK for savings, TTL ... GROUP BY for aggregation magic, and TTL on columns for GDPR. And don't forget MATERIALIZE TTL when you need to apply rules immediately.

---

← Previous: ORDER BY and PRIMARY KEY in ClickHouse: How to Get the Index Right
→ Next: Dictionaries in ClickHouse: Fast Lookup Without JOIN

— Editorial Team