SQLite WAL mode.

What is WAL

Writes append to <db>-wal file. Readers see committed snapshot from main db. Readers don’t block writers (and vice versa).

Enable

PRAGMA journal_mode = WAL;

Persistent setting per DB.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -64000;        -- 64MB
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 268435456;       -- 256MB mmap
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;

Apply on every connection (some persist, some don’t).

Checkpoints

WAL file grows until checkpoint moves changes to main DB.

PRAGMA wal_checkpoint(PASSIVE);     -- doesn't block
PRAGMA wal_checkpoint(FULL);
PRAGMA wal_checkpoint(RESTART);
PRAGMA wal_checkpoint(TRUNCATE);

Auto-checkpoint every 1000 pages by default:

PRAGMA wal_autocheckpoint = 1000;

WAL files

  • mydb.sqlite: main DB.
  • mydb.sqlite-wal: WAL log.
  • mydb.sqlite-shm: shared memory.

All three needed during use. Backup all if cold-copying.

Concurrency

  • Many readers concurrent.
  • One writer at a time (per DB).
  • Readers don’t block writer.

Multiple writers

SQLite serializes writes. Set busy_timeout so writes wait briefly:

PRAGMA busy_timeout = 5000;

Avoids SQLITE_BUSY.

When WAL not appropriate

  • Read-only file system (can’t write WAL).
  • NFS / network filesystems (may have issues).

For these: journal_mode = DELETE (default).

synchronous setting

  • OFF: fastest, risk corruption on power loss.
  • NORMAL: safe in WAL mode (default with WAL).
  • FULL: paranoid, slower.

NORMAL with WAL is the sweet spot.

Common mistakes

  • Not enabling WAL → readers/writers serialize.
  • Forgetting busy_timeout → write contention errors.
  • Copying only main DB during use → corruption.
  • Long-running readers blocking checkpoint → WAL grows.

Read this next

If you want my SQLite tuning, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .