The MergeTree On-Disk Format

The mental model

A MergeTree part is a directory, and everything about how a query reads it follows from a small number of decisions about what goes in that directory and how it's laid out.

Every column's data lives in its own compressed file, unless the part is small enough that ClickHouse decided otherwise. ClickHouse's own documentation describes two physical part formats: "In Wide format each column is stored in a separate file in a filesystem, in Compact format all columns are stored in one file." Which one a part uses isn't a setting you pick per table — it's decided automatically per part, based on min_bytes_for_wide_part and min_rows_for_wide_part: a part smaller than those thresholds (10 MB by default, as of the version this behavior became default) is written Compact, storing every column consecutively in one file "to increase the spatial locality for reads and writes," which helps exactly the case this course has already warned about — lots of small, frequent inserts producing lots of small parts. A part large enough to cross that threshold gets Wide format instead, and Wide format is where the rest of this lesson's detail lives, because it's the shape a mature, actively-growing table's parts settle into.

Within a Wide-format part, a column's compressed data and the index that finds a specific granule inside it are two genuinely separate files, serving two different questions. column.bin holds the column's values, compressed, as a sequence of independent compressed blocks. A separate column.mrk (or .mrk2, with adaptive granularity, or .mrk3 for Compact parts) file stores one entry per granule — not the granule's data, but where that granule's data physically sits: "The first offset ('block_offset') is locating the block in the compressed column data file... the second offset ('granule_offset') provides the location of the granule within the uncompressed block data." That two-part offset exists because compression operates on blocks of many rows at once, not per-row — so pinpointing one granule means first finding which compressed block it's in, then where inside that block (once decompressed) it starts.

The primary index answers a different question than the marks do, and conflating them is the single easiest mistake to make at this depth. sparse-primary-index, back in Module 3, already established that primary.idx is a small, uncompressed array holding one primary-key entry per granule, used to decide which granules could possibly match a filter via binary search. The per-column .mrk files answer a completely different question, asked only after the primary index has already narrowed the search: given that granule 4,821 is one we need, where physically is it, in this specific column's .bin file? One structure (primary.idx) is about narrowing a search space; the other (per-column marks) is about physically locating what that search already selected. They're both "marks" in casual conversation, which is exactly why it's worth being precise: the primary index has its own array, and every column has its own separate mark file, and a query touching five columns consults five different mark files, once each, after the primary index has already done its job once.

A checksum manifest ties the whole directory together. checksums.txt records, per this course's research into MergeTreeDataPartChecksum (the source class handling this — see the Codebase Tour below), a checksum for every file in the part; columns.txt records the part's column list and types; count.txt and partition.dat record the part's row count and partition value respectively. This manifest is what makes a corrupted part detectable rather than silently wrong — covered directly in this lesson's failure mode gallery.

Codebase tour: where this lives in ClickHouse's source

Reading a compressed block's bytes directly

Every compressed block ClickHouse writes — inside any column's .bin file — starts with the same fixed header, documented precisely enough to read by hand:

checksum   16 bytes   CityHash128 of (header + compressed_data)
raw_size    4 bytes   length of (header + compressed_data)
data_size   4 bytes   size of the data once decompressed
mode        1 byte    codec identifier
[compressed_data follows]

The mode byte is the codec identifier, and a handful of its values are worth recognizing on sight: 0x02 for no compression (checksums only), 0x82 for LZ4, and 0x90 for ZSTD. That single byte is a genuinely useful diagnostic fact: you don't need system.columns or any query at all to know what codec compressed a given block — you can open the .bin file and read it directly, which is exactly what the next section does.

Debugging walkthrough: "we changed the codec, why hasn't disk usage changed?"

Symptom. A team runs ALTER TABLE events MODIFY COLUMN payload String CODEC(ZSTD(3)) on a large, long-running table, expecting payload's stored size to shrink — ZSTD generally compresses better than the LZ4 default. A day later, system.parts shows almost no change in bytes_on_disk. Is the codec change even working?

Step 1 — confirm the setting actually changed. ALTER TABLE ... MODIFY COLUMN ... CODEC(...) is a metadata-only operation: it updates the table's schema so future writes use the new codec, but it does not rewrite any bytes that already exist on disk — "new codec applied only to new parts, or after background merges or after mutations," in the words of the ClickHouse user who filed a real feature request asking for per-part codec visibility in system.part_columns, precisely because this metadata-only behavior leaves a table's parts on genuinely mixed codecs until something rewrites them. That single fact already explains the symptom: existing parts, written before the ALTER, are still compressed with the old codec, sitting untouched on disk.

Step 2 — find a specific part's real path. system.parts has a path column pointing at the part's directory on disk — the same table this course has used since Module 3, now used to go one level deeper:

SELECT name, path, bytes_on_disk
FROM system.parts
WHERE table = 'events' AND active
ORDER BY modification_time ASC
LIMIT 1;

┌─name───────────┬─path──────────────────────────────────────────────────────┬─bytes_on_disk─┐
│ 202401_1_50_3  │ /var/lib/clickhouse/store/d9f/d9f36a1a.../202401_1_50_3/   │     284091822 │
└────────────────┴──────────────────────────────────────────────────────────┴───────────────┘

Step 3 — read the codec byte directly, without trusting any system table. This is the payoff of Step 1's mental model: open payload.bin for this specific old part and look at the first block's header.

offset  0-15   checksum (CityHash128)         — 16 bytes, skip for this check
offset 16-19   raw_size                        — 4 bytes
offset 20-23   data_size                       — 4 bytes
offset 24      mode                            — 1 byte  <-- the codec
               0x82 observed here → LZ4, not the ZSTD(3) the ALTER requested

For this old part, the byte at offset 24 reads 0x82 — LZ4, exactly as the byte-value table above defines it, confirming the part predates the ALTER and was never touched by it.

Step 4 — confirm the fix, and the mechanism, without guessing. Since MODIFY COLUMN ... CODEC only affects new and re-merged parts, the two ways to actually get old data onto the new codec are the same two levers this course has used for every other "existing parts don't reflect a recent change" situation: wait for background merges to naturally rewrite old parts (Module 3), or force it immediately with OPTIMIZE TABLE events FINAL — which, like every other FINAL use in this course, does real, synchronous work proportional to how much data it has to rewrite, not a free operation.

Failure mode gallery

"Too many parts." The exact, verbatim error — DB::Exception: Too many parts (300). Merges are processing significantly slower than inserts — is real and common enough that ClickHouse maintains an official knowledge-base page specifically for it, and multiple real, publicly filed GitHub issues show the identical message across different users' incidents. The signature: writes suddenly start failing outright, not just running slowly, once a partition's active part count crosses a hard threshold — background merges have fallen behind the insert rate for long enough that ClickHouse refuses new parts rather than let the count grow unbounded. Root cause is almost always upstream of the table itself: too many small, frequent inserts, the exact anti-pattern loading-data and log-ingestion-pipelines warned about, now surfacing as a hard failure instead of a soft inefficiency.

CHECKSUM_DOESNT_MATCH — a part fails its own integrity check. This is checksums.txt doing its job: ClickHouse recomputes a file's checksum when reading or merging a part and compares it against the manifest, and a mismatch throws Code: 40. DB::Exception: ... (CHECKSUM_DOESNT_MATCH). The most common root cause for a single replica is exactly what it sounds like at the hardware level. ClickHouse's own creator, Alexey Milovidov, built a diagnostic tool directly into the project specifically to distinguish this: "if checksum difference is caused by single bit flip, we can be sure that this is hardware error, because random bit flips have low probability to happen due to software bugs" — reporting that this was worth building because it showed up "a few times a year in a fleet of ~1200 ClickHouse servers," traced to causes like bad RAM or a bad CPU on a network switch, or disk bit rot. A checksum error's exact wording can even name the location: ClickHouse users report messages like "The mismatch is caused by single bit flip in data block at byte 37672, bit 7" — precise enough to make "is this hardware" a question with a real answer, not a guess. On a replicated table, the fix path this course's own replicated-engines lesson set up conceptually now has a concrete tool: ALTER TABLE ... DETACH PART moves the suspect part into a detached directory — "the server forgets about the detached data partition as if it does not exist" — after which a healthy replica's copy resyncs to replace it, rather than trying to repair bytes that are already known to be wrong.

A codec change that "didn't take effect." Covered in full in the debugging walkthrough above — worth including in this gallery on its own because it's easy to mistake for a bug report ("the ALTER didn't work") when it's actually expected behavior once you know MODIFY COLUMN ... CODEC is metadata-only. The signature: system.parts (or a direct byte-level check like the walkthrough's) shows old parts still compressed with the old codec, indefinitely, until something — a background merge or a forced OPTIMIZE ... FINAL — actually rewrites them.

Hands-on forensics: reading a part end to end

A compact mental checklist for approaching an unfamiliar part directory, in the order an experienced SME would actually work through it:

1. Find the part's path. SELECT path FROM system.parts WHERE table = '...' AND name = '...'.
2. List the directory. Expect either many <column>.bin / <column>.mrk2 pairs (Wide format) or a single shared data file (Compact format) — which one tells you immediately whether this part crossed the wide-format size threshold.
3. Check checksums.txt exists and the part isn't already sitting in detached/. A part that's been moved to detached is already known-bad and waiting for a decision, not a mystery to re-diagnose.
4. For a specific column's storage question, read <column>.mrk2 before <column>.bin. The mark file tells you how many granules exist and roughly how block boundaries fall; jumping straight into the compressed .bin bytes without this is like reading a book by opening to a random page.
5. For a compression question specifically, read the mode byte at the start of a block, per this lesson's walkthrough, rather than trusting a setting's current value to describe data written under a previous setting.