Batching Inserts to Cut Checkpoint Pressure

A telemetry writer that commits one row at a time is the classic edge-device performance trap. Each INSERT in its own transaction appends a frame to the write-ahead log and, under a durable synchronous setting, forces an fsync — so a sensor emitting 200 readings a second produces 200 commits, 200 WAL-frame appends, and 200 syncs per second. That cadence drives the WAL toward its autocheckpoint threshold constantly, so the writer spends its time checkpointing instead of writing, and the flash underneath wears from the relentless sync-and-checkpoint churn. Wrapping N inserts in a single explicit transaction amortizes all of it: one BEGIN/COMMIT, one sync, and WAL growth paced to the batch rather than the row. This page sizes that batch precisely, as one case within Threshold Tuning for High-Write Workloads, part of the broader WAL Optimization & Concurrency Tuning discipline. The tension the whole page turns on: too small a batch keeps the checkpoint pressure, while too large a batch delays durability, grows the WAL past its checkpoint trigger, and risks losing the in-flight batch on a crash.

Diagnosis

The symptom is a writer that is busy but slow, with a WAL that grows and checkpoints far more often than the data volume justifies. Confirm it by comparing commits to rows, then by watching the WAL.

The defining ratio is commits per second versus rows per second. If they are equal, every row is its own transaction — the exact anti-pattern batching fixes:

# Instrument the write path: count COMMITs and rows over a fixed window.
# commits/sec == rows/sec  ->  one row per transaction: maximum WAL + fsync overhead.
# commits/sec << rows/sec  ->  already batching; look elsewhere.
print(f"commits/sec={commits/window:.0f} rows/sec={rows/window:.0f} ratio={rows/max(commits,1):.1f}")

Then read the WAL’s actual state. Each committed transaction appends frames; wal_checkpoint in passive mode reports the WAL size and how much of it a checkpoint could reclaim, without forcing a blocking checkpoint:

PRAGMA wal_autocheckpoint;     -- pages of WAL growth that auto-triggers a checkpoint (default 1000)
PRAGMA page_size;              -- WAL frame ~= page_size + 24 byte header; converts frames to bytes
PRAGMA wal_checkpoint(PASSIVE);-- returns (busy, wal_pages, reclaimed_pages) without blocking writers

A high commit rate with a WAL that keeps hitting wal_autocheckpoint (default 1000 pages) tells you checkpoints are firing on frame count, not on time or data need. Watch it from outside to see the flash cost directly:

# The -wal file growing and shrinking rapidly in a tight loop == checkpoints firing constantly.
# fsync-per-commit shows as a write-heavy, sync-heavy profile disproportionate to row bytes.
watch -n1 'ls -l /var/lib/telemetry/sensors.db-wal' ; iostat -x 2

If commits track rows one-to-one, the WAL repeatedly reaches its autocheckpoint threshold, and the device syncs far more than the payload size warrants, the fix is to batch inserts into transactions sized to a sensible frame count — enough to amortize the sync, not so much that the WAL overruns its trigger before you commit.

Figure — One row per commit forces a WAL frame and fsync each time, hammering the checkpoint trigger; a batched transaction pays those costs once and paces WAL growth to the batch.

Per-row commits versus a batched transaction and their effect on WAL and checkpoint pressure Top row: many small per-row commits each append a WAL frame and force an fsync, so the WAL repeatedly hits its autocheckpoint threshold and checkpoints fire constantly. Bottom row: N rows wrapped in one transaction produce a single commit and one fsync, with WAL growth paced to the batch and far fewer checkpoints. one row per commit commit commit commit commit × N → N fsyncs WAL hits autocheckpoint checkpoints fire constantly N rows per transaction BEGIN … executemany … COMMIT one commit, one fsync for N rows WAL paced to the batch few checkpoints

Solution

Wrap the inserts in one explicit transaction and hand SQLite the whole batch with executemany, sizing the batch so its WAL frames stay comfortably under wal_autocheckpoint. The routine below computes a batch size from the checkpoint threshold, drives one transaction per batch, and reads back both the row count and the WAL state so you can prove the amortization worked:

import sqlite3
import logging

logger = logging.getLogger("sqlite_batch_writer")


def batch_size_for_checkpoint(conn: sqlite3.Connection, target_fraction: float = 0.5) -> int:
    """Rows per transaction so its WAL frames stay under ~half the autocheckpoint threshold."""
    autockpt = conn.execute("PRAGMA wal_autocheckpoint;").fetchone()[0]   # pages that trigger checkpoint
    if autockpt <= 0:                       # autocheckpoint disabled -> pick a safe fixed batch
        return 500
    # A row is far smaller than a page, but be conservative: cap so the batch's frames stay
    # well below the trigger, leaving headroom for index pages touched by the same inserts.
    return max(50, int(autockpt * target_fraction))


def write_batches(db_path: str, rows, timeout: float = 15.0) -> int:
    conn = sqlite3.connect(db_path, timeout=timeout, isolation_level=None)  # manual txn control
    written = 0
    try:
        conn.execute("PRAGMA journal_mode=WAL;")        # WAL is what makes frame-paced batching work
        conn.execute("PRAGMA synchronous=NORMAL;")      # one fsync at checkpoint, not per commit
        conn.execute(f"PRAGMA busy_timeout={int(timeout * 1000)};")  # ms: wait out a checkpoint, don't fail
        n = batch_size_for_checkpoint(conn)

        buffer = []
        for row in rows:
            buffer.append(row)
            if len(buffer) >= n:
                conn.execute("BEGIN IMMEDIATE;")        # take the write lock once for the whole batch
                conn.executemany(
                    "INSERT INTO readings(sensor, value, ts) VALUES (?, ?, ?);", buffer)
                conn.execute("COMMIT;")                 # single fsync amortized across n rows
                written += len(buffer)
                buffer.clear()
        if buffer:                                      # flush the tail so no rows are stranded
            conn.execute("BEGIN IMMEDIATE;")
            conn.executemany(
                "INSERT INTO readings(sensor, value, ts) VALUES (?, ?, ?);", buffer)
            conn.execute("COMMIT;")
            written += len(buffer)

        # Read back: confirm the rows landed and the WAL did not blow past its trigger mid-run.
        wal_pages = conn.execute("PRAGMA wal_checkpoint(PASSIVE);").fetchone()[1]
        logger.info("wrote %d rows, batch=%d, wal_pages_after=%s", written, n, wal_pages)
        return written
    except sqlite3.Error:
        conn.rollback()                                 # discard the current uncommitted batch cleanly
        logger.exception("batched write failed for %s", db_path)
        raise
    finally:
        conn.close()

The important decisions: BEGIN IMMEDIATE acquires the write lock up front so the batch does not begin optimistically as a reader and fail late with SQLITE_BUSY when it tries to upgrade. The batch size is derived from wal_autocheckpoint (default 1000 pages) at half the threshold, so a batch’s frames plus the index pages it dirties stay under the trigger and the WAL is checkpointed between batches rather than mid-batch. synchronous=NORMAL is what turns per-commit fsync into per-checkpoint fsync under WAL — the durability trade-off is examined in synchronous=NORMAL vs FULL Trade-off Matrix. And the tail flush guarantees the final partial batch is not silently dropped when the input ends.

Verification

Prove the rows landed, then prove the batching actually reduced the commit and checkpoint pressure.

First, assert every input row was written — the tail-flush bug (a dropped final partial batch) hides exactly here:

written = write_batches("/var/lib/telemetry/sensors.db", sample_rows)
conn = sqlite3.connect("/var/lib/telemetry/sensors.db")
count = conn.execute("SELECT count(*) FROM readings;").fetchone()[0]
assert count >= written, f"row loss: wrote {written} but table holds {count}"
assert written == len(sample_rows), f"batching dropped rows: {written} of {len(sample_rows)}"

Second, confirm the WAL stayed within its checkpoint budget during the run instead of overrunning the trigger. A passive checkpoint reports the current WAL size in pages:

busy, wal_pages, reclaimed = conn.execute("PRAGMA wal_checkpoint(PASSIVE);").fetchone()
autockpt = conn.execute("PRAGMA wal_autocheckpoint;").fetchone()[0]
# WAL should sit at or below the trigger between batches; far above it means the batch is too large.
assert wal_pages <= autockpt * 2, f"WAL grew to {wal_pages} pages vs trigger {autockpt}: shrink the batch"

If the row count matches and the WAL sits near or below wal_autocheckpoint between batches, the batch size is right: the commits-per-second has dropped by roughly the batch factor while no rows were lost and the WAL never ran away. A WAL that climbs far past the trigger means the batch is too large and should be shrunk toward the checkpoint threshold.

Failure Modes & Gotchas

Too large a batch delays durability and grows the WAL past its autocheckpoint trigger. Batching trades per-row durability for throughput — the rows in the current transaction are not durable until COMMIT, so a batch of 50,000 rows means up to 50,000 rows vanish if the process dies before it commits, and the WAL holding those uncommitted frames can blow past wal_autocheckpoint before the commit that would let a checkpoint reclaim it. Size the batch to the checkpoint threshold, not to “as big as possible,” and for a telemetry stream where recent data matters, add a time bound (commit at least every few seconds) so a slow trickle of rows is not held un-committed indefinitely. The full threshold interaction is developed in Threshold Tuning for High-Write Workloads.

A crash loses the current uncommitted batch — that is the deal, so make it explicit. Everything committed is durable under WAL recovery, but the in-flight batch is gone. This is acceptable for high-frequency telemetry where a lost second of readings is noise, and unacceptable for a batch containing a control command or a billing event. Decide per data type: keep critical writes on small or single-row transactions, and reserve large batches for lossy-tolerant streams. On restart, WAL recovery replays committed transactions automatically, so no manual recovery is needed for the durable rows — only the last open batch is lost.

Holding the write lock too long starves readers and collides with busy_timeout. A batch is one transaction holding the single write lock for its whole duration; make it large enough and a reader or another writer waiting on that lock exhausts its busy_timeout and gets SQLITE_BUSY. On a device with concurrent readers, keep each batch’s wall-clock hold short (favour more, smaller batches over one giant transaction), and set every connection’s busy_timeout high enough to wait out a batch commit — the interplay is covered in Tuning busy_timeout Against Application Retries. Batching for the writer’s benefit at the cost of starving every reader is a net loss.