A checkpoint records progress that lets a database recover without treating its entire log history as outstanding work. The exact operation depends on the engine. PostgreSQL synchronizes the data changes required for a checkpoint and establishes a redo starting point. SQLite in WAL mode transfers eligible committed page frames from its WAL into the main database file. Neither operation is simply another name for transaction commit.

When it breaks

Checkpoint requests and complete checkpoints are different observations. SQLite readers can hold snapshots that prevent newer WAL frames from being copied into the database file or prevent the log from being reset. A passive checkpoint can return successfully after doing only the work currently possible. Reading only its success flag hides the remaining frame count and the reader that still prevents completion.

Log size also needs context. Reusing an existing file can preserve its allocated size while useful checkpoint progress advances. PostgreSQL can retain older WAL for archives or replication slots even after a checkpoint. Removing log files manually to make a graph smaller can destroy recovery material. Check the engine’s progress markers and retention obligations before equating filesystem size with unfinished recovery work.

An interval is not a hard recovery-time bound. Replay speed, storage throughput, workload shape, and the actual crash point affect recovery duration. More frequent checkpoints may reduce later replay while increasing normal write traffic. Moving WAL onto another device does not move all the data-page writes there or make the checkpoint’s cost disappear.

Check it quickly

Save the following as checkpoint_lab.py and run python3 checkpoint_lab.py with assertions enabled. It uses Python’s standard sqlite3 module and private temporary files. The recorded run used Python 3.14.4 with SQLite 3.53.0. Check your runtime’s SQLite version when comparing results, since the Python and database engine versions are distinct.

import sqlite3
import tempfile
from pathlib import Path

with tempfile.TemporaryDirectory(prefix="xbcatm-checkpoint-") as directory:
    path = Path(directory) / "test.db"
    writer = sqlite3.connect(path, isolation_level=None, timeout=0.1)
    reader = sqlite3.connect(path, isolation_level=None, timeout=0.1)
    try:
        assert writer.execute("PRAGMA journal_mode=WAL").fetchone() == ("wal",)
        writer.execute("PRAGMA synchronous=FULL")
        writer.execute("PRAGMA wal_autocheckpoint=0")
        writer.execute("CREATE TABLE marker (id INTEGER PRIMARY KEY, value INTEGER)")
        writer.execute("INSERT INTO marker VALUES (1,10)")
        assert writer.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone() == (0, 0, 0)
        reader.execute("BEGIN")
        assert reader.execute("SELECT value FROM marker").fetchone() == (10,)
        writer.execute("UPDATE marker SET value=20 WHERE id=1")
        assert writer.execute("SELECT value FROM marker").fetchone() == (20,)
        passive = writer.execute("PRAGMA wal_checkpoint(PASSIVE)").fetchone()
        blocked = writer.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone()
        print("passive", passive)
        print("truncate_with_reader", blocked)
        assert passive[0] == 0 and passive[1] > passive[2] >= 0
        assert blocked[0] == 1 and blocked[1] > blocked[2]
        assert Path(str(path) + "-wal").stat().st_size > 0
        assert reader.execute("SELECT value FROM marker").fetchone() == (10,)
        reader.execute("COMMIT")
        completed = writer.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone()
        print("truncate_after_reader", completed)
        assert completed == (0, 0, 0)
        assert Path(str(path) + "-wal").stat().st_size == 0
        assert reader.execute("SELECT value FROM marker").fetchone() == (20,)
        print("fresh_reader", 20)
    finally:
        reader.close()
        writer.close()

The initial truncate checkpoint moves value ten into the database file and resets the WAL. A reader then opens an explicit transaction and reads ten. Another connection commits value twenty with automatic checkpointing disabled. The writer can read twenty immediately, while the old reader still needs its original snapshot. This schedule makes the constraint visible without relying on concurrent thread timing.

In the recorded run, passive checkpoint output is (0, 1, 0): no blocking failure is reported, one frame exists, and none is checkpointed. The assertions deliberately check the relationship between frame counts instead of requiring that exact count on every engine build. The meaningful result is that a successful passive call left work behind.

The truncate request next returns (1, 1, 0) after the short busy wait. Its first field signals that the requested completion was blocked. The WAL remains nonempty, and the old reader still sees ten. The Python call returning a tuple is therefore not evidence that truncation succeeded; the tuple’s status must be inspected.

After the reader commits, another truncate checkpoint returns (0, 0, 0). The script checks the actual WAL length is zero and a fresh read sees twenty. The update was already committed before this final checkpoint. Both connections close and the temporary directory is removed even if an assertion fails.

Diagnose the work that remains

For SQLite, inspect long-lived read transactions and unfinished cursors when checkpoints cannot finish. Decide whether passive progress is sufficient or a blocking checkpoint belongs in a controlled maintenance window. Increasing the busy timeout merely waits longer for the same constraint; it does not release an application transaction. Forcing truncation after every write can add contention and defeat the concurrency benefit of WAL mode.

For PostgreSQL, use checkpoint and WAL-retention statistics appropriate to the deployed version. A completed checkpoint can coexist with retained log files and ongoing transactions. Compare foreground latency and a measured restart rehearsal when changing checkpoint frequency. This fixture verifies SQLite’s completion boundary; it does not measure a production recovery-time objective or claim identical checkpoint APIs across database engines.

Sources