# Optimizing Database Performance Through CPU Cache Utilization Reducing memory latency by transforming naive aggregation algorithms into cache-friendly partitioned processes. - **Source:** [Hacker News (news.ycombinator.com)](https://brifing.si/source/13c923cd-6d99-442b-9b8b-1181f4d8cd91) - **Published:** 2026-10-31 - **Topic:** [Software & Engineering](https://brifing.si/c/engineering) - **Original article:** - **On Brifing:** https://brifing.si/s/706159a0-74a7-4267-9f8d-eb4f5aa585f5 ## The CPU Utilization Myth High CPU usage percentages often mask underlying inefficiencies rather than reflecting pure computational throughput. A processor showing 100% utilization may actually be stalling while waiting for data from slower memory tiers. Understanding that CPUs operate significantly faster than RAM is the first step toward building truly high-performance database engines. ## Memory Latency Bottlenecks Modern CPUs process instructions at nanosecond speeds, while accessing RAM takes 50 to 100 nanoseconds. This massive disparity means a CPU can execute hundreds of instructions during a single memory fetch. Consequently, the primary performance constraint in modern computing is often memory latency rather than raw clock speed or instruction count. ## The Role of Caches CPU caches like L1 are integrated directly into the chip to provide near-instant data access, operating up to 100 times faster than DRAM. However, these caches are extremely limited in size, typically ranging from 64KB for L1 to a few hundred megabytes for higher levels. Effective engineering requires fitting data into these small windows. ## Naive Aggregation Failures Standard GROUP BY operations using a single large HashTable frequently result in cache misses. When a dataset contains millions of unique keys, the HashTable grows too large for the CPU cache. This forces the system to perform 'cold' hits to RAM for nearly every access, stalling the CPU despite high reported usage. ## Experimental Setup Details Testing was conducted on a bare metal server featuring an AMD EPYC 9124 Genoa processor with 16 cores and 128GB of DDR5 RAM. By pinning processes to a single core using taskset, researchers could isolate performance counters and accurately measure memory traffic passing from the DRAM controller to the CPU during query execution. ## Measuring Memory Stalls Initial benchmarks on a naive GROUP BY query revealed that 50.1% of CPU slots were stalled due to memory latency. Only 16.1% of the time was spent retiring actual operations. This confirmed that the database was effectively waiting for data to arrive from DRAM rather than performing meaningful computational work on the dataset. ## Implementing Radix Partitioning To improve cache locality, the team implemented radix partitioning by splitting keys into 4096 distinct partitions. By hashing keys and using the top 12 bits to assign them, the algorithm ensures that each resulting HashTable is small enough to fit comfortably within the CPU cache, drastically reducing the frequency of RAM lookups. ## Performance Gains Observed After implementing the partitioning strategy, the query execution time dropped from 3.6 seconds to 2.7 seconds on the second run. More importantly, memory stalls decreased from 50% to 23.5%, while the percentage of time spent retiring operations increased to 32.2%, proving that more instructions can be executed when memory latency is minimized. ## DRAM Traffic Reduction The optimized approach significantly reduced the total data pulled from DRAM during the query. The naive version required 4.5GB of data transfers, whereas the partitioned version required only 1.5GB. This reduction in memory traffic directly translates to faster execution times and more efficient use of the available hardware resources during heavy aggregation. ## Further Optimization Potential While cache-aware partitioning provides substantial gains, it is not the final limit for performance. Additional techniques, such as prefetching data into the cache before it is requested and maximizing memory throughput through specialized algorithms, offer further avenues for improvement. Future work will explore these advanced strategies, particularly regarding complex JOIN probing operations. ## Engineering for Hardware Reality True performance engineering requires aligning software algorithms with the physical constraints of modern hardware. By prioritizing cache locality over simple code logic, developers can unlock significant speed improvements. As demonstrated, even if an algorithm performs more total operations, reducing DRAM latency remains the most effective strategy for accelerating database query performance. ## Sources covering this story - https://pivotlake.io/blog/its-the-memory-latency-stupid/ — --- _Summary written by Brifing from the original reporting. Copyright in the original article belongs to its publisher; read the full piece at the link above._