GoKawiilEngineering team cuts ClickHouse query latency from 85 seconds to sub-second
A team tasked with speeding up ClickHouse for its largest customers found a critical dashboard query taking over 85 seconds and scanning nearly 2 billion rows across 198GB of data. Over four months, they applied a series of incremental, non-flashy optimizations—tackling issues like the overhead of ReplacingMergeTree's FINAL clause on mutable event data and an inefficient partition key—to bring the query down to sub-second response times.
GoKawiil's interpretation of the reporting above, not reported fact.
The case illustrates a common tension in analytics databases: ClickHouse is built for immutable, append-only data, but real-world systems often need to update or delete historical records, forcing costly workarounds like FINAL merges. The story is a reminder that dramatic performance gains often come not from clever tricks but from patiently identifying and removing compounding inefficiencies.
- A dashboard query scanning 1.96 billion rows and 198GB took 85+ seconds before optimization work began.
- Mutable event data forced reliance on ClickHouse's ReplacingMergeTree engine and FINAL clause, which are performance-costly.
- Sustained incremental improvements over four months achieved sub-second query latency without any single breakthrough fix.
Designing Data-Intensive Applications by Martin Kleppmann — If shaving an 85-second ClickHouse query down to sub-second latency sounds like your kind of puzzle, Kleppmann's book is the definitive deep dive into how storage engines, log-structured merges, replication and stream processing actually work. It explains the fundamentals behind column stores, Kafka ingestion and mutable-event modeling that make incremental optimizations like these click.
See Designing Data-Intensive Applications by Martin Kleppmann on Amazon → Affiliate link — we may earn a commission on purchases, at no extra cost to you. Product picked by AI based on this article; it is not a tested recommendation.Source: jordivillar.com, 2026-09-09
Published there as: “Every Millisecond Counts”
Read the original report → The summary and analysis above are GoKawiil's own, written from reporting by the source above. Facts and quotes belong to the original publisher.