What is the MergeTree engine family, and why is it the foundation of ClickHouse?
Quick Answer
MergeTree is ClickHouse's primary storage engine that stores data as immutable sorted parts which are periodically merged in the background. It provides sparse primary indexing, data partitioning, and efficient compression. All production ClickHouse tables use MergeTree variants including ReplacingMergeTree, AggregatingMergeTree, and ReplicatedMergeTree.
Detailed Answer
Think of a filing cabinet where new documents are always placed in a new folder (part) rather than inserted into existing folders. Periodically, a clerk comes in and merges small folders into larger organized ones, keeping everything sorted. This is how MergeTree works: new inserts create new immutable parts, and background merges consolidate them into larger, optimally sorted parts.
MergeTree is the core storage engine in ClickHouse and the foundation upon which all other production engines are built. When you insert data into a MergeTree table, ClickHouse writes it as an immutable part (a directory on disk containing column files, a primary index, and metadata). Each part contains rows sorted by the ORDER BY key. The engine never modifies existing parts; instead, background merge operations combine smaller parts into larger ones, maintaining sort order and applying any engine-specific logic (deduplication for ReplacingMergeTree, aggregation for AggregatingMergeTree).
Internally, each part is organized as follows: every column is stored in a separate compressed file (.bin), with a marks file (.mrk2) that maps granule numbers to byte offsets. The primary index (primary.idx) stores the first value of the ORDER BY key for each granule (default 8192 rows). This sparse index is tiny enough to fit in RAM even for tables with billions of rows. When a query filters by the ORDER BY key, ClickHouse checks the sparse index to identify which granules might contain matching rows, skipping the rest entirely.
At production scale, the MergeTree family includes specialized variants: ReplacingMergeTree deduplicates rows by sorting key during merges, AggregatingMergeTree stores pre-aggregated states for materialized views, CollapsingMergeTree handles mutable data through sign-based cancellation, and ReplicatedMergeTree adds synchronous replication via ClickHouse Keeper. Choosing the right variant is a critical design decision that depends on your data mutation patterns and consistency requirements.
The non-obvious gotcha is that merges are not instantaneous and not guaranteed to have completed at query time. Until all parts are merged, you may see duplicate rows in ReplacingMergeTree or unmerged intermediate states. Using FINAL in queries forces a merge at read time but significantly impacts performance. Production systems should either tolerate eventual consistency or design queries that handle unmerged parts correctly using techniques like argMax or GROUP BY with the latest version.