Optimizing wal_autocheckpoint for Continuous Logging

A continuous logging workload is the one write pattern that punishes SQLite’s default checkpoint threshold the hardest. A telemetry gateway, a diagnostic agent, or a Python automation worker appends rows to a single table thousands of times a minute, forever, with no natural idle window for the log to drain. In Write-Ahead Logging mode every one of those commits lands first in the -wal file, and the default PRAGMA wal_autocheckpoint of 1000 pages (~4 MB at the standard 4 KB page size) only schedules a checkpoint — it never caps the file. On a device where a dashboard query or an export cursor keeps a read snapshot open, the log sails straight past 4 MB and keeps growing. Lower the threshold naively to “fix” it and you swing to the opposite failure: a checkpoint firing every few hundred writes, hammering an SD card with erase cycles. This page tunes that single knob for the continuous-append case, as one page within Checkpoint Frequency Tuning under the WAL Optimization & Concurrency Tuning discipline. Sizing the WAL to a hard ceiling, throttling long-running readers, and correcting a genuinely stuck checkpoint are separate problems handled elsewhere; here the goal is picking and enforcing the right autocheckpoint page count.

Diagnosis

Confirm you have the continuous-logging variant of WAL growth — a cadence problem — and not a reader that is pinning the log open, before you touch the threshold. Two signals together are conclusive: the file that never drains, and the checkpoint that either can’t keep up or isn’t allowed to run.

First, watch the -wal file under steady write load. On a healthy continuous logger the file oscillates: it climbs to roughly the autocheckpoint size, a checkpoint folds it back, it climbs again. A file that only ever grows is the symptom:

import os, time

wal = "/var/lib/telemetry/logs.db-wal"
for _ in range(5):
    print(f"{time.strftime('%H:%M:%S')}  -wal = {os.path.getsize(wal)/1024:.0f} KiB")
    time.sleep(10)
# Climb-then-drop across samples == healthy. Monotonic climb == your problem.

Second, read the threshold actually in force and ask the checkpoint machinery what it managed to do. PRAGMA wal_checkpoint(PASSIVE) returns a three-integer row — (busy, log, checkpointed) — that tells you precisely why the file is not draining:

import sqlite3

conn = sqlite3.connect("/var/lib/telemetry/logs.db")
print("threshold:", conn.execute("PRAGMA wal_autocheckpoint;").fetchone()[0])   # 1000 == untouched default

busy, log, ckpt = conn.execute("PRAGMA wal_checkpoint(PASSIVE);").fetchone()
print(f"busy={busy}  log_frames={log}  checkpointed={ckpt}")

Read the result like this: log far above your intended page budget with busy=0 and ckpt == log means the checkpoint works fine and you simply left the threshold too high for your storage — a pure cadence fix, exactly what this page addresses. If instead ckpt is well below log (or busy=1), a live reader is holding an old snapshot and blocking truncation; no autocheckpoint value will help, and you should treat it as WAL file bloat on constrained storage instead.

-wal file size over time at two autocheckpoint thresholds Two stacked timelines of the same continuous-append logging workload. Top: with wal_autocheckpoint at 1000 pages the -wal file climbs to about 4 MB and holds a long high-water plateau before a widely spaced, large checkpoint drop, exposing reader-pin risk and big I/O bursts. Bottom: with wal_autocheckpoint at 256 pages the file oscillates in a tight roughly 1 MB sawtooth band with frequent small checkpoints, giving a flat storage and RAM footprint at the cost of more erase cycles.

Solution

Set the threshold once, at connection open, immediately after establishing WAL mode and before the first log write, then read it back and assert. The factory below configures a connection for continuous logging and nothing else — WAL as the journaling prerequisite, synchronous=NORMAL as the crash-safety baseline the checkpoint cadence depends on, and a page count chosen to keep the -wal file inside a predictable band. Every line that sets a durable value is commented with its trade-off:

import sqlite3
import logging

logger = logging.getLogger("continuous_logger")

def open_log_writer(db_path: str, checkpoint_pages: int = 256) -> sqlite3.Connection:
    """Open a continuous-logging connection with a verified wal_autocheckpoint."""
    # isolation_level=None -> autocommit: each PRAGMA runs immediately instead of
    # being deferred inside an implicit BEGIN that could silently discard it.
    conn = sqlite3.connect(db_path, timeout=15.0, isolation_level=None)

    # WAL is the prerequisite for concurrent readers + a drainable log. This is a
    # persistent header change, but re-asserting it costs nothing and documents intent.
    mode = conn.execute("PRAGMA journal_mode=WAL;").fetchone()[0]
    if mode.lower() != "wal":
        conn.close()
        raise RuntimeError(f"expected WAL, got {mode!r} -- checkpoint tuning is moot without it")

    # NORMAL defers fsync to checkpoint time, so commits never block on a flush --
    # essential for a high-rate logger. Durability across power loss still holds; only
    # the last few pre-crash commits are at risk. (0=OFF, 1=NORMAL, 2=FULL, 3=EXTRA)
    conn.execute("PRAGMA synchronous=NORMAL;")

    # THE knob. 256 pages ~= 1 MiB at the 4 KiB page size: small enough to keep the
    # -wal file and its OS page-cache footprint flat on constrained flash, large enough
    # that a checkpoint fires roughly once per ~256 appended pages rather than per write
    # (which would multiply SD/eMMC erase cycles). Setting this returns no row.
    conn.execute(f"PRAGMA wal_autocheckpoint={checkpoint_pages};")

    # Read the value BACK from SQLite -- not from your variable -- and assert it stuck.
    applied = conn.execute("PRAGMA wal_autocheckpoint;").fetchone()[0]
    if applied != checkpoint_pages:
        conn.close()
        raise RuntimeError(
            f"wal_autocheckpoint not applied: wanted {checkpoint_pages}, got {applied}"
        )

    logger.info("continuous-logger wal_autocheckpoint verified at %d pages (~%.1f KiB)",
                applied, applied * 4096 / 1024)
    return conn

Pick checkpoint_pages from your flush cadence, not by copying a number. Multiply your steady append rate (pages committed per second) by the checkpoint interval you can tolerate on the target media: a device that can absorb a checkpoint every second at ~250 pages/s wants a threshold near 256; a desktop NVMe target that appends in larger bursts can sit at 512–1000 without wear concerns. Keep the byte size of the threshold well under any memory-mapped I/O window you configure so the WAL working set never crowds the mmap region. Do not reach for wal_autocheckpoint=0 to “disable” checkpointing here — that turns a bounded log into an unbounded one and guarantees the growth you are trying to prevent.

Verification

Three checks, cheapest first.

The read-back is already baked into the factory: a startup that logs continuous-logger wal_autocheckpoint verified at 256 pages proves the value reached this connection. Treat the absence of that line as a failed deployment.

Next, prove the log actually drains under sustained writes. Append past the threshold and confirm the -wal file returns to a small size rather than climbing monotonically:

import os

wal = db_path + "-wal"
for i in range(2000):                       # ~2000 rows > 256-page threshold
    conn.execute("INSERT INTO logs(ts, level, msg) VALUES (?,?,?)",
                 (i, "INFO", f"event {i}"))
size_kib = os.path.getsize(wal) / 1024
print(f"-wal after burst = {size_kib:.0f} KiB")
assert size_kib < 2048, "WAL did not drain -- reader pin or threshold not applied"

Finally, confirm the checkpoint reports clean completion — busy=0 and every logged frame reclaimed — so you know the cadence, not a stuck snapshot, governs the file size:

busy, log, ckpt = conn.execute("PRAGMA wal_checkpoint(PASSIVE);").fetchone()
assert busy == 0 and ckpt == log, f"checkpoint incomplete: busy={busy} log={log} ckpt={ckpt}"

Failure Modes & Gotchas

A connection pool vends handles that never saw your factory. wal_autocheckpoint is set per connection, and a fresh handle inherits the 1000-page default. If a pool or ORM opens connections outside open_log_writer, the write path you tuned and the write path in production are different connections — the read-back assertion never runs on the latter, and the WAL bloats exactly as before. Route every logging connection through one initializer (a pool on_connect hook, SQLAlchemy’s connect event) and assert the value inside it, the same recycled-handle discipline covered in connection pooling strategies.

A long-lived reader silently converts your cadence fix into unbounded growth. The threshold only schedules a passive checkpoint; the checkpoint can only truncate frames older than the oldest live read snapshot. One dashboard connection that opens a transaction and forgets to close it pins the WAL, and no autocheckpoint value — however low — will drain it. If your wal_checkpoint(PASSIVE) row shows checkpointed < log, stop tuning the threshold and go fix the reader, then bound the file for real with journal_size_limit or an explicit wal_checkpoint(TRUNCATE) as described in threshold tuning for high-write workloads.

Too small a threshold trades storage for flash lifespan. Driving the count down to a handful of pages to keep the -wal file tiny makes a checkpoint fire almost every write, and each checkpoint is an fsync() plus a write-back that SD cards and eMMC amortize into erase cycles. On wear-sensitive media that shortens device life measurably while barely shrinking RAM use. Size the threshold to your flush cadence — not to the smallest number that “works” — and pair it with synchronous=NORMAL (detailed in configuring the synchronous PRAGMA for crash safety) so commits are not each forcing a separate flush on top of the checkpoint.