Turning on WAL mode in SQLite
By default SQLite uses a rollback journal: a writer takes a lock that blocks readers for the duration of the write. For a small service that serves reads while a background job writes, that shows up as occasional "database is locked" errors.
Write-ahead logging changes the arrangement. Writers append to a separate log file and readers keep reading the main database, so readers do not block the writer and the writer does not block readers. Only one writer at a time is still allowed.
Switching it on
PRAGMA journal_mode=WAL;The setting is stored in the database file, so you only need to run it once. The statement returns the new mode, which is a handy way to confirm it worked.
The extra files
You will now see name.db-wal and name.db-shm next to the database. The first holds changes that have not yet been copied back; the second is shared memory used to coordinate connections. Copy all three together if you copy a live database by hand, or better, use the .backup command in the sqlite3 shell.
Checkpoints
Changes are moved from the log into the main file by a checkpoint, which normally runs automatically once the log reaches about a thousand pages. A long-running reader can keep the log from being reset, so if the -wal file keeps growing, look for a connection that never finishes its read transaction.
When not to use it
WAL needs shared memory between processes on the same machine, so it does not work reliably when the database lives on a network filesystem. In that case stay with the default journal.