SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers for Low-Latency App Servers

The "local only" label stopped being accurate a while ago

SQLite got filed away as the database for phones, sensors and test suites. For a long time that was reasonable. The reflex answer for anything with users in it was Postgres or MySQL, and often that is still the right answer.

What changed is the shape of the hardware. A single machine with NVMe storage and a lot of RAM handles workloads that used to need a small cluster. And if you deploy single tenant, one box per customer, the client-server split buys you less than it costs. Every query in a client-server setup is a serialization step, a socket write, a scheduler hop, a socket read and a deserialization step. With SQLite in-process, a read is a function call into a B-tree that is probably already in the page cache. That is the whole trick. There is no magic in SQLite that Postgres lacks. You just removed a hop.

The part people skip is that the defaults are tuned for correctness on unknown hardware with unknown filesystems, not for an app server you control. So here is what I change, and why.

Rollback journal versus WAL, in mechanics

The default journal mode is delete, a rollback journal. Before a page is modified, the original page is copied into a separate journal file. Commit means fsync the journal, write the pages in place, fsync the database, delete the journal. Recovery replays the journal backwards.

The problem is not speed, it is the locking model. During the write, the writer holds an exclusive lock on the whole database file. Readers wait. A reader that arrives first makes the writer wait.

WAL flips it:

PRAGMA journal_mode = WAL;

Now writes append frames to mydb.sqlite-wal. Readers pick a snapshot (an end mark in the WAL), and read pages from the WAL up to that mark, falling back to the main file for pages that are not in the WAL. A shared memory file, mydb.sqlite-shm, holds the wal-index: a hash table that maps page numbers to WAL frames so readers do not scan.

Two consequences worth internalising. First: WAL is still a single-writer database. WAL gives you reader-writer concurrency, not write parallelism. Second: because of the -shm file, all connections must be on the same machine and see the same shared memory. NFS and SMB are out. If you set PRAGMA locking_mode = EXCLUSIVE in WAL mode, SQLite keeps the wal-index in heap memory and creates no -shm file at all, which is a neat option for a single-process server, at the cost of no second process being able to open the database.

Checkpointing is a scheduling problem

The WAL only helps while it is small. Every reader that needs an old page has to look it up in the wal-index; a huge WAL means more frames, more cache pressure and a longer recovery if the process dies. Checkpointing copies WAL frames back into the main database file so the WAL can be reused from the top.

By default SQLite runs a PASSIVE checkpoint on the connection that commits a transaction pushing the WAL past 1000 pages (PRAGMA wal_autocheckpoint). Two things go wrong with that in a busy server.

The first is a latency spike in the wrong place. The user request that happens to cross the threshold pays for the checkpoint. Your p99 write latency now has a sawtooth in it that has nothing to do with that request.

The second is checkpoint starvation. PASSIVE cannot reset the WAL while any reader still holds an older snapshot. If your server always has at least one open read transaction, the checkpoint copies what it can and gives up, the WAL never resets, and it grows without bound. I have seen a multi-gigabyte WAL next to a small database, and the cause was not write volume. It was one query whose result rows were never fully consumed, so the read transaction stayed open forever.

What I do instead:

PRAGMA wal_autocheckpoint = 0;      -- take it off the request path
PRAGMA journal_size_limit = 67108864; -- cap the file after a reset

Then one background task, on its own connection, on a timer, runs PRAGMA wal_checkpoint(TRUNCATE) and looks at the result. The pragma returns three values: a busy flag, the number of frames in the WAL, and the number of frames checkpointed. If the busy flag stays 1 over several rounds, you have a long reader and that is a bug in your application, not a tuning parameter. Fix the reader.

TRUNCATE and RESTART do block new writers for the duration. On a small WAL that duration is short, and it is a background task, so you can schedule it when traffic is low. journal_size_limit matters even with TRUNCATE because a PASSIVE reset leaves the file at its high water mark otherwise.

SQLITE_BUSY is two different errors with one name

Almost everyone sets a busy timeout and thinks the topic is closed.

PRAGMA busy_timeout = 5000;

The default is 0, which means the first writer conflict fails immediately, which is why untuned SQLite feels fragile under load. busy_timeout installs a handler that sleeps in increasing steps and retries. It is per connection, and you have to set it on every connection, including the ones your pool opens later.

But there is a case where the busy handler is deliberately not called. If you open a deferred transaction, read something, and then try to write, SQLite has to upgrade your read snapshot to a write lock. If another connection committed in the meantime, your snapshot is stale. Retrying cannot fix that, because your reads already saw an old version of the database. So SQLite returns SQLITE_BUSY_SNAPSHOT right away and skips the handler. Waiting would be pointless.

The fix is to declare intent up front:

BEGIN IMMEDIATE;

BEGIN IMMEDIATE takes the write lock at the start of the transaction. Now a conflict happens before you have read anything, the busy handler applies, and the retry is safe. Every transaction that might write goes through BEGIN IMMEDIATE in my code. Read-only transactions stay deferred.

You still need application-level retry around the whole transaction for the snapshot case, because the busy handler will not save you there. The retry has to restart the transaction from the beginning, not from the failing statement.

Connection topology: one writer, many readers

Since there is exactly one writer, do not let your pool discover this at runtime through SQLITE_BUSY. Model it.

• One dedicated write connection, serialized in the application. In Go that is a second sql.DB against the same file with SetMaxOpenConns(1). In Rust it is a single connection behind a mutex or an actor.
• A pool of read connections, sized to something like your CPU count. Reads scale with cores, so this is where the parallelism lives.
• Different pragmas per pool. Readers can use PRAGMA query_only = 1, which turns "oops, this path writes" into an error instead of a lock conflict.

Serializing writes in-process is not a downgrade. The lock exists either way, and in-process you get a fair queue and useful backpressure instead of a retry storm.

The pragmas I actually set

Scope matters. journal_mode is stored in the database file and survives. The rest are per connection and have to be applied on open, every time.

Two of those deserve a warning.

synchronous = NORMAL in WAL mode is durable across a process crash. It is not durable across a power cut or kernel panic: you can lose the most recent transactions, though the database stays consistent. That is a product decision, not a performance knob. For an app server holding derived state that can be rebuilt, I take it. For a ledger, use FULL.

mmap_size removes a copy from the read path, and it changes your failure mode. With normal I/O, a corrupt page comes back as an error code. With a memory mapping, bad storage can hand you a SIGBUS or a segfault inside your process. Also note the compile-time SQLITE_MAX_MMAP_SIZE ceiling: if it is 0 in your build, the pragma silently does nothing.

And while you are on the connection: the planner needs statistics. Run ANALYZE after bulk loads, and on long-lived connections run PRAGMA analysis_limit = 400; PRAGMA optimize; periodically. The analysis_limit keeps optimize from turning into a full scan.

VFS layers: what they are for, and when to skip them

SQLite reaches the disk through a sqlite3_vfs struct: a table of function pointers for opening files, reading, writing, truncating, syncing, locking and mapping shared memory. You can register your own and pass its name when opening a database. That is how SQLite runs on platforms it was never designed for, and it is the extension point people reach for too early.

Useful things that already exist:

Where custom layers genuinely earn their place is replication and storage tricks. Litestream watches the WAL and ships frames to object storage, so you get continuous backup without touching your queries. LiteFS goes one level lower and puts a FUSE filesystem underneath, so it can see and forward writes across machines. Both are, in effect, someone doing the hard part of the storage layer so you do not have to.

I have written exactly one VFS shim in anger, for instrumentation: wrap the default VFS, count xRead, xWrite and xSync calls, log the ones over a threshold. That is a good use of the interface. It is small, it delegates everything, and when the numbers are in hand you throw it away.

What I would not do is implement locking or shared memory yourself. xLock, xUnlock and xShmMap are where the correctness lives. If you get them subtly wrong, you do not get an error, you get a corrupted database in six months on one customer's machine. Before writing a VFS, check whether a pragma, a different journal mode, or one of the built-in variants already covers your case. Usually it does.

Backups, because this part is easy to get wrong

Copying the database file with cp while a writer is active gives you a file that may or may not be valid, and you find out during the restore. Options that actually work:

• VACUUM INTO 'snapshot.sqlite'. Consistent, defragmented, single statement. It reads the whole database, so it costs I/O.
• The online backup API (sqlite3_backup_*, or .backup in the CLI). Copies page by page and restarts if the source is written to mid-copy.
• Litestream or equivalent for continuous replication, if losing hours of writes is not acceptable.

And test the restore. A backup you have never restored is a hypothesis.

Where this lands in my own work

IoT Data Flow is single tenant on purpose. One box per customer, connected read-only to their own databases, because the alternative is asking a manufacturer to ship their machine data into someone else's cloud. That is the Leitsatz: the software comes to your data. Once you accept that deployment shape, an embedded database for the box's own state is the obvious choice. No second service to run, no credentials to rotate, no network partition between the app and its metadata. The customer's data stays in the custom