SQLite Journal Mode: When to Use WAL Instead of Rollback

Problem and Takeaway

Many applications need to serve read‑only queries while a write operation is in progress. With SQLite’s default rollback journal, a writer holds an exclusive lock on the database file, so concurrent readers receive SQLITE_BUSY errors and must wait. This bottleneck can degrade latency in web services, analytics tools, or any workload with many simultaneous readers.

Decision Constraints

The key constraints for choosing a journal mode are: (1) your workload must support concurrent reads, (2) all connections must see the same local filesystem, (3) you must accept a different crash‑recovery model, and (4) you need administrative rights to change the journal mode.

Supported Options Comparison

Feature	Rollback (default)	WAL
Concurrency model	One writer, exclusive lock; readers wait	Multiple readers, one writer; readers do not block writer
Sidecar files	None	Creates -wal and -shm files alongside the database
Crash recovery	Database is always consistent after a crash; changes are flushed before commit	Changes are flushed after each transaction; durability depends on PRAGMA synchronous setting
Typical use case	Single‑writer applications, simple scripts	Web services, analytics tools, any scenario with many concurrent readers

Trade‑offs

Enabling WAL improves read latency and can increase write throughput because readers do not block the writer. The -wal file must be checkpointed periodically; long‑running readers can prevent checkpoints and cause the WAL file to grow, consuming disk space in write‑heavy deployments. With PRAGMA synchronous=NORMAL you retain consistency after most crashes but may lose the very latest commit on sudden power loss. WAL also works poorly on network filesystems that do not support atomic file updates, so verify your storage layer first.

Concrete Implementation

To migrate to WAL, open a connection with exclusive access (no other connections holding the database) and run:

PRAGMA journal_mode=WAL;

This command changes the journal mode for the database located at database_path. You need write permission on the directory containing the database file and on a filesystem that supports WAL (local disk, not most network mounts). After enabling WAL, consider tuning:

1. PRAGMA wal_autocheckpoint = 1000; – sets how many pages are written before an automatic checkpoint.
2. PRAGMA busy_timeout = 5000; – how long a connection waits for a lock before returning SQLITE_BUSY.
3. PRAGMA synchronous = NORMAL; – keeps transactions consistent after a crash while allowing some durability trade‑off.

Running these PRAGMAs requires the same exclusive connection; they do not alter the on‑disk format but affect behavior.

Validation

Confirm the mode change:

• Open a new connection and execute PRAGMA journal_mode;. It should return wal.
• Check the file system for -wal and -shm files next to database_path. Their presence indicates WAL is active.
• Run a concurrency test: start one writer that inserts rows in a loop, and at least two readers that execute simple SELECT statements concurrently. Verify that readers never return SQLITE_BUSY.
• Inspect PRAGMA synchronous and PRAGMA wal_autocheckpoint to ensure they match your intended settings.

If any of these checks fail, roll back by re‑executing PRAGMA journal_mode=DELETE; in an exclusive connection; this restores the default rollback journal.

Risks and Mitigations

Network filesystems often cannot handle the concurrent updates required by WAL, leading to corruption or silent failures. Before deploying, test on a representative storage backend. The exclusive access needed for the initial PRAGMA means you must schedule a brief maintenance window or use a connection that holds a private transaction. Monitor the size of the -wal file; if it grows beyond expected limits, lower wal_autocheckpoint or force checkpoints manually with PRAGMA wal_checkpoint;. Remember that synchronous=NORMAL reduces durability on abrupt power loss, so balance durability requirements against performance gains.