What I Learned Writing GPU Kernels for SQL Aggregates

The first design that didn't work

The first design I shipped was a single radix-partitioned hash table per query. Read column, hash each value, atomically update the slot. On NVIDIA CUDA this is the textbook approach — fast atomics, deep VRAM, and you're done in 50 lines. On Apple Silicon Metal it ran fine on small workloads and fell off a cliff at SF10 GROUP BY l_orderkey. v0.1.2 was 1.78× slower than DuckDB 16-thread on the canonical TPC-H workload. The number every GPU-database submission gets attacked on. The number that, if you can't beat it, you don't have a product.

The reason was specific and unobvious: Apple Silicon GPUs in the M3-and-earlier era didn't expose atomic_fetch_add on 64-bit integers in Metal Shading Language. They did 32-bit. They did 64-bit load and store. They did 64-bit compare-and-swap. They did not have a single-instruction add-to-a-64-bit-value-in-shared-memory. Which meant every SUM update on a 64-bit column had to be a CAS loop, and CAS loops contend, and contention crushes throughput.

The second design that didn't work

The next idea was to push everything to 32-bit, sacrifice precision, and hope the workloads tolerated it. They didn't. TPC-H SF10 has integer columns that overflow 32 bits well before you've summed the table. So that path closed.

What I tried next was a more aggressive radix partitioning — 4K partitions instead of 256, hoping that the per-partition contention would drop low enough that even CAS-loop updates would saturate. It worked at moderate cardinalities and saturated around 3M unique groups, because per-partition Poisson tails would clip the 1024-slot threadgroup table. SF10 has 15M unique l_orderkey values. The math wasn't going to close.

The slot-lock kernel

The breakthrough was conceptually small but architecturally specific. I doubled the partition count twice — to 32,768 — and built a per-partition slot-lock structure. Each partition has 1024 slots in threadgroup memory. Each slot is a (key, sum, count) triple. Updates use a 32-bit CAS on the slot's lock word to gate the non-atomic 64-bit add to the sum field. With 32,768 partitions × 1024 slots = 33.5M total slots, the load factor for SF10's 15M unique groups is 0.45 mean, ~543 worst-case per partition (Poisson tail). Comfortable headroom.

The kernel itself is ~120 lines of Metal Shading Language. The clever part is what's not in it: there's no global hash table, no inter-threadgroup synchronization during the inner loop, and no spills out of threadgroup memory. Each threadgroup processes its partition end-to-end and emits a per-partition result table at the end. A second tiny merge kernel collapses the per-partition tables into the final answer. The 32-bit CAS on the slot lock is the only contention point, and at 0.45 load factor it almost never collides.

The dispatcher decision

One kernel doesn't fit all workloads. Slot-lock is great for medium-cardinality (1M-30M unique groups). At very low cardinality (~50 unique groups) it lock-contends because all 32K partitions hash to the same 50 slots. At very high cardinality (>50M unique groups) it overflows the per-partition slot table.

So the dispatcher picks at runtime. The decision is based on a quick cardinality estimate from the column statistics:

• < 1K unique: route to a small-table kernel (fits in L1, single-pass radix).
• 1K-30M unique: route to slot-lock (the sweet spot).
• > 30M unique: route to radix-opt (the older partition-then-sort path; slower but bounded).

The dispatcher is one of the most important pieces of code in the engine and one of the least-discussed in the literature. Most published GPU-database papers benchmark a single kernel against CPU and call it a day. In production you need three kernels and a chooser, because workload-shape variance is the rule, not the exception.

What I'd do differently

Three things, looking back. First, I should have read the Apple Silicon GPU ISA documentation in week one rather than week eight. The 64-bit atomic gap was the single most important constraint, and I designed two kernels around the assumption that it would be there before discovering it wasn't. Second, I should have started with the slot-lock approach, not the textbook hash-table approach — slot-lock generalizes better to a CAS-only architecture. Third, I should have built the dispatcher before the second kernel, not after. Building one kernel makes you optimize for one workload shape; building a dispatcher forces you to think about the workload distribution from day one.

The takeaway, if there is one, is that GPU SQL kernels are an engineering problem, not a research problem. The textbook designs work on the textbook hardware. They don't survive contact with a real ISA, a real workload distribution, and a real performance bar that says "you can't be slower on the canonical TPC-H query." The work is in the constants — the slot count, the partition count, the load factor, the dispatcher thresholds. None of that shows up in a paper. All of it shows up in the benchmark.