# ClickHouse Table Engines: A Decision Tree for Picking the Right One The worst ClickHouse bug I have had to unpick did not throw an error, did not appear in a log, and had been silently wrong for four months. A table was declared `SummingMergeTree` because someone wanted a running total. It also carried a `user_id` column, which is numeric, which is not part of the sorting key — so ClickHouse had been faithfully *summing the user IDs* on every merge. The dashboard read fine. The numbers were nonsense. That is the shape of engine mistakes in ClickHouse. You do not get a stack trace. You get a table that quietly means something other than what you thought, because **the engine is not storage configuration — it is a declaration of what happens to your rows when nobody is looking.** This is the article I wish I could hand people before they run their first `CREATE TABLE`. I covered engine choice briefly in [Part 2 of the ClickHouse series](clickhouse-schema-query-optimization); this is that section expanded into the full family, with the trap each one hides. 🧭 **Evaluating ClickHouse itself?** I scored it on ten axes against Snowflake, StarRocks and Druid/Pinot — workload fit, ops burden, and a 4-week POC plan — in the [Architecture Radar assessment](/architecture-radar/clickhouse). ## The one idea that makes the family make sense **Every MergeTree variant is plain MergeTree plus a transformation that runs during background merges.** That is the whole model. Parts get written on insert; a background process merges small parts into bigger ones; and the engine decides what happens to rows that share a sorting key when two parts meet. Two consequences follow, and both bite people: **The transformation is eventual, not immediate.** Merges happen on ClickHouse's schedule, not yours. Between insert and merge, both versions of a row are present and a naive `SELECT count()` is wrong. Every engine below inherits this. **The transformation applies to rows sharing the `ORDER BY` key** — which is why the sorting key stops being a performance decision and becomes a correctness one. In a `ReplacingMergeTree`, the sorting key *is* the primary key in the "identity" sense. Get it wrong and you deduplicate rows that were meant to be distinct. ```mermaid graph TD A["What are you storing?"] --> B{"Rows ever need replacing?"} B -->|"No — append only"| C{"Do you need pre-aggregation?"} B -->|"Yes — updates or dedupe"| D{"Do you know the previous row's values?"} C -->|"No"| E["MergeTree the default"] C -->|"Sum a few numeric columns"| F["SummingMergeTree"] C -->|"Any aggregate: uniq, quantile, avg"| G["AggregatingMergeTree"] D -->|"No — just keep the newest"| H["ReplacingMergeTree"] D -->|"Yes — can write a cancel row"| I{"Can inserts arrive out of order?"} I -->|"No"| J["CollapsingMergeTree"] I -->|"Yes — streaming, retries"| K["VersionedCollapsingMergeTree"] E --> L{"Cluster?"} L -->|"Yes, self-managed"| M["Replicated* + Distributed"] L -->|"Yes, ClickHouse Cloud"| N["SharedMergeTree"] ``` The two questions that do the work are the first ones. "Do rows need replacing?" splits the family in half, and "do you know the previous values?" is what separates the collapsing engines from Replacing — because a cancel row requires you to reproduce the old row exactly. ## The MergeTree family, one at a time ### MergeTree — the default, and usually the right answer Append-only. No merge-time transformation. What you insert is what you read. I reach for this unless I can name the specific behaviour I need from something else, and I would encourage the same discipline. The exotic engines look like they save you work; what they actually do is move a correctness concern from your query into the storage layer where it is invisible. That is a good trade when you need it and a bad one by default. | Use it for | Advantage | Disadvantage | | --- | --- | --- | | Events, logs, metrics, facts — anything immutable | Predictable; no surprise semantics; fastest merges | You handle dedup and aggregation in queries | ### ReplacingMergeTree — updates, eventually Rows sharing the sorting key collapse to one on merge. With a version column, the highest version wins; without one, the last inserted wins. ```sql CREATE TABLE orders ( order_id UInt64, status LowCardinality(String), total Decimal(12,2), updated_at DateTime ) ENGINE = ReplacingMergeTree(updated_at) -- version column ORDER BY order_id; ``` This is how you get mutable rows in a database that does not really do updates, and it is the workhorse behind most CDC-into-ClickHouse pipelines. It is also the engine most likely to be used wrongly, because "eventually" does a lot of work in that sentence. ⚠️ **Until a merge runs, duplicates are visible, and `FINAL` is not a free fix.** `FINAL` forces the merge at query time — correct, but it raises memory use and weakens data skipping, and it degrades non-linearly as parts accumulate. It passes staging at 10 million rows and rots in production. Prefer `ORDER BY … LIMIT 1 BY` or `argMax()`, both of which stay fast as the table grows. The same decision shows up at scale in [ClickHouse under LLM observability](clickhouse-llm-observability-backend). ### SummingMergeTree — and the trap from the opening paragraph On merge, rows sharing the sorting key are combined by **summing every numeric column that is not part of the sorting key**. Read that again, because the default is the problem: it sums *everything* numeric it is not told to leave alone. ```sql -- Wrong: user_id is numeric and not in the sorting key, so it gets summed. CREATE TABLE daily_totals ( day Date, user_id UInt64, revenue Decimal(12,2) ) ENGINE = SummingMergeTree() ORDER BY (day); -- Right: name the columns to sum explicitly, and nothing else is touched. CREATE TABLE daily_totals ( day Date, user_id UInt64, revenue Decimal(12,2) ) ENGINE = SummingMergeTree(revenue) ORDER BY (day, user_id); ``` Always pass the explicit column list. It costs six characters and removes an entire class of silent corruption. ### AggregatingMergeTree — for aggregates that are not sums Stores intermediate *aggregate states* rather than values, which is what lets it handle `uniq`, `quantile` and `avg` — aggregates that cannot be combined by simple addition. You write with the `-State` combinator and read with `-Merge`. ```sql CREATE TABLE hourly_stats ( hour DateTime, endpoint LowCardinality(String), requests AggregateFunction(count, UInt64), uniq_users AggregateFunction(uniq, UInt64), p95_latency AggregateFunction(quantile(0.95), Float64) ) ENGINE = AggregatingMergeTree() ORDER BY (hour, endpoint); -- Reading REQUIRES the -Merge combinator: SELECT hour, endpoint, countMerge(requests) AS requests, uniqMerge(uniq_users) AS users, quantileMerge(0.95)(p95_latency) AS p95 FROM hourly_stats GROUP BY hour, endpoint; ``` The confusing part for newcomers: a plain `SELECT requests FROM hourly_stats` returns serialized binary state, not a number. That is not a bug — the column genuinely holds a state object. If your aggregate is one of the simple combinable ones (`sum`, `min`, `max`, `any`), use `SimpleAggregateFunction` instead and skip the combinator dance entirely. Almost nobody writes to these tables directly. They are the target of a materialized view. ### Collapsing and VersionedCollapsing — cancel rows `CollapsingMergeTree` takes a `sign` column of `+1` (state) and `-1` (cancel). On merge, a matching pair annihilates. To change a row you write a `-1` row reproducing the old values exactly, then a `+1` row with the new ones. That requirement — *reproducing the old values exactly* — is the whole cost. Your producer must know the previous state, which usually means keeping it somewhere, which is often the thing you were hoping the database would do for you. The failure mode is worse than it looks: `CollapsingMergeTree` depends on the cancel row being ordered after the state row. With concurrent producers, retries, or a Kafka consumer rebalancing mid-flight, they can arrive out of order and simply never collapse. `VersionedCollapsingMergeTree` adds a version column so collapsing is order-independent. 💡 **The short version: if your source is a stream, use `VersionedCollapsingMergeTree`, never plain `Collapsing`.** Anything with at-least-once delivery or more than one writer can reorder, and plain Collapsing has no way to notice. Most teams that reach for the collapsing engines at all would be better served by `ReplacingMergeTree` plus `LIMIT 1 BY` — the collapsing engines earn their complexity mainly when you need to *subtract* from running aggregates, not merely replace rows. ## The engines that hold no data Some of the most useful engines store nothing at all, which is exactly why people miss them. | Engine | What it does | Reach for it when | | --- | --- | --- | | `Distributed` | Holds no data; routes queries to shards and merges results | Any self-managed cluster — it is the table your application actually queries | | `Null` | Discards every row — but materialized views still fire | High-volume ingest where only the aggregates matter | | `Merge` | Reads across several tables matching a pattern, as one | Querying across manually partitioned or time-sharded tables | | `Dictionary` | Exposes a dictionary as a table | Fast key-value lookups instead of a join — often the fix for a slow join | | `Buffer` | Buffers rows in RAM, flushes to a target table | Last resort for unbatchable small inserts | ### The Null-plus-materialized-view pattern This one is worth its own mention because it looks like a mistake until it clicks: ```sql -- Raw events land here and are immediately thrown away. CREATE TABLE events_in (…) ENGINE = Null; -- …but the materialized view still fires on every insert. CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly AS SELECT toStartOfHour(ts) AS hour, endpoint, countState() AS requests, uniqState(user_id) AS uniq_users FROM events_in GROUP BY hour, endpoint; ``` You ingest a firehose, store only the rollups, and never pay for the raw rows. For telemetry where the raw feed has no long-term value, this is the difference between a bill you can defend and one you cannot. ⚠️ **Buffer is a trap dressed as a convenience.** Its contents live in memory and are **lost if the server dies**, and every query against the target table must also read the buffer or you see stale data. It exists for the case where you genuinely cannot batch on the client. Since async inserts arrived, that case is rare — prefer `async_insert=1`, which gets you server-side batching with durability. The whole batching story, including the `too many parts` failure, is in [Part 3](clickhouse-ingestion-streaming). ## Integration engines: query it where it lives These make an external system look like a table. They are excellent for ingestion and for one-off joins against reference data, and consistently disappointing when treated as a substitute for loading data in. | Engine | Good for | Watch out for | | --- | --- | --- | | `Kafka` | Streaming ingest, paired with an MV into MergeTree | It is a consumer, not a table — reading it twice consumes twice | | `S3` / `URL` | Bulk load, querying Parquet in place, unloading | No index; every query is a full remote scan | | `Iceberg` / `DeltaLake` | Reading lakehouse tables without a copy | Read-oriented; not a lakehouse-native engine | | `MySQL` / `PostgreSQL` | Pulling reference data, small dimension joins | Every query hits the source database | | `EmbeddedRocksDB` | Genuine point lookups by key | Not analytical; a narrow tool | The Kafka engine's semantics catch people out. It behaves like a consumer group: rows are read once and the offset advances. You never `SELECT` from it in anger — you attach a materialized view that writes into a MergeTree table, and query that. Running an ad-hoc `SELECT` against a Kafka table in production consumes messages your pipeline needed. ## Replication and the cluster shapes Every MergeTree engine has a `Replicated` twin — `ReplicatedMergeTree`, `ReplicatedReplacingMergeTree`, and so on — coordinating through ClickHouse Keeper (or ZooKeeper). Replication is per-table, not per-server, which surprises people migrating from other databases. Two shapes in practice: - **Self-managed:** `Replicated*` tables on each node, plus a `Distributed` table in front as the query entry point. You choose the sharding key, and choosing it badly produces hot shards that no amount of hardware fixes. - **ClickHouse Cloud:** `SharedMergeTree`, which puts parts in object storage and separates storage from compute — so replicas coordinate through shared state rather than replicating data between themselves, and scaling out stops meaning copying. If you are weighing this against other engines entirely, the head-to-head is in [StarRocks vs ClickHouse vs Doris](starrocks-vs-clickhouse-vs-doris). ## A short table of what actually goes wrong | Engine | The mistake I see most | | --- | --- | | MergeTree | Being talked out of it by a fancier option nobody needed | | ReplacingMergeTree | `FINAL` everywhere, then confusion about why queries slowed down over six months | | SummingMergeTree | No explicit column list — silently summing IDs and foreign keys | | AggregatingMergeTree | Forgetting `-Merge` on read, or using it where `SimpleAggregateFunction` would do | | CollapsingMergeTree | Using it with a streaming source that reorders; rows never collapse | | Buffer | Reaching for it instead of async inserts, then losing data on a restart | | Kafka | Running an exploratory `SELECT` and eating the pipeline's messages | | Distributed | A sharding key with poor cardinality, producing one hot shard | ## What to carry away **The engine is a semantic declaration, not a storage setting.** It says what happens to rows sharing a sorting key when a background merge runs, and it will do that quietly and forever without ever raising an error. Default to `MergeTree` and move only when you can name the behaviour you need. If rows need replacing, `ReplacingMergeTree` with a version column and `LIMIT 1 BY` on read covers most of it. Reach for the aggregating engines through materialized views rather than writing to them directly, always pass `SummingMergeTree` an explicit column list, and if a stream is involved and you truly need collapsing, use the Versioned variant. And remember that every transformation here is eventual. Any query whose correctness depends on the merge having happened is a query that is wrong some of the time — which, given how these failures present, means wrong in a way nobody notices until a number is defended in a meeting. #### 🟡 The ClickHouse series 1. [Part 1 — Internals: columnar storage, granules, the sparse index →](clickhouse-architecture-internals) 2. [Part 2 — Optimization: ORDER BY, data types, the JOIN trap →](clickhouse-schema-query-optimization) 3. [Part 3 — At scale: insert performance and streaming →](clickhouse-ingestion-streaming) 4. [ClickHouse under LLM observability →](clickhouse-llm-observability-backend) ## Frequently asked questions ### Which ClickHouse table engine should I use by default? Plain `MergeTree`, or its `Replicated` variant in a cluster. It is append-only with no merge-time transformation, so what you insert is what you read back. Every other engine trades that predictability for a specific behaviour, and you should only take the trade when you can name the behaviour you need. ### What is the difference between CollapsingMergeTree and VersionedCollapsingMergeTree? Both cancel rows via a `sign` column of +1 and −1. Plain `CollapsingMergeTree` depends on the cancel row arriving after the state row, so out-of-order or concurrent inserts can leave rows that never collapse. `VersionedCollapsingMergeTree` adds a version column making collapsing order-independent — which is what any real streaming source requires. ### Why does SELECT return binary garbage from an AggregatingMergeTree table? Because it stores intermediate aggregate states, not final values. Columns are written with `-State` and must be read with `-Merge`, so a plain `SELECT` returns the serialized state. Use `countMerge()`, `uniqMerge()` and friends — or `SimpleAggregateFunction` where the aggregate is simply combinable. ### What is the Null table engine used for? It discards every row written to it, which is useful precisely because materialized views still fire on insert. Point ingestion at a `Null` table, attach materialized views, and you store the rollups without ever storing the raw feed. ### Can I change a table's engine after creating it? Not in place. You create a new table with the desired engine and copy the data across with `INSERT INTO … SELECT`, then swap names with `EXCHANGE TABLES`. Which is the practical reason to think about the engine before the first insert rather than after the first million.