We run SQLite in production for a bunch of small services. It’s great. The operational load is roughly zero most days. But I’ve been burned enough times on WAL specifically that I want to write down the sharp edges.

The setup

Service has a ~500MB SQLite file on a local SSD. WAL mode on, synchronous=NORMAL, a single writer process and maybe ten readers per host. Throughput is tiny — a few hundred transactions per second at peak.

Pitfall 1: the WAL doesn’t shrink itself

The WAL file will grow to whatever size it needs and stay there. Checkpoints flush content from the WAL into the main database, but they don’t truncate the WAL by default. If something holds a long read transaction — a long ANALYZE, a backup tool, a stuck client — the WAL grows unboundedly.

I had a service where the WAL hit 18GB overnight because a monitoring probe had opened a read transaction and forgotten to commit. The main file was 400MB. Disk alarm at 3am. Fun.

Fix:

PRAGMA wal_autocheckpoint = 1000;  -- default is 1000 pages
PRAGMA journal_size_limit = 67108864;  -- cap at 64MB-ish, refuses to grow past this

The journal_size_limit isn’t a hard cap — it kicks in on checkpoint — but combined with PASSIVE vs TRUNCATE checkpoints you get something reasonable.

Pitfall 2: NFS

Don’t put a WAL-mode SQLite database on NFS. I know, I know. You didn’t want to hear that. Neither did I. But SQLite uses shared memory (the -shm file) to coordinate readers and writers, and that coordination is undefined over NFS. The SQLite docs even say so, and yet every year someone tries and gets corruption.

If you’re tempted: use rqlite or LiteFS or just run a real database. You’ll save yourself three weeks.

Pitfall 3: backups

You cannot just cp mydb.sqlite backup.sqlite and be done. The WAL might contain committed changes that haven’t been checkpointed. Your backup is a consistent-at-some-point snapshot of the main file, but without the WAL it’s missing recent writes.

Three options:

  1. sqlite3 mydb.sqlite ".backup backup.sqlite" — uses the online backup API, safe.
  2. Copy both the main file and the -wal file. Good if you’re careful about ordering, but racy.
  3. Checkpoint to TRUNCATE first, then copy the main file while holding a read transaction:
BEGIN;
PRAGMA wal_checkpoint(TRUNCATE);
-- copy the main file here, from another process
COMMIT;

We use option 1. It’s the boring correct answer.

Pitfall 4: fsync and crash recovery

WAL mode with synchronous=NORMAL (the default after enabling WAL) gives you durability on commit except across power loss. Specifically, recent commits can disappear after a kernel panic if the WAL hasn’t been fsynced yet.

For most services this is fine — you retry or the upstream re-sends. For anything where a lost commit is bad (financial, idempotency keys, audit logs), crank it up:

PRAGMA synchronous = FULL;

The performance hit on a modern SSD is smaller than you’d expect. Maybe 15-25% on write-heavy workloads. For read-heavy workloads, effectively zero.

Pitfall 5: wal mode is per-database, and sticky

When you enable WAL mode on a database file, it stays in WAL mode until you change it back. Other processes opening that file will use WAL mode whether you intended or not. This bit me when a debugging script opened the database in a weird way and left it in a mode that the main service didn’t expect.

# check mode
sqlite3 mydb.sqlite "PRAGMA journal_mode;"

Bottom line

WAL mode is a genuine improvement. Multi-reader + single-writer without blocking is huge. I’d never go back. But “set and forget” isn’t quite right — you still need to think about checkpoints, backups, and fsync semantics. Treat it like a small but real database, not a zero-cost library.

Related: SQLite strict tables migration has more on operational gotchas.