Header Ad Placement

SQLite WAL Checkpoints: Why a Successful Commit Can Leave the Log Growing

DATABASES AND STORAGE

A committed update, an old reader snapshot and an unfinished checkpoint can coexist. A small reproducible test shows how to tell them apart without deleting database files.

By Hossam

KEY FINDINGS

  • Committing a WAL transaction and copying its pages into the main database are different operations. A growing WAL does not, by itself, prove that commits are failing.
  • A long-lived reader can keep an older snapshot while another connection successfully writes. A PASSIVE checkpoint can return a leading zero without copying every frame.
  • Compare the checkpoint's log and checkpointed counts, transaction lifetime and subsequent progress. File size alone cannot distinguish retained allocation from outstanding work.
  • This guide includes an original, disposable two-connection test, not a throughput benchmark or a durability test. Never delete a live WAL to make a graph look healthier.

The operational mistake: treating one database as one file

An application reports that its update committed, but the database file's size barely changes and the adjacent -wal file keeps growing. It is tempting to decide that the update is stuck, that SQLite ignored COMMIT, or that the extra file is disposable. In write-ahead logging mode, none of those conclusions follows from the file sizes alone.

SQLite's WAL documentation describes a different storage path: changes are appended to the write-ahead log, and a commit record establishes a transaction boundary there. A later checkpoint copies eligible content back into the main database. Meanwhile, readers can keep using an earlier snapshot. That separation is useful concurrency, but it gives operators several different kinds of progress to measure.

This guide answers a specific question: how can a write succeed while a checkpoint remains incomplete? It then turns the answer into a safe diagnostic sequence. The goal is to identify the transaction holding back progress before changing checkpoint policy. It is not a recommendation to force truncation whenever a WAL file looks large.

Three questions that need separate evidence

First, did the transaction commit? That concerns the writer's result and what a suitably fresh connection or transaction can read. Second, how much WAL content has been copied into the main database? That concerns checkpoint progress. Third, can SQLite restart or truncate the WAL? That adds conditions about readers still using the log and the checkpoint mode requested.

A successful answer to the first question does not automatically answer the other two. An existing reader can legitimately report the old value after a different connection commits the new one. Nor does copying all eligible pages require the operating system to show a smaller WAL file immediately. SQLite commonly reuses an allocated WAL instead of truncating it after every checkpoint.

This distinction is related to a broader storage-design question: when does accepting a write differ from materializing it into the structure used for later reads? Our earlier AirDB analysis examines that separation in a different database architecture. SQLite's mechanisms and guarantees are its own; the comparison is a way to ask better operational questions, not a claim that the two systems share an implementation.

Why a reader can hold a checkpoint back

The official isolation guide explains that WAL mode gives a read transaction a stable snapshot. A reader starts a transaction and actually reads data. Another connection can then commit an update. Further reads within the first transaction still see the earlier state until that transaction ends and a new view is taken.

Internally, the WAL guide describes a reader's end mark. A checkpoint must not copy content into the main database in a way that destroys the older view an active reader still needs. It therefore stops at the relevant safe boundary. This is not evidence that the writer's later transaction was uncommitted. It is evidence that retaining the older read view has a storage consequence.

The practical investigation starts with reader lifetime. Look for transactions held open while an application waits on remote calls, streams a response slowly, or performs unrelated work. Those are diagnostic possibilities, not a finding about your application. Instrument transaction start and end points so you can test which explanation applies.

Also distinguish a database connection from a read transaction. An open connection is not automatically a permanently pinned snapshot. In the demonstration below, BEGIN followed by SELECT establishes the read transaction we intentionally retain. Fetching a row does not end that explicit transaction; COMMIT does.

How to read a checkpoint result without overclaiming

PRAGMA wal_checkpoint(PASSIVE) returns a row of three integers. The official PRAGMA reference describes the first as the result indicator corresponding to success or SQLITE_BUSY, followed by the log count and the checkpointed count. The C API describes its output counts in WAL frames. Do not interpret these counts as a count of SQL statements or committed business operations.

PASSIVE does as much checkpoint work as it can without waiting for readers or writers, and it does not invoke the busy handler. Consequently, a leading zero is not a certificate that the entire log was copied. Compare the second and third values. If the log count exceeds the checkpointed count, outstanding work remains at the end of that call.

There is another important detail: the reported checkpointed count includes frames already checkpointed before the current call. It is not necessarily the amount of new work performed by this one invocation. Treat the result as a view of the log's checkpoint state, not an incremental throughput counter.

If the database is not in WAL mode, the two counts can be -1. A successful TRUNCATE checkpoint returns zero counts after truncating the log. These cases are why a useful incident record includes the requested checkpoint mode, the actual journal mode, the full result row and the timestamp, rather than just a single success flag.

Checkpoint modes solve different problems

PASSIVE favors doing available work without waiting. FULL waits, subject to the busy-handler behavior, for conditions that let it checkpoint the log completely. RESTART adds the requirement needed for the next writer to restart the WAL from the beginning. TRUNCATE additionally reduces the WAL file to zero bytes on successful completion.

Those stronger modes are not interchangeable with a harmless status query. The checkpoint API documents writer-lock and waiting behavior for FULL, RESTART and TRUNCATE. If the busy handler stops waiting, they can leave work incomplete and return SQLITE_BUSY. Plan such maintenance around the application's latency and concurrency requirements rather than issuing increasingly aggressive commands in a loop.

For ordinary diagnosis, a single PASSIVE attempt can provide useful state while also doing real work. It is not read-only monitoring. If you need a monitoring-only API, current documentation also describes NOOP, but check whether your deployed SQLite version supports it before relying on that option. The example uses PASSIVE because it deliberately demonstrates checkpoint behavior, not passive observation.

Prerequisites and a version warning

Run the example only against the temporary database it creates on local storage. You need Python 3 with its sqlite3 module and permission to create temporary files. The script uses two independent connections in one thread and explicit SQL transaction boundaries through isolation_level=None. It does not use shared-cache mode, network filesystems, concurrent threads or an application's production database.

Check the SQLite runtime, not only the Python version. The current WAL documentation reports a rare WAL-reset corruption race affecting older releases through 3.51.2, fixed in 3.51.3 and later, with fixes also backported to 3.44.6 and 3.50.7. Confirm your vendor's actual patched build before deploying concurrent writers and checkpoints. This is separate from ordinary checkpoint starvation; a long-lived reader is not proof that this bug occurred.

The demonstration was executed with Python 3.10.12 and SQLite 3.37.2. That is an older, affected library and is not a recommended production version. Its strictly sequential test establishes the illustrated snapshot/checkpoint behavior only. It does not exercise the timing race, establish crash safety, or certify an unpatched installation. Use a supported patched build for real deployments.

Assumptions and limitations

A production diagnosis should start by recording the journal mode, SQLite runtime and vendor patch status, the current checkpoint policy and the full result of one planned checkpoint attempt. Check whether reader transactions end promptly and whether writers continue to commit. Compare subsequent observations at a cadence appropriate to your workload; do not turn a diagnostic into a rapid checkpoint loop.

If an old reader is the cause, shorten or correctly close the relevant transaction when application semantics allow. Do not kill transactions blindly. Recheck progress after the reader ends. If that does not explain the behavior, investigate competing checkpoint operations, write volume, storage errors and application configuration instead of repeatedly assuming the same cause. This example intentionally isolates one cause, not every possible SQLITE_BUSY condition.

SQLite's default automatic checkpoint threshold is 1000 pages unless configured differently. That is a trigger, not a guaranteed ceiling on log size. The default mechanism uses PASSIVE checkpoints. Disabling it can make unchecked growth easier, while lowering its threshold can move more checkpoint work onto commits. There is no universal threshold recommended by this test; measure application latency, write activity and storage behavior before tuning it.

A WAL file that remains allocated after checkpointing is not necessarily full of uncheckpointed work. Conversely, a successful commit is not permission to copy only the main database file as a backup. SQLite's documentation says the WAL is part of the database's persistent state; separating it from the database can lose committed data or cause corruption. Do not manually delete, truncate or detach a live -wal file. For a live backup, use an appropriate SQLite-aware mechanism such as the Online Backup API, and verify the resulting backup and restore procedure. Python exposes a Connection.backup method, but this article has not tested your backup workflow.

Commit visibility and power-loss durability are also separate. In WAL mode, synchronous=FULL and synchronous=NORMAL have different synchronization behavior; NORMAL can lose recently committed transactions after a power failure or system crash even though WAL consistency is maintained. This guide does not change that setting, simulate a power failure, or claim storage hardware guarantees. Read the official synchronous documentation and apply the durability requirements of your application.

The useful result is a diagnosis, not an empty file. If the writer commits, a fresh read sees the update, an old read retains its snapshot, and checkpoint progress resumes after that old transaction ends, you have identified a concrete transaction-lifetime problem. If any of those observations differs, preserve the evidence and investigate the different condition. A smaller log is not worth losing the distinction between committed, checkpointed and safely backed up.

Worked Example: One committed update and one pinned snapshot

The original script below produced Reader before: 1, Writer after: 2, Reader pinned: 1 and Pinned checkpoint: (0, 1, 0) in the stated local environment. After the reader's COMMIT it produced Reader renewed: 2 and After release: (0, 0, 0). Exact frame counts can differ with builds and layout; the assertions test the relationship that matters. Auto-checkpointing is disabled only on this disposable writer so the manual steps remain visible. The final TRUNCATE is attempted only after the example releases its reader and has no other workload.

import sqlite3
import tempfile as tf
from pathlib import Path

def one(c, sql):
    row = c.execute(sql).fetchone()
    return row[0]

with tf.TemporaryDirectory() as d:
    p = str(Path(d) / 'demo.db')
    w = sqlite3.connect(
        p, isolation_level=None)
    r = sqlite3.connect(
        p, isolation_level=None)
    ck = 'PRAGMA wal_checkpoint'
    get = 'SELECT v FROM t'
    print('SQLite:',
          sqlite3.sqlite_version)
    print('Mode:', one(w,
          'PRAGMA journal_mode=WAL'))
    ac = 'PRAGMA wal_autocheckpoint'
    w.execute(ac + '=0')
    w.execute(
        'CREATE TABLE t(v INTEGER)')
    w.execute(
        'INSERT INTO t VALUES (1)')
    w.execute(ck + '(TRUNCATE)')
    r.execute('BEGIN')
    print('Reader before:',
          one(r, get))
    w.execute('UPDATE t SET v=2')
    print('Writer after:',
          one(w, get))
    print('Reader pinned:',
          one(r, get))
    a = w.execute(
        ck + '(PASSIVE)').fetchone()
    print('Pinned checkpoint:', a)
    assert a[0] == 0 and a[1] > a[2]
    r.execute('COMMIT')
    print('Reader renewed:',
          one(r, get))
    b = w.execute(
        ck + '(TRUNCATE)').fetchone()
    print('After release:', b)
    assert b == (0, 0, 0)
    r.close()
    w.close()

Sources & Provenance

This is an original operational explanation with an independently executed disposable example. Official documentation supplies the database semantics. It is not a product benchmark, a production incident report, a complete backup guide, or a claim of testing on every supported SQLite release.

Comments

Kubernetes Liveness Is Not Readiness: Why a Health Check Can Amplify an Outage

KUBERNETES FAILURE RECOVERY A failed health check is an observation. Restarting a container and withholding traffic are different responses, and choosing the wrong one can amplify an outage. By Hossam The important question is what failure should trigger A web application loses access to its shared database. Its processes are still running, but requests that require database access fail. A health endpoint reports the dependency problem. Should Kubernetes restart every application container, or stop directing those requests to it? The endpoint alone cannot choose the right recovery action. Kubernetes separates startup, liveness and readiness probes because they answer different operational questions. Startup protects initialization, liveness can trigger container termination and restart handling, and readiness controls eligibility for traffic from matching Services. A green check is meaningful only within the check and response that were designed. This is a documentation-based de...

Editorial Governance & Pages