Optimizing DuckDB Against Its Official and Academic Benchmarks

What this is about

DuckDB is an in-process analytical database — it plays the role for OLAP workloads that SQLite plays for transactional ones, embedded directly in the host application with no server. This post describes making the current DuckDB development tree faster on five benchmark suites: the two official industry standards that ship with DuckDB’s own benchmark runner (TPC-H at scale factor 1, 22 queries, and TPC-DS at scale factor 1, 99 queries), the academic Join Order Benchmark over the IMDB dataset (113 queries), the h2oai db-benchmark group-by/join suite (15 queries), and ClickBench, a web-log analytics workload (42 queries measured).

The problem

DuckDB is one of the most heavily optimized analytical engines in existence. Its own team lands single-digit-percent wins per operator per release, and the fastest research engine (Umbra) beats it on ClickBench by only about 3–4× on identical hardware, using a fundamentally different architecture built on compiled queries. There is no low-hanging fruit; whatever is gained has to come from real engineering at the margins the DuckDB team has not yet reached, without touching a single benchmark file, query, dataset, or setting.

Profiling did expose one genuine hot spot: ClickBench q28, a REGEXP_REPLACE over roughly 100 million rows, spends 77% of its CPU inside the vendored RE2’s BitState backtracking engine, the path RE2 takes when capture groups are required and its one-pass engine does not apply. Everything else had to come from the build: how the binary is compiled, laid out, and trained.

Measurement itself turned out to be a second problem. Run-to-run drift of up to 30% was measured across sessions on the same machine, large enough to manufacture or hide any realistic gain. Every comparison therefore runs interleaved — baseline and candidate alternate within a single session — and every timed query must first pass DuckDB’s own answer verification, so a wrong result voids the timing.

Summary

Result: verified geometric-mean speedups of 1.152–1.237× per suite over the stock release build, with the largest single-query win at ~1.73× (ClickBench q28). Three of the five suites were held out from all profile training and still improve the most, so the gains generalize rather than memorize. Nothing is broken: the full fast unit-test suite (6,377 tests) shows zero failures attributable to the changes, and ~15,500 adversarial statements plus two randomized fuzz corpora produce byte-identical results against the stock engine.

All of the work (profiling, engine changes, the benchmark harness, adversarial testing, security hardening, and reviews) was carried out by KISS Sorcar, using claude-fable-5 for development, kimi-k3 for security hardening, and gpt-5.6-sol for independent read-only review.

Final measured results

Protocol: baseline and candidate binaries alternate within one session (A,B,A,B), 5 timed runs plus warmup per pass, 10 pooled samples per side, medians compared per query, geometric mean over each suite’s queries. Answers are verified on every run by DuckDB’s own benchmark_runner, and git diff of benchmark/ against upstream is empty — no benchmark file, query, dataset, or setting was modified. ClickBench q24 fails answer verification in the unmodified stock build (a pre-existing upstream nondeterminism with tied ORDER BY rows) and is excluded identically on both sides.

Suite	Queries	Stock (s)	Optimized (s)	Geomean speedup
TPC-H sf1 (official)	22	0.54	0.47	1.152×
TPC-DS sf1 (official)	99	3.14	2.68	1.184×
IMDB/JOB (academic; held out)	113	9.31	7.94	1.215×
h2oai group/join (held out)	15	1.89	1.51	1.234×
ClickBench (held out)	42	—	—	1.237×

Geometric-mean speedup of the optimized stack over the stock release build (interleaved A/B, answers verified, q24 excluded on both sides). Green bars are suites held out from all profile training; they improve the most, which is the opposite of what overfitting would produce.

The notable single-query win is ClickBench q28, the REGEXP_REPLACE query over ~100M rows: ~1.73× in the final stack, 1.79× from the source-level regexp work alone, re-verified at 1.76× after the post-review fix described below.

What was changed

The final stack is three commits on branch ks-opt-regex: source-level regexp changes (d565d7427), a reproducible build pipeline (9fcad94fb), and review fixes with a regression test (04c94c4c4). Each layer was kept only after winning its own interleaved A/B test.

1. BitState self-loop run acceleration (largest single-query gain)

In the vendored RE2 (third_party/re2/re2/bitstate.cc, +265 lines): when a byte-range instruction loops back to its own list head — the compiled shape of x*, x+, and character-class loops — the engine now consumes a maximal run of matching input bytes in bulk. One run-length-encoded backtrack job replaces the per-byte stack pushes, and visited bits are set word-wise.
Search order, greedy semantics, and submatches are provably identical to the sequential engine; the argument was independently re-derived line-by-line during review. Per-row byte steps on q28 dropped from 97 to 17.

2. regexp_replace fast paths

In src/function/scalar/string/regexp.cpp: a capture-free pre-scan, answered by RE2’s fast DFA, gates the expensive captures pass — rows that cannot match never enter BitState and are returned zero-copy.
Single replacements are assembled directly in the result string heap, and rewrite-string validation runs once per chunk (lazily, on the first non-NULL row) instead of once per row.

3. Build pipeline: clang-18, thin-LTO, PGO, BOLT

Shipped as scripts/ks-optimized-build/ (build_optimized.sh, bolt.sh): clang-18 with -march=native (typically ~1.1–1.15× over the gcc-13 portable build on its own), thin-LTO, and 3-stage profile-guided optimization.
The PGO profile trains only on TPC-H, TPC-DS, and micro benchmarks; IMDB/JOB, ClickBench, and h2oai are never seen during training. Own contribution of thin-LTO+PGO: tpch 1.159×, tpcds 1.126×, imdb 1.181×, h2oai 1.230×, clickbench 1.211×.
BOLT post-link layout (instrumentation mode, ext-tsp) adds tpch 1.043×, tpcds 1.032×, imdb 1.217×, h2oai 1.024×, clickbench 1.019× on top, kept only after a 10-run A/B showed no regression anywhere.

4. Measured and rejected

gcc -march=native alone: 0.91–1.16× with regressions on tpcds and imdb — rejected. gcc PGO+LTO: lost to the clang equivalent on every suite — rejected.
Benchmark-setting knobs (thread pinning, insertion-order tricks, and similar): rejected as cheating, unmeasured.
A row-based rewrite of the aggregate Combine step in GroupedAggregateHashTable: genuine per-query wins (TPC-DS q32 1.088×, q18 1.142×, h2oai q10 1.078×) but full-suite geomeans of only 1.006×/1.011×, below the pre-registered 1.05× keep bar. Rejected per protocol; the fully tested patch is preserved at patches/s2-row-based-aggregate-combine.patch as an upstreaming candidate (it additionally passed 253,721 aggregate-test and 19,440 join-test assertions).

How the work was verified

Full fast unit-test suite (6,377 tests) on the final optimized build: 6,356 passed, 440 skipped, 21 failed — and every failure was triaged. 19 were missing loadable-demo-extension artifacts in the minimal build directory (all 19 pass after building all targets); the remaining 2 (http_logging, memory_limit_batch_load) fail identically on the stock build in this sandbox. Zero failures attributable to the optimizations.
Regexp tests: all 13 test files pass (713 assertions), including a new regression test for the review-found NULL-validation issue.
Adversarial differential testing against the stock engine: ~15,500 statements from the hardening pass (pathological ReDoS patterns, embedded NULs, empty strings, 100 MB and >2 GB strings, full UTF-8 ranges, 32-thread stress) plus a 43,162-row development-time fuzz corpus and a 6,000-statement randomized corpus. All results byte-identical, no crashes, no hangs, and the candidate used equal-or-less memory.
Security hardening (kimi-k3): static memory-safety/overflow/thread-safety analysis of the RE2 change plus the adversarial battery above — clean bill of health, no fix needed.
Independent read-only review (gpt-5.6-sol): it independently re-derived the BitState correctness argument (pass), verified benchmark honesty (pass: no benchmark diffs, verification active, symmetric q24 exclusion, genuine holdouts), and caught 5 real issues — most importantly a NULL-handling behavior change from the hoisted rewrite validation, fixed with a regression test in 04c94c4c4, plus stale-PGO-profile reuse in the build script, silent training failures, harness failure-set robustness, and a q28 claim precision error. All fixed and re-verified; q28 re-measured at 1.76× after the fix.
No diagnostic code left behind: all temporary perf counters and timing prints added during the experiments were removed, and their absence was re-verified by two independent audits.

Reproducing the results

The optimized build pipeline is committed on the branch as scripts/ks-optimized-build/, and the interleaved measurement harness lives alongside the clone. The branch is not yet published, so the steps below describe the protocol as it runs locally: start from the same upstream commit, apply the three commits of ks-opt-regex, build, and compare against a stock release build with the interleaved harness.

git clone https://github.com/duckdb/duckdb && cd duckdb
git checkout e500d7786

# apply the three commits of branch ks-opt-regex:
#   d565d7427  regexp/RE2 source optimizations
#   9fcad94fb  scripts/ks-optimized-build/ (reproducible build pipeline)
#   04c94c4c4  review fixes + NULL-validation regression test

scripts/ks-optimized-build/build_optimized.sh   # clang-18 -march=native,
                                                # thin-LTO, 3-stage PGO
scripts/ks-optimized-build/bolt.sh              # BOLT: instrument, train,
                                                # re-layout (ext-tsp)

# interleaved A/B: stock and optimized runners alternate in one session,
# 5 timed runs + warmup per pass, answers verified on every run
harness/ab.sh <stock_runner> <optimized_runner> <suite>

Raw per-query result CSVs from every A/B session are kept next to the harness. PGO and BOLT both retrain during the build, so exact ratios vary a few percent with the machine and the profile; the interleaved protocol exists precisely because single-session absolute times drifted by up to 30% on this hardware. All measurements so far are from one machine class: a 32-core x86-64 VM with 125 GB RAM on Ubuntu 24.04.

DuckDB optimization by KISS Sorcar: benchmarked, adversarially tested, and independently reviewed (claude-fable-5 development, kimi-k3 security hardening, gpt-5.6-sol read-only review). The work lives on branch ks-opt-regex of a local upstream clone at e500d7786 and is not yet published. Machine: 32-core x86-64 VM, 125 GB RAM, Ubuntu 24.04; all numbers from interleaved A/B sessions, medians of 10 pooled samples per side, answers verified on every run. Last updated August 2026.