SQLite WAL Mode — Database Integrity, Crash Recovery, and Concurrency

By Marcus Vance · Lead Systems & Backend Architect · November 2026

WAL Fundamentals — How It Differs from Rollback Journal

The Rollback Journal Problem

Traditional SQLite (DELETE journal mode) writes a copy of original pages to a rollback journal before modifying the database file. On commit, the journal is deleted. On crash, the journal is played back to restore original pages. This means:

• Readers block writers — a read transaction holds a shared lock; writer needs exclusive lock.
• Writers block readers — exclusive lock prevents any concurrent access.
• Double I/O — every modified page written twice (journal + database).

WAL Changes the Model

Write-Ahead Logging (WAL) appends changes to a separate -wal file instead of modifying the database file directly. The database file remains read-only for readers.

• Readers never block writers — readers snapshot the database at transaction start, read from DB + WAL.
• Writers never block readers — writer appends to WAL; readers see consistent snapshot.
• Single I/O per page — page written once to WAL; checkpoint merges to DB later.
• Crash recovery — on restart, WAL frames replayed to reconstruct state.

WAL File Structure

database.db          # Main database (unchanged during writes)
database.db-wal      # Write-ahead log (append-only frames)
database.db-shm      # Shared memory (WAL index, 32KB default)

Each WAL frame = 24-byte header + page content (page size, default 4096 bytes). Header: page_number, size, checksum, salt.

WAL Mechanics — Checkpointing, Frames, and Recovery

Checkpointing — Merging WAL to Database

WAL grows indefinitely until a checkpoint copies frames back to the database file. Three modes:

Automatic checkpoint: triggered every 1000 frames (default wal_autocheckpoint). Configure:

PRAGMA wal_autocheckpoint = 1000;  -- frames (0 = disable)

Frame Structure & Checksums

struct WalFrameHeader {
  uint32_t page_number;    // Database page number (1-based)
  uint32_t size;           // Page size (must match db)
  uint32_t checksum1;      // CRC32 of page + salt
  uint32_t checksum2;      // Second checksum (different salt)
  uint32_t salt1;          // Random per-WAL salt
  uint32_t salt2;          // Second salt
};

Dual checksums with different salts detect corruption with probability ~2^-64. On recovery, frames validated before replay.

Crash Recovery Process

1. Open database, detect -wal and -shm files.
2. Read -shm to find valid frame range (last committed transaction).
3. Scan WAL frames forward from last checkpoint.
4. For each frame: verify checksums → write page to database file.
5. Delete -wal and -shm on clean shutdown; keep if dirty.

Recovery is automatic on first connection — no manual intervention needed.

Concurrency Model — Readers, Writers, and the SHM Index

Shared Memory (SHM) Index

The -shm file is a memory-mapped index (32KB default) mapping page numbers to WAL frame offsets. Multiple processes map the same SHM — lock-free coordination via atomic operations.

• Read transaction: Records current WAL frame count (“read mark”) in SHM. Reads see database pages + WAL frames up to that mark.
• Write transaction: Appends frames to WAL, updates SHM index atomically. Commits by advancing “write mark”.
• Checkpoint: Finds minimum read mark across all readers → checkpoints frames before that mark.

Read Performance — Snapshot Isolation

Each reader gets a consistent snapshot at transaction start. Long-running reads don’t block checkpoints (checkpoint stops at oldest read mark). Use PRAGMA read_uncommitted = true for dirty reads (faster, no snapshot).

Write Serialization

Only one writer at a time (SQLite is single-writer). Writer holds exclusive lock on WAL file. For high write throughput: batch transactions, use PRAGMA synchronous = NORMAL, consider separate databases per writer.

Performance Tuning — PRAGMA Settings for Production

Checkpoint Strategy for High Write Load

-- Disable auto-checkpoint (prevents stalls)
PRAGMA wal_autocheckpoint = 0;

-- Background thread: checkpoint every 30s or 10MB WAL
-- Python example
import sqlite3, time, os

def checkpoint_loop(db_path):
    conn = sqlite3.connect(db_path)
    conn.execute('PRAGMA wal_autocheckpoint = 0')
    while True:
        time.sleep(30)
        wal_size = os.path.getsize(db_path + '-wal') if os.path.exists(db_path + '-wal') else 0
        if wal_size > 10_000_000:  # 10MB
            conn.execute('PRAGMA wal_checkpoint(TRUNCATE)')

Production Patterns & Gotchas

Pattern 1: Read Replicas via File Copy

WAL enables consistent file-level backups: copy database.db, database.db-wal, database.db-shm atomically (or use sqlite3_backup API). Replica can open read-only and serve queries immediately — no replay needed.

Pattern 2: Multi-Process Access (Web Server + Workers)

Multiple processes (Gunicorn workers, Celery tasks) can share a SQLite DB in WAL mode. Each opens its own connection. SHM coordinates. Requirement: all processes on same host (SHM is POSIX shared memory). Not compatible with network filesystems (NFS, SMB) — SHM mmap fails.

Pattern 3: libSQL / Turso for Distributed SQLite

libSQL (Turso fork) adds native replication over HTTP/WebSocket, embedded replicas (read locally, write to primary), and vector search. Uses WAL internally for replication log.

// Turso HTTP client
const db = createClient({ url: 'libsql://mydb.turso.io', authToken: '...' });
await db.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, embedding VECTOR(384))');
await db.execute('INSERT INTO users (name, embedding) VALUES (?, ?)', ['alice', new Float32Array(384)']);
const results = await db.execute('SELECT * FROM users WHERE embedding MATCH ? AND name = ?', [queryVec, 'alice']);

Common Pitfalls

Monitoring & Diagnostics

Key Metrics

-- WAL file size (should stay bounded)
PRAGMA wal_checkpoint(PASSIVE);  -- Returns (busy, log_frames, checkpointed_frames)

-- Page cache hit rate
PRAGMA cache_size;  -- Negative = pages in cache

-- Database integrity
PRAGMA integrity_check;  -- Full scan (slow)
PRAGMA quick_check;      -- Faster, less thorough

WAL Frame Analysis

# Count frames in WAL (each frame = page_size + 24 bytes)
wc -c database.db-wal
# frames = (file_size - 32) / (page_size + 24)  # 32 = WAL header

Migrating from Rollback Journal to WAL

-- One-time migration (run once)
PRAGMA journal_mode = WAL;

-- Verify
PRAGMA journal_mode;  -- Should return 'wal'

-- The -wal and -shm files appear on next write transaction.
-- To force creation:
BEGIN; COMMIT;

Rollback is automatic — existing data preserved. No downtime required.

TL;DR — Key Takeaways

• WAL = append-only log + shared memory index. Readers never block writers; writers never block readers.
• Three files: .db (data), .db-wal (frames), .db-shm (index).
• Checkpoint merges WAL → DB. Modes: PASSIVE, FULL, RESTART, TRUNCATE.
• Crash recovery = automatic WAL frame replay on next open.
• Production PRAGMAs: journal_mode=WAL, synchronous=NORMAL, wal_autocheckpoint=0 (manual), mmap_size=256MB.
• Not for network filesystems. For distributed: libSQL/Turso.
• Monitor WAL size, checkpoint frames, cache hit rate.

Next in series: PostgreSQL MVCC Deep-Dive — multi-version concurrency control internals and best practices.