Read the SQLite WAL Checkpoint Before Forcing TRUNCATE

An SQLite -wal file can stay large even after useful pages have been copied back to the main database. File size alone therefore cannot tell you whether checkpointing is healthy, partly blocked, or failing to reset the log. Read the checkpoint result before treating the file as disposable space.

Diagnosis begins with three facts: record the WAL frame count, compare it with checkpointed frames, and find any read transaction whose snapshot still needs older WAL history. A forced TRUNCATE may wait for that reader and zero the file after success, but it does not repair an application that repeatedly holds read transactions open.

A large file is not the same as a large backlog

In WAL mode, commits append changed pages to database.db-wal. A checkpoint copies eligible frames into database.db; later, a writer can reuse the WAL from the beginning when every frame has been checkpointed and no reader still needs the old log. SQLite’s WAL documentation explicitly notes that ordinary checkpoints do not normally truncate the file. Capacity monitoring must therefore distinguish allocated WAL bytes from frames that remain outstanding.

That distinction also prevents a terminology mistake. SQLite WAL is a local journal shared by connections on one host. PostgreSQL replication-slot WAL retention uses the same acronym but has different ownership, replication, and recovery semantics; commands or alarms from one system do not transfer to the other.

Start with read-only filesystem facts, then ask SQLite for its own state through an authorized connection:

DB=/srv/myapp/app.db

stat --printf='%n %s bytes\n' "$DB" "$DB-wal" "$DB-shm" 2>/dev/null
sqlite3 "$DB" 'PRAGMA journal_mode; PRAGMA page_size; PRAGMA wal_autocheckpoint;'

journal_mode must return wal for this investigation. page_size helps convert a frame count into an approximate page payload, although WAL headers and frame headers mean file bytes are not exactly frames × page_size. wal_autocheckpoint is a threshold in pages, not a promise that the file will be truncated at that size.

If the database directory is already read-only because its filesystem exhausted space or detected an error, stop database tuning and preserve kernel evidence. ext4 read-only containment covers that lower storage-layer incident; a checkpoint cannot make a damaged or full filesystem trustworthy.

Capture checkpoint progress without demanding an outage

From a maintenance connection, run a PASSIVE checkpoint and save its single result row:

DB=/srv/myapp/app.db
date -u +'%Y-%m-%dT%H:%M:%SZ'
sqlite3 -batch "$DB" 'PRAGMA wal_checkpoint(PASSIVE);'

The official wal_checkpoint reference defines the three returned integers as busy | log frames | checkpointed frames. PASSIVE copies as many frames as it can without waiting for readers or writers and never invokes the busy handler. For that reason, do not interpret a leading zero from PASSIVE as proof that the checkpoint completed. The useful evidence is the relationship between the second and third values.

Consider a result of 0|8120|1735. The WAL contains 8,120 frames, while only 1,735 were copied into the main database by the end of this checkpoint. Preserve the row, timestamp, WAL size, application latency, write rate, and process list. A second comparable sample can show whether the checkpoint frontier advances, stalls at nearly the same frame, or falls farther behind new commits.

sudo lsof "$DB" "$DB-wal" "$DB-shm"

lsof identifies processes with the files open, not the exact connection holding a read transaction. It narrows ownership to a service, worker, backup process, administration shell, or sidecar. Connection-pool telemetry and application traces must supply the remaining transaction identity.

Avoid escalating straight to FULL, RESTART, or TRUNCATE during live traffic. FULL can wait for writers and for readers to move to the newest snapshot. RESTART additionally waits until readers finish with the WAL, while TRUNCATE adds a zero-length file after successful completion. Those modes are operational actions with latency consequences, not better first measurements.

The oldest reader decides how far history can move

When a WAL-mode read transaction starts, SQLite records the last valid commit visible to that reader—its end mark. Writers may append newer commits, but the reader continues seeing one consistent snapshot. A checkpoint can copy frames concurrently until it would pass history still required by an active reader; then progress stops and resumes on a later checkpoint.

An open connection is not automatically a pinned reader. The material boundary is an active read transaction, including one kept open by an unconsumed result set, streaming response, ORM session, background export, dashboard query, or forgotten transaction scope. Conversely, a process can close and reopen connections while repeatedly creating overlapping readers, leaving no quiet interval in which the WAL can reset.

Application evidence should answer four questions:

1. Which code path began the read transaction?
2. When did it fetch or iterate the final row?
3. When did it commit, roll back, or close the cursor and transaction?
4. Did request cancellation, client disconnect, timeout, or exception bypass cleanup?

Add transaction-age and operation-name telemetry at the database wrapper, not by logging SQL parameters or row values. The goal is lifecycle evidence without leaking customer data or secrets. A useful trace records connection ID, transaction type, start and end timestamps, route/job name, rows consumed, and cleanup outcome.

WAL also depends on shared memory and same-host locking. SQLite states that all processes using a WAL database must run on the same host; network filesystems do not provide the required shared-memory model. NFS export identity recovery is relevant when a mount is already misbehaving, but repairing an NFS handle does not make network-hosted SQLite WAL a supported architecture.

Repair reader lifetime before tuning file limits

The durable fix is usually in application control flow. Bound a read transaction to the shortest coherent unit of work, consume results promptly, and guarantee cleanup in finally, defer, context-manager, or framework-equivalent logic. Do not hold the transaction open while calling another API, waiting on a queue, rendering a long stream, or sleeping between pages.

Connection pools need separate checks. Returning a connection to the pool is safe only after the framework has ended the transaction and finalized statements. Set request cancellation to close the cursor and roll back the transaction; then test a disconnected client, a timed-out export, and an exception halfway through iteration. A pool-size increase can make checkpoint starvation worse by allowing more overlapping long reads.

After releasing the identified readers, repeat PASSIVE samples under representative write traffic. Checkpointed frames should catch up to log frames, and subsequent writes should reuse the WAL rather than extending it without bound. Only then decide whether application-controlled checkpoints, a different autocheckpoint threshold, or journal_size_limit would improve the workload. A size limit controls post-checkpoint file retention; it cannot authorize overwriting history an active reader still needs.

Forced maintenance remains a fallback. Announce a write-latency window, stop admission of new long reads, let current transactions finish, and run the least aggressive mode that meets the goal. If zeroing the file is truly required, capture before/after results and application latency around TRUNCATE instead of treating a zero-byte file as the only success metric.

Close with a frame, backup, and durability record

Prove recovery across several normal checkpoint cycles, not one quiet command. Record WAL bytes, log frames, checkpointed frames, oldest application read-transaction age, write throughput, checkpoint duration, and request latency. Acceptance requires bounded WAL reuse, no persistent frame gap, and no new latency regression during the same representative workload.

Backup integrity is a separate gate. Copying only app.db while transactions are active can produce an inconsistent backup. SQLite’s backup guidance names the backup API, VACUUM INTO, and newer sqlite3_rsync as safe live-copy approaches; select one supported by the deployed SQLite version and rehearse restore. Vaultwarden operators can compare that boundary with tested application-aware backup and restore steps, where the database is only one part of the workload.

Keep the database, -wal, and -shm paths on a local filesystem with sufficient headroom, alert on growth rate plus checkpoint lag, and retain the application trace that identified the old reader. If the workload needs multiple writers, cross-host access, or consistently long analytical snapshots, the correct outcome may be a client-server database rather than increasingly aggressive SQLite checkpoints.

Continue through Voxfor database operations when the next task is capacity, migration, pooling, or recovery for another database engine. The decisive artifact for this incident remains small: two comparable checkpoint rows, one transaction owner, one lifecycle correction, and a restored WAL reuse pattern.