What SQLite's WAL mode actually buys you

SQLite defaults to a rollback journal. Every write takes an exclusive lock, and readers wait. For a single process with a few background goroutines that is already enough to see latency spikes on the read path whenever a write lands.

What changes

With PRAGMA journal_mode=WAL, writes go to a separate append-only file and readers keep reading the main database at the snapshot they started with. Readers no longer block writers and writers no longer block readers. There is still only one writer at a time, which for most services is fine.

What you now have to tune

The WAL file grows until a checkpoint copies its pages back into the main file. Automatic checkpoints run every 1000 pages by default, and they run inside whatever connection happened to cross the threshold, so a random write occasionally pays for the whole checkpoint. Two settings matter:

PRAGMA wal_autocheckpoint = 1000;   -- pages; raise if writes are bursty
PRAGMA synchronous = NORMAL;        -- fsync on checkpoint, not every commit

synchronous=NORMAL under WAL is durable against process crashes and loses at most the last few transactions on power loss. For a cache or a queue that trade is obvious. For a ledger it is not, keep FULL.

The catch

WAL needs shared memory between connections, so it does not work over network filesystems. If your database lives on NFS you are back to the rollback journal, and honestly you have other problems.