WAL Mode — The #1 Performance Win

One PRAGMA, two orders of magnitude

The default SQLite journal mode is DELETE: writers acquire an exclusive lock that blocks readers. WAL (Write-Ahead Logging) mode flips this — readers and writers don't block each other. Readers see a consistent snapshot; writers append to a log file (db-wal) which is periodically checkpointed back into the main database.

Turn it on once per database (it's persistent):

PRAGMA journal_mode = WAL;

Why you almost always want it:

Readers never block on writers, writers never block on readers.
Writes are dramatically faster — fewer fsyncs, sequential append.
It's the prerequisite for any concurrent workload (a web server, an async app, a desktop app with background sync).

Warning: WAL mode requires the database file to live on a real filesystem with proper file locking. It does not work safely on NFS, SMB, FUSE, or anything else with quirky locking semantics. Local disk only.

Pair WAL with a sensible busy_timeout (5–30 seconds) so transient locks during checkpoints just wait briefly instead of returning SQLITE_BUSY.

Code

The 'production-ready opener'·python

import sqlite3

def open_db(path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(path, timeout=30.0)
    conn.execute('PRAGMA journal_mode = WAL')
    conn.execute('PRAGMA synchronous = NORMAL')   # safe with WAL, faster
    conn.execute('PRAGMA foreign_keys = ON')
    conn.execute('PRAGMA busy_timeout = 5000')
    return conn

# Confirm WAL is on:
print(open_db('demo.db').execute('PRAGMA journal_mode').fetchone())
# ('wal',)

Manual checkpoint — usually not needed·sql

-- Force a checkpoint (writer activity is paused while it runs)
PRAGMA wal_checkpoint(TRUNCATE);

-- Inspect WAL state
PRAGMA wal_checkpoint;
-- (busy, log_pages, checkpointed_pages)

Exercise

Reproduce the WAL win on your own machine. Build a small benchmark: spawn one writer thread inserting rows in a loop and one reader thread doing SELECT count(*) in a loop. Run it once with the default journal_mode (DELETE) and once with WAL. Compare both throughputs. Note where the bottleneck moved.

WAL Mode — The #1 Performance Win

One PRAGMA, two orders of magnitude

Code

External links

Exercise

Progress

Comments 0