WAL Checkpoints, Autocheckpointing, Long Readers, and WAL Growth

Learning outcomes

A committed WAL transaction is durable according to the configured guarantees even if its pages have not yet been copied to the main database. A checkpoint performs that copy. Checkpoints are not cosmetic file cleanup: their scheduling affects WAL size, read cost, commit latency distribution, and operational behavior under long readers.

Checkpointing closes the loop

Writers append committed frames to the WAL; readers may depend on those frames for their snapshots. Eventually SQLite wants the latest safe committed page versions back in the main database so the WAL can be reused/reset. That transfer is checkpointing.

write path:       main.db  +  append committed frames -> main.db-wal
                                              |
                                              v
checkpoint path:  main.db  <- copy safe frames --------+

constraint: a checkpoint must not overwrite a main-db page in a way
            that breaks an older reader snapshot.

The checkpoint stops at readers’ end marks

Suppose reader R began before a new WAL transaction. R expects the older main-database page. If a checkpoint overwrote that main page with a version newer than R’s snapshot while R still needed it, isolation would break. So the checkpointer stops when it reaches WAL content beyond a current reader’s safe boundary. SQLite records checkpoint progress and resumes later.

Autocheckpoint: useful default, not a universal tuning target

SQLite normally enables automatic PASSIVE checkpointing when a commit causes the WAL to reach about 1000 pages. The compile-time option SQLITE_DEFAULT_WAL_AUTOCHECKPOINT can change that default, and applications can change it per connection with PRAGMA wal_autocheckpoint=N. Therefore, record the observed value instead of assuming every bundled SQLite uses exactly 1000.

PRAGMA journal_mode;        -- should be wal for this labPRAGMA page_size;           -- useful for translating pages to approximate bytesPRAGMA wal_autocheckpoint;  -- normally 1000 unless build/runtime changed it

Checkpoint modes: progressively stronger goals

Use the least aggressive operation that serves the operational goal. Repeated TRUNCATE checkpoints on every request are not a substitute for understanding workload shape.

Read the checkpoint result tuple

PRAGMA wal_checkpoint(...) returns one row of three integers. The first indicates whether a blocking checkpoint mode was prevented from completing (1 corresponds to BUSY; otherwise 0). The second is the number of WAL frames/pages in the log, and the third is how many had been checkpointed when the operation ended. If WAL is not active, the latter values can be -1.

PRAGMA wal_checkpoint(NOOP);-- current SQLite supports NOOP for reading checkpoint counters without checkpointingPRAGMA wal_checkpoint(PASSIVE);-- compare log-frame count with checkpointed-frame count

Do not hard-code exact frame counts from a tutorial: schema creation, page size, row size, and runtime details change them. Interpret relationships such as “log frames are greater than checkpointed frames while an old reader is open.”

Monitoring lab: deliberately create a pinned old reader

This lab disables autocheckpoint so manual behavior is easy to observe. It creates a clean WAL baseline, starts a reader snapshot, writes many rows from another connection, and measures the WAL file before attempting a PASSIVE checkpoint.

import sqlite3from pathlib import Pathp = Path("wal-checkpoint-lab.db")for x in (p, Path(str(p)+"-wal"), Path(str(p)+"-shm")):    x.unlink(missing_ok=True)admin = sqlite3.connect(p, isolation_level=None)assert admin.execute("PRAGMA journal_mode=WAL").fetchone()[0] == "wal"admin.execute("PRAGMA wal_autocheckpoint=0")admin.execute("CREATE TABLE event(id INTEGER PRIMARY KEY, payload TEXT NOT NULL)")admin.execute("INSERT INTO event(payload) VALUES('baseline')")admin.execute("PRAGMA wal_checkpoint(TRUNCATE)")reader = sqlite3.connect(p, isolation_level=None)writer = sqlite3.connect(p, isolation_level=None)reader.execute("BEGIN")reader.execute("SELECT COUNT(*) FROM event").fetchone()  # establish old snapshotfor batch in range(20):    writer.execute("BEGIN IMMEDIATE")    writer.executemany(        "INSERT INTO event(payload) VALUES(?)",        [(f"batch-{batch}-" + "x"*300,)] * 40,    )    writer.execute("COMMIT")wal = Path(str(p)+"-wal")print("wal bytes before checkpoint:", wal.stat().st_size)print("PASSIVE:", writer.execute("PRAGMA wal_checkpoint(PASSIVE)").fetchone())print("reader still sees:", reader.execute("SELECT COUNT(*) FROM event").fetchone()[0])# expected reader count: 1, even though writer committed many new rowsreader.execute("COMMIT")print("TRUNCATE after reader ends:", writer.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone())print("wal bytes after truncate:", wal.stat().st_size if wal.exists() else 0)reader.close(); writer.close(); admin.close()

On the old reader, the count remains 1. While it is open, the checkpoint should be unable to complete/reset all post-snapshot WAL work. After the reader ends, a successful TRUNCATE checkpoint can reduce the WAL to zero bytes. Exact intermediate counters vary; the direction and snapshot behavior are the lesson.

Why WAL growth matters

SQLite’s wal-index keeps lookup efficient, but official documentation notes that read performance can deteriorate as the WAL grows. Large WALs also consume disk space. Common causes include disabled autocheckpointing, long/continuous readers that prevent checkpoint completion, or very large write transactions that naturally generate many frames before reset is possible.

Checkpoint ownership is an application architecture choice

The default—automatic PASSIVE checkpoints triggered by commits—is intentionally simple and often sufficient. Applications with strict latency goals may disable or adjust autocheckpointing and assign a maintenance thread/process to checkpoints during quieter periods. That adds responsibility: monitoring, failure handling, shutdown behavior, and ensuring checkpoints are not permanently starved.

A monitoring record worth keeping

If WAL operation matters in production, periodic observability can capture: database path/identity, SQLite version/source ID, journal mode, page size, WAL byte size, autocheckpoint threshold, checkpoint result tuple, oldest known application reader age, write transaction durations, BUSY counts, and checkpoint duration. These measurements turn “WAL got big” into a diagnosable timeline.

Checkpoint lab review

Production judgment and bridge

WAL maintenance should usually be boring: bounded transaction lifetimes, normal autocheckpointing or one documented checkpoint owner, metrics, and no manual companion-file deletion. Lesson 5 adds the reliability history and deployment rules that determine whether this concurrency architecture is production-ready for your environment.

Authoritative references

• SQLite — Write-Ahead Logging: checkpointing and WAL growth.
• SQLite — PRAGMA wal_autocheckpoint and wal_checkpoint.
• SQLite C API — Checkpoint a database.
• SQLite — PRAGMA page_size.