Twelve-hour reports became four-second queries on a real ClickHouse cluster
ClickHouse — sharded and replicated cluster
Where they started
Analysts queried a read replica of the transactional database. Reports took hours, sometimes timed out, and occasionally affected production throughput.
The problem, precisely
Analysts needed sub-second explores over billions of rows without touching the transactional system.
What we did
Modelled the schema around query patterns first — sort keys and partitions chosen from real analytic questions rather than from the source schema — then built the cluster with Keeper for coordination.
Delivered
- Schema designed against the top forty analytic queries, with sort keys and partitions justified
- Cluster with two shards and two replicas, Keeper quorum separate from data nodes
- Streaming ingest via the Kafka engine with a materialised view aggregation layer
- Cold-data tiering to object storage, cutting local disk requirement substantially
- Query resource governance so one analyst cannot monopolise a node
Technologies used
Measured result
Every number below was measured and reported to the customer. Where we do not have a measured figure, the entry is a fact rather than a percentage — we would rather be precise than impressive.
- 12h → 4s flagship report runtime, before → after
- 0 analytical load reaching the transactional replica
- 22B rows queryable interactively
- 1 replica failure survived during a live query window
In ClickHouse the sort key is the product. Choose it from the queries people ask, and everything after that is operational routine.