sqlite point-lookup benchmarks

a warm-cache point-lookup benchmark against stock SQLite measures SQLite's defaults, not its engine. driven properly, SQLite is about sixteen times faster on the same workload than in its default configuration, and a 24× lead for a new engine shrank to about 1.5× once all three defaults below were fixed.

the three defaults

in order of impact:

1. per-statement locking. outside an explicit transaction, every sqlite3_step acquires and releases the file lock, which is fcntl syscalls per lookup (about 9.5 µs/op measured). holding a read transaction (BEGIN ... COMMIT) around the loop brought it to about 2 µs/op.
2. the page cache. cache_size defaults to about 2 MB, and any larger working set thrashes it. size it to the file (PRAGMA cache_size = -N for N KiB) before comparing warm performance.
3. no mmap. PRAGMA mmap_size defaults to 0, so every page miss is a pread plus a copy into SQLite's cache. with mmap, warm reads come from the OS page cache without the copy.

most applications run the default configuration, so a fair comparison reports both.

what moves the number

random point lookups are memory-latency bound. instruction-level micro-optimizations (varint fast paths, slimmer probe parsing) measure as noise; cache misses and indirection are what to attack. the harness rules (identical input, a parity checksum, warmups, medians) are in benchmark validity.

how a page cache can beat SQLite's own is in page caches; how a value's size changes SQLite's B-tree layout is in compact point-read indexes. see also zig database patterns and mst hot paths (small sorted nodes prefer a linear scan).

sources

• zlite — bench/, a SQLite reimplementation in zig 0.16; M-series mac, macOS system SQLite 3.51.
• zuvloop — bench-suite measurement hygiene
• SQLite PRAGMA (cache_size, mmap_size defaults)