How to Reduce Log Data Storage Costs with ClickHouse MergeTree Partitions and TTL

When you're operating petabyte-scale logs, the storage bill starts to feel terrifying at some point. "I need to delete old data — how do I do that?" — At first you create a cron job, schedule DELETE queries, and eventually build a separate pipeline, but honestly it's all cumbersome and error-prone.

Once I started using ClickHouse properly, that concern largely went away. The engine manages much of the data lifecycle with just a partition key and a TTL declaration. That said, it's not fully automatic — if you misalign the partition granularity and the TTL interval, IO costs can actually explode, and misunderstanding how column TTL works can cause problems in a GDPR audit. In this post, I'll cover how to design the two in sync, along with the pitfalls I've encountered in production.

After reading this, you'll understand how MergeTree partitions work with TTL to reduce IO costs, how to declaratively configure hot/warm/cold tiering, and the common mistakes in partition design.

You Need to Understand How MergeTree Works First

Parts, Partitions, and Merges

When you INSERT data into ClickHouse, a directory called a part is created on disk. The core behavior of MergeTree is that these parts are merged in the background into progressively larger parts.

The partition key (PARTITION BY) is the criterion for physically separating these parts. If you declare toYYYYMM(ts), data that arrives in 2026-01 and data that arrives in 2026-02 will never be merged into the same part. This separation is directly tied to the efficiency of TTL deletion.

The important point here is that TTL is evaluated at background merge time, at intervals defined by merge_with_ttl_timeout (default 14400 seconds, 4 hours). In other words, data does not disappear immediately after the TTL expires.

Three Levels of TTL

TTL is not simply "delete after N time." It behaves completely differently depending on where it is applied.

Column TTL requires particular caution. It replaces with the column type's default value, not NULL. For String it becomes an empty string (''), for UInt64 it becomes 0. If you want NULL, you must declare the column as Nullable(String). This distinction is critical for GDPR compliance — if an empty string remains when you go through an audit, it's hard to claim the value was fully deleted.

Practical Design — A Few Scenarios

Scenario 1: Basic Log Table — Auto-Delete After 1 Year

CREATE TABLE user_events (
    ts       DateTime,
    user_id  UInt64,
    event    LowCardinality(String),
    payload  String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (user_id, ts)
TTL ts + INTERVAL 1 YEAR DELETE;

The combination of monthly partitions and yearly TTL is the key. At expiration time, an entire partition expires at once, so deletion happens by dropping the part file itself. To activate this more reliably, it's a good idea to add the following to your server config.

<!-- config.xml or users.xml -->
<merge_tree>
    <ttl_only_drop_parts>1</ttl_only_drop_parts>
</merge_tree>

When ttl_only_drop_parts=1 is set, a part is dropped only when all of its rows have expired, avoiding row-level rewrites. Without this setting, if even a few expired rows are mixed in with valid ones, the entire part is rewritten at significant IO cost.

Scenario 2: Observability Platform — 3-Tier Hot/Warm/Cold Tiering

At scale, "store everything on NVMe" is not realistic cost-wise. This setup automatically moves recent, frequently queried data to fast storage and pushes older data to cheaper storage over time.

First, configure the storage policy.

<!-- storage_configuration in config.xml -->
<storage_configuration>
    <disks>
        <nvme>
            <path>/mnt/nvme/</path>
        </nvme>
        <hdd>
            <path>/mnt/hdd/</path>
        </hdd>
        <s3>
            <type>s3</type>
            <endpoint>https://s3.amazonaws.com/my-bucket/</endpoint>
            <access_key_id>...</access_key_id>
            <secret_access_key>...</secret_access_key>
        </s3>
    </disks>
    <policies>
        <tiered>
            <volumes>
                <hot>  <disk>nvme</disk> </hot>
                <warm> <disk>hdd</disk>  </warm>
                <cold> <disk>s3</disk>   </cold>
            </volumes>
        </tiered>
    </policies>
</storage_configuration>

Then declare the TTL chain on the table.

CREATE TABLE logs (
    ts      DateTime,
    level   LowCardinality(String),
    host    LowCardinality(String),
    message String
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(ts)
ORDER BY (level, ts)
SETTINGS storage_policy = 'tiered'
TTL
    ts + INTERVAL 7  DAY  TO VOLUME 'warm',
    ts + INTERVAL 90 DAY  TO VOLUME 'cold',
    ts + INTERVAL 1  YEAR DELETE;

When you query this table, ClickHouse handles it transparently regardless of which tier the data is in. No application code changes are needed.

One caveat: queries against the cold volume (S3) incur network latency. This is fine for workloads that mostly query recent data, but if you need long-term trend analysis, you should also consider extending the warm retention period or pre-aggregating with materialized views.

Scenario 3: Expiring Only PII Columns — Nullable Declaration Caution

This is a practical example of applying the column TTL behavior mentioned in the table above. Suppose you want to retain the events themselves (ts, user_id, event_type) long-term, but delete only the personally identifiable field email after 30 days.

If replacement with the default value (empty string) is acceptable, write it like this.

ALTER TABLE events
    MODIFY COLUMN email String TTL ts + INTERVAL 30 DAY;

In this case, the email of rows older than 30 days is replaced with '' (empty string). The column itself doesn't disappear — only the value is reset.

However, for GDPR "right to be forgotten" compliance, there are cases where the value must be left in a state of explicitly not existing (NULL) to satisfy audit requirements. In that case, you must declare the column as Nullable from the start.

CREATE TABLE events (
    ts         DateTime,
    user_id    UInt64,
    event_type LowCardinality(String),
    email      Nullable(String) TTL ts + INTERVAL 30 DAY
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (user_id, ts);

With this, the email of expired rows is replaced with NULL. Since Nullable has storage and query overhead, it's best to apply it only to columns that truly require it.

Scenario 4: Raw Data Rollup — TTL GROUP BY

This is useful when you want to retain only hourly aggregated values instead of raw logs after 7 days. There's one constraint that's easy to miss here: the expressions in TTL GROUP BY must match the prefix of the table's ORDER BY.

That is, if you declare ORDER BY (host, toStartOfHour(ts)), then GROUP BY must also start with the same order: host, toStartOfHour(ts).

CREATE TABLE metrics (
    ts    DateTime,
    host  LowCardinality(String),
    value Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(ts)
ORDER BY (host, toStartOfHour(ts), ts)
TTL ts + INTERVAL 7 DAY
    GROUP BY host, toStartOfHour(ts)
    SET value = avg(value);

After 7 days, raw rows are automatically aggregated into (host, hour) averages. The storage savings are significant. If you reverse the order and write GROUP BY toStartOfHour(ts), host, it violates the prefix constraint and the DDL itself will fail.

Trade-offs — Things You Must Think About When Designing

The Partition Key Determines Almost Everything

I used to think "the more granular the partitions, the faster the queries and the better the TTL," but I was wrong. Too many partitions actually becomes a liability.

Using a high-cardinality column (user_id, UUID, etc.) as the partition key creates millions of partitions. Both INSERT and SELECT performance drop sharply. As a rule, the partition key should be time-based (monthly or daily).

Aligning Partition Boundaries with TTL Intervals Is the Key

To avoid row-level rewrites, it's not enough to simply ensure TTL interval ≥ partition unit. The TTL interval must be an integer multiple of the partition unit for expiration times to align exactly with partition boundaries.

For example, suppose partitions are monthly (toYYYYMM) but the TTL is 45 days. The ≥ condition is satisfied, but since 45 days is not an integer multiple of one month, there will always be a state where expired and still-valid rows are mixed within the 2026-03 partition. This results in row-level rewrites instead of part drops, causing IO costs to spike significantly.

Conversely, partition=month with TTL=30 days is also subtly misaligned. Data from March 1st expires March 31st (still in the March partition), while data from March 31st expires April 30th (while the April partition exists), so inter-partition mixing inevitably occurs. To be safe, when partition=month, set the TTL to an integer multiple of months such as 12 months or 24 months; when partition=day, align the TTL to an integer number of days as well.

Common Mistakes

How to Check Status in Production

Once you have the configuration in place, you need to verify it's working correctly. The column layout of system.parts varies slightly by version, so it's safest to first check which columns are exposed in your cluster.

-- Check which TTL-related columns exist in system.parts on this server
SELECT name, type
FROM system.columns
WHERE database = 'system' AND table = 'parts'
  AND name LIKE '%ttl%';

The TTL-related column that typically exists is move_ttl_info (a Nested structure). Whether row TTL-related information is exposed depends on the version, so use the query above to confirm actual availability before choosing column names.

-- Basic status and tier check per part
SELECT
    partition,
    name,
    rows,
    disk_name,
    min_time,
    max_time,
    modification_time
FROM system.parts
WHERE table = 'logs' AND active
ORDER BY partition DESC;
 
-- Move TTL info per part (next scheduled tier migration time)
SELECT
    partition,
    name,
    disk_name,
    move_ttl_info.expression,
    move_ttl_info.min,
    move_ttl_info.max
FROM system.parts
WHERE table = 'logs' AND active
  AND notEmpty(move_ttl_info.expression);
 
-- Check currently running background merges
SELECT
    table,
    elapsed,
    progress,
    is_mutation
FROM system.merges
WHERE table = 'logs';
 
-- Check storage policy and volumes
SELECT *
FROM system.storage_policies
WHERE policy_name = 'tiered';

If parts are persisting longer than expected, check the merge_with_ttl_timeout setting alongside the actual execution frequency in system.merges.

To Reduce Storage Costs Further: Codec Combinations

Beyond TTL and partitions, there are other effective settings for reducing storage costs.

CREATE TABLE metrics_compact (
    ts     DateTime CODEC(Delta, ZSTD),
    value  Float64  CODEC(Gorilla, ZSTD),
    host   LowCardinality(String),
    level  LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (host, ts);

CODEC(Delta, ZSTD) compresses well for monotonically increasing time series (timestamps, counters, etc.), and Gorilla is suited for floating-point time series. LowCardinality(String) optimizes encoding for repeatedly occurring strings like status values and log levels.

Summary

The reason the ClickHouse partition + TTL combination is powerful is not simply automation. The key is that when partition boundaries and TTL expiration align, deletion can happen by dropping the part file itself — almost zero IO.

Here's a summary of what to keep in mind when designing:

• Partition key should be time-based (monthly or daily); never use high-cardinality columns as partition keys
• Align the TTL interval to an integer multiple of the partition unit (for monthly partitions, use 12/24 months, etc.)
• ttl_only_drop_parts=1 server setting is essential in production
• Optimize storage costs with 3-tier tiering (hot → warm → cold S3)
• Be aware that column TTL replaces with the column default value; declare Nullable if GDPR compliance is required
• TTL GROUP BY expressions must match the prefix of ORDER BY
• In production, regularly check status via system.parts and system.merges

Early on I tried to make the partition design too complex and hit Too Many Parts errors, and I also had experience where IO costs came out higher than expected due to row-level rewrites from misaligning TTL and partition units. Keep the design simple, keep TTL declarations explicit — that's ultimately what makes operations comfortable.

References

• MergeTree table engine — ClickHouse official documentation
• Manage data with TTL — ClickHouse official documentation
• Managing data (Observability) — ClickHouse official documentation
• Custom Partitioning Key — ClickHouse official documentation
• system.parts — ClickHouse official documentation
• ClickHouse Partitioning: When It Helps, When It Hurts — BigData Boutique
• Scaling ClickHouse with Hot-Warm-Cold Storage Via TTL — Towards Dev
• ClickHouse TTL in Production — Medium
• Mastering TTL in ClickHouse — TechTrends Digest
• ClickHouse Tiered Storage: Volumes, Storage Policies and TTL — Towards Dev
• ClickHouse Fine-Tuning for Time Series and Logs — Logalarm Devs
• Successful ClickHouse Partitioning for Optimal Query Speed — ChistaData
• MODIFY TTL in ClickHouse — Altinity KB