ClickHouse query latency reduced from 85 seconds to sub-second with incremental optimisations

The event is a real-world performance tuning case on ClickHouse. A query that initially took 85 seconds to process 2 billion rows was brought under one second through a series of small, incremental changes. No single change was a silver bullet; instead, the team compounded basic optimisations over four months. The workload involves mutable event streams, a common scenario in monitoring and observability stacks, where events are updated or deleted after ingestion.

The root cause of the latency was the ReplacingMergeTree engine combined with the FINAL clause. ClickHouse is built on immutable storage, so mutable events force the engine to deduplicate rows at query time. FINAL merges rows with the same primary key, but its performance depends heavily on how data is partitioned. The original time-based partitioning spread a single client’s events across many partitions, forcing ClickHouse to build complex pipeline ranges and often serialising the merge. This created a bottleneck that no amount of hardware could overcome.

The team’s first major change was re-partitioning the table by client_customer_id modulo 36. This counterintuitive strategy ensured that each insert created only 36 parts, controlled merge pressure, and distributed data evenly across partitions. More importantly, it enabled parallel FINAL merges without requiring ClickHouse to compute intersecting ranges. The trade-off was losing partition pruning, but since the queries already scanned full client histories, the cost was negligible. The change also allowed disabling two internal settings, saving additional seconds in pipeline setup.

A second optimisation involved reordering the sort key to move event_type earlier. This was not an ALTER operation but required creating a new table and rewriting all data. The change reduced the number of rows scanned by 16 %, as some event types were irrelevant to the query. Despite the rewrite cost, the improvement in query performance was immediate and significant. The team noted that the sorting key’s fixed nature at table creation means such changes require careful planning and downtime.

The post highlights that performance tuning in analytical databases often involves balancing competing trade-offs. Mutable data in an immutable storage engine, partitioning for parallelism versus pruning, and sort key optimisations all require understanding the workload’s access patterns. The team’s approach, measuring, changing one variable at a time, and compounding small wins, is replicable for engineers facing similar latency challenges in ClickHouse or other columnar databases.