SQLite WAL vs Rollback Journal: A Decision Guide

The Core Decision

You are building an embedded application that uses SQLite and need to pick a journaling mode. When a writer holds a transaction, readers may block, slowing down the whole system.

Constraints That Drive the Choice

• Concurrency model: single‑threaded embedded vs multi‑threaded or multi‑process access
• Filesystem permissions: WAL requires write access to the database directory for -wal and -shm files
• Deployment targets: legacy SQLite versions (3.7.0) cannot open WAL databases
• Transaction scope: ATTACH databases behave differently in each mode
• Durability requirements: checkpoint behavior affects fsync frequency and write latency spikes

Quick Comparison

Trade‑offs in Practice

When WAL Wins

Multi‑threaded applications, web servers using SQLite via URI sharing (file:db.sqlite?mode=ro), or any scenario where read latency must stay predictable during writes. WAL’s MVCC‑like behavior means a long‑running SELECT won’t stall an INSERT, and vice versa. Crash recovery is also faster—SQLite replays the -wal file forward rather than undoing partial page writes.

When Rollback Journal Makes Sense

Single‑threaded embedded apps (IoT firmware, mobile apps with one connection), read‑only filesystems, or deployments targeting ancient SQLite versions. Rollback journal avoids the -shm shared‑memory file entirely, so it works on network drives or read‑only media where WAL fails with SQLITE_CANTOPEN. Short transactions also leave less temporary disk footprint.

The Checkpoint Trap

WAL’s -wal file grows until a checkpoint copies pages back to the main database. By default, SQLite checkpoints automatically every 1000 pages (PRAGMA wal_autocheckpoint=1000). On write‑heavy workloads, this can cause noticeable latency spikes. You can disable auto‑checkpoint (PRAGMA wal_autocheckpoint=0) and call sqlite3_wal_checkpoint_v2() manually during idle periods, but then you must monitor -wal size to prevent unbounded growth.

Concrete Implementation: Switching and Validating Modes

1. Check Current Mode

sqlite3 /path/to/app.db 'PRAGMA journal_mode;'

Expected output: wal or delete. If the command returns delete and you want WAL, proceed.

2. Switch to WAL Mode

sqlite3 /path/to/app.db 'PRAGMA journal_mode=WAL;'

This creates /path/to/app.db-wal and /path/to/app.db-shm on first write. The mode persists across restarts—no need to re‑run on each connection.

3. Verify Concurrent Access Behavior

Open two terminal sessions (simulating two connections):

# Terminal 1: start a write transaction\nsqlite3 /path/to/app.db\nsqlite> BEGIN;\nsqlite> INSERT INTO events (ts, type) VALUES (strftime('%s','now'), 'test');\n-- leave transaction open

# Terminal 2: attempt a read\nsqlite3 /path/to/app.db\nsqlite> SELECT count(*) FROM events;

In WAL mode: Terminal 2 returns immediately with the count (including uncommitted row if same connection, otherwise pre‑transaction count).
In DELETE mode: Terminal 2 blocks until Terminal 1 commits or rolls back.

4. Control Checkpoint Timing (WAL Only)

Disable auto‑checkpoint and run manual full checkpoint during maintenance window:

sqlite3 /path/to/app.db 'PRAGMA wal_autocheckpoint=0;'

... application runs, -wal grows ...
During low‑traffic period:
sqlite3 /path/to/app.db '.shell sqlite3_wal_checkpoint_v2 /path/to/app.db SQLITE_CHECKPOINT_FULL'

Or from application code (C example):

int rc = sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_FULL, NULL, NULL);\nif (rc != SQLITE_OK) { /* handle error */ }

The NULL database name means \"main\". SQLITE_CHECKPOINT_FULL blocks until all -wal frames are synced; use SQLITE_CHECKPOINT_PASSIVE for non‑blocking attempt.

Limitations and Gotchas

• Network filesystems: NFS, SMB, and FUSE often break WAL's shared‑memory locking. Test PRAGMA locking_mode=EXCLUSIVE with WAL, or fall back to DELETE mode.
• Read‑only deployments: if the database file is on read‑only media, WAL cannot create -wal/-shm. Use PRAGMA journal_mode=DELETE or PRAGMA query_only=ON with a pre‑built WAL database (copied with its -wal/-shm files).
• Multi‑database transactions: in WAL mode, ATTACH databases commit independently. If atomicity across databases is required, use DELETE mode or implement application‑level compensation.
• Legacy clients: if any process uses SQLite 3.7.0, the database must stay in DELETE mode. Check with sqlite3 -version on all deployment targets.

Verification Checklist

1. Run PRAGMA integrity_check; after any mode switch or crash simulation.
2. Simulate power loss: kill -9 the writer mid‑transaction, reopen, run PRAGMA integrity_check;—should return ok in both modes.
3. Measure -wal growth: insert 100k rows with wal_autocheckpoint=0, note file size, then run manual checkpoint and confirm -wal shrinks to 0 or -wal header size (~32 bytes).
4. Load‑test concurrent readers: spawn 10 connections reading while one writes; in WAL mode, read latency should stay flat. In DELETE mode, expect blocking.