Optimizing SQLite Against Its Official and Academic Benchmarks

What this is about

SQLite is the most widely deployed database engine in the world: it ships inside every phone, every major browser, and most embedded devices. This post describes making a development build of SQLite faster on four benchmarks: the two official ones that ship with SQLite itself (speedtest1, a broad mix of ~30,000 SQL statements, and kvtest, raw blob I/O) and two standard academic workloads (TATP, a telecom-style OLTP transaction mix, and the Star Schema Benchmark, an OLAP query suite).

The problem

SQLite’s out-of-the-box configuration is deliberately conservative: it defaults to rollback-journal mode, which pays a create–write–fsync–unlink cycle on every transaction; its bytecode interpreter re-enters a giant switch statement for every opcode; and small write-ahead-log writes reach the filesystem one frame at a time. Profiling showed these costs dominate real workloads — on speedtest1 alone, roughly 45% of the runtime was journal churn.

For context: the SQLite team has spent nearly 20 years tuning this code. Their own measurements show roughly a 3.5× total CPU improvement since 2008, earned a few percent at a time. The gains described here come from trading away default conservatism (journaling mode, portability of the interpreter loop, per-frame WAL writes), not from finding waste the SQLite authors missed.

SQLite’s reputation rests on correctness and durability, so the binding constraint was to get the speed without breaking anything: every change had to produce byte-identical query results, pass the entire million-plus-case SQLite test suite, hold up under adversarial and security testing, and stay opt-in so that default builds remain untouched.

Summary

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.

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 (largest gain)

• 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, shipped as benchks/build_pgo.sh (trained on different workload sizes — and, where the tool supports them, different seeds — than the measured runs, so the profile cannot memoize benchmark answers).
• 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

Reproducing the results

The complete protocol ships as benchks/README.md in the public repository. The PGO step (build_pgo.sh) is required for the full speedup — without it the CPU-bound SSB benchmark loses most of its gain (1.03× instead of 1.30×).

git clone https://github.com/ksenxx/sqlite-optimized
cd sqlite-optimized && mkdir build-base build-opt
( cd build-base && ../configure && make sqlite3.c )   # generate amalgamation
cp build-base/sqlite3.{c,h} build-opt/

benchks/build_bench.sh build-base                     # pristine baseline
benchks/bench.sh build-base baseline 3                # medians of 3 reps

. benchks/optflags.sh                                 # optimized + PGO build
benchks/build_pgo.sh build-opt $OPT_DEFS
benchks/bench.sh build-opt final 3

# upstream test suite (default build options)
( cd build-base && make testfixture && \
  ./testfixture ../test/testrunner.tcl --jobs 24 full )

Generated benchmark outputs land in benchks/results/ (gitignored; not tracked on GitHub). Reviews and audits are included in the repository: benchks/ADVERSARIAL.md, benchks/HARDENING.md, benchks/REVIEW.md. Absolute times, and therefore exact ratios, vary a few percent with machine load, thermal state, and the exact PGO profile; following the published protocol end-to-end reproduces 1.53–1.55× geomean on the same class of machine (1.50× if the PGO step is skipped). All testing so far has been on standard Linux provided by GCP; other operating systems and filesystems have not yet been benchmarked.