The parts you never read were the culprit: Cloudflare's hidden ClickHouse bottleneck!

Heyo, it's Shiichan! Today I've got a thrilling debugging detective story about an invisible bottleneck hiding deep inside a database. Cloudflare almost couldn't send out its invoices, so buckle up!

Cloudflare Blog blog.cloudflare.com

What was announced?

Cloudflare's Blog published the story of how they tracked down why their billing pipeline suddenly got slow. The culprit was hiding deep inside ClickHouse, the OLAP database a lot of us use.

Every day, Cloudflare makes millions of calls to ClickHouse to figure out how much each user should be billed. This pipeline powers hundreds of millions of dollars in revenue and fraud systems, so any delay is a big deal. After one migration, the daily aggregation jobs kept getting slower and slower. And yet I/O, memory, rows scanned, parts read, every metric they'd normally check looked totally clean. That's the scary kind of bug where you can't see the cause at all.

The story so far

Cloudflare keeps over 100 petabytes of data across a few dozen ClickHouse clusters. To make onboarding easy for internal teams, they built a system called "Ready-Analytics" in early 2022. Instead of designing new tables, teams just stream data into one giant table. Datasets are separated by a namespace, and everyone uses a shared schema (20 float fields, 20 string fields, a timestamp, and an indexID). The primary key looks like (namespace, indexID, timestamp). By December 2024 it had grown past 2PiB, ingesting millions of rows per second.

But it had one weakness: retention was "31 days for everything." That was a leftover from before ClickHouse had TTL features, back when they partitioned by day and simply dropped partitions older than 31 days. So teams that needed years of history and teams that needed just a few days got the same rule. They wanted per-namespace retention instead.

So they chose to change the partitioning key from (day) to (namespace, day). This let the existing retention system keep working, but now with per-namespace granularity. Total part count would grow, but they assumed "since every query filters by a specific namespace, the number of parts a single query reads shouldn't change." So they believed performance would be unaffected. That assumption was the trap. The migration began in January 2025.

What changes

The most important thing: the billing crisis, which was down to the wire, got resolved. When the team first reported it there were 30,000 parts per replica; a year later that hit 160,000 per replica, yet query durations stayed stable.

What's also great is that Cloudflare didn't keep these fixes locked in their own fork, they contributed them upstream to ClickHouse. Optimizations 1 and 2 (below) landed as PR #85535 and have been available since ClickHouse version 25.11. So any team running ClickHouse gets a faster planning phase on tables with lots of parts, for free.

There's also a humbling lesson here. The reasonable-sounding assumption "a single query reads the same number of parts, so performance won't change" tripped them up through the sheer growth in total part count. It's a clear example of how even a well-planned change can fall victim to one wrong assumption.

Dive Deep

Two months after the migration, in late March 2025, the billing team reported that the daily aggregation was slow, and getting slower the further it went. Per-query metrics didn't show more data or more parts being read. It took several days before they plotted query duration against the cluster's total part count, and the correlation was undeniable. Extra parts they weren't even reading were dragging everything down just by existing.

To dig in, they used ClickHouse's built-in trace_log, a table that records traces from the running server and lets you filter by user and query ID. The first CPU flame graph showed 45% of query time going into a single function, filterPartsByPartition, the query planning phase that decides which parts to read. Their first patch reordered the pruning heuristics and got only a 5% improvement. Close, but not the real target.

Then they switched from "CPU" traces (only active threads) to "Real" traces (all threads, including waiting ones), and the truth appeared. The real culprit wasn't CPU work, it was lock contention.

More than half of our query duration was spent waiting to acquire a single mutex (MergeTreeData) that protects the table's list of parts.

To plan a query, every thread had to (1) take an exclusive lock on the mutex MergeTreeData guarding the table's parts list, (2) copy the entire list of parts, (3) release the lock, and (4) filter down to only the parts it needed. With tens of thousands of parts and hundreds of concurrent queries, they all lined up single file. That was the true bottleneck.

Cloudflare shipped a trio of patches:

• Optimization 1, shared lock: the planner only reads the parts list, it doesn't modify it, yet it used an exclusive lock. Switching to std::shared_lock let all planners enter concurrently, and the contention vanished.
• Optimization 2, stop copying: even with a shared lock, copying a vector of tens of thousands of elements hundreds of times a second still cost a lot. So they keep one "shared copy" of the parts list that read-only work (like planning) just reads from, and only regenerate the cache when something changes the set of parts (like an insert). Planners now copy only the filtered list they actually need. Optimizations 1 and 2 landed as PR #85535 in ClickHouse 25.11.
• Optimization 3, binary search: as part counts grow, things slowly slow down again. The filter did a linear scan over all parts, but the list is sorted by the partition key, and its first column is the namespace that most queries filter on (it identifies the tenant). So they binary-search on namespace to exclude a huge range of parts without looking at them, then check the small remaining range one by one as before. After deploying this in March 2026, query durations dropped by 50%, and finally the correlation with part count broke.

That said, binary search doesn't help much for conditions like namespace in (5,10), so they're exploring more general approaches like extending the query condition cache to cover part filtering. And growing part counts also strain ZooKeeper, which tracks metadata for all parts; they teased a future story about "the 100 gigabyte ZooKeeper cluster."

Wrap-up

• The real culprit behind Cloudflare's slow billing pipeline was mutex contention in ClickHouse's query planning phase. Parts you never read still formed a queue just by existing.
• Changing partitioning from (day) to (namespace, day) exploded the total part count. The assumption "a single query reads the same number of parts, so performance won't change" was the trap.
• The key was the "Real" trace flame graph. Watching only CPU kept the wait time hidden the whole time.
• A trio of fixes (shared lock, avoid copying, binary search) solved it. The first two are upstream in ClickHouse 25.11, so everyone benefits. Parts went from 30,000 at report time to 160,000 a year later, yet query durations stayed stable.
• A great read for anyone running ClickHouse at scale, wrestling with partition design, or who loves hunting bottlenecks with flame graphs!