Scheduling Incremental VACUUM for Continuous Writers

A telemetry ingest service that writes around the clock has a reclamation problem with no obvious solution: it deletes aged rows constantly, so its freelist grows, yet it can never stop long enough to run a full VACUUM, which demands an exclusive lock the writer will never yield. Wait for a quiet moment that never comes and the file grows until the volume fills; force a VACUUM and you stall ingest for the entire rebuild. The answer is to decouple reclaiming space from stopping the world: run the database in auto_vacuum=INCREMENTAL mode and drain the freelist in small, bounded incremental_vacuum(N) calls timed to low-traffic ticks, so each reclamation costs a few milliseconds of write lock instead of a multi-second freeze. This is the continuous-writer case of VACUUM & Space Reclamation, part of the Backup, Recovery & Data Integrity guide, and it assumes the mode was already chosen correctly — the create-time setup lives in Configuring auto_vacuum on Embedded Flash.

Figure — A steady writer inflates the freelist; an idle-window scheduler reclaims a bounded page count on each tick, holding the file within a band instead of letting it climb.

Scheduled incremental vacuum loop for an always-on writer A four-stage cycle. A continuous telemetry writer inserts and deletes rows, which inflates freelist_count. On each low-traffic scheduler tick a bounded incremental_vacuum reclaims a fixed number of freelist pages, returning that space to the filesystem. Control flows back to the writer with the freelist held bounded, so the file size oscillates within a band rather than trending upward. Telemetry writer inserts + deletes, always on freelist_count rises dead pages accumulate Idle-window tick scheduler, low-traffic slot incremental_vacuum(N) reclaim bounded chunk inserts + deletes each idle tick reclaim N pages freelist bounded

Diagnosis

The condition that calls for scheduled reclamation is a file that grows without bound while a large fraction of it is already free. Confirm both halves. Read the freelist and total page counts, and assert the mode actually supports on-demand reclamation:

PRAGMA auto_vacuum;     -- must return 2 (INCREMENTAL); 0 or 1 means incremental_vacuum won't help
PRAGMA freelist_count;  -- dead pages available to reclaim right now
PRAGMA page_count;      -- total pages; dead fraction = freelist_count / page_count
PRAGMA page_size;       -- bytes per page, to turn a reclaim count into bytes freed

If freelist_count / page_count is climbing over hours while the writer stays busy, the freelist is outpacing reuse and needs draining. Pair that with a file-size sample so you can see the trend the reclaimer is meant to flatten:

import os
dead_bytes = freelist_count * page_size          # space you could return to the volume today
file_bytes = os.path.getsize(db_path)            # sample on a timer; a steady climb confirms the need

A high dead_bytes that never falls, on a file whose file_bytes only trends upward, is the exact signature this schedule is built to correct.

Solution

Reclaim a fixed budget of pages on each low-traffic tick. Sizing N is the entire design decision: incremental_vacuum(N) briefly holds the write lock while it truncates, so N is chosen against the pause your writer can absorb, not against how much is free. A few hundred pages — roughly 1–2 MB at a 4 KB page size — reclaims meaningfully while keeping the lock held for only milliseconds.

import sqlite3
import logging

logger = logging.getLogger("incremental_reclaimer")


def reclaim_freelist(db_path: str, budget_pages: int = 256) -> int:
    """Drip up to budget_pages back to the filesystem. Call from a low-traffic scheduler tick."""
    conn = None
    try:
        conn = sqlite3.connect(db_path, timeout=5.0, isolation_level=None)  # short timeout: skip a busy tick
        conn.execute("PRAGMA busy_timeout=2000;")  # ms; ride out a brief writer overlap, then yield

        # Reclamation only works in INCREMENTAL mode; verify before acting so a misconfigured
        # database fails loudly instead of silently doing nothing every tick.
        mode = conn.execute("PRAGMA auto_vacuum;").fetchone()[0]
        if mode != 2:
            raise RuntimeError(f"auto_vacuum must be INCREMENTAL (2) to reclaim, got {mode}")

        before = conn.execute("PRAGMA freelist_count;").fetchone()[0]
        # Bounded reclaim: NEVER pass 0 here on a live writer — 0 drains the WHOLE freelist
        # in one call and can hold the write lock long enough to stall ingest.
        conn.execute(f"PRAGMA incremental_vacuum({budget_pages});")  # up to N pages, bounded on purpose
        after = conn.execute("PRAGMA freelist_count;").fetchone()[0]

        reclaimed = before - after
        if reclaimed < 0:
            raise RuntimeError(f"freelist grew during reclaim: {before} -> {after}")
        logger.info("reclaimed %d pages this tick (freelist %d -> %d)", reclaimed, before, after)
        return reclaimed
    except sqlite3.OperationalError as e:
        # A lock conflict on a busy tick is expected; log and let the next tick retry.
        logger.warning("reclaim skipped, database busy: %s", e)
        return 0
    except sqlite3.Error as e:
        logger.error("incremental reclaim failed: %s", e)
        raise
    finally:
        if conn:
            conn.close()

Call reclaim_freelist from whatever cadence owns your quiet windows — a periodic timer, a cron-like tick, or a gap detector that fires when ingest rate drops below a threshold. Each call reclaims at most budget_pages, so a large backlog drains over several ticks rather than in one stall, and the file settles into a band. Because both reclamation and WAL checkpoints contend for write access, align the two cadences instead of letting them collide — the checkpoint side is tuned in Optimizing wal_autocheckpoint for Continuous Logging.

Verification

Confirm each tick returns space and that the file stops trending upward across a full retention cycle.

freed = reclaim_freelist("/var/lib/telemetry/ingest.db", budget_pages=256)
assert freed >= 0, "reclaim reported negative progress — freelist should never grow during a drip"

conn = sqlite3.connect("/var/lib/telemetry/ingest.db", isolation_level=None)
mode = conn.execute("PRAGMA auto_vacuum;").fetchone()[0]
assert mode == 2, f"mode drifted from INCREMENTAL: {mode}"        # a recycled handle can revert
remaining = conn.execute("PRAGMA freelist_count;").fetchone()[0]
print(f"freelist now {remaining} pages; keep ticking until it holds steady")

The healthy pattern is a freelist_count that rises between ticks and falls on each drip, oscillating within a band, while the file size measured over a full delete cycle stays flat instead of climbing. If the freelist grows faster than a single budget can drain, raise the cadence or the per-tick budget — not to incremental_vacuum(0).

Failure Modes & Gotchas

incremental_vacuum(0) reclaims the entire freelist and can stall ingest. Passing 0 (or calling it with no argument) drains every free page in a single call, which on a large backlog holds the write lock long enough to block the writer — exactly the multi-second freeze scheduled reclamation exists to avoid. Always pass an explicit bounded N sized to the pause your writer tolerates, and let the backlog drain across ticks.

Reclamation and checkpoints contend for the same write access. An incremental_vacuum drip and a WAL checkpoint both need to write, so firing them together doubles the contention window and raises the odds of a SQLITE_BUSY on the ingest path. Schedule the drip inside the same low-traffic window you use for checkpoints, or stagger them, so the writer only ever competes with one maintenance task at a time; coordinate the cadence with Optimizing wal_autocheckpoint for Continuous Logging.

A drip on a non-INCREMENTAL database is a silent no-op. If the file is in auto_vacuum=NONE, no pointer maps exist and incremental_vacuum reclaims nothing while returning no error, so the file keeps growing tick after tick with the reclaimer apparently running. The mode assertion in the routine turns that into a loud failure; a recycled connection that skipped initialization can also revert behavior, which is why the mode is re-checked rather than assumed.

For authoritative reference, see the official incremental_vacuum pragma documentation.