Making a dashboard query scan 230× less data

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 to add 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. That’s 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 small materialized result instead of re-scanning the base table. That gives the 54× speedup.
• Right-sized types. LowCardinality and tighter column types reduce how much has to be read and held in memory in the first place.
• A regression check. The benchmark runs in CI on every push and fails the build if a schema change quietly makes a query scan more again, so an improvement stays an improvement.

Why this is my kind of problem

Data-platform reliability is my core strength. Most of my backend work since 2019 was building and running ETL and storage under load: a cross-database migration of seven microservices, throughput taken from 50 to 5,000 QPS, and a binlog-level recovery that restored 100% of lost data with no downtime. The approach here is the same: don't argue about performance, measure it, prove the improvement with the database's own numbers, and put a check around it so it can't quietly degrade.

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 slowed down as the data grew. If a query feels slow and nobody can say exactly why, the first step is to measure what it actually reads, then make it read much 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."