Making DuckDB Faster: An Honest Optimization Campaign

Repository: ~/duckdb-ks/duckdb (upstream clone @ e500d7786, work on branch ks-opt-regex, final commit 04c94c4c4) · Machine: 32-core x86-64 VM, 125 GB RAM, Ubuntu 24.04 · August 2026

1. Goal and the honesty contract

The task: optimize DuckDB itself against its official and standard academic benchmark suites, with a stated target of 5× per suite. Every measurement in this report obeys a strict no-cheating contract:

• No benchmark file, query, dataset, or setting was modified — verified by git diff e500d7786..HEAD -- benchmark/ being empty.
• Answers are always verified. DuckDB's own benchmark_runner checks each query result against the reference answer; a wrong answer voids the timing.
• Interleaved A/B protocol. Baseline and candidate run alternately in the same session (A,B,A,B; 5 timed runs + warmup per pass; 10 pooled samples per side; medians compared; geometric mean over queries). This defeats machine drift, which we measured at up to 30% across sessions.
• Held-out validation. Profile-guided passes train only on TPC-H, TPC-DS and micro benchmarks; IMDB/JOB, ClickBench and h2oai are never seen during training.
• All optimizations are general-purpose: no pattern special-casing, no result caching, no benchmark-mode detection. Two independent model-driven audits (a hardening pass and a read-only review) confirmed this.

2. Benchmarks used

ClickBench q24 fails the runner's answer verification in the unmodified stock build (a pre-existing upstream nondeterminism with tied ORDER BY rows); it is excluded identically on both sides of every comparison.

3. Final result

Notable single-query win: ClickBench q28 (a REGEXP_REPLACE-dominated query over ~100M rows) improves ~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).

4. What the optimized stack consists of

4.1 Source-level: regexp engine (commit d565d7427 + 04c94c4c4)

Profiling showed ClickBench q28 spends 77% of its CPU in the vendored RE2's BitState backtracking engine (used when capture groups are required and the one-pass engine does not apply). Two general-purpose changes:

• BitState self-loop run acceleration (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+/character-class loops), a maximal run of matching input bytes is consumed in bulk — one run-length-encoded backtrack job replaces per-byte pushes, and visited bits are set word-wise. Search order, greedy semantics and submatches are provably identical to the sequential engine (independently re-derived line-by-line during review). Per-row byte steps on q28 dropped 97 → 17.
• regexp_replace fast paths (src/function/scalar/string/regexp.cpp): a capture-free pre-scan (answered by RE2's fast DFA) gates the expensive captures pass, so non-matching rows never enter BitState and are returned zero-copy; single replacements are assembled directly in the result string heap; rewrite-string validation is done once (lazily, on the first non-NULL row) rather than per row.

4.2 Build-level

gcc PGO+LTO was also built and measured — it lost to clang on every suite and was discarded. jemalloc was already DuckDB's allocator (nothing to change).

5. What was tried and honestly rejected

6. Functionality: nothing is broken

• Full fast unittest 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 (network/memory environment), i.e. zero failures attributable to the optimizations.
• Regexp tests: all 13 files pass (713 assertions), including a new regression test added for the review-found NULL-validation issue.
• Adversarial differential testing against the stock engine: ~15,500 statements from a hardening pass (pathological ReDoS patterns, embedded NULs, empty strings, 100 MB and >2 GB strings, full UTF-8 ranges, 32-thread stress, randomized fuzz) plus 43,162 rows from the development-time fuzz and a 6,000-statement randomized corpus — all byte-identical, no crashes, no hangs, candidate used equal-or-less memory.
• The S2 experiment additionally ran 253,721 aggregate-test and 19,440 join-test assertions (its change was ultimately not kept).
• All temporary diagnostic code (perf counters, timing prints) added during the experiments was removed and its absence re-verified by two independent audits.

7. Process and audits

• claude-fable-5 performed all development (research synthesis, source experiments S1/S2, fixes) via isolated sub-tasks.
• openrouter/moonshotai/kimi-k3 ran the robustness/security hardening pass: static memory-safety/overflow/thread-safety analysis of the RE2 change plus the 15,500-statement adversarial battery — clean bill of health, no fix needed.
• gpt-5.6-sol ran a strictly read-only review (a small fraction of the 20% budget cap): 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 hoisted rewrite validation (fixed with a regression test in 04c94c4c4), plus build-script reproducibility (stale PGO profile reuse), silent training failures, harness failure-set robustness, and a q28 claim precision error (1.80× → 1.73× final / 1.79× source-only). All fixed and re-verified.

8. The 5× goal, honestly

9. Deliverables

• ~/duckdb-ks/duckdb, branch ks-opt-regex (3 commits on top of upstream):
  • d565d7427 — regexp/RE2 source optimizations,
  • 9fcad94fb — scripts/ks-optimized-build/ (reproducible PGO/BOLT pipeline + README),
  • 04c94c4c4 — review fixes + NULL-validation regression test.
• ~/duckdb-ks/harness/ — benchmark harness (bench.py, ab.sh, run_all.sh), raw results CSVs, fuzz harnesses.
• ~/duckdb-ks/patches/s2-row-based-aggregate-combine.patch — the verified but not-kept aggregation optimization.
• Optimized binaries: build/clangpgo2/… and the BOLT-ed runner (rebuildable from the scripts).