TL;DR
Get the latest gadgets delivered free with Prime
- Fast, free delivery on millions of items
- Prime Video, Amazon Music and more included
- Member-only deals all year
A Pivotlake engineering blog post reports that a deliberately simplified, single-core GROUP BY query over ClickBench’s 99-million-row hits dataset took 3.6 seconds on a repeat run. Its performance counters attributed 50.1% of CPU slots to memory stalls and 16.1% to retiring operations, illustrating how data access can constrain database performance even when CPU usage appears high.
Pivotlake reported that a deliberately simplified, single-core database query spent 50.1% of measured CPU slots stalled on memory, while only 16.1% were retiring operations, in a test over ClickBench’s 99-million-row hits dataset. The result illustrates why a database process can appear CPU-bound while much of its time is spent waiting for data rather than executing instructions.
The test ran a GROUP BY UserID count query against ClickBench’s hits dataset, which the post says contains 99 million rows and about 17 million distinct UserID values. Pivotlake replaced its usual aggregation logic with a simple hash-table approach: for each row, it looked up the user key and incremented a count. The authors describe this as an intentionally stripped-down version of their engine, not a comparison of production database systems.
The benchmark ran on a bare-metal server with an AMD EPYC 9124 processor, 16 cores and 128 GB of DDR5 memory. The test was confined to one core because the simplified grouping implementation could not run across multiple cores. The first query took 3.9 seconds; a second run, with the data already in the operating system’s page cache, took 3.6 seconds. The performance-counter measurements shown in the post report about 4.03 seconds elapsed, so the article gives slightly different timings for the repeat run and the counter run.
Using AMD performance counters through Linux’s perf tooling, the authors measured 50.1% backend-bound memory stalls and 16.1% retiring, a measure of slots in which the processor completed operations. These readings describe the tested workload and setup. They do not establish that every database query with high CPU utilization is primarily limited by memory.
Why Memory Waits Slow Queries
The result highlights a distinction that matters when diagnosing database performance: CPU utilization is not the same as useful computation. A processor can be busy from the operating system’s point of view while its execution units are frequently waiting for data to arrive from memory. Looking only at a CPU percentage may leave engineers with an incomplete account of why a query is slow.
In this test, the simplified aggregation maintained a growing hash table with millions of keys. As the working data grows beyond what can fit in the processor’s small, fast caches, lookups are more likely to require access to slower levels of the memory hierarchy. The post argues that this pattern can cause repeated waiting and that data layout and cache use are relevant to database performance. The benchmark is a concrete illustration, not a general measurement of the speed of Pivotlake or a claim that memory is the limiting factor in every database workload.
For readers operating analytical databases, the practical point is to diagnose the bottleneck before adding cores or changing hardware. Memory-stall counters, query shape and the size and access pattern of the working set can help distinguish waiting on data from executing instructions. The report does not quantify how much a redesigned aggregation would improve this query.
Products Worth Considering
Apple MacBook Air 15-inch (2026) with M5 chip, 16GB memory, 512GB SSD, Silver
Samsung Galaxy Tab S10 FE 128GB Wi-Fi Android Tablet, Blue
Apple MacBook Air 13-inch with M5 chip, 16GB memory, 1TB SSD, and 12MP Center Stage camera (Midnight)
As an affiliate, we earn on qualifying purchases.
The Test Behind the Memory Claim
Modern processors use several cache levels to keep frequently needed data close to their cores. The Pivotlake post gives approximate access times of about 1 nanosecond for L1 cache, 3–4 nanoseconds for L2, 10–15 nanoseconds for L3, and 50–100 nanoseconds for DRAM. These are figures presented by the authors as broad estimates; actual latency depends on the processor, memory configuration and access pattern.
The report connects those differences to GROUP BY aggregation. A compact workload whose frequently accessed data fits in cache can make more effective use of the processor than one that repeatedly accesses scattered data. In the example, the simple hash table had a distinct entry for each of roughly 17 million user IDs. The authors’ test therefore focuses on an aggregation pattern with many unique groups, rather than on all forms of grouping or query execution.
The reported repeat run was intended to reduce disk effects: the authors say the data was already in the page cache. They pinned the database process to a CPU and used hardware counters, and explain that they chose bare metal because the cloud machines they considered did not expose the counters they wanted. That setup gives a view of one workload on one machine; it is not a broad survey or an independently reproduced result.
“50.1% backend_bound_memory”
— Pivotlake report
Limits of the Single-Core Result
The report does not provide a comparison against Pivotlake’s full aggregation implementation, another database, or an alternative data structure. It therefore does not show how much the observed stall rate or query time could be reduced by changing the code. The test is also limited to a single core and one server configuration, and the post gives no results across other hardware or workloads.
The figures should be read as the authors’ measurements, not as independently verified findings. The supplied report does not state a publication date, include a full set of repeatability results, or provide enough detail here to assess run-to-run variability. It also does not include the further performance-counter discussion that its text begins to introduce after the reported results.
Finally, the measured percentages apply to the specific pinned CPU and query run described. They do not tell readers how much of the entire database service’s total runtime was spent waiting on memory, nor do they establish that high CPU usage generally signals a memory bottleneck.
Further Benchmarks Needed
The immediate next step for readers is to treat the benchmark as a diagnostic example and compare it with measurements from their own queries. Reproducing the test would require matching the dataset, query, hardware and counter definitions; a single timing or counter snapshot is not enough to establish performance across systems.
To support broader conclusions, further reporting would need to show repeated runs and comparisons with other aggregation implementations, along with results across processor and memory configurations. The supplied material does not announce a follow-up test or a schedule for one. Whether Pivotlake will publish additional measurements remains unclear.
Key Questions
What did Pivotlake test?
It ran a simplified COUNT grouped by UserID query on ClickBench’s hits dataset, which the report describes as containing 99 million rows. The deliberately simple hash-table implementation produced about 17 million distinct groups.
What does the 50.1% figure mean?
It is the performance-counter reading the post labels backend_bound_memory for the measured CPU slots. In the authors’ interpretation, that share reflects time when the processor was stalled waiting on memory. It is not a claim that 50.1% of all database queries are memory-bound.
Did the query take 3.6 or 4.03 seconds?
The report says the repeat query took 3.6 seconds and separately shows a performance-counter run with 4.03 seconds elapsed. It does not explain the difference, so these should be treated as timings from separate reported runs rather than a single consistent measurement.
Does this prove adding CPU cores will not help?
No. The test was confined to one core because this simplified GROUP BY could not run across multiple cores. It shows memory stalls in that particular single-core workload, but it does not compare scaling across cores or establish what hardware change would improve other queries.
Source: pivotlake.io
