SQLite — WAL File Grows Without Bound and Checkpoints Stall

Diagnose an ever-growing SQLite WAL file, find the long-running reader blocking checkpoints, and restore checkpointing with timeouts and reader discipline.

advanceddebuggingsqlitedatabasestorage

Symptoms

The -wal sidecar file grows to gigabytes while the main database file stays small
PRAGMA wal_checkpoint(TRUNCATE) returns busy or does not shrink the file
Disk usage alerts fire on a database whose logical size is a fraction of the footprint

Possible causes

A long-running read transaction holds a snapshot, so the checkpoint cannot advance past its start frame
A connection opened with a statement never finalized keeps a read mark pinned
Continuous write traffic plus the default passive checkpointing never catches up to frame end
A reader crashed without cleaning up, leaving a stale read mark under some filesystem conditions

Diagnosis steps

Inspect WAL size and checkpoint state

sqlite3 app.db "PRAGMA wal_checkpoint(PASSIVE); PRAGMA journal_mode;" && ls -lh app.db-wal

Interpretation

A PASSIVE checkpoint returning busy=1 means frames exist that some reader prevents from being recycled — the read mark is pinned.

Find the pinning readers

sqlite3 app.db "SELECT * FROM pragma_compile_options;" >/dev/null; lsof app.db 2>/dev/null | awk '{print $1, $2}' | sort -u

Interpretation

Each open connection that recently read may hold a snapshot; long-lived PIDs are the suspects.

Test recovery of the checkpoint

sqlite3 app.db "PRAGMA wal_checkpoint(TRUNCATE);" && ls -lh app.db-wal

Interpretation

After stopping the long reader, a successful TRUNCATE that shrinks the file to zero confirms the reader was the blocker.

Root cause

WAL checkpoints can only recycle frames older than the oldest active read snapshot. A long-lived reader pins the read mark, every subsequent write appends new frames beyond it, and the WAL grows without bound because checkpointing is structurally unable to advance past the pinned mark.

Fix

Keep read transactions short and close idle connections. Enable wal_autocheckpoint with a small page threshold, schedule periodic checkpoints, use PRAGMA journal_size_limit to cap file size, and set busy_timeout so writers and checkpoints contend gracefully instead of failing busy.

Risk note

Forcing TRUNCATE checkpoints too aggressively under write load stalls writers behind the checkpoint. Tune the autocheckpoint threshold rather than hammering manual checkpoints.

Verify the fix

Under representative traffic, the WAL file oscillates around a bounded size, checkpoints report busy=0, and stopping the long reader immediately lets the file shrink to its cap.

Symptom walkthrough

The database footprint contradicts its contents:

$ ls -lh app.db*
-rw-r--r--  12M  app.db
-rw-r--r--  2.7G  app.db-wal   <- the real tenant

The PASSIVE checkpoint result tells you why:

sqlite> PRAGMA wal_checkpoint(PASSIVE);
0|1430|1430        <- busy=1: a reader blocks recycling

busy=1 is the confession: frames exist that the oldest reader snapshot forbids from being checkpointed. Every write since that reader began appends after the pinned mark, and the file grows forever.

Fix in practice

Fix reader discipline first, then tune the machinery. In Python that means short transactions and explicit closes:

# bad: implicit snapshot held for the app lifetime
cur = conn.cursor()
rows = cur.execute("SELECT ...").fetchall()  # snapshot stays open

# good: read, then release
with contextlib.closing(conn.cursor()) as cur:
    rows = cur.execute("SELECT ...").fetchall()
conn.close()  # or per-request connections

Then configure the checkpoint machinery:

PRAGMA journal_size_limit = 67108864;    -- cap at 64 MB
PRAGMA wal_autocheckpoint = 1000;        -- checkpoint every 1000 pages
PRAGMA busy_timeout = 5000;              -- contend gracefully

For reporting queries that must scan, run them against a read-only connection that you close promptly, or from a snapshot copy, so analytics never pins the OLTP read mark.

Verification

　　Run representative traffic including the reporting workload. Success is a WAL file that oscillates in a bounded band: it grows toward the autocheckpoint threshold, checkpoints report busy=0, and journal_size_limit holds the file at its cap. Then run the causal experiment — start the long reader, watch busy flip to 1 and the file climb; stop it, watch TRUNCATE immediately reclaim the space. That reversibility demonstrates the fix addressed the mechanism rather than the symptom.