Our billing pipeline was suddenly slow. The culprit was a hidden bottleneck in ClickHouse

Cloudflare's billing pipeline slowed after redesigning a ClickHouse table to enable per-namespace data retention.

• •Redesigned partitioning from (day) to (namespace, day) to allow different retention periods for different teams
• •Query performance degraded over time despite each query reading the same number of parts
• •Lock contention on the MergeTreeData mutex protecting the parts list was the hidden bottleneck
• •Query planners sequentially acquired exclusive locks and copied all parts lists, causing concurrent queries to queue
• •Fixes included using shared locks for read-only operations and deferring vector copies to filtered parts only

This summary was automatically generated by AI based on the original article and may not be fully accurate.