How We Fixed a Hidden Lock Contention Nightmare in ClickHouse Query Planning

The Problem: A Partitioning Change Broke Our Billing Pipeline

At Cloudflare, we rely on ClickHouse to process usage data for billing, fraud detection, and more. Our internal system, Ready-Analytics, stores over a hundred petabytes across dozens of clusters. In early 2022, we built a simplified onboarding model: instead of custom tables, teams stream data into one massive table, disambiguated by a namespace field. The primary key is (namespace, indexID, timestamp). This worked beautifully—until we tried to fix a critical limitation.

The Retention Bottleneck

The original system had a single 31-day retention policy applied to all namespaces. Some teams needed years of data (legal/compliance), others only a few days. This forced many teams to bypass Ready-Analytics and use a complex manual setup. We needed per-namespace retention.

The “Obvious” Fix

We changed the partition key from just day to (namespace, day). This allowed us to drop partitions per namespace. Our reasoning: since every query filters by namespace, the number of parts read per query wouldn't change, so performance should be unaffected. We were wrong.

The Slowdown: A Mystery Unfolds

By March 2025, the billing team reported that daily aggregation jobs were progressively slowing down. I/O, memory, rows scanned—all normal. Yet queries were taking longer. We plotted query duration against total part count per replica and saw a clear linear correlation. But why? If we weren't reading more parts, why did their existence slow us down?

The Investigation: Flame Graphs Reveal the Truth

We used ClickHouse's built-in trace_log to generate flame graphs. The first CPU-based graph showed 45% of leaf query CPU time spent in filterPartsByPartition. A small heuristic reorder gave a 5% improvement—we were on the right track, but missing the real issue.

Then we switched to Real traces (sampling all threads, including waiting ones). The revelation: more than half of query duration was spent waiting for a single mutex (MergeTreeData) that protects the list of active parts. Every thread had to:

1. Acquire an exclusive lock.
2. Copy the entire list of parts.
3. Release the lock.
4. Filter the copy.

With tens of thousands of parts and hundreds of concurrent queries, they were all queuing single-file.

The Fixes: Three Patches

We contributed all three optimizations upstream to ClickHouse (PR #85535, available since version 25.11).

Optimization 1: Shared Lock (std::shared_lock)

The query planner only reads the parts list—it never modifies it. Using an exclusive lock was overkill. We switched to a shared lock, allowing multiple planners to enter the critical section concurrently. Result: lock contention vanished, query durations dropped immediately.

Optimization 2: Deferred Vector Copy

Even with the shared lock, the next bottleneck was copying the giant vector of parts. Copying a vector with tens of thousands of elements hundreds of times per second adds up. We created a shared read-only snapshot; only operations that modify the parts list regenerate it. Result: another significant performance gain.

Optimization 3: Binary Search for Part Pruning

Months later, as part counts kept growing (from 30k to 160k per replica), performance degraded again—but more slowly. The filtering code path was still doing a linear scan. Since the parts list is sorted by partition key (namespace first), we implemented a binary search on namespace. Result: query durations dropped 50%, and the correlation with part count was finally broken.

Limitations and Caveats

• Binary search doesn't generalize to arbitrary query conditions (e.g., namespace IN (5,10)). We're exploring more generic approaches like extending the query condition cache.
• ZooKeeper overhead also grew with part count—our ZooKeeper cluster reached 100 GB. This is a separate challenge.
• Long-term architecture question: Was this partitioning scheme the right choice? We've bought breathing room, but a different architecture (e.g., table-per-namespace) may eventually be necessary.

Next Steps for Learning

• Read the upstream PR #85535 for implementation details.
• Explore ClickHouse's trace_log and flame graph generation—it's a powerful debugging tool.
• Study lock contention patterns in OLAP databases; the same pattern can appear in PostgreSQL, MySQL, etc.
• For more on ClickHouse partitioning strategies, check out our related article on How Advanced Browsing Protection Checks URLs Without Compromising Privacy.

Conclusion

This was a classic case of a well-intentioned change hitting a hidden, non-obvious bottleneck. The key lesson: never assume that unchanged per-query metrics mean unchanged system performance. Lock contention and data structure copying can silently destroy throughput. The three patches—shared lock, deferred copy, binary search—restored our billing pipeline and gave us a scalable path forward. We hope this deep dive helps you avoid similar pitfalls.

This article is based on a real debugging story from Cloudflare. For more on edge AI and real-time inference, see NVIDIA TensorRT Edge-LLM Running Large AI Models on Autonomous Vehicles and Robots.

Core Code: The Three Patches

// Optimization 1: Use shared_lock instead of exclusive lock
// File: src/Storages/MergeTree/MergeTreeData.cpp

// Before:
std::lock_guard lock(mutex);
auto parts = getParts();

// After:
std::shared_lock lock(mutex);
auto parts = getParts();

// Optimization 2: Deferred copy of parts list
// File: src/Storages/MergeTree/MergeTreeData.cpp

// Before: every query planner copies the entire vector
std::lock_guard lock(mutex);
std::vector<PartPtr> allParts = dataParts;  // O(N) copy

// After: shared read-only snapshot, regenerated only on mutation
std::shared_lock lock(mutex);
const auto& sharedParts = getPartsShared();  // no copy
// Filter returns a new vector of only relevant parts
auto filteredParts = filterParts(sharedParts, queryInfo);

// Optimization 3: Binary search for part pruning
// File: src/Storages/MergeTree/MergeTreeDataPart.cpp

// Before: linear scan over all parts
for (const auto& part : allParts) {
    if (matchPartition(part, partitionId)) {
        result.push_back(part);
    }
}

// After: binary search on namespace (first partition key column)
// Assumes parts are sorted by (namespace, day)
auto range = std::equal_range(allParts.begin(), allParts.end(),
                              namespaceId, compareByNamespace);
for (auto it = range.first; it != range.second; ++it) {
    // Only check remaining conditions (e.g., day range)
    if (matchDayRange(*it, timeRange)) {
        result.push_back(*it);
    }
}

Performance Impact at a Glance

Note: part count grew from 30k to 160k per replica over the year. Without patches, query duration would have scaled linearly.

Key Takeaways

• Always profile with real (wall-clock) traces, not just CPU traces. Lock contention is invisible in CPU flame graphs.
• Shared locks for read-only paths are a simple, high-impact fix.
• Deferred copying prevents O(N) overhead from polluting every query.
• Binary search on sorted data can turn O(N) filtering into O(log N).
• Monitor total part count as a leading indicator of query planning health.

Recommended Reading

• How Advanced Browsing Protection Checks URLs Without Compromising Privacy
• NVIDIA TensorRT Edge-LLM: Running Large AI Models on Autonomous Vehicles and Robots