ClickHouse MergeTree Table Engines

The table engine you choose in ClickHouse determines how rows are stored, how they are merged in the background, and what happens to rows that share a sorting key. The MergeTree family is the set of engines built for analytical workloads, and almost every production ClickHouse table uses one of them. They share the same physical layer (data is written as immutable parts, indexed by a sparse primary index, and merged in the background) but differ in what that background merge does to rows with the same sorting key.

This guide covers the main MergeTree-family engines, what each one is for, and the one behavior that trips up nearly everyone: the special merge logic is only guaranteed to have happened eventually, not at query time.

How MergeTree Works Underneath

When you insert into a MergeTree table, ClickHouse writes the rows into a new immutable part on disk, sorted by the table's ORDER BY key. It does not modify existing parts. A background process periodically merges smaller parts into larger ones, which is where the family name comes from. During those merges, sorting keeps rows with the same ORDER BY value adjacent, and that adjacency is what the specialized engines exploit.

A plain MergeTree does nothing special on merge beyond combining parts:

CREATE TABLE events
(
    event_date Date,
    user_id    UInt64,
    event_type String,
    value      Float64
)
ENGINE = MergeTree
ORDER BY (event_date, user_id);

ORDER BY here is the sorting key, and (unless you set PRIMARY KEY separately) it also defines the sparse primary index used for skipping data during reads. It is not a uniqueness constraint. Plain MergeTree will happily store two rows with identical ORDER BY values. If you need uniqueness, summing, or aggregation, you need one of the specialized engines below, and even then "uniqueness" means something looser than it does in PostgreSQL or MySQL.

ReplacingMergeTree

ReplacingMergeTree deduplicates rows that share the same sorting key, keeping one row per key. This is the engine people reach for when they want upsert-like behavior.

CREATE TABLE current_state
(
    user_id    UInt64,
    status     String,
    updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

The optional version column (updated_at above) tells ClickHouse which row wins when keys collide: the one with the highest version value. Without a version column, the last inserted row wins. Insert a new row for an existing user_id, and after a background merge only the newest survives.

The trap: deduplication happens during merges, which run on ClickHouse's schedule, not yours. Between an insert and the next merge of the relevant parts, a plain SELECT will return both the old and new rows. There is no guarantee about when merges happen. To get the deduplicated result at query time you must do one of:

• Add FINAL to the query (SELECT * FROM current_state FINAL), which forces the merge logic at read time. This is correct but can be expensive on large tables because it has to merge on the fly.
• Aggregate the duplicates away yourself, for example argMax(status, updated_at) ... GROUP BY user_id, which sidesteps the engine entirely and is often faster than FINAL.

ReplacingMergeTree also supports an optional is_deleted column (a second engine argument alongside the version column) so a row can mark a key as deleted during deduplication, which is useful for change-data-capture pipelines.

SummingMergeTree

SummingMergeTree collapses rows with the same sorting key into a single row, summing the numeric columns that are not part of the key.

CREATE TABLE daily_totals
(
    event_date  Date,
    metric_name String,
    total       UInt64
)
ENGINE = SummingMergeTree
ORDER BY (event_date, metric_name);

Insert (2026-06-08, 'clicks', 5) and later (2026-06-08, 'clicks', 3), and after a merge you get a single row with total = 8. You can restrict which columns are summed by passing them as an argument: SummingMergeTree((total, count)). Columns not in the sorting key and not summed take an arbitrary value from one of the merged rows, so do not rely on them.

The same eventual-consistency caveat applies: until the merge runs, both rows are present. Wrap reads in a sum() over GROUP BY (which you typically want anyway) and the result is correct regardless of merge state. SummingMergeTree is really an optimization that keeps the stored data pre-aggregated so those GROUP BY queries scan fewer rows.

AggregatingMergeTree

AggregatingMergeTree generalizes the summing idea to arbitrary aggregate functions. Instead of storing raw values, columns store intermediate aggregation states, produced by the -State combinator and read back with the -Merge combinator.

CREATE TABLE user_metrics
(
    user_id      UInt64,
    visits       AggregateFunction(sum, UInt64),
    unique_pages AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY user_id;
 
-- Inserting requires -State functions
INSERT INTO user_metrics
SELECT user_id, sumState(toUInt64(1)), uniqState(page_id)
FROM raw_events
GROUP BY user_id;
 
-- Reading requires -Merge functions
SELECT user_id, sumMerge(visits), uniqMerge(unique_pages)
FROM user_metrics
GROUP BY user_id;

This is more involved than SummingMergeTree, but it handles aggregations that cannot be expressed as a plain sum, such as uniq, quantile, or avg. It is most often used as the target of a materialized view that maintains a rollup as data streams in. If your only need is summing integers, SummingMergeTree is simpler and SummingMergeTree is effectively a special case of this engine. For the combinator mechanics, see the ClickHouse aggregate functions guide.

CollapsingMergeTree and VersionedCollapsingMergeTree

CollapsingMergeTree handles mutable rows by storing pairs of "state" and "cancel" rows. You add a Sign column holding 1 (a row to add) or -1 (a row that cancels a previous one). During merges, a matching +1 and -1 pair with the same sorting key cancel out and both disappear, leaving only the net state.

CREATE TABLE account_balance
(
    account_id UInt64,
    balance    Int64,
    sign       Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY account_id;

To update a row you insert the old version with sign = -1 and the new version with sign = 1. This avoids rewriting data parts, which is what a mutation would do. The cost is operational: your application has to track the previous row values to write the cancel row correctly, and if rows arrive out of order the collapse can fail. VersionedCollapsingMergeTree adds a version column so collapsing works correctly even when the cancel and state rows are inserted in any order, at the cost of one more column to manage.

These engines suit high-throughput mutable data but they push real complexity into your write path. Most teams are better served by ReplacingMergeTree unless they specifically need the running-sum semantics that collapsing provides.

The Replicated and Distributed Prefixes

Each engine above has a Replicated variant (for example ReplicatedMergeTree) that adds data replication across nodes via ClickHouse Keeper or ZooKeeper. On ClickHouse Cloud, replication is the default and you generally use the Replicated engines transparently. Distributed is a separate engine that does not store data itself; it routes queries across a cluster of underlying tables. Both are orthogonal to the merge behavior described here: you pick the merge semantics (Replacing, Summing, etc.) and the replication topology independently.

Choosing an Engine

The single most common mistake is treating ReplacingMergeTree or SummingMergeTree as if deduplication or summing happens at insert time. It does not. The engine guarantees the result after a background merge, and you must write your read queries (FINAL, argMax, or GROUP BY aggregation) to be correct in the meantime. Start with plain MergeTree, and only move to a specialized engine when you have a concrete need for its merge behavior.

When you are experimenting with these engines and want to compare what a raw SELECT returns against a SELECT ... FINAL, Mako's AI autocomplete can help you draft and adjust the queries quickly.

Related Guides

• ClickHouse Materialized Views for the most common way to feed an AggregatingMergeTree or SummingMergeTree rollup.
• ClickHouse Aggregate Functions for the -State and -Merge combinators that AggregatingMergeTree relies on.
• ClickHouse Partitioning for the data-management layer that sits alongside any MergeTree engine.

Mako connects to ClickHouse with AI-powered autocomplete for writing and exploring analytical queries. Try it free at mako.ai.