The MergeTree Storage Engine

Most ClickHouse 29,491 tables use an engine of the MergeTree family. Every INSERT writes a new immutable part: a directory with one compressed file per column, sorted by the table's ORDER BY key. A background process merges small parts into bigger ones, the way an LSM tree compacts. Inside a part, every 8,192 rows (the default index_granularity) form a granule, and the primary index stores only the key of each granule's first row: a sparse index small enough to stay in memory, which finds granules by binary search, not rows.

1441-parts.sql: three inserts make three parts, and a merge makes oneSQL

CREATE DATABASE IF NOT EXISTS booknest;
DROP TABLE IF EXISTS booknest.parts_demo;
CREATE TABLE booknest.parts_demo (order_date Date, genre LowCardinality(String), qty UInt16)
ENGINE = MergeTree ORDER BY (genre, order_date);
SYSTEM STOP MERGES booknest.parts_demo;               -- hold merges to see the parts
INSERT INTO booknest.parts_demo
  VALUES ('2026-06-01', 'Travel', 1), ('2026-06-01', 'Cooking', 2);
INSERT INTO booknest.parts_demo VALUES ('2026-06-02', 'Fiction', 1);
INSERT INTO booknest.parts_demo VALUES ('2026-06-02', 'Cooking', 3);
SELECT name, rows FROM system.parts WHERE table = 'parts_demo' AND active ORDER BY name;
SYSTEM START MERGES booknest.parts_demo;
OPTIMIZE TABLE booknest.parts_demo FINAL;             -- force the merge now
SELECT name, rows FROM system.parts WHERE table = 'parts_demo' AND active;

Output

   ┌─name──────┬─rows─┐
1. │ all_1_1_0 │    2 │
2. │ all_2_2_0 │    1 │
3. │ all_3_3_0 │    1 │
   └───────────┴──────┘
   ┌─name──────┬─rows─┐
1. │ all_1_3_1 │    4 │
   └───────────┴──────┘

A part name reads partition, first block, last block and merge level: all_1_3_1 covers blocks 1 to 3 after one merge. Each insert costs a part, so insert in batches of thousands of rows (or use async_insert), never row by row. Variants change what a merge does: ReplacingMergeTree keeps the latest row per key, SummingMergeTree adds measures (Incremental Materialized Views), and AggregatingMergeTree combines partial aggregate states.

MENU