Table engines

Every MergeTree variant shares the same machinery: parts, background merges, and the sparse primary index. They differ in what they do to rows while merging parts.

The MergeTree family#

Engine	Merge behaviour	Use when
`MergeTree`	Sorts and merges, no row transformation	Append-only facts. The default.
`ReplacingMergeTree([ver])`	Keeps one row per sorting key — the highest `ver`, otherwise the last inserted	Deduplication, upserts, latest state per id
`SummingMergeTree([cols])`	Sums numeric columns of rows sharing the sorting key	Pre-aggregated counters, materialized view targets
`AggregatingMergeTree`	Merges `AggregateFunction` states	View targets needing uniq, avg, or quantile, not just sums
`CollapsingMergeTree(sign)`	Cancels pairs of `sign = 1` and `sign = -1` rows	Mutable rows, when you can emit a cancel row
`VersionedCollapsingMergeTree(sign, version)`	Same, but order-independent via `version`	As above, with out-of-order arrival

CREATE TABLE latest_state (id UInt64, data String, version UInt64)
ENGINE = ReplacingMergeTree(version)
ORDER BY id;
 
CREATE TABLE daily_totals (day Date, key String, hits UInt64, revenue Float64)
ENGINE = SummingMergeTree((hits, revenue))
ORDER BY (day, key);
 
CREATE TABLE collapsing (id UInt64, value String, sign Int8)
ENGINE = CollapsingMergeTree(sign)
ORDER BY id;

The rule that catches everyone#

The transformation happens at an unspecified time during background merges, and only between rows within the same part.

A ReplacingMergeTree therefore does not guarantee that a SELECT returns one row per key. Duplicates persist until the parts holding them merge, which may be seconds or hours. Write queries that tolerate this, or force the semantics at read time:

-- Correct, but expensive: applies merge semantics during the query
SELECT * FROM latest_state FINAL;
 
-- Idiomatic alternative for CollapsingMergeTree
SELECT id, sum(value * sign)
FROM collapsing
GROUP BY id
HAVING sum(sign) > 0;

Do not reach for OPTIMIZE TABLE ... FINAL on a schedule to work around this. It rewrites every part in the table and is not a substitute for a query pattern that tolerates un-merged rows.

Aggregating engines and materialized views#

AggregatingMergeTree stores intermediate aggregate states rather than final values, which is what lets counts and unique counts merge correctly across parts. Write with -State combinators and read with -Merge:

CREATE TABLE daily_stats
(
    day Date,
    country LowCardinality(String),
    visits AggregateFunction(count),
    unique_users AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (day, country);

SELECT
    day,
    country,
    countMerge(visits) AS visits,
    uniqMerge(unique_users) AS unique_users
FROM daily_stats
GROUP BY day, country
ORDER BY day DESC;

These engines are almost always paired with a materialized view that feeds them — see materialized views.

Engines outside the MergeTree family#

ReplicatedMergeTree — replication

Any MergeTree variant prefixed with Replicated replicates at the table level. Replicas coordinate through ClickHouse Keeper or ZooKeeper, which holds the replication log and part metadata; the data itself moves directly between replicas. Inserts are deduplicated by block checksum, so retrying a failed insert is safe.

In ClickHouse Cloud, SharedMergeTree replaces this: replicas share one copy of the data in object storage, making replication a metadata concern only.

Distributed — sharding

The Distributed engine stores no data of its own. It fans a query out to the shards defined in the cluster configuration, merges the results, and routes writes using a sharding key.

CREATE TABLE distributed_table AS local_table
ENGINE = Distributed(cluster_name, database, local_table, rand());

Shard to go beyond one machine's capacity; replicate for availability and read throughput. The two are orthogonal and usually combined.

Integration engines

Kafka, S3, PostgreSQL, MySQL, and MongoDB engines present an external system as a table. They store connection configuration persistently, unlike the equivalent table functions. See integrations.

Null — discard writes, keep triggers

A Null table discards everything written to it, but materialized views attached to it still fire. This makes it a transform-only ingestion point: raw rows land in the Null table, views write the derived rows, and nothing stores the raw data.

Memory, Log, and Buffer

Memory holds data in RAM and loses it on restart, useful for temporary intermediate results. The Log family (TinyLog, StripeLog, Log) suits small write-once tables. Buffer accumulates writes in memory and flushes them to another table periodically.

MergeTree and data parts — the merge machinery these engines hook into
Materialized views — what typically writes into aggregating engines
Data types — AggregateFunction and the rest of the type system