Our ClickHouse setup to scale

ObsessionDB scales ClickHouse® on both sides of the workload. On ingest, one migration loaded hundreds of terabytes in a few days at over 300 inserts per second, and a single 32-CPU node sustained 31.8 million rows per second for 90 minutes. On serve, a 203-billion-row, 56 TB table answers random lookups under a second at p99. Underneath sit one copy of the data on object storage, stateless compute, locality-aware merges, and settings any ClickHouse cluster can use.

Last week we published our setup to reduce latency. This post is the other axis of ClickHouse scaling: what happens to the same cluster when the data grows ten times, and the ingest rate grows with it.

Both curves come from a stack of decisions about where bytes live and which node does which work. The first half of this post walks the stack one layer at a time, each set against what a self-hosted cluster does in the same situation. The second half is the toolkit: the settings that move scale on any cluster, split into ingestion, merges and query.

The setup, layer by layer

Adding a node to one of our clusters moves no data. Most of the layers below rest on that one property, and it is the one a self-hosted cluster cannot buy with settings.

One copy of the data, stateless compute

Alloy is our storage engine, developed against the SharedMergeTree API. A table's metadata lives in a coordination layer, its data lives in object storage as immutable objects, and the compute nodes hold nothing durable. A node that joins subscribes to the metadata and starts serving; its share of the cache ring fills on first touch. A node that leaves hands its keys to the next-ranked node and the ring heals. Our scaling docs put it in one line: changing the shape of the cluster moves no data, with no re-sharding and no rebalancing. Building on decoupled ClickHouse covers what changes for the tables on top.

ReplicatedMergeTree, the engine a self-hosted cluster runs, keeps a full copy of the bytes on every replica and coordinates them through Keeper. Three replicas of a 54 GiB table hold about 162 GiB. Each logical table becomes a _local table per shard plus a Distributed facade, and each ALTER goes out ON CLUSTER, where it can apply on some shards and fail on others. We ran that way for a year; adding a shard meant copying more than 25 TB.

Putting an S3 disk under MergeTree does not change that. With a tiered storage policy each replica still keeps its own copy of the files in S3, and the part metadata stays on each node's local disk. Zero-copy replication shares the objects but keeps the metadata local, and the ClickHouse docs mark it as not ready for production. It has been disabled by default since 22.8. Tiered storage is a cold tier with the same coupling.

Separate compute over the same objects

One cluster used to mean one workload mix. With nothing durable on a node, a second group of nodes can read the same objects, and the two groups do not compete for memory or bandwidth. We run customers' serving nodes as one group and a separate node for their heavy backfills and rebuilds as another if needed. Reingestion jobs run the same way, on isolated infrastructure instead of the nodes that serve the API.

The extreme version is a datashare: another organization's compute reads your objects, read-only, with no copy and no replica, and neither side can burn the other's resources.

The distributed cache

Every read goes through the distributed cache. Each node contributes its NVMe to one ring under rendezvous hashing, and a part is cached the moment it is written, so the freshest partitions serve warm on their first read. A node-to-node fetch is sub-millisecond, whereas object storage runs tens of milliseconds at the median and hundreds at the tail, as the latency post measured. Its bandwidth grows with the cluster: 20 nodes give 20 times one node's network where a centralized cache tops out near three. Size it so the working set per node fits in about 80% of that node's cache disk (the docs give the formula), or your latency tracks object storage instead. That post also covers how the cache behaves under load.

Merges that stay where the data is

17% is the share of merges a six-node shared-storage cluster would do locally if parts landed at random, one chance in six that the inputs sit on the node doing the work. Our busiest clusters run around 70% local. We put the difference at roughly four times less merge bandwidth on six nodes, and the 10-billion-row benchmark is where that headroom shows: we merge parts up to 150 GB without congesting the network.

The mechanism has no scheduler. In a stateless cluster, any node can merge any set of parts, because the parts live in object storage. Each node runs the same merge selector and applies the same rendezvous-hash ranking to one local question, whether it is the best-placed node for this candidate set. The node that ranks highest for the inputs takes the merge, and its output lands on its ranked owner, which is where reads for that part are routed next. The docs describe the placement as emerging from every node applying the same ranking to the same facts. Two knobs exist, and you do not need to touch them: cache_locality_aware_merges is on, and min_bytes_for_locality_aware_merges defaults to 100 MB so that tiny parts, which are the most numerous, merge wherever a slot is free instead of bunching up on one node.

A merge often reads several hundred input parts. On a cluster where placement is random, the share of those inputs on the cache of the merging node is 1/N, so every node you add moves more bytes across the network to do the same merge work. Locality holds the local share near 70% as N grows. That is the whole reason adding nodes helps merges here and hurts them on a shared-storage cluster that places merges at random.

Queries that fan out to where the data is warm

Our parallel-replica fan-out differs from stock ClickHouse in routing. The read planner reuses the rendezvous-hash ranking that places merges: each sub-query first takes the ranges its own node owns in the cache ring, then steals remaining ranges from the next-ranked node rather than a random one, so the hit rate holds even when the owner is busy. On the 56 TB table, in an idealized network-read case, the same query took 1.3 s on one node, 1.0 s across six nodes with plain parallel replicas, and 0.5 s on those six nodes with locality routing.

Open-source ClickHouse 26.6 added an experimental second execution mode, multi-stage distributed execution, and 26.8 added a cost-based optimizer for it. The planner splits a query into stages joined by scatter, broadcast, gather and shuffle exchanges, so a high-cardinality GROUP BY no longer funnels every partial result through one coordinator's memory. We have been testing it on 26.8. On our three-node test cluster in September, a high-cardinality aggregation ran 2.86x faster than on one node, and a heavier one that fails on a single node and under parallel replicas completes under multi-stage execution with far less memory.

The toolkit: what moves scale on any ClickHouse cluster

The five layers ship with the platform. The settings below are the ones you hold, on any cluster, and they decide whether the layers get to help.

Ingestion

Batch your inserts. ClickHouse recommends about one insert per second per table, with thousands of rows in each. Many small inserts make many small parts, and every part has to be merged later. Async inserts do the batching on the server. Keep wait_for_async_insert = 1 so a failed insert fails loudly, and give the client a longer timeout than the server needs, or a retry writes the same rows twice. Since 26.8 max_insert_threads defaults to all cores, which makes more parts and takes CPU from merges; pin it low for bulk loads.

Setting	What it buys	What it costs
Batching, ~1 insert/s per table	Fewer parts, fewer merges	A little latency on the client
async_insert = 1, wait_for_async_insert = 1	Server-side batching	A timeout you have to size
max_insert_threads (auto since 26.8)	Faster bulk loads	More parts, less CPU for merges
deduplicate_insert = enable	Retries do not duplicate rows	Only within the dedup window

Merges

A merge rewrites a set of parts into one, and keeps going until parts reach about 150 GB. A stock node runs up to 32 merges at once and picks the small ones first, so a low part count does more for you than any pool setting. Two things we learned the slow way: OPTIMIZE FINAL on parts past 150 GB can fail, and FINAL slows down with every part it has to read, so do_not_merge_across_partitions_select_final = 1 is a cheap win on partitioned tables.

Setting	What it buys	What it costs
background_pool_size	More merges in flight	CPU and memory taken from queries
parts_to_delay_insert / parts_to_throw_insert	A warning before Too many parts	Raising them only defers the work
min_age_to_force_merge_seconds	Quiet partitions still compact	Rewrites on a schedule
do_not_merge_across_partitions_select_final	Cheaper FINAL	Nothing on a partitioned table

Query

Fix the access pattern before you add a node. A customer asked us for two more nodes; one query was scanning a petabyte a day, and a single index cut the cluster's CPU by 99.5%. max_threads defaults to all cores; on lookups under heavy inserts, pinning it to 4 or 8 per query gave us a stable p99. Parallel replicas help big scans and hurt point lookups, so turn them on per query with parallel_replicas_min_number_of_rows_per_replica as the guard. After adding nodes, run SYSTEM PREWARM so the data is warm on the cache.

Setting	What it buys	What it costs
A sorting key that matches the filter	Skips granules instead of scanning	A redesign, once
max_threads 4 to 8 per lookup query	Stable p99 under mixed load	Peak speed on wide scans
enable_parallel_replicas per query	Big scans use the whole cluster	Overhead on small queries
SYSTEM PREWARM after a resize	New nodes start warm	Network time up front

What the layers add up to

The layers show up on the three axes a ClickHouse cluster is judged on: how fast it takes data in, how fast it processes it, and how fast it answers.

Ingest first, because it is where a replicated cluster runs out of road earliest. On ReplicatedMergeTree an insert is written once and then copied to every replica, roughly ten Keeper entries per INSERT and budget a whole cluster at a few hundred inserts per second, and every replica repeats every merge on its own copy. Here an insert is one write: the receiving node writes the part once, through the distributed cache to object storage, hands the metadata to the coordination layer, and the other nodes get a new-part notice and prewarm the index without copying a byte. Packed storage puts a part's columns into a single object, about 15 times fewer writes for wide parts. The numbers follow. One 32-CPU node sustained 31.8 million rows per second for 90 minutes this June, about a million rows per second per CPU, with merges running the whole time. The migration behind the Numia post loaded hundreds of terabytes across more than twenty chains in a few days at over 300 inserts per second per table, and the clients feeding the cluster were the part that limited it; one busy hour on the same cluster in May produced 335,000 new parts and between two and three million object-storage requests without a merge or storage error. A customer's backfill later in the summer ran at five to six million rows per second across nine nodes, and two proof-of-concept customers, both familiar with ClickHouse Cloud, told us they had not seen that ingest rate before.

Merges are where adding nodes pays or punishes. Because any node can merge any set of parts, merge capacity grows with the cluster instead of being repeated on every replica, and locality routing keeps about 70% of merge inputs on the node doing the work where random placement on six nodes would give 17%, roughly four times less merge bandwidth. That headroom is what lets us merge parts up to 150 GB without congesting the network, and it is why the ingest rates above hold while the compaction behind them runs.

Queries get the same one copy of the data, warm the moment it is written, with reads routed to the node that holds it. The 56 TB table answers random-address lookups at a p99 of 703 ms on six nodes, and the 18 TB dataset served through materialized views came in under 410 ms at p99 across 25,000 queries on five. Parallel replicas take the heavy scans, 16.3x on the heaviest query of the 10-billion-row benchmark, per query only, and multi-stage execution on 26.8 takes the high-cardinality aggregations that neither a single node nor parallel replicas handle well.

The last axis is what a change of shape costs you. Adding or removing a node moves no data, a heavy job can run on its own compute group without the API noticing, and the bill is compute plus storage with no per-query charge, which is why the cost-per-terabyte curve in the intro falls as the data grows.

If something is off, work down this table. Each row is cheaper to check than the one below it, and most tickets close before the bottom.

Symptom	Check first	The move
Inserts throttled or rejected	Parts per partition in system.parts	Batch; then raise the thresholds on that table and pay the merge debt later
Merges fall further behind as you add nodes	Cross-node fetch volume during merge windows	Locality-aware merges, or fewer, larger nodes
Big scans ignore your node count	enable_parallel_replicas per query, analyzer on	Fan out the heavy shapes only, keep the min-rows guard; on 26.8, multi-stage execution for high-cardinality aggregations
A backfill slows the API	Which nodes the job runs on	Separate compute over the same objects
Retries double-count	The dedup window against your retry interval	Block dedup on inserts; idempotent views for the fan-out
Everything green and still slow at N nodes	S3 requests per query, cache working set vs disk	You are at the architecture line; no setting moves it

The last question is the shape of the cluster itself. Our sizing rule: heavy warehouse-style operations want fewer nodes with more concentrated resources, because a big merge or a big join needs one node's memory; tight-SLA serving with elastic demand wants a larger fleet, for the cache disk and the parallel scan capacity; when in doubt, go with fewer, bigger nodes, and not fewer than three. A two-node cluster loses half its capacity on a rolling update. A nine-node one loses 11%.

The claim all of it serves is the same on both axes: less hardware for the same performance, or more performance from the same hardware. If your cluster is not behaving that way, we will look at it with you. Our performance audit takes your query log and your part counts, runs this checklist and the deeper ones, and hands back the findings, on ObsessionDB or not.

FAQ