In ClickHouse, what happens on disk each time you INSERT into a MergeTree table?

## The unit of storage: a data part In ClickHouse's MergeTree family, table data does not live in one big file. It lives in **parts**. A part is a directory on disk containing the rows of one write, already sorted by the table's `ORDER BY` (sorting) key, with each column stored in its own compressed file, alongside a sparse primary-index file and mark files that map index granules to offsets inside the column files. Every part belongs to exactly one partition — the value of the `PARTITION BY` expression, if the table declares one. Part directory names encode that partition plus a block-number range and a merge level, which is why you see names like `all_1_1_0` or `202408_15_20_1`. Parts are **immutable**. Once written, a part's files are never edited in place. Everything else in the engine follows from that choice. ## What an INSERT actually does When an INSERT arrives, the server: 1. accumulates the incoming rows into one or more in-memory blocks; 2. sorts each block by the table's sorting key; 3. applies the per-column compression codecs; 4. writes a complete new part directory and flushes it to disk; 5. atomically adds that part to the in-memory list of active parts, at which point it becomes visible to queries. No existing file is touched, so readers see either the whole part or none of it, and a crashed or failed insert leaves at most an unreferenced temporary directory that is cleaned up later. The count of parts produced is **not** one per statement. It is roughly one per block per partition. Two things drive it: block sizing settings such as `max_insert_block_size` and `min_insert_block_size_rows` / `min_insert_block_size_bytes`, and how many distinct partition values the inserted rows span. A single INSERT of a million rows spread over 300 daily partitions writes hundreds of parts, not one — this is why ClickHouse guards inserts with `max_partitions_per_insert_block` and why a badly chosen partition key turns ordinary loads into part storms. ```sql INSERT INTO events VALUES (...); SELECT partition, name, rows, level FROM system.parts WHERE table = 'events' AND active; ``` ## Background merges Because each write leaves a new part, a background pool continuously merges parts within the same partition into larger sorted parts, re-running the merge sort over their sorting keys and re-compressing the result. The old parts stay on disk, inactive, until they are dropped. Merging is what keeps the part count bounded, keeps compression ratios good (long runs of similar values compress far better inside one big part than split across many small ones), and, for the specialised engines, is where row collapsing or aggregation actually happens. Merging is background work with finite throughput. Inserts are foreground work with none. If inserts create parts faster than merges retire them, the part count grows without bound — the classic ClickHouse production failure. ## Why the part count matters for reads A query must consider **every active part** of every partition it did not prune away. For each part the engine opens files, reads the sparse primary index, decides which granules to read, and spins up read tasks. That per-part overhead is small but not free, and it is paid regardless of how few rows the part holds. A table with 40 well-merged parts and a table with 40,000 tiny parts can hold identical data and differ by orders of magnitude in scan time and memory. ## Practical consequences - **Batch your writes.** Aim for tens of thousands to hundreds of thousands of rows per INSERT rather than one row per statement, and keep the per-table insert rate low (on the order of one insert per second, not thousands). If the producer cannot batch, let the server do it with `async_insert`. - **Do not partition finely.** Partitioning by hour or by a high-cardinality id multiplies parts per insert. Monthly or daily partitioning is the usual choice; the sorting key, not the partition key, is what makes queries fast. - **Expect eventual, not immediate, tidiness.** Right after a big load the table will hold many parts; they shrink in number over the following minutes as merges run. `OPTIMIZE TABLE ... FINAL` forces the issue but rewrites data and is expensive, so it is a maintenance tool, not part of the load path. - **Watch it directly.** `system.parts` shows active parts and their sizes; `system.merges` shows merges in flight; `system.part_log` records each part's creation and merge history. The mental model to carry into an interview: an INSERT in ClickHouse is a file write, not a row update, and everything about ingestion tuning is about controlling how many files you create per second.