Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 35 ยท Tables and MergeTree Engines

TTL, Codecs and Mutations

Expire data automatically with TTL, shrink columns with compression codecs like Delta and ZSTD, and change tables with ALTER, mutations and lightweight deletes.

Three practical topics round out the MergeTree chapter: TTL (data that expires by itself), codecs (how columns are compressed) and mutations (changing existing data with ALTER ... UPDATE and DELETE). Together they answer: how do I keep storage under control, how do I make it smaller, and what do I do when data must be corrected?

ClickHouse cannot run on this site. The DDL and mutation statements are read-only. The runnable blocks use SQLite to compute what a TTL rule would select and to compare a column with and without delta encoding, by arithmetic.

TTL: time to live

A TTL expression says when a row, or a column's value, expires. Expired data is removed or changed during background merges, not at the exact second.

SQL
CREATE TABLE logs
(
    ts      DateTime,
    level   LowCardinality(String),
    message String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (level, ts)
TTL ts + INTERVAL 90 DAY DELETE;

Rows older than 90 days are deleted at merge time. Options beyond DELETE:

SQL
TTL ts + INTERVAL 7 DAY  TO VOLUME 'cold',             -- move to cheaper storage
    ts + INTERVAL 30 DAY TO DISK 'archive',
    ts + INTERVAL 365 DAY DELETE
SQL
-- Column TTL: keep the row but reset a heavy column
message String TTL ts + INTERVAL 14 DAY      -- becomes an empty string after 14 days

-- Roll up old rows instead of deleting
TTL ts + INTERVAL 30 DAY GROUP BY toStartOfDay(ts) SET n = sum(n)

Useful settings: merge_with_ttl_timeout (how often TTL merges run, default 4 hours) and ttl_only_drop_parts = 1 (drop a whole part only when all its rows have expired, which is very cheap; it fits well with partitioning by the same time unit). If your partitions are monthly and TTL is 90 days, expired parts fall away whole.

TTL rules can be added later: ALTER TABLE logs MODIFY TTL ts + INTERVAL 30 DAY. Test the rule on a copy before applying it to a big table, because MATERIALIZE TTL rewrites existing data.

Which rows would a TTL rule remove?

You can check a rule by running the same condition in a SELECT before applying it.

SQL Stand-in: preview a 90-day TTL (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Compression codecs

Every column is compressed in blocks. The default codec is LZ4: fast, decent ratio. You can choose another, and chain a specialised codec in front of a general one:

SQL
CREATE TABLE sensor
(
    ts    DateTime  CODEC(DoubleDelta, ZSTD(1)),
    id    UInt32    CODEC(T64, LZ4),
    temp  Float32   CODEC(Gorilla, ZSTD(1)),
    note  String    CODEC(ZSTD(3))
)
ENGINE = MergeTree ORDER BY (id, ts);
Codec Good for
LZ4 (default) Fast general-purpose
LZ4HC(level) Slower writes, better ratio
ZSTD(level) Better ratio than LZ4; level 1 to 22 (1 to 3 common)
Delta Slowly changing integers: stores differences
DoubleDelta Regularly spaced timestamps and counters
Gorilla Floats that change slowly (sensor data)
T64 Integers that use few of their bits
FPC Floating-point data
NONE No compression

A specialised codec prepares the data (turning values into small, repetitive numbers) and a general codec then compresses that. Higher compression costs CPU on writes and sometimes on reads, so benchmark on your data. You can see the effect:

SQL
SELECT name, formatReadableSize(data_compressed_bytes) AS compressed,
       formatReadableSize(data_uncompressed_bytes) AS raw,
       round(data_uncompressed_bytes / data_compressed_bytes, 1) AS ratio
FROM system.columns
WHERE table = 'sensor';

Why delta encoding helps

A timestamp column of readings taken every 10 seconds looks like 1000, 1010, 1020, 1030. Delta encoding stores the first value and then 10, 10, 10, which compress to almost nothing. The query below does that arithmetic.

SQL Stand-in: delta encoding by hand (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Notice the deltas are small repeating numbers (10, 10, 10, 11) and the second differences are almost all zero, which is what DoubleDelta exploits.

Changing data: ALTER and mutations

Because parts are immutable, changing existing rows means rewriting parts. Two statements do it:

SQL
ALTER TABLE users UPDATE plan = 'pro' WHERE user_id = 42;
ALTER TABLE users DELETE WHERE signup_date < '2020-01-01';

These are mutations: they run asynchronously in the background, rewrite every part that contains matching rows, and cannot be rolled back. They are meant for occasional corrections (GDPR erasure, a bad import), not for routine updates. Watch them with:

SQL
SELECT mutation_id, command, parts_to_do, is_done, latest_fail_reason
FROM system.mutations WHERE table = 'users';
KILL MUTATION WHERE mutation_id = 'mutation_12.txt';

Schema changes that touch only metadata are cheap and instant:

SQL
ALTER TABLE users ADD COLUMN country LowCardinality(String) DEFAULT 'unknown';
ALTER TABLE users DROP COLUMN legacy_flag;
ALTER TABLE users RENAME COLUMN mail TO email;
ALTER TABLE users MODIFY COLUMN plan LowCardinality(String);   -- may rewrite data

Lightweight delete

A plain SQL DELETE also exists:

SQL
DELETE FROM users WHERE user_id = 42;

It first marks rows as deleted with a hidden mask, so it returns quickly and the rows disappear from queries at once; the physical removal happens later at merge time. It is far cheaper than ALTER TABLE ... DELETE, but it still is not for frequent single-row churn. Updates have a lightweight form in recent versions too (UPDATE ... SET ... WHERE); check that your server version supports it. For high-churn data use a ReplacingMergeTree.

Dropping data by partition

When data is partitioned by time, the cheapest delete of all is DROP PARTITION (partitioning). Plan retention with TTL plus partitions, and keep mutations for rare cases.

Common beginner mistakes

  • Expecting TTL to delete rows on the exact second. It happens during merges.
  • Using ALTER ... UPDATE in a loop, flooding the server with rewrites.
  • Applying the strongest codec everywhere without measuring.
  • Adding a TTL to a huge table and not realising MATERIALIZE TTL rewrites it.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. When is TTL-expired data removed?

    TTL is applied during merges, controlled by merge_with_ttl_timeout.
  2. 2. Which codec is designed for regularly spaced timestamps?

    DoubleDelta stores the change of the change, which is near zero for regular intervals.
  3. 3. What is a mutation?

    Mutations rewrite whole parts and are heavy; use them sparingly.
  4. 4. How does lightweight DELETE FROM differ from ALTER TABLE ... DELETE?

    Lightweight delete writes a mask so rows vanish from queries at once; merges clean up later.

Summary

  • TTL deletes, moves or aggregates old data during merges; use ttl_only_drop_parts with matching partitions for cheap expiry.
  • Codecs combine a specialised step (Delta, DoubleDelta, Gorilla, T64) with LZ4 or ZSTD; measure on your data.
  • ALTER ... UPDATE/DELETE are heavy background mutations; DELETE FROM is a lightweight mask.
  • Metadata-only ALTER operations such as ADD COLUMN are instant.

Next: SELECT in ClickHouse: extras