ClickHouse and the MergeTree Family

The previous chapter ended with the line "in the next chapter you'll trade the embedded model for the server model and look at ClickHouse's MergeTree family." That trade is not a cosmetic one. The embedded model assumes one process, one user, data on one machine. The server model assumes the opposite of all three: dozens of concurrent ingest streams, thousands of concurrent dashboards, hundreds of terabytes of data spread across a cluster. The architecture that wins one of those workloads loses the other badly, and ClickHouse is the canonical answer to the second.

To see why, take a concrete workload. Cloudflare's HTTP analytics: every HTTP request that touches Cloudflare's edge — currently around 70 million per second globally — produces an event with maybe 100 columns (URL, status code, country, ASN, cache status, response time, bot signals). All of those events are ingested into ClickHouse. Customers query them with arbitrary WHERE and GROUP BY clauses through a UI, and expect dashboards to render in under a second. There is no precomputed cube that covers the query space; it is too high-dimensional. The query engine has to actually scan the events. At the volumes Cloudflare runs, "scan the events" means reading on the order of a trillion rows in well under a second. A row store cannot do this. A general-purpose columnar warehouse like Snowflake can do it but at substantial cost and with multi-second latency. ClickHouse does it on commodity hardware, in milliseconds, at a fraction of the cost. Why this matters: there is a class of analytical product — observability, real-time dashboards, product analytics — where the latency requirement is interactive (under one second) and the data is too large for a per-tenant cube. ClickHouse is what you build those products on.

What "the fastest" actually means

The phrase "fastest OLAP database" gets thrown around a lot, so it is worth being precise about what ClickHouse is fast at.

Single-table scans with filters and aggregations. This is the bread-and-butter analytical query: SELECT country, count() FROM events WHERE timestamp > now() - 1 day AND status = 500 GROUP BY country ORDER BY count() DESC LIMIT 20. ClickHouse can scan and aggregate on the order of a billion rows per second per CPU core when the columns are well-encoded and the filter is selective. On a 64-core machine that is 60+ billion rows per second of aggregation throughput. Why this is plausible: a column of Int32s is 4 bytes per row, so a billion rows is 4 GB. Modern DDR5 memory delivers 50+ GB/s of sequential read throughput per socket; AVX-512 can sum eight Int32s per cycle. A billion rows per second per core is not a marketing number — it is what falls out of streaming a column through SIMD.

Time-range queries on time-ordered data. When the table's sort key is (timestamp, ...), a query for "the last 5 minutes" only touches the parts whose timestamp range overlaps that window. The other parts — possibly representing years of history — are skipped without being opened.

Pre-aggregated queries via materialised views. When you can express your dashboard as "give me the count of events per country per minute," you can materialise that into a SummingMergeTree updated continuously by a materialised view. The dashboard then queries a thousand-row table instead of a billion-row table. Sub-millisecond latency.

Where ClickHouse is not the fastest: point lookups by primary key (Postgres or any indexed row store wins), updates and deletes (ClickHouse can do them but they are expensive and asynchronous), high-concurrency transactional writes (it is not an OLTP system at all). If you need to update individual rows millions of times a day, you are using the wrong tool. Why: the immutable-parts model that makes scans fast is structurally bad at point updates. Picking the right system for the access pattern, again.

The MergeTree storage model

Everything in ClickHouse comes back to MergeTree. You should think of a MergeTree table as a directory of immutable, sorted, columnar files, plus a background process that periodically combines small files into bigger ones. That sentence has four words doing a lot of work — immutable, sorted, columnar, combining — and each is worth unpacking.

The picture is a literal one — a MergeTree table is a directory on disk, and inside that directory you can ls and see exactly the parts shown above. A part is a self-contained mini-table. It has one binary file per column (the column's values, compressed with LZ4 or ZSTD), an index file, and a small columns.txt describing the schema. Crucially, every part is sorted by the table's primary key. Why sorted: when the data inside a part is sorted by, say, (event_time, user_id), a query that filters on event_time > X can binary-search the sparse index to find the first matching row and stream forward, skipping everything before. Sorting transforms a full scan into a range scan for free.

Inserts work like this: a batch of rows arrives, ClickHouse sorts them by the primary key in memory, encodes each column to its compressed binary form, writes the resulting files to disk as a new part directory, and atomically renames it into place. The existing parts are never touched. This is the LSM-tree trick — writes are pure appends, never in-place mutations — and it is what gives MergeTree its very high ingest throughput. Why immutability matters: in-place mutation requires you to lock the data, find the row, decompress the column block it lives in, modify it, recompress, write back. None of that is needed when you only ever append. The cost is that you accumulate small parts, which is exactly what the background merger fixes.

The background merger runs continuously. It picks small adjacent parts (by some size and time heuristic) and k-way merges them into one larger part — exactly like LSM compaction. Because every part is already sorted by the same primary key, the merge is a streaming merge-sort, linear in the data size. The newly merged part replaces its inputs atomically; queries in flight that were reading the old parts finish reading them, and queries that arrive after see the merged part. Old parts get garbage-collected after their reference count drops to zero. Why merging is essential: without it, after a million inserts you would have a million tiny parts, and a query would have to open and read sparse-index data from all of them. Merging keeps the part count to a few hundred per partition, even after months of continuous ingest.

A part's other significant feature is its sparse primary index. Unlike a B-tree index which stores one entry per row, ClickHouse stores one index entry per N rows (default 8192, configurable). Each entry holds the primary-key value of the first row in that block, plus an offset into the column files. To find rows matching a WHERE clause, ClickHouse binary-searches the sparse index to find the candidate blocks (granules), then scans only those granules from disk. Why sparse: a billion-row table at one entry per row would have a billion index entries — gigabytes of index, far too large to keep in memory. At one entry per 8192 rows, the index has 122,000 entries, fits in a couple of MB of RAM, and still narrows a billion-row scan to one or two granules of 8192 rows each for an equality lookup. The trade-off is that you read 8192 rows even when you only want one — but for analytical workloads where you usually want many rows in a range, that is a great trade.

The PRIMARY KEY is the sort order, not a uniqueness constraint

This is the single most important conceptual gotcha for engineers coming from Postgres. In ClickHouse, PRIMARY KEY does not mean "unique identifier." It means "the columns the data is physically sorted by inside each part." There is no uniqueness check. You can insert ten rows with identical primary key values and ClickHouse will happily store all ten — they will just sit next to each other on disk because they sort the same way.

The right way to read ORDER BY (event_time, user_id) (which is the recommended way to declare it in modern ClickHouse) is: "sort the data by event_time first, then by user_id within each event_time." The choice of sort key is the most consequential schema decision you make, because it controls which queries are cheap and which are expensive. Why: a query that filters on a prefix of the sort key (e.g. WHERE event_time > X when the sort is (event_time, user_id)) can use the sparse index to skip granules. A query that filters only on a non-prefix (WHERE user_id = X alone) cannot — it has to scan every granule.

This is the rule that drives the entire schema design playbook for ClickHouse: figure out the dominant query pattern first, then sort the table by the columns those queries filter on, in the right order. In a time-series workload that is almost always (timestamp, some_dimension). In a per-user analytics workload where most queries filter by user, it might be (user_id, timestamp). There is no one right answer — only "right for your queries."

The MergeTree family

MergeTree is the base. From it ClickHouse derives a small family of engines, each adding a twist to what the background merger does when it combines parts. Every member of the family inherits the immutable-parts, sparse-index, columnar storage of MergeTree — they only differ in what happens when two rows with the same sort key meet during a merge.

ReplacingMergeTree is the simplest variant. Declare an optional version column. When the merger sees two rows with the same sort key, it discards the older one (lowest version) and keeps the newest. Useful for "latest state per entity" tables — fed by a CDC stream from your OLTP database, you get a table where the latest version of each row eventually wins. The catch is eventual: until the merge happens, both versions are on disk, and queries that don't tolerate duplicates must use SELECT ... FINAL or argMax(value, version) GROUP BY id to filter to the latest. Why this is fine in practice: most analytical queries either aggregate (where duplicates roll up cleanly) or use argMax explicitly. The "background dedup" story is good enough for things like a search index keyed by document ID.

SummingMergeTree sums numeric columns when sort keys collide during merge. The classic use case is a pre-aggregated counter table: you ingest "user X did action Y at time T, count = 1" and after merging you have one row per (user, action, hour) with the total count. The base table effectively becomes a continuously-rolled-up counter. Combined with materialised views (next section), this is one of the most powerful patterns in ClickHouse.

AggregatingMergeTree generalises SummingMergeTree to arbitrary aggregations. Instead of summing, columns hold aggregate function states — partial computations of uniq(), quantile(), avg() and so on, which can be merged associatively. A column might store the HyperLogLog state of uniqState(user_id) rather than a final count. When the merger combines two rows it merges the HLL states; when you query, you finalise with uniqMerge(state) to get the count. This is the engine you build production materialised views on for sub-second dashboards over billions of events.

CollapsingMergeTree uses a sign column that is +1 for "row added" and -1 for "row removed". On merge, matching pairs cancel each other out, leaving zero rows. This is how ClickHouse models row-level changes: to "delete" a row, you insert its mirror with sign = -1 and let the merger eventually collapse them. Aggregations have to weight by sign to be correct between merges. VersionedCollapsingMergeTree adds a version column so that out-of-order arrivals (a -1 arriving before its +1 because of network reordering) still collapse correctly. Why this contortion: ClickHouse fundamentally cannot do efficient in-place row deletes — the parts are immutable and the columns are compressed. Sign-based collapse is the workaround that gives you logical mutability while keeping the physical append-only model.

ReplicatedMergeTree is not a merger variant — it is the multi-node version of MergeTree. Replicas coordinate via ZooKeeper (or its in-house replacement, ClickHouse Keeper) to agree on what parts exist. Each insert is replicated to all replicas; merges are coordinated so that the same merge runs on every replica and produces byte-identical parts. The Replicated prefix composes with every other variant: ReplicatedReplacingMergeTree, ReplicatedAggregatingMergeTree, and so on. Why ZooKeeper / Keeper: replication needs a consensus log of "which parts exist, in what order, on which replicas." That is a classic Paxos / Raft problem. Yandex used ZooKeeper for years; modern ClickHouse ships Keeper — a Raft-based drop-in replacement — to get rid of the JVM dependency.

The killer feature: incremental materialised views

The MergeTree family becomes much more powerful when you combine it with ClickHouse's materialised views. Unlike Postgres materialised views (which are recomputed periodically), ClickHouse materialised views are incremental: they fire on each insert into the source table and write the transformation result into a target table. If the target is a SummingMergeTree or AggregatingMergeTree, the result is a continuously-maintained roll-up.

The pattern looks like this. You have a billion-row events table. You want a dashboard showing daily active users per country. You write:

CREATE MATERIALIZED VIEW dau_by_country_mv
TO dau_by_country
AS SELECT
    toDate(event_time) AS day,
    country,
    uniqState(user_id) AS unique_users_state
FROM events
GROUP BY day, country;

dau_by_country is an AggregatingMergeTree table holding uniqStates. Every insert into events fires the view, which writes the per-batch partial HLL states into dau_by_country. The background merger then merges those states. Your dashboard queries SELECT day, country, uniqMerge(unique_users_state) FROM dau_by_country GROUP BY day, country — which touches a table with thousands of rows instead of billions, and returns in single-digit milliseconds. Why this beats traditional materialised views: there is no recompute step, no staleness window, no "the view rebuilt at midnight so the dashboard is wrong until then." The aggregation grows incrementally as data arrives, in roughly the same I/O the insert was already going to do.

This pattern is why ClickHouse is the analytics backend at Posthog, Plausible and many others: dashboards are queries against materialised AggregatingMergeTree rollups, while the raw event table sits underneath for ad-hoc deep-dives.

Real-world scale

ClickHouse's reputation comes from the workloads it actually runs in production, not from benchmarks. The numbers are worth internalising as a calibration of what is possible.

The headline that sticks: a 10-node ClickHouse cluster routinely answers dashboard queries with sub-second p99 latency over trillions of rows. That is the operating point of products like Cloudflare's analytics dashboard, Posthog's session insights, and Sentry's error timelines. The same workload on a row store would either take minutes per query or require a multi-million-rupee cube-precomputation pipeline. Why ClickHouse hits this and Snowflake mostly does not: Snowflake is a general-purpose warehouse priced for query elasticity; ClickHouse is a specialised analytics database run by you on your own machines with a tightly engineered hot path. For the specific workload of "interactive dashboards over time-series events," ClickHouse is one to two orders of magnitude cheaper per query. Comparison studies consistently put ClickHouse at the top of price-performance for this workload class.

Worked example: a billion clickstream events a day

Here is the canonical real-world design. You are an engineer at an Indian e-commerce startup growing fast — say a Flipkart-shaped company at scale, or a newer Tata Neu / Meesho. The product team wants every page view, every product click, every add-to-cart, every search, every checkout step tracked. The marketing team wants to slice these events by user, by city, by acquisition channel, by hour. The volume is about a billion events per day, peaking at 30,000 events per second during evening sales. You have to design the ClickHouse schema.

CREATE TABLE events
(
    event_time      DateTime CODEC(DoubleDelta, ZSTD(3)),
    user_id         UUID,
    session_id      UUID,
    event_name      LowCardinality(String),
    page_url        String,
    product_id      Nullable(UInt64),
    city            LowCardinality(String),
    channel         LowCardinality(String),
    properties      Map(String, String),
    event_date      Date MATERIALIZED toDate(event_time)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id)
TTL event_time + INTERVAL 18 MONTH DELETE
SETTINGS index_granularity = 8192;

SELECT count()
FROM events
WHERE user_id = '550e8400-e29b-41d4-a716-446655440000'
  AND event_time > now() - INTERVAL 1 DAY;

Where ClickHouse fits — and where it doesn't

Pulling the threads together, ClickHouse occupies a very specific niche, and being honest about its boundaries makes it more usable.

Use ClickHouse when: your workload is heavy on time-series or event data, you need interactive (sub-second) latency on aggregations over billions to trillions of rows, your queries are predominantly filter-and-aggregate (not point lookups, not multi-table joins on huge tables), and your write pattern is bulk inserts rather than single-row OLTP transactions. This is observability, product analytics, web analytics, ad analytics, IoT telemetry, financial market data — basically any "events arriving fast, dashboards queried fast" workload.

Don't use ClickHouse when: you need OLTP transactions (use Postgres), you need millisecond point updates and deletes (use Postgres or a key-value store), your workload is a star-schema warehouse with many big-to-big joins (Snowflake, BigQuery and Spark are better), or your data is small enough to fit on a laptop (the previous chapter — DuckDB).

Between DuckDB and ClickHouse you have a remarkably wide coverage of the analytical workload space: DuckDB for the embedded, single-process, laptop-scale case; ClickHouse for the server-grade, real-time, cluster-scale case. The chapters that follow go up one level of abstraction — into data warehouses and lakehouses (Snowflake, BigQuery, Iceberg, Delta Lake) where the design priority shifts from raw query speed to multi-tenancy, governance and elastic separation of storage from compute.

What you should take away

1. ClickHouse is the fastest open-source OLAP database for time-series and event-analytics workloads. A billion rows per second per core scan throughput is a real, measurable number, not marketing. A 10-node cluster routinely answers dashboard queries with sub-second p99 over trillions of rows.

2. MergeTree is the heart of it. Immutable, sorted, columnar parts; sparse primary index; LSM-style background merging. Inserts append; merges compact; queries skip whole granule ranges via the sparse index.

3. The "primary key" is the sort order, not a uniqueness constraint. The single most consequential schema decision is the ORDER BY clause: it determines which queries are fast and which are not. For time-series data, (timestamp, dimension) is the default that pays back almost always.

4. The MergeTree family is variants on the merge step. ReplacingMergeTree dedupes by version, SummingMergeTree sums numeric columns, AggregatingMergeTree maintains aggregate states, CollapsingMergeTree handles soft deletes via a sign column, ReplicatedMergeTree adds multi-replica coordination. They compose orthogonally with each other.

5. Materialised views are the killer feature. Combine a base events table with a SummingMergeTree or AggregatingMergeTree target through an incremental materialised view, and you get continuously-maintained sub-millisecond rollups over billions of rows. This is how every ClickHouse-powered analytics product (Posthog, Plausible, Cloudflare dashboards) is built.

6. Schema design is the whole game. Pick the engine, pick the partition key, pick the sort key, declare the right LowCardinality and codec hints, layer materialised views on top. The rest takes care of itself.

In the next chapter you'll move up the stack from real-time analytical engines to the warehouse era — star and snowflake schemas, the dimensional modelling vocabulary that drives Snowflake, BigQuery and Redshift workloads.

References

1. ClickHouse — Architecture overview (official docs)
2. ClickHouse — ClickHouse Keeper: a Raft-based ZooKeeper replacement
3. Altinity blog — ClickHouse MergeTree engine series
4. Cloudflare blog — HTTP analytics for 6M requests per second using ClickHouse
5. Altinity — ClickHouse vs Snowflake for real-time analytics: a benchmark study
6. Alexey Milovidov — ClickHouse: Yandex Highload talks (architecture and history)