Why your CPU hits 100% but barely works
Original: It's the memory latency, stupid
Why This Matters
Cache-aware query design is central to analytical database performance at scale.
Pivot blog post explains how memory latency, not compute, bottlenecks GROUP BY queries. At 50-100ns per RAM access, a CPU can run 400-800 instructions while waiting for one memory read.
A post from Pivot's engineering blog makes a simple but often overlooked point: a CPU at 100% utilization is not the same as a CPU doing 100% useful work. The culprit is memory latency. While modern CPUs execute 4-8 instructions per nanosecond, a single RAM access takes 50-100ns — enough time for the processor to have run up to 800 instructions if it had data to work with.
The post uses GROUP BY aggregations as a practical case study. The naive implementation — a growing hash table keyed on group values — sounds simple enough. But with millions of unique groups (the ClickBench 'hits' dataset has ~17 million unique UserIDs across 99 million rows), nearly every hash table lookup is a cache miss that goes all the way to RAM. The CPU stalls, waiting. Linux still reports 100% CPU usage, because no task is idle — but the silicon is spinning its wheels.
CPU caches exist precisely to paper over this gap: L1 at ~1ns and 64KB, L2 at ~3-4ns and 1MB, L3 at ~10-15ns and a few hundred MB. Good database engines try to keep working sets small enough to stay in cache. The post sets up a deliberately stripped-down version of Pivot's engine to demonstrate how badly performance degrades when that discipline is abandoned — a useful baseline before showing what the optimized version actually does differently.