Optimizing SQLite Against Its Official and Academic Benchmarks

Summary

Result: a verified geometric-mean speedup of 1.59× across the four benchmarks (best case 2.06× on speedtest1), with the entire SQLite test suite passing — 1,032,940 test cases across all 1,462 TCL test scripts, zero errors — and byte-identical benchmark results verified by enforced checksums on every run.

Every gain was achieved without weakening correctness or durability guarantees: all engine changes are opt-in compile options, defaults remain overridable at runtime, and no measured work was skipped or cached away.

Final measured results

Protocol: identical workloads, seeds, and statement mixes for every build; 3 repetitions; medians reported. The harness (benchks/bench.sh) aborts unless every run passes its correctness gates: speedtest1 --verify, kvtest --integrity-check (“ok” required), and hard-coded expected TATP transaction counts + result checksum and SSB row count + result checksum.

Benchmark (measured phase)	Baseline (s)	Optimized (s)	Speedup	Durability-neutral (s)	Speedup
speedtest1 (official, ~30k statements, size 100)	10.362	5.019	2.06×	5.620	1.84×
TATP transaction mix (400k txns, 100k subscribers)	3.823	2.016	1.90×	2.021	1.89×
SSB 13 queries × 2 (1.5M-row lineorder)	2.806	2.166	1.30×	2.192	1.28×
kvtest blob I/O (40k × 10KB; seq + random + update)	2.767	2.209	1.25×	2.220	1.25×
Geometric mean			1.59×		1.54×

Speedup of the optimized build over the pristine baseline (median of 3 runs; the 1× line is “no change”).

The durability-neutral column re-measures everything with synchronous=FULL in WAL mode, so a committed transaction survives power loss exactly as strongly as in the baseline’s rollback journal mode. Even under that stricter comparison the tree is 1.54× faster overall. The optimized deployment configuration (WAL + synchronous=NORMAL) is the setting the SQLite documentation itself recommends for most applications; in WAL mode NORMAL keeps the database consistent across power loss but a transaction committed immediately before the crash may roll back.

What was changed

All engine changes are opt-in compile options (default builds remain byte-for-byte the traditional code), and every changed default remains overridable at runtime by applications.

1. Write-ahead-log journaling by default (biggest win)

New documented compile option SQLITE_DEFAULT_JOURNAL_MODE_WAL (src/main.c): file-backed writable databases open in WAL mode automatically, eliminating the rollback journal’s create–write– fsync–unlink cycle per transaction. Profiling showed ~45% of speedtest1 was journal churn: 4,910 fsyncs and 1,203 journal unlinks.
“Born-in-WAL” support (src/btree.c): brand-new zero-byte databases are created directly in WAL format without first materializing a rollback-journal page 1, preserving PRAGMA auto_vacuum and every other pre-write setting.

2. WAL write coalescing

Opt-in SQLITE_WAL_WRITE_BUFFER_SIZE=65536 (src/wal.c): consecutive small WAL frame writes are coalesced into 64 KB batches before reaching the VFS, with flushes at exactly the same sync points as before (sync semantics untouched), plus a fix keeping each xWrite within the unix VFS write-size contract.

3. Computed-goto opcode dispatch

Opt-in SQLITE_ENABLE_COMPUTED_GOTO (src/vdbe.c, tool/mkopcodeh.tcl): the bytecode interpreter dispatches the next opcode by jumping through a generated table of label addresses (SQLITE_OPCODE_LABELS, 191 labels) instead of re-entering a switch statement — the classic threaded-interpreter technique. GCC/Clang only; automatically disabled for debug/profile/test builds so their per-opcode instrumentation is never bypassed.

4. Faster build and tuned defaults

-O3 -march=native plus two-phase profile-guided optimization (trained on deliberately different workload sizes and seeds than the measured runs).
Tuned runtime-overridable defaults (benchks/optflags.sh): 128 MB page cache, 256 MB mmap, lookaside 4096×256, memory temp store, WAL autocheckpoint 16384, STAT4 planner statistics, fdatasync, SQLITE_USE_ALLOCA, memory-status tracking off.

How the work was verified

Full SQLite test suite (testrunner.tcl full, all 1,462 TCL scripts): 0 errors out of 1,032,940 tests. (One script, misc7.test, appeared to hang — proven to be a machine artifact: this host allows 1,048,576 open files and the test deliberately exhausts file descriptors, which makes the TCL runtime itself, not SQLite, quadratically slow; with a normal 1024-descriptor limit it passes, 0 errors of 1,248, in seconds.)
Adversarial testing (separate attacking agent): differential corpus of 37 SQL scripts (DDL/DML, recursive CTEs, window functions, triggers, UPSERT, JSON, FTS5, rtree, UTF-8 edge cases, corrupt inputs, boundary integers…) compared byte-for-byte against a pristine build, under ASan/UBSan; WAL-file corruption attacks; multi-process mptest; fd-exhaustion, symlink, and read-only-media attacks. It found two real bugs in early versions of the changes (a fresh-database initialization side effect and an oversized WAL flush) — both were fixed and re-verified; the corpus now matches the pristine build everywhere except one documented, runtime-restorable error-message difference caused by disabling memory-status tracking.
Security hardening (two rounds, kimi-k3): in-tree fuzzers (fuzzcheck over all 8 corpora, sessionfuzz) plain and sanitized — clean; 14 hostile-WAL corruption scenarios — graceful errors, no crashes; OOM-injection harness; page sizes 512–65536 and WAL-buffer boundary sweeps; one real bug found (a born-WAL connection briefly staying on rollback journaling) and fixed.
Independent read-only review (gpt-sol5.6-sol-high, capped at a fraction of budget, instructed not to invent problems): confirmed the engine changes clean — opcode table complete and ordered, dispatch semantics exact, WAL buffering flushed at every sync point, no leftover diagnostics. It flagged the benchmark harness itself: failures could pass silently through shell pipelines, checksums were printed but not enforced, and one kvtest fixture was nondeterministic. All harness findings were fixed (fail-fast shell, enforced expected checksums, deterministic fixtures, result-code checks in the TATP/SSB harnesses) and the fixed gates were negative-tested (a deliberately failing binary now aborts the whole benchmark). It also found two opcodes still using the safe fallback dispatch path; both were wired to direct dispatch. All numbers above were re-measured after these fixes.
No diagnostic code left behind: profiling was done externally (perf, strace, cachegrind), and the final source diff was checked to contain no printf/timing instrumentation.

How this work was produced

The entire optimization — profiling, engine changes, benchmark harness, adversarial testing, security hardening, and this report — was carried out by KISS Sorcar (github.com/ksenxx/kiss_ai), a multi-model AI agent framework, in less than 8 hours and under a $150 budget. Human direction consisted of 1 main short prompt, 2 minor short prompts, and a couple of steering prompts.

claude-fable-5 — all development work: profiling, engine changes, build tuning, benchmark harness, and testing.
kimi-k3 — all security hardening: fuzzing, hostile-WAL corruption scenarios, OOM injection, and boundary sweeps.
gpt-sol5.6-sol-high — read-only reviews of the engine changes and the benchmark harness.

Reproducing the results

# in ~/sqllite-ks
benchks/build_bench.sh build-base                       # pristine baseline
. benchks/optflags.sh
CFLAGS="-O3 -march=native -g" benchks/build_bench.sh build-opt $OPT_DEFS
benchks/bench.sh build-base baseline 3                  # medians of 3 reps
benchks/bench.sh build-opt  final    3
# full test suite
cd build-cg && make testfixture && \
  ./testfixture ../test/testrunner.tcl --jobs 24 full

Raw result files: benchks/results/baseline-v4.txt, final-v4.txt, walfull-v4.txt. Reviews and audits: benchks/ADVERSARIAL.md, benchks/HARDENING.md, benchks/REVIEW.md.