Imagine you're a librarian in a vast warehouse. Every time a patron asks for a book, you walk to a random shelf — sometimes nearby, sometimes a quarter-mile away. You're always busy walking, never idle, but you're barely reading anything. That's what a CPU does when it chases pointers through a giant hash table that doesn't fit in cache: it's at 100% utilization, doing almost nothing useful. This post from the Pivot database team makes a deceptively simple claim: the dominant bottleneck in GROUP BY aggregation isn't computation — it's memory latency. A modern CPU can execute 4-8 instructions per nanosecond, but a single DRAM access costs 50-100ns. That's 400-800 instructions of dead time per cache miss. When your hash table has 17 million entries scattered across RAM, nearly every lookup is a miss. The CPU shows 100% busy, but perf counters reveal 50.1% of execution slots are stalled waiting for memory, and only 16.1% are retiring actual operations. The fix is radix partitioning — a well-known technique in database internals. Instead of one massive hash table, you hash each key and use the top 12 bits to route it into one of 4,096 partitions. Phase one scatters; phase two aggregates each partition independently. Each partition's hash table is small enough to fit in L2 or L3 cache, so lookups hit warm memory instead of cold DRAM. You do more total work (an extra scatter pass), but the work that matters — hash table probes — runs 50-100x faster per access. The results on a 99-million-row ClickBench dataset (17M unique UserIDs, AMD EPYC 9124, single core, DDR5) are clean. The naive single-hash-table approach runs in 3.6 seconds on a warm page cache. The radix-partitioned version runs in 2.7 seconds — a 25% wall-clock improvement. But the perf counter story is more dramatic: memory stall percentage drops from 50.1% and DRAM fills drop from 76 million cache-line loads (4.5GB pulled from DRAM). The post was cut off before revealing the partitioned perf numbers, but the architectural logic is sound and well-demonstrated. The engineering methodology here is notably rigorous for a blog post. The authors pin processes to specific cores with taskset, use AMD's hardware performance counters (exnoretire.loadnotcomplete, lsdmndfillsfromsys.dramionear), run on bare metal to access counters that cloud VMs hide, and separate cold vs warm runs. They also explain why they use SlabVec over standard Vec for memory variance control. This isn't a toy benchmark — it's production-grade measurement on a real dataset. The radix partitioning technique itself isn't novel — it's been standard in analytical database engines since at least the 2010s (MonetDB, Vectorwise/Actian, and academic work by Balkesen et al.). What's valuable here is the pedagogical clarity: showing the exact perf counter numbers that prove the memory bottleneck, then showing the fix. The implicit claim is that Pivot's production engine uses more sophisticated versions of these techniques, and this post strips them back to show why they matter. The broader lesson generalizes beyond databases: any algorithm that does random access over a working set larger than L3 cache is leaving enormous performance on the table. The 400-800 instruction penalty per DRAM access means that restructuring data access patterns to fit in cache often matters more than reducing instruction count. This is the core insight behind cache-oblivious algorithms, data-oriented design in games, and columnar storage formats. If you write performance-sensitive code and haven't profiled your memory stall rate, you're optimizing blind.
