MergeTree is not a feature of ClickHouse — it's the table engine almost everything else in this module sits on top of. Partitions, primary keys, TTL, replication, skip indexes and projections are all properties of a MergeTree table, not separate systems.
Why does it exist?
Row-oriented databases like Postgres are built around fast, transactional access to individual rows. ClickHouse is built for the opposite workload: scanning and aggregating billions of rows for analytical queries, where a single slow write in exchange for very fast reads is a good trade. MergeTree is the storage engine that makes that trade deliberately.
Where the name comes from
Every INSERT doesn't modify existing data — it writes a brand new, immutable part: a small self-contained directory of sorted, columnar files. A background process continuously merges smaller parts into larger ones. "MergeTree" is literally: a tree of parts, merged over time.
Parts & Background Merges goes through that loop in detail, later in the Storage Engine section. This page is about the engine family sitting around it.
Declaring a MergeTree table
CREATE TABLE events
(
user_id UInt64,
event_type LowCardinality(String),
event_time DateTime,
payload String
)
ENGINE = MergeTree
ORDER BY (event_type, event_time)
PARTITION BY toYYYYMM(event_time);ORDER BY is not cosmetic — it defines the physical sort order every part is written in, and doubles as the primary key unless you specify one separately. Get this wrong and every later optimization (sparse index, skip indexes, projections) inherits the mistake.
The MergeTree family
Plain MergeTree keeps every row you insert. Several variants change what happens during a merge, without changing anything else about how parts are stored or scanned:
- ReplacingMergeTree — during a merge, keeps only the newest row per sort key. Useful for deduplicating upserts, but duplicates can still exist between merges.
- SummingMergeTree — during a merge, sums numeric columns that share the same sort key. Good for pre-aggregating metrics without a separate rollup job.
- AggregatingMergeTree — merges rows holding partial aggregate states (from functions like
avgState), used almost exclusively as the target of a materialized view. - CollapsingMergeTree / VersionedCollapsingMergeTree — a "sign" column marks rows for cancellation, letting you emulate deletes/updates in an append-only engine.
- ReplicatedMergeTree — any of the above, plus coordinated replication via Keeper. Covered in Replication & Keeper.
Same mechanism, five different outcomes — pick an engine below to see what actually happens when its parts merge, and a realistic table that would use it:
A retried INSERT lands as a literal duplicate — plain MergeTree never merges rows away, only sorts them. Dedupe downstream if it matters.
CREATE TABLE raw_events
(
user_id UInt64,
event_time DateTime,
event String
)
ENGINE = MergeTree
ORDER BY (user_id, event_time);When to use which
Start from what your query needs to be true, not from the feature list — most of these have a narrower "reach for something else instead" case than their name suggests:
| Engine | Use it when | Reach for something else when |
|---|---|---|
| MergeTree | Every row is independently meaningful — raw events, logs, immutable facts you never need to collapse. | You need dedup, rollups, or update/delete semantics — any variant below. |
| ReplacingMergeTree | You only care about the latest row per key — CDC sync, upserts, slowly-changing dimensions. | You need an accurate row count or full history right now — duplicates aren't actually gone until a merge runs;FINAL or argMax is required for correctness before then. |
| SummingMergeTree | You're pre-aggregating a running numeric total per key — counters, revenue, event counts. | The aggregate isn't a plain sum (avg, uniq, quantile) — AggregatingMergeTree; or you need per-row detail — plain MergeTree. |
| AggregatingMergeTree | A materialized view needs incremental, non-additive aggregates — uniq, avg, quantile, any *State function. | A plain sum would do — SummingMergeTree is simpler and doesn't need a *Merge combinator at query time. |
| CollapsingMergeTree | Emulating UPDATE/DELETE via a sign column, and one writer already guarantees the cancel-then-insert pair arrives in order. | Multiple writers/partitions can't guarantee that order — VersionedCollapsingMergeTree; or "latest wins" is all you need — ReplacingMergeTree is simpler. |
| VersionedCollapsingMergeTree | Same cancel-and-replace pattern, but rows can arrive out of order — multiple Kafka partitions, multiple producers. | A single writer already guarantees order — plain CollapsingMergeTree needs one fewer column. |
| ReplicatedMergeTree (any of the above) | Any of the above, running on more than one node in production. | Local single-node dev/testing — the plain variant is one less moving part (no Keeper dependency). |
FINAL or aggregate explicitly — don't rely on merge timing.What you get by choosing MergeTree
- Data physically sorted by your chosen key, enabling range scans.
- Columnar storage per part — queries only read the columns they touch.
- Immutable parts — safe concurrent reads, no locking on writes.
- A background process that continuously reorganizes storage for you.