Making a dashboard query scan 230× less data

A ClickHouse tuning demo you can run yourself: the same 50-million-row events table in a naive schema versus a tuned one, measured with the database's own query_log. A filtered dashboard query drops from scanning 1.9 GiB to about 8 MiB — roughly 230× less data, 16× faster — and a daily rollup gets 54× faster with a projection. One command rebuilds it from empty; a CI job re-runs it on every push and fails the build if the tuning ever regresses.

The problem

An analytical table grows to tens or hundreds of millions of rows, and the dashboards on top of it slow to a crawl. Each query reads far more data than it needs, latency climbs, and — on a metered warehouse — the bill climbs with it. The usual response is "add more hardware," which pays to scan the same wasted bytes faster instead of scanning fewer of them.

The constraint: prove the win with numbers, not vibes

Tuning advice is easy to assert and hard to trust. So the whole thing is built to be measured and reproduced: identical data and identical queries on both schemas, the win read straight from query_log (bytes read, rows read, duration), and a single command that rebuilds the benchmark from an empty database so anyone can check the result themselves.

What moves the numbers

• A sorting key that matches how you query — the right ORDER BY plus partitioning lets ClickHouse skip whole granules of data instead of reading them, which is where the 230× reduction in bytes scanned comes from.
• Projections for the heavy rollups — a projection precomputes a daily aggregate so the recurring report reads a tiny materialized result instead of re-scanning the base table, for the 54× speedup.
• Right-sized types — LowCardinality and tighter column types shrink what has to be read and held in memory in the first place.
• A regression gate — the benchmark runs in CI on every push and fails the build if a schema change quietly makes a query scan more again, so a win stays a win.

Why this is my kind of problem

Data-platform reliability is my center: most of six years of backend was building and operating ETL and storage under load — a 7-microservice cross-database migration, throughput taken from 50 to 5,000 QPS, a binlog-level recovery that restored 100% of lost data with zero downtime. The instinct is the same here: don't argue about performance, instrument it, prove the win with the database's own numbers, and put a gate around it so it can't silently rot.

Proof

Where else this applies

Any analytical or OLAP workload where queries scan more than they should: product analytics, event pipelines, reporting warehouses, dashboards that got slow as the data grew. If a query feels slow and nobody can say exactly why, the first move is to measure what it actually reads — and then make it read far less.

When this approach doesn't apply

• If the slowness is network, concurrency queueing, or client-side deserialization, physical re-layout doesn't help. Measure first: if read_bytes looks fine, it isn't this problem.
• If the filters differ on every query (genuinely ad-hoc analysis), one layout can't serve them all, and the win shrinks sharply.
• If your bottleneck is writes rather than reads, that's a different problem with different tools.

The one-line version, if you're passing this along

"He used the database's own log to show scan volume dropping 230×, then wrote a CI gate so it can't quietly come back."

← All case studies