How MergeTree stores data

Almost every ClickHouse performance and operations question comes back to one object: the part. Inserts create parts, merges combine them, mutations rewrite them, TTL expires them, replicas exchange them by name, and queries decide which ones to read. This guide follows one MergeTree table through all of that, looking at the actual files and system tables rather than the documentation’s description of them. Everything below was run on ClickHouse 26.4 with clickhouse local --path, so the directory listings are real; the one thing a local run cannot show (the merge history in system.part_log) is described but not run.

How the sorting key, partition key and skipping indexes decide which parts and granules a query reads is covered in ORDER BY, PARTITION BY and pruning and data skipping indexes. This guide stays on the storage side of that line.

Contents

• A part is a directory
• Reading a part name
• Partitions are logical
• Merges: what, when and how
• Horizontal and vertical merges
• OPTIMIZE and FINAL
• Changing data: mutations
• Lightweight DELETE and UPDATE
• TTL: expiry by the engine
• Which one to use

A part is a directory

Each INSERT into a MergeTree table creates, for every partition it touches, one new directory on disk: a part. Inside it the rows are stored sorted by the table’s ORDER BY key, column by column, together with a sparse primary index and a few metadata files. A part is immutable: ClickHouse never edits one. Changes are made by writing new parts and discarding old ones, which is why merges, mutations and TTL all work by rewriting.

CREATE DATABASE analytics;

CREATE TABLE analytics.events
(
    event_time DateTime, user_id UInt64, event_type LowCardinality(String),
    url String, duration_ms UInt32, country LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time);

Insert 2000 hourly rows twice, starting two weeks apart, so the data covers a few months:

INSERT INTO analytics.events
SELECT toDateTime('2026-01-01 00:00:00') + number * 3600, number % 1000,
       ['view','click','buy'][number % 3 + 1], concat('/p/', toString(number % 50)),
       number % 5000, ['US','DE','TR'][number % 3 + 1]
FROM numbers(2000)
SETTINGS async_insert = 0;

INSERT INTO analytics.events
SELECT toDateTime('2026-01-15 00:00:00') + number * 3600, number % 1000,
       ['view','click','buy'][number % 3 + 1], concat('/p/', toString(number % 50)),
       number % 5000, ['US','DE','TR'][number % 3 + 1]
FROM numbers(2000)
SETTINGS async_insert = 0;

SELECT partition_id, name, rows, min_time, max_time, level, part_type
FROM system.parts
WHERE database = 'analytics' AND `table` = 'events' AND active
ORDER BY name;

┌─partition_id─┬─name─────────┬─rows─┬────────────min_time─┬────────────max_time─┬─level─┬─part_type─┐
│ 202601       │ 202601_1_1_0 │  744 │ 2026-01-01 00:00:00 │ 2026-01-31 23:00:00 │     0 │ Compact   │
│ 202601       │ 202601_4_4_0 │  408 │ 2026-01-15 00:00:00 │ 2026-01-31 23:00:00 │     0 │ Compact   │
│ 202602       │ 202602_2_2_0 │  672 │ 2026-02-01 00:00:00 │ 2026-02-28 23:00:00 │     0 │ Compact   │
│ 202602       │ 202602_5_5_0 │  672 │ 2026-02-01 00:00:00 │ 2026-02-28 23:00:00 │     0 │ Compact   │
│ 202603       │ 202603_3_3_0 │  584 │ 2026-03-01 00:00:00 │ 2026-03-25 07:00:00 │     0 │ Compact   │
│ 202603       │ 202603_6_6_0 │  744 │ 2026-03-01 00:00:00 │ 2026-03-31 23:00:00 │     0 │ Compact   │
│ 202604       │ 202604_7_7_0 │  176 │ 2026-04-01 00:00:00 │ 2026-04-08 07:00:00 │     0 │ Compact   │
└──────────────┴──────────────┴──────┴─────────────────────┴─────────────────────┴───────┴───────────┘

Two inserts created seven parts, because each insert was split by month and each piece became its own part. Part counts are therefore decided by how you insert, which is the whole story of too many parts. Even a single INSERT ... SELECT is cut into blocks: three million rows with default settings arrive as three parts of roughly a million rows, because max_insert_block_size is 1,048,449 rows.

What is inside

A tiny part is stored in the Compact format: all columns share one data file. Past a size threshold (min_bytes_for_wide_part, 10 MiB by default) a part is Wide: one stream file per column. To look at a wide part, build a larger table with all three million rows in one block:

CREATE TABLE analytics.events_big
(
    event_time DateTime, user_id UInt64, event_type LowCardinality(String),
    url String, duration_ms UInt32, country LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time);

INSERT INTO analytics.events_big
SELECT toDateTime('2026-03-01 00:00:00') + intDiv(number * 2592000, 3000000), number % 100000,
       ['view','click','buy'][number % 3 + 1], concat('/page/', toString(number % 5000), '/item'),
       (number * 7919) % 5000, ['US','DE','TR'][number % 3 + 1]
FROM numbers(3000000)
SETTINGS max_insert_block_size = 3000000, min_insert_block_size_rows = 3000000;

SELECT name, part_type, rows, marks,
       formatReadableSize(bytes_on_disk) AS on_disk,
       formatReadableSize(data_uncompressed_bytes) AS uncompressed,
       formatReadableSize(primary_key_bytes_in_memory) AS pk_in_memory
FROM system.parts
WHERE database = 'analytics' AND `table` = 'events_big' AND active
FORMAT Vertical;

Row 1:
──────
name:         202603_1_1_0
part_type:    Wide
rows:         3000000
marks:        368
on_disk:      38.43 MiB
uncompressed: 116.68 MiB
pk_in_memory: 1.84 KiB

Three million rows are 368 marks: one mark per granule of index_granularity = 8192 rows (3,000,000 ÷ 8192 rounds up to 367, plus a final mark that closes the last granule). The primary index holds one entry per mark, which is why it is 1.84 KiB in memory for a table of 117 MiB; this smallness is the reason the whole index of a huge table stays in RAM. The compressed data is a third of the uncompressed size, and where the bytes go is visible per column:

SELECT column,
       formatReadableSize(sum(column_data_compressed_bytes)) AS compressed,
       formatReadableSize(sum(column_data_uncompressed_bytes)) AS raw,
       round(sum(column_data_uncompressed_bytes) / sum(column_data_compressed_bytes), 1) AS ratio
FROM system.parts_columns
WHERE database = 'analytics' AND `table` = 'events_big' AND active
GROUP BY column
ORDER BY sum(column_data_compressed_bytes) DESC;

┌─column──────┬─compressed─┬─raw───────┬─ratio─┐
│ url         │ 11.86 MiB  │ 65.17 MiB │   5.5 │
│ user_id     │ 11.50 MiB  │ 22.89 MiB │     2 │
│ event_time  │ 11.49 MiB  │ 11.44 MiB │     1 │
│ duration_ms │ 3.55 MiB   │ 11.44 MiB │   3.2 │
│ event_type  │ 13.83 KiB  │ 2.87 MiB  │ 212.3 │
│ country     │ 13.82 KiB  │ 2.87 MiB  │ 212.4 │
└─────────────┴────────────┴───────────┴───────────────┘

The two LowCardinality columns cost 14 KiB each against megabytes for the others, and event_time does not compress at all here: sorted timestamps are 4-byte integers that LZ4 alone cannot shrink, which is what the delta codecs in choosing codecs are for. This is the quickest way to find which column to attack when a table is too big.

On disk, the same part is a directory under the table’s store path (system.parts.path). ls -l of 202603_1_1_0 on 26.4 shows:

12051843  0b2ca14139f4db0f1550cae306a6515c.bin     <- stream data (compressed blocks)
    1078  0b2ca14139f4db0f1550cae306a6515c.cmrk2   <- its marks (offsets per granule)
   ... seven more .bin/.cmrk2 pairs, 46 bytes to 12 MB ...
    1256  checksums.txt
     358  columns_substreams.txt
     179  columns.txt
       7  count.txt
      10  default_compression_codec.txt
       1  metadata_version.txt
       8  minmax_0b2ca14139f4db0f1550cae306a6515c.idx
       4  partition.dat
    1076  primary.cidx
     427  serialization.json

Three things worth noticing. First, nine stream files for six columns: LowCardinality columns have a .dict stream and a data stream, and the String column has a .size stream, as columns_substreams.txt lists. Second, on 26.4 the stream files are named by a hash of the stream name, so you cannot read the column off the file name; match them by size against system.parts_columns, or read columns_substreams.txt. Third, the files you would expect to be big are not: primary.cidx (the compressed sparse primary index) is 1,076 bytes, count.txt holds the text 3000000, and partition.dat holds the partition value as four bytes, 6b 17 03 00, which read as a little-endian integer is 0x0003176b = 202603. The small minmax_*.idx file holds the minimum and maximum of the partition column in this part, which comes back below. In a Compact part the stream files collapse to data.bin and data.cmrk4 plus the same metadata files, and the exact file set is a detail of the version, not a contract.

Reading a part name

A part name has the form <partition_id>_<min_block>_<max_block>_<level>, optionally followed by a mutation version:

• partition_id identifies the partition (202601).
• min_block and max_block are the range of block numbers, a per-table counter incremented on each insert. A fresh part covers one block (1_1); a merged part covers the union of the ranges of the parts it replaced.
• level counts merge generations. New parts are level 0; merging level-0 parts yields level 1.
• A fifth number, the mutation version, appears after a mutation: all_1_1_0_2 is the part all_1_1_0 after mutation number 2 rewrote it.

The counter is table-wide, not per partition, which is why block numbers jump (202601_1_1_0, then 202601_4_4_0: blocks 2 and 3 went to other partitions). These numbers are the replication contract: replicas exchange part names, and a part with the same name is assumed to hold the same data (replication internals). The name is also readable history: the block range tells you which inserts a part contains, and the level tells you how much merging it has been through.

Partitions are logical

A partition is not a thing on disk. There is no partition directory and no partition object. It is a label: all parts whose rows evaluate to the same PARTITION BY value belong to the same partition, and each part keeps a tiny partition.dat with the key for the whole part. The value is not stored per row.

What partitions are for:

• Lifecycle. DROP PARTITION, DETACH PARTITION, MOVE PARTITION and ATTACH PARTITION FROM operate on whole partitions as cheap metadata operations (fast bulk load with ATTACH PARTITION).
• Merge boundaries. Parts from different partitions are never merged together, so a partition is also a unit of merge work.
• Coarse pruning, through the min-max index.

What they are not for is query speed in general: that is the sorting key’s job. Partitioning too finely produces many small parts, which makes both inserts and queries slower; a monthly partition is a good default for time series, daily is sensible for very large tables, and anything finer needs a reason.

The min-max index is the piece of storage detail that the pruning guide builds on. For each column used in the partition expression, every part records the minimum and maximum value found in it (the minmax_*.idx file, also held in memory). A filter on that column is compared with those ranges and non-matching parts are ignored before the primary index is consulted. EXPLAIN indexes = 1 shows it on the small table:

EXPLAIN indexes = 1
SELECT count() FROM analytics.events
WHERE event_time >= '2026-03-01' AND event_time < '2026-04-01';

          Partition Min-Max
            Keys:
              event_time
            Condition: and((event_time in (-Inf, 1774990799]), (event_time in [1772312400, +Inf)))
            Parts: 2/7
            Granules: 2/7

Parts: 2/7 means: of seven active parts, only the two covering March need to be read. The query never knew about partitions, only about the min and max of event_time per part. The full plan, with the Partition and PrimaryKey stages that follow, is annotated in the pruning guide.

Merges: what, when and how

A background merge picks several parts from the same partition, reads them (they are already sorted, so it is a streaming k-way merge) and writes one new part. The source parts become inactive and are deleted old_parts_lifetime seconds later (480 by default, so disk usage briefly exceeds the table’s real size). The new part’s level is above any of its sources, and its block range covers theirs. Merges are why the engine is called MergeTree, and the engine variant defines what “merging rows” means:

Engine	At merge time
MergeTree	rows are only concatenated in sort order
ReplacingMergeTree	rows with the same sorting key collapse to the latest one
SummingMergeTree	rows with the same sorting key are summed
AggregatingMergeTree	aggregate states with the same key are combined
CollapsingMergeTree / VersionedCollapsingMergeTree	pairs of rows with opposite sign cancel

For the specialised engines, the table is only eventually deduplicated or aggregated: until two rows meet in a merge, both exist.

Which parts get merged is the job of a selector (merge_selector_algorithm is Simple by default on 26.4). Two experiments show what that means in practice. First, sixteen tiny parts, with merges stopped while they are created:

CREATE TABLE analytics.m (id UInt64, v UInt32) ENGINE = MergeTree ORDER BY id;
SYSTEM STOP MERGES analytics.m;

INSERT INTO analytics.m SELECT number, 1 FROM numbers(16000)
SETTINGS max_block_size = 1000, max_insert_block_size = 1000,
         min_insert_block_size_rows = 0, min_insert_block_size_bytes = 0, max_threads = 1;

SELECT count() AS active_parts FROM system.parts WHERE `table` = 'm' AND active;   -- 16

SYSTEM START MERGES analytics.m;
-- the merge scheduler wakes every few seconds (merge_selecting_sleep_ms = 5000)
SELECT sleep(3) FORMAT Null;
SELECT sleep(3) FORMAT Null;
SELECT sleep(3) FORMAT Null;

SELECT name, level, rows FROM system.parts WHERE `table` = 'm' AND active ORDER BY name;

┌─name───────┬─level─┬──rows─┐
│ all_1_16_1 │     1 │ 16000 │
└────────────┴───────┴───────┘

All sixteen were merged in one go into all_1_16_1: block range 1 to 16, level 1. The second experiment puts one large part next to five small ones:

CREATE TABLE analytics.m2 (id UInt64, v UInt32) ENGINE = MergeTree ORDER BY id;
SYSTEM STOP MERGES analytics.m2;

INSERT INTO analytics.m2 SELECT number, 1 FROM numbers(1000000);
INSERT INTO analytics.m2 SELECT number, 1 FROM numbers(5000)
SETTINGS max_block_size = 1000, max_insert_block_size = 1000,
         min_insert_block_size_rows = 0, min_insert_block_size_bytes = 0, max_threads = 1;

SYSTEM START MERGES analytics.m2;
SELECT sleep(3) FORMAT Null;
SELECT sleep(3) FORMAT Null;
SELECT sleep(3) FORMAT Null;
SELECT sleep(3) FORMAT Null;

SELECT name, level, rows FROM system.parts WHERE `table` = 'm2' AND active ORDER BY name;

┌─name──────┬─level─┬────rows─┐
│ all_1_1_0 │     0 │ 1000000 │
│ all_2_6_1 │     1 │    5000 │
└───────────┴───────┴─────────┘

The five small parts merged with each other and the million-row part was left alone. That is the design: rewriting a large part to absorb a small one would copy a million rows to add a thousand, so the selector prefers merging parts of similar size, which keeps write amplification low as the table grows. A consequence people trip over is that there is no guarantee of “one part per partition”: big parts are left alone, and old partitions end up with a handful of large parts. The selector is also bounded by the merge pool (background_pool_size is 16 threads with a concurrency ratio of 2 on 26.4), by max_bytes_to_merge_at_max_space_in_pool (150 GiB per merge), and by max_parts_to_merge_at_once (100). When the pool is saturated, merging falls behind inserts and active parts accumulate until parts_to_delay_insert (1,000) starts delaying inserts and parts_to_throw_insert (3,000) rejects them with Too many parts. Merges are automatic; you control them mostly by controlling how many parts you create.

Horizontal and vertical merges

ClickHouse has two merge algorithms. They give the same result and differ in how they read and write.

A horizontal merge reads all columns of all source parts together, row by row, and writes all columns of the result. Memory grows with the number of columns, because every column of every source part needs read buffers at the same time.

A vertical merge works in two phases. It first merges only the key columns (the sorting key, plus the version or sign column the engine needs) and records, for every output row, which source row it came from. It then produces every remaining column one at a time using that mapping. Peak memory is roughly one column wide instead of the full row width, which matters for tables with hundreds of columns.

The choice is automatic and set by three MergeTree settings (defaults from system.merge_tree_settings on 26.4):

Setting	Default	Meaning
enable_vertical_merge_algorithm	1	allow the vertical algorithm at all
vertical_merge_algorithm_min_rows_to_activate	131072	use it only when the merged parts total at least about this many rows
vertical_merge_algorithm_min_columns_to_activate	11	use it only when there are at least this many non-key columns

Small merges and narrow tables stay horizontal because the vertical path has more overhead; a table with a handful of columns never shows vertical merges, however large it is. To see merges in flight, system.merges lists each running merge with its algorithm, progress and memory:

SELECT `table`, partition_id, num_parts, merge_algorithm,
       round(progress * 100) AS pct, elapsed,
       formatReadableSize(memory_usage) AS mem
FROM system.merges
ORDER BY elapsed DESC;

The history lives in system.part_log (event type MergeParts, with merge_algorithm, merge_reason and duration_ms), which exists only on a server where the log is enabled, so this guide could not run it. You rarely need to change the settings. They matter when merges of a very wide table run out of memory (keep vertical enabled and lower the activation thresholds) or when you wonder why one merge uses so much RAM. For a replicated table’s view of the same machinery see the runbook for parts, merges and mutations.

OPTIMIZE and FINAL

OPTIMIZE TABLE asks the server to schedule a merge now rather than wait for the scheduler. On analytics.events, partition 202601 has two active parts:

OPTIMIZE TABLE analytics.events PARTITION ID '202601';

SELECT name, level, rows, active
FROM system.parts
WHERE database = 'analytics' AND `table` = 'events' AND partition_id = '202601'
ORDER BY name;

┌─name─────────┬─level─┬─rows─┬─active─┐
│ 202601_1_1_0 │     0 │  744 │      0 │
│ 202601_1_4_1 │     1 │ 1152 │      1 │
│ 202601_4_4_0 │     0 │  408 │      0 │
└──────────────┴───────┴──────┴────────┘

The two level-0 parts became 202601_1_4_1: blocks 1 to 4, level 1, all 1152 rows; the sources stay listed as inactive until cleanup removes them.

What plain OPTIMIZE does not do. Without FINAL it does nothing for a partition that is already a single part, such as 202604, and by default it still answers “Ok”, which is why people say it “doesn’t work”. Set optimize_throw_if_noop = 1 to get the reason as an error:

OPTIMIZE TABLE analytics.events PARTITION ID '202604' SETTINGS optimize_throw_if_noop = 1;

Code: 388. DB::Exception: Cannot OPTIMIZE table: There is only one part inside partition. (CANNOT_ASSIGN_OPTIMIZE)

What FINAL does. OPTIMIZE ... FINAL also rewrites a lone part, and ignores the maximum merge size. For plain MergeTree that is pointless work, and it is not free. On the 38 MiB single-part partition of events_big, each FINAL rewrote the whole part and bumped its level:

OPTIMIZE TABLE analytics.events_big PARTITION ID '202603' FINAL;   -- run three times
SELECT name, level, rows, active, formatReadableSize(bytes_on_disk) AS size
FROM system.parts WHERE `table` = 'events_big' ORDER BY name;

After three runs the surviving part is 202603_1_1_3, level 3 with the same 3,000,000 rows and 38.43 MiB, the product of three full rewrites that changed nothing. A clickhouse local process doing nothing but start up and exit took 0.13 s; the same process running FINAL on this one 38 MiB part took 0.27 to 0.29 s, so the rewrite cost about 0.15 s for 38 MiB, which scales with the bytes in the selected partitions. On a 500 GiB partition the same statement reads and writes 500 GiB. For engines that deduplicate or aggregate, FINAL is sometimes the right tool, since the rewrite applies the engine’s merge logic to rows inside one part. Restrict it to one partition (PARTITION ID '...'), run it off-peak, and find the candidates by counting active parts:

SELECT database, `table`, partition_id, count() AS active_parts
FROM system.parts
WHERE `table` = 'events' AND active
GROUP BY database, `table`, partition_id
ORDER BY active_parts DESC
LIMIT 20;

On a replicated table, OPTIMIZE runs on the replica you connect to and the resulting part is replicated; use ON CLUSTER only if you want every shard to do it.

FINAL in a SELECT is another thing. The same word in a query writes nothing: SELECT ... FROM t FINAL applies the engine’s merge logic on the fly while reading, so you see deduplicated rows before the parts have merged.

CREATE TABLE profiles (user_id UInt64, plan String, updated_at DateTime)
ENGINE = ReplacingMergeTree(updated_at) ORDER BY user_id;

SYSTEM STOP MERGES profiles;

INSERT INTO profiles SELECT number, 'free', '2026-01-01 00:00:00' FROM numbers(3);
INSERT INTO profiles SELECT number, 'pro',  '2026-02-01 00:00:00' FROM numbers(2);

SELECT count() FROM profiles;          -- 5, all rows are stored
SELECT count() FROM profiles FINAL;    -- 3, one per user_id, latest wins

SYSTEM START MERGES profiles;
OPTIMIZE TABLE profiles FINAL;
SELECT count() FROM profiles;          -- 3, now physically deduplicated

That is exactly what the run printed: 5, 3, then 3. Read-time FINAL costs extra CPU and memory because it merges on read, and recent versions parallelise it much better than old ones. To put a number on it: on a three-part, 3.5-million-row ReplacingMergeTree, a plain count() answered from metadata in about 1 ms, count() with FINAL took about 19 ms, uniqExact(user_id) about 42 ms, and the aggregation alternative (argMax(plan, updated_at) grouped by user_id) about 66 ms, each a single run on a small machine. So FINAL is not automatically the slow way to read, and it loses the metadata shortcut of an unqualified count(); measure on your own data. The long-term answer for deduplication remains letting background merges do it, and aggregating where exact freshness matters.

Changing data: mutations

Parts are immutable, so how do you change or remove a row already in one? ClickHouse has four mechanisms with very different costs: mutations, lightweight DELETE, lightweight UPDATE and TTL. A mutation is ALTER TABLE ... UPDATE or ALTER TABLE ... DELETE, queued as a background job. For every part it applies to, ClickHouse writes a new part with the change applied and swaps it in. The consequences:

• It is asynchronous by default. The statement returns when the mutation is queued; SETTINGS mutations_sync = 1 waits on this server, 2 on all replicas.
• Mutations are applied part by part, so for a while some parts have the change and others do not, and a query in between sees a mix.
• On replicated tables the mutation is recorded in Keeper and every replica applies it independently.
• Mutations run one after another per table, so a long one blocks those behind it.

The cost model is the interesting part, and it is not what most descriptions say. The cost is proportional to the data in the columns the mutation has to rewrite, because a new part reuses unchanged column files by hard link. Three experiments on a 300,000-row table (one wide part, 9 stream files), listing the new part’s files with ls -li and counting links:

ls -li chpath/store/.../all_1_1_0_2/ | awk 'NR>1 && $10 ~ /\.bin$/ {print $1, $3, $6, $10}'

Statement	New part	Stream files rewritten	Stream files hard-linked from the old part
ALTER TABLE events UPDATE duration_ms = 0 WHERE event_type = 'click'	all_1_1_0_2	1 (385,364 to 272,131 bytes)	8, plus primary.cidx and count.txt
ALTER TABLE events DELETE WHERE event_type = 'buy'	all_1_1_0_2	all 9 (each smaller)	0
DELETE FROM events WHERE event_type = 'buy'	all_1_1_0_2	0, one new 1,357-byte file	all 9

So an UPDATE of one column rewrites that column and links the rest (link count 2, same inode), a heavyweight DELETE has to rewrite every column because rows disappear from all of them, and the lightweight DELETE rewrites nothing at all. Deleting one row from a 100 GB part with ALTER ... DELETE rewrites the part; updating one small column of the same part rewrites only that column. In both cases the old part sits there inactive for a few minutes, so disk usage dips up before it comes down. Mutations are the right tool for rare, large corrections and the wrong tool for application logic that updates single rows all day.

Watch them through system.mutations. The columns you need are parts_to_do, is_done and, when something is wrong, latest_failed_part, latest_fail_reason and latest_fail_time:

SELECT database, `table`, mutation_id, command, create_time, parts_to_do, latest_fail_reason
FROM system.mutations
WHERE NOT is_done
ORDER BY create_time;

When parts_to_do reaches zero the mutation is complete. One that never finishes usually cannot be applied to some part (a type conversion that fails) and sits there blocking the queue; fix the cause or cancel it with KILL MUTATION WHERE ....

Lightweight DELETE and UPDATE

DELETE FROM is the lightweight variant. It records which rows are deleted in a hidden mask column, _row_exists; queries filter masked rows, and later merges drop the rows physically. It is internally still a mutation, an update of _row_exists, and system.mutations shows it:

CREATE TABLE events (event_time DateTime, user_id UInt64, event_type LowCardinality(String),
                     url String, duration_ms UInt32, country LowCardinality(String))
ENGINE = MergeTree ORDER BY (event_type, event_time);

INSERT INTO events
SELECT toDateTime('2026-03-01 00:00:00') + number, number,
       ['view','click','buy'][number % 3 + 1], '/', number, 'DE'
FROM numbers(30);

DELETE FROM events WHERE event_type = 'buy';

SELECT count() FROM events;   -- 20

SELECT mutation_id, command, parts_to_do, is_done
FROM system.mutations
WHERE database = currentDatabase() AND `table` = 'events'
FORMAT Vertical;

Row 1:
──────
mutation_id: mutation_2.txt
command:     (UPDATE _row_exists = 0 WHERE event_type = 'buy')
parts_to_do: 0
is_done:     1

Points to remember:

• lightweight_deletes_sync. It works like mutations_sync, and on 26.4 its default is 2: the statement waits until all replicas have applied it, so a big delete holds your connection open and a dropped connection can look like a failure. With lightweight_deletes_sync = 0 the statement returns at once, and the rows are not yet hidden: with 0, count() returned 30 twice in a row after the delete and 20 only after a two-second wait, and the mutation showed parts_to_do: 1 in between (three runs, same result). If code deletes and then reads, keep the default or wait on system.mutations.
• Reads get slower the more you delete. The mask must be applied on every query until the affected parts are merged. Keep it for a small fraction of rows; for bulk removal drop partitions or rebuild.
• Space is not freed immediately. The rows stay on disk until a merge rewrites the part; system.parts.rows still counts them.
• Projections. A table with projections rejects DELETE FROM unless lightweight_mutation_projection_mode is set (its default is throw).
• Applying the mask yourself. APPLY DELETED MASK rewrites the parts that carry a mask, drops the hidden column and the deleted rows:

SELECT count() AS parts_with_mask
FROM system.parts_columns
WHERE `table` = 'events' AND active AND column = '_row_exists';   -- 1

ALTER TABLE events APPLY DELETED MASK SETTINGS mutations_sync = 2;

-- the same query now returns 0; system.parts shows the new part with 20 rows (it had 30)

ON CLUSTER, IN PARTITION and mutations_sync variants are allowed, so you can apply the mask partition by partition on a large table.

Lightweight UPDATE

UPDATE ... SET ... WHERE, without ALTER TABLE, is the lightweight update. Instead of rewriting a column it writes a small patch part that holds only the changed rows and columns, and queries apply the patch when they read; later merges fold patches into the main part. It needs two table settings, which materialize the system columns the patches refer to:

CREATE TABLE lw (id UInt64, v UInt32)
ENGINE = MergeTree ORDER BY id
SETTINGS enable_block_number_column = 1, enable_block_offset_column = 1;

INSERT INTO lw SELECT number, 0 FROM numbers(5);

UPDATE lw SET v = 7 WHERE id = 2;

SELECT * FROM lw ORDER BY id;

SELECT name, rows, active FROM system.parts WHERE `table` = 'lw' ORDER BY name;

0	0
1	0
2	7
3	0
4	0

all_1_1_0                                          5   1
patch-9834becdf7e2e15ad7c410ffd6a5a63c-all_3_3_0_2 1   1

The patch part holds exactly one row. On a table without those two settings the same statement fails with an error that names the setting to enable. The documentation labels lightweight updates beta, designed for small updates (up to about 10% of a table), unable to change columns used in the primary or partition key, and costly if you issue a stream of tiny updates, since each creates a part. Batch changes into one statement, and check the status for your release before depending on it.

TTL: expiry by the engine

A TTL expression lets ClickHouse remove or move data on its own, with merges as the vehicle. The basic form drops rows after a time, TTL event_time + INTERVAL 90 DAY. The less known form removes only rows that match a condition, which is exactly right for a work queue where finished items should disappear while pending ones must stay:

CREATE TABLE jobs_queue
(
    job_id UInt64,
    payload String,
    state Enum8('new' = 1, 'processing' = 2, 'done' = 3),
    state_changed_at DateTime DEFAULT now()
)
ENGINE = MergeTree
ORDER BY job_id
TTL state_changed_at + INTERVAL 1 MONTH DELETE WHERE state = 'done';

Rows in state new or processing are never removed by this rule however old they are, and done rows disappear a month after their last state change. To watch it work, take the same table without the TTL line (so that nothing is dropped on insert), load nine rows that are two months old, three per state, and three fresh done rows, and add the rule afterwards:

CREATE TABLE jobs_queue_plain
(
    job_id UInt64,
    payload String,
    state Enum8('new' = 1, 'processing' = 2, 'done' = 3),
    state_changed_at DateTime DEFAULT now()
)
ENGINE = MergeTree
ORDER BY job_id;

INSERT INTO jobs_queue_plain
SELECT number, 'x', multiIf(number % 3 = 0, 'done', number % 3 = 1, 'new', 'processing'),
       now() - INTERVAL 2 MONTH
FROM numbers(9);

INSERT INTO jobs_queue_plain SELECT number + 100, 'x', 'done', now() FROM numbers(3);

SELECT state, count() FROM jobs_queue_plain GROUP BY state ORDER BY state;

new         3
processing  3
done        6

ALTER TABLE jobs_queue_plain
    MODIFY TTL state_changed_at + INTERVAL 1 MONTH DELETE WHERE state = 'done'
    SETTINGS mutations_sync = 2;

SELECT state, count() FROM jobs_queue_plain GROUP BY state ORDER BY state;

new         3
processing  3
done        3

The three old done rows are gone; the three fresh ones remain because they are not yet a month old. MODIFY TTL applies the rule to existing data through a mutation (it shows in system.mutations as MATERIALIZE TTL; materialize_ttl_after_modify is on by default). Rows older than the TTL are also dropped as they are inserted, so back-filling old data into a table with a TTL silently discards it.

How the deletion is carried out matters operationally:

• Expired rows are removed when parts are merged. A background job schedules TTL merges on its own every merge_with_ttl_timeout seconds (14,400, four hours, by default), so deletion is not immediate.
• At most max_number_of_merges_with_ttl_in_pool TTL merges run at a time (2 by default). On busy replicated tables this is the cause of log lines about postponed merges.
• OPTIMIZE TABLE ... FINAL forces expired rows out now, at the price of a rewrite. ttl_only_drop_parts = 1 (default 0) makes ClickHouse drop whole parts instead of rewriting them, but only when every row in the part has expired; with a partition key aligned to the TTL, that turns expiry into cheap part drops.
• A TTL ... TO VOLUME or TO DISK clause moves parts between disks instead of deleting, see tiered storage.

Which one to use

Need	Use
Remove old data by age	TTL, or drop partitions
Remove a whole partition	ALTER TABLE ... DROP PARTITION
Delete a modest number of rows now	DELETE FROM
Remove a large share of a table	rebuild with INSERT SELECT, or REPLACE PARTITION
Rare large correction of a few columns	ALTER TABLE ... UPDATE
Small, frequent updates	lightweight UPDATE (beta), or model the data as inserts with ReplacingMergeTree

The thread through all of it is the same: ClickHouse never edits a part, so every operation is a question of how many bytes get rewritten and how many parts get created. When a table misbehaves, the first three queries are always the same: active parts per partition in system.parts, running work in system.merges, and pending work in system.mutations.