Inserts create immutable sorted parts that background merges combine; a sparse index over granules makes range scans fast.
The problem
Analytical queries scan billions of rows, and writes arrive continuously. Updating sorted files in place is slow. MergeTree makes writes cheap by never modifying data: it appends sorted immutable parts and merges them in the background.
How it works
- Each INSERT writes a new part: a directory of column files sorted by the table's ORDER BY key. Parts are immutable.
- Background merges combine small parts into bigger ones, keeping them sorted, so reads see a few large parts instead of thousands of small ones. Parts from different partitions are never merged together.
- Storage is columnar: each column compresses well and only the columns a query touches are read. Parts are Wide (one file per column) or Compact (all columns in one file) depending on size.
- The primary key is a sparse index: one entry per granule of 8192 rows (`index_granularity`), small enough to stay in RAM. A query uses it to skip granules, not to find single rows.
- Insert in large batches (thousands of rows or more, or use `async_insert`): many tiny inserts create many parts faster than merges can combine them and end in a “too many parts” error.
- Engine variants change what a merge does: Replacing (keep latest row per key), Summing, Aggregating, Collapsing. Their results are only final after merging, so reads that need exactness use FINAL or an aggregate.
- UPDATE and DELETE are mutations that rewrite parts in the background — heavy, not for per-row traffic. Prefer append plus ReplacingMergeTree, or lightweight deletes for occasional removals.
Examples
An events table: ORDER BY puts the columns you filter on first.
CREATE TABLE events
(
tenant_id UInt32,
ts DateTime,
user_id UInt64,
event LowCardinality(String),
payload String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (tenant_id, event, ts);
Watch parts and merges; many active parts means inserts are too small.
SELECT table, count() AS active_parts, sum(rows) AS rows
FROM system.parts
WHERE active AND database = currentDatabase()
GROUP BY table
ORDER BY active_parts DESC;
In practice
- Observability stacks — SigNoz, Uptrace and similar store logs, traces and metrics in MergeTree tables partitioned by day and ordered by service and time.
- Web analytics / product analytics — Event tables ordered by tenant and time, with AggregatingMergeTree materialized views for rollups.
- CDC pipelines — ReplacingMergeTree with a version column keeps the latest row per key, read with FINAL or argMax.
- Pitfall — Thousands of single-row inserts per second: batch them (or use async inserts) before they become a parts backlog.
Trade-offs
Pros
- Very fast ingestion (sequential writes) and scans (columnar, sparse index, compression)
- Simple recovery model: parts are immutable files
- Flexible aggregation and dedup via engine variants
Cons
- Not for point updates or transactional workloads; mutations are heavy
- Dedup and rollups are eventual until merges run
- Needs batched inserts and a sensible ORDER BY, or performance collapses