Publishing a SQLite database through one compare-and-swap

SQLite expects a file, and a tenant application on Ferrite, the platform I own, doesn’t get one. It has no ambient filesystem, the platform’s application library as its one runtime dependency, five seconds to finish any service call, and a durable key/value store that it reaches through platform handles. Offering relational queries in that environment means answering a question SQLite and a filesystem normally settle between them: how does a new version of the database become the current one, when publishing it takes many separate writes and the process can die between any two of them?

The SQL adapter I was developing in August 2026 was deliberately plain. SQLite is compiled to WebAssembly and shipped as data inside the application, where a vendored copy of the tinywasm interpreter runs it under a fuel limit, behind a minimal WASI shim with a virtual clock, captured stdio and no filesystem. A query loads the database’s whole serialized image into linear memory, runs to completion with no host calls along the way and, if it was a write, commits a new image back. SQLite, tinywasm with four local patches and the first version of this service all came in as imported foundations. I led the integration and durability work on top of them with coding agents.

One fence

An image is cut into 256 KiB chunks. Every write takes a fresh transaction nonce and puts its chunks under keys built from the database name, that nonce and the chunk index, so two concurrent writers use disjoint keys and cannot tear each other’s work. A single manifest key per database names the live transaction, and it changes only through a conditional put against the version the writer read. That compare-and-swap is the only fence in the design. Chunks and manifest live in the same store, so read-your-writes holds and there is no window in which one system has durably accepted something another has yet to hear about.

A commit then comes down to a few ordered steps:

1. Write the new image's chunks under a fresh nonce.
2. Compare-and-swap the manifest to the new transaction, carrying the gc list.
3. If the swap lost: reclaim this call's own chunks within budget,
   report the conflict, and leave the manifest alone.
4. If the swap won: sweep the gc list oldest first, within budget, and
   re-publish whatever remains, best effort.

The order of the deletes

For a winning writer, a superseded generation is deleted only after the manifest swap has published its successor. A crash between those two steps leaves an old generation taking up storage, which is a leak. Reverse them and the same crash leaves a live manifest naming chunks that no longer exist, which is data loss. Stated like that the choice sounds easy, but it is only available to code that is careful about which step comes first.

A losing writer deletes only the chunks that call wrote itself, under its own nonce, and never touches the generation the winner published. The tempting alternative is to clean up by comparing the store against the manifest the writer read, and that manifest is by definition out of date. A writer that has just lost a race knows two things for certain: which chunks it wrote, and that it has no further say over this database. The second point is also why a loser must not write the manifest, even to record cleanup it failed to finish. Whatever it leaves behind stays there as orphan chunks that nothing names and no read can reach, off the correctness path.

The other crash points follow from the same layout. A writer that dies before the swap leaves the manifest as it was, so readers keep getting the old image and the new chunks sit unnamed. A writer that dies just after the swap leaves the new image live and the generation it replaced recorded in the manifest’s bounded gc list for later cleanup. A crash partway through sweeping that list, before the remainder is re-published, means the next commit deletes a few keys that are already gone, a tombstone over a tombstone, which costs a round trip and nothing else. And since the new image is durable as soon as the swap succeeds, a failed delete never fails a won commit. The sweep stops at its first error and keeps what is unfinished, and the client is told its write succeeded, which is true, and which saves it from retrying a write that has already landed.

Deleting is the expensive part

The store offers single-key delete and nothing else: no batched delete, no bounded prefix sweep. At the measured floor of about 4 ms per key, an 85.8 MB generation of 328 keys takes 1.3 seconds to remove, inside a request that also has to load the image, run the query and land its swap within five seconds. Reclamation that can consume a quarter of the deadline cannot be allowed to run unconditionally, so I bounded it. A winning commit retires at most 64 keys, about 16 MB and 256 ms at that floor. Its manifest stores pending work as a transaction, a chunk count and a cursor, and the next commit resumes a retained generation from that cursor. Clearing all 328 keys would consume six full sweep budgets, so the amount remembered matters as well as the per-call limit.

The arithmetic isn’t flattering at that width. A steady writer to an 85 MB database strands 328 keys with every commit and can retire 64. The manifest remembers only the four most recent unreclaimed generations and drops the oldest when that list fills, leaving any of its remaining chunks as untracked orphans. The budget bounds cleanup work per request; it guarantees neither the whole request’s deadline nor that reclamation keeps pace. A batched delete would reduce the number of round trips needed to retire a generation. The write path already batches chunks in windows of at most 8 MiB, comfortably under the 64 MiB frame ceiling, which takes the same image from 328 round trips to 11.

Tests that read like the specification

Three tests pin this down, and their names do most of the explaining: a_won_flip_retires_the_generation_it_superseded, a_lost_cas_retires_only_the_chunks_that_call_wrote and a_bounded_sweep_records_its_remainder_and_the_next_commit_resumes_it. The last builds a generation one key wider than the budget, expects 64 keys reclaimed and one pending with the gc cursor at 64, and then expects the next commit to finish the job. They read their results back through a driver client rather than the application’s own loader, because a loader under test could hide the very gaps the test exists to find. They reach the resume path by exhausting the budget and not by killing a process, so the crash cases above rest on the order of the writes, which is a good reason to keep that order short enough to check by reading it.

What the simplicity costs

The weakness of an image engine is the obvious one: every read loads the whole image and every write replaces it. At 89.7 MB an early benchmark showed 247 ms for a point lookup and at most about 3.1 seconds for an insert, before the batched write windows arrived.

The adapter keeps the durability boundary at the request: a write runs against an in-memory image and commits it through the manifest. Its publication protocol comes down to writing a private generation and publishing it through one key. Cleanup follows the outcome: a winner reclaims superseded generations, and a loser reclaims only its own chunks.

Chris Chabot, November 2026