SQLite performance notes

Measured findings for SQLite from Mojo: transactions around batch writes, mmap_size for large random reads, json_each for IN lists.

Measured against m0-sqlite on macOS (arm64, Apple system libsqlite3 3.51.0), August 2026. Every number here came from a benchmark in this repo's style — build a real database, run the real bindings, time the real calls. Reproduce with the scripts described at the end.

The short version:

Written in parallel with m0_array. This study and the m0_array virtual table landed from two separate sessions that did not see each other's work. They agree on the facts and answer the same question differently: this document establishes that the carray extension cannot be reached from here, and m0_array supplies the capability anyway by registering a Mojo-side virtual table instead of loading a C one. Where they overlap, m0_array is the answer; the analysis below is why it had to be built rather than borrowed.

What the library we link actually has

PRAGMA compile_options on the macOS system library, filtered to what matters:

OMIT_LOAD_EXTENSION        <- no runtime extensions, ever
DEFAULT_MMAP_SIZE=0        <- mmap is off until you ask
MAX_MMAP_SIZE=1073741824   <- and can be raised to 1 GB
DEFAULT_CACHE_SIZE=2000    <- 2000 pages, ~8 MB at the 4 KB page size
THREADSAFE=2               <- multi-thread, NOT serialized
MAX_VARIABLE_NUMBER=500000 <- generous; Linux builds are usually 32766
ENABLE_SETLK_TIMEOUT       <- blocking locks, which busy_timeout uses
ENABLE_FTS5, ENABLE_RTREE, ENABLE_MATH_FUNCTIONS, ENABLE_SESSION
DEFAULT_WAL_AUTOCHECKPOINT=1000

Two of these deserve attention. OMIT_LOAD_EXTENSION means no loadable extension can ever be used on macOS with the system library — that single flag decides the carray question below. And THREADSAFE=2 means the library defaults to multi-thread rather than serialized mode; open_serialized's SQLITE_OPEN_FULLMUTEX still selects per-connection serialization, so that helper works, but the docstring's "serialized by default" describes the upstream default, not this build.

Probed, not assumed: json_each and FTS5 are present; carray and generate_series are not. sqlite3_bind_pointer links; sqlite3_enable_load_extension does not link at all.

carray(), and whether Mojo structs can feed it

Short answer: no, on two independent grounds.

It isn't there. SELECT ... FROM carray(...) fails with no such table: carray on the library we link. carray only became part of the amalgamation in SQLite 3.51.0 and even then it is off unless built with -DSQLITE_ENABLE_CARRAY; before that it was a loadable extension. Loading it at runtime is not an option on macOS, because the system library is compiled OMIT_LOAD_EXTENSION — the symbol sqlite3_enable_load_extension isn't merely disabled, it doesn't exist, and a binary referencing it fails to link. Linux distributions usually do enable loading, so a shipped carray.so could work there, but a feature that exists on one of two supported platforms isn't a feature this package can offer.

Structs wouldn't work if it were. carray binds one flat, homogeneous C array and reads it with a fixed element type: int32, int64, double, char*, or struct iovec. It has no concept of a stride, a field offset, or a record layout. Handing it a List[MyStruct] would make it read the first four or eight bytes of each struct as a value and then step forward by the element size rather than the struct size — garbage, not an error.

What does map cleanly is a flat numeric list:

There's a pleasing consequence: the parallel-array (SoA) style this repo already uses — because List[Struct] fights ImplicitlyCopyable — is exactly the layout carray wants. A List[Int] field of an SoA struct is already a bindable C array. So if carray ever becomes available, the data is in the right shape; the blocker is availability, not representation.

sqlite3_bind_pointer does resolve, so the pointer-passing machinery carray depends on is present. If a future toolchain links a libsqlite3 built with SQLITE_ENABLE_CARRAY, wiring it up would be a small change.

The portable substitute: json_each

The problem carray solves is passing N values into one prepared statement when N varies. SQLite has a built-in answer that needs no extension: bind a JSON array as text and unpack it with json_each, which is present here and in every SQLite built since 3.38.

SELECT name, payload FROM items
 WHERE id IN (SELECT value FROM json_each(?1))

Nanoseconds per call, 50k-row table, warm cache:

Read this as three regimes. At N=1 the plain loop wins and json_each's parsing overhead is pure loss. Around N=10 they cross. From N=50 up the loop is losing badly — 4.5x at 50, 6.7x at 1000 — because it pays a full statement round trip per id.

Rebuilding IN (?,?,…) is competitive but has two problems the table doesn't show: it needs a different prepared statement for every distinct N, which defeats any statement reuse, and it is bounded by MAX_VARIABLE_NUMBER (500k here, but 32766 on typical Linux builds). json_each needs one statement forever.

Building the JSON array is a string concatenation this repo is already equipped for — m0-core's escape_json_string for text ids, plain String(i) for integers.

Pragmas

20,000 random point reads over a 45 MB database (deliberately past the ~8 MB default page cache):

mmap_size is worth 40% and cache_size is worth approximately nothing. That asymmetry makes sense: raising the cache only helps if the working set fits it and the reads repeat, while mmap removes a read() syscall and a buffer copy from every page fetch. On a database that fits entirely in the default cache (a 4 MB, 50k-row table) neither pragma moved the needle at all, which is the other half of the same story.

The caveat on mmap, from SQLite's own documentation: with memory-mapped I/O a disk error that would have been an SQLITE_IOERR becomes a SIGBUS, and a stray pointer write in the process can corrupt the database file instead of being caught. That is a real trade, so open() does not set it. It belongs to the application that knows its storage.

temp_store=MEMORY showed no measurable effect on these workloads — nothing here spilled to a temp file. Set it if you sort large result sets without a supporting index.

Writes: the number that dwarfs everything else

10,000 single-row inserts:

Batching is 46x. Weakening durability to synchronous=OFF — which risks database corruption on an OS crash — buys 2.6x, still eighteen times slower than just wrapping the loop in a transaction. This is the single most valuable thing to know about writing to SQLite, and it costs two method calls this package already exposes.

Statement caching, revisited

The decision recorded in ROADMAP.md not to build a statement cache still holds, and now has a second measurement behind it. For the same trivial point query:

• prepare + step + finalize: 1,651 ns
• reset + step on a cached statement: 999 ns

So a prepare costs about 650 ns, fixed. Against the 17 µs a 50-row fetch takes, that is under 4% — consistent with the ~10% measured earlier on a smaller query, and not worth the ownership complexity of a cache that has to outlive the statements it hands out. Callers who care can hold their own Statement, which is what the benchmark's fastest column does.

Aggregates: SQL is not automatically the fast side

Statement.fetch_ints was written to hand back a column-major List because that "is what a SIMD pass over the results wants". m0_sqlite.stats_ints and friends are that pass, and measuring them turned up something worth writing down: pulling a column out and reducing it here beats asking SQLite to aggregate it.

200,000 integer rows, in-memory database, sum + min + max, values checked against SQLite's own answers before timing (bench_reduce in bench_sqlite.mojo):

SQLite runs three aggregate steps through its bytecode VM for every row; the read-out runs one column fetch. That is the whole difference — and it is why the vectorization is almost beside the point. The reduction is 0.5% of the pipeline. It is 4x its own scalar loop, and that 4x buys nothing you would notice unless the column is already materialised and you intend to pass over it repeatedly.

Do not read this as "aggregate in Mojo". It is one shape: a full scan, in memory, three aggregates at once, no index, no WHERE. Any of those moving can move the answer past it — a single aggregate halves the VM work, an index can make SQLite skip rows entirely, and a result set larger than memory makes the read-out untenable at any speed. What it does establish is narrower and still useful: when you already need the rows, computing an aggregate from them is not the expensive choice it looks like, and reaching for SQL to avoid a Mojo-side pass is not automatically right.

Integers only, deliberately. A vector sum reassociates the additions, and floating point is not associative — a Float64 version would disagree with a scalar loop in the last ulp with no single right answer to test against. Integer addition reassociates exactly, so these agree bit for bit at every length, which test_reduce.mojo asserts from 0 to 80 elements and against SQLite over 1,000 rows.

The WSGI-bridge techniques, checked against this package

The bridge work (docs/WSGI_PERFORMANCE.md) distilled into a short method: split a cost by part before designing against it; check whether a missing API is missing or merely unbound; eliminate copies; hoist per-call overhead; and measure a cache's hit path before building it. Checked here deliberately, item by item:

Two things did come out of the check.

A text scan pays 2.1x for its per-row String, and the zero-allocation read already existed — undocumented. The bench had int, float and blob rows but no TEXT row, so column_text's per-row allocation had never been priced. Now it is (bench_sqlite.mojo, 100k rows, best of 5):

column_blob_into works on TEXT because SQLite converts TEXT to blob bytes on request, and for TEXT stored as UTF-8 that conversion is a pointer handoff. No new API was added — the fix is the two docstrings that now point at each other, per this package's own rule: add the variant the day something needs it, not speculatively.

The verdict on the package: it already embodies the method. The remaining per-row cost of a scan is SQLite's own VM (sqlite3_step), which is the same kind of floor the WSGI bridge just reached — the boundary code is direct calls and single copies, and what is left is the engine itself.

Recommendations

1. Document batching prominently. A README example that inserts in a loop without a transaction teaches the 46x-slower pattern.
2. Consider a mmap_size argument or an open_tuned variant rather than changing open(), given the SIGBUS trade. Left undone deliberately: it changes failure modes, so it is your call.
3. Add a json_each helper if fetching by id-set becomes common — the ergonomics, not the speed, are the reason: building the JSON array by hand at every call site invites an injection-shaped mistake that ? normally prevents.
4. Do not pursue carray. Unavailable on macOS, unusable with structs, and both m0_array and json_each already cover what motivates it — m0_array for binding a Mojo List directly (~3x on bulk inserts), json_each for an id-set that arrives as data rather than as a live buffer.
5. Revisit if the toolchain ever links its own SQLite. Building the amalgamation with -DSQLITE_ENABLE_CARRAY -DSQLITE_ENABLE_MATH_FUNCTIONS and a controlled version would make several of these questions moot — at the cost of owning the build.

Reproducing

The benchmarks were written as standalone Mojo programs against -I packages/m0-sqlite, in the same shape as packages/m0-core/run_benchmarks.mojo. They are not checked in; the measurements above are the deliverable. To redo them, the four workloads are: N-row fetch by the four strategies above; 20k random point reads under each pragma set; 10k inserts under each transaction mode; and prepare-versus-reset for one trivial query.