Use write-ahead logging when it helps

By default, SQLite uses a rollback journal: a writer copies the pages it’s about to change into notes.db-journal, modifies the main file, and readers must wait while that happens. Write-ahead logging inverts the flow — new changes go to a log file and the main database stays untouched until later.

Enable write-ahead logging with:

PRAGMA journal_mode = WAL;
-- wal

The pragma answers wal to confirm the switch. Unlike most pragmas, this one is persistent: it’s recorded in the database file, so every future connection opens in WAL mode without repeating it.

SQLite stores new changes in notes.db-wal. Connections coordinate through notes.db-shm. These sidecar files are part of the active database, so do not delete, move, or copy them separately.

You can watch them appear:

ls notes.db*
# notes.db  notes.db-shm  notes.db-wal

What WAL buys you

WAL mode lets readers keep using an existing snapshot while one writer appends changes. It does not create multiple writers.

That’s the whole trade in two sentences. In the default mode, a write blocks readers and readers block the writer. In WAL mode, readers never wait for the writer and the writer never waits for readers, which is why WAL is the usual choice for web applications where reads and writes overlap constantly. Writes are still serialized — one at a time — exactly as before.

Checkpoints

A checkpoint moves committed pages from the WAL file back into the main database. SQLite normally checkpoints automatically once the WAL grows past about 1000 pages. You can request a non-blocking checkpoint with:

PRAGMA wal_checkpoint(PASSIVE);
-- 0|55|55

The three numbers report whether the checkpoint was blocked, the WAL’s size in pages, and how many pages were moved back. When the last two match, the whole log was transferred.

The failure mode to watch: a long-running read transaction pins the WAL, checkpoints can’t complete past that reader’s snapshot, and notes.db-wal grows without bound. If you find a WAL file several times larger than the database, look for a connection holding a transaction open — often a forgotten shell session or a leaked connection in application code.

Keep a WAL database on local storage. WAL needs shared memory between processes on the same machine and does not work over a network filesystem. If the database must live on NFS or a mounted volume, stay on the default rollback journal.