Cloudflare's HTTP analytics pipeline hit a wall at 15 queries per second on a Postgres/Citus cluster. They rebuilt it on ClickHouse, shut down 12 database nodes, and deleted tens of thousands of lines of Go, SQL, Bash and PHP.

Cloudflare’s HTTP analytics pipeline started with a log forwarder pulling Cap’n Proto logs off the edge into a Kafka cluster of 106 brokers, replicated three times, averaging 6 million logs a second. From there, 106 Go consumers — internally called Zoneagg — did per-partition aggregation and wrote into a single PostgreSQL instance (RollupDB), which fed a Citus cluster of one main node plus 11 workers, replicated twice. A Zone Analytics API of 5 Go instances queried Citus, sitting behind a PHP proxy layer and an Nginx load balancer.

It was a lot of moving parts, and the codebase behind them had grown to match: thousands of lines of Bash and SQL for the aggregations, thousands more of Go for the API and Kafka consumers.

The system that replaced it: 36 ClickHouse nodes, replicated three times, doing the aggregation work that used to live in Postgres and Bash. The 106 Go consumers stayed, but their job shrank — they now extract and prepare more than 100 fields per request and hand them to ClickHouse, with no aggregation logic left in Go at all. The Zone Analytics API was rewritten to query ClickHouse directly.

The Citus cluster — main node plus 11 workers, 12 nodes in total — was shut down. Query throughput moved from a 15 QPS ceiling to a 40 QPS average with capacity for 150 QPS, while ingestion held at the same 6 million requests a second on average, 8 million at peak.

The first ClickHouse schema didn’t survive contact with reality. It used eight materialized views on the ReplicatedAggregatingMergeTree engine, and it produced queries over 300 lines long — because ClickHouse only supports pairwise joins, the same columns had to be selected over and over. Cloudflare rebuilt around SummingMergeTree instead, which cut the number of tables needed, though it took two more fixes to get there: a custom sumMap function for final shard aggregation, and pulling unique-count fields out into a separate ReplicatedAggregatingMergeTree view because SummingMergeTree doesn’t support AggregateFunction columns.

Tuning mattered as much as the schema. Index granularity — how many rows ClickHouse indexes per block — stayed at 16,384 for the raw, non-aggregated table, which holds billions of rows. But for the much smaller aggregated tables, dropping granularity from 8,192 to 32 cut query latency by 50% and roughly tripled throughput. Once the design settled, Cloudflare moved history over: more than 60 billion rows transferred successfully in a couple of days.

What they got in exchange for that open question: no more single point of failure, a query ceiling that moved from 15 to 150, and — in their own words — fewer incidents than before, less on-call burden, and, as they put it, being able to “sleep peacefully at night.”

A database migration isn’t finished when the new system runs your old queries — it’s finished when you stop designing the new system to behave like the old one.