VACUUM & Space Reclamation #

A SQLite database file only ever grows on its own. Delete a million telemetry rows and the file stays exactly the same size — the pages those rows occupied are moved onto an internal freelist and reused by future inserts, but they are never handed back to the filesystem unless you explicitly ask. On a desktop with terabytes free that is invisible; on a 4 GB eMMC partition or an SD card carrying a rolling retention window, it is the difference between a stable service and one that hits SQLITE_FULL weeks after the delete workload looked correct in testing. Controlling file growth and reclaiming dead space is a first-class production concern, and it sits inside the Backup, Recovery & Data Integrity guide because a database that has run out of room can neither checkpoint its WAL nor complete a backup. This topic covers the three mechanisms SQLite gives you — the full VACUUM rebuild, the auto_vacuum modes that truncate automatically, and incremental_vacuum for controlled reclamation — and, more importantly, when each one is safe to run and when it will quietly make your problem worse.

Figure — Deleted rows release pages onto the freelist; from there four reclamation strategies with very different cost profiles decide whether and when that space returns to the filesystem.

Core Mechanism & Crash-Safety Defaults #

When a row is deleted or a table is dropped, SQLite marks the pages that held that data as free and links them into a freelist stored inside the database file. PRAGMA freelist_count returns how many such pages are waiting, and PRAGMA page_count returns the total pages in the file; multiply either by PRAGMA page_size to convert to bytes. The on-disk file size is therefore page_count * page_size, and the dead fraction of it is freelist_count / page_count. A file that is 40% freelist pages is a file you are paying to store and back up for no benefit.

The full VACUUM command reclaims that space by rebuilding the database from scratch: it copies every live page into a fresh, defragmented file with no gaps, then swaps it into place. This is the only mechanism that also defragments — it repacks the b-trees, applies the current page_size, and eliminates fragmentation left by interleaved inserts and deletes. It is also the most expensive and the most dangerous operationally. Because it builds a complete second copy before discarding the original, VACUUM transiently needs free space up to roughly the size of the database itself (plus temporary journal space), and it acquires an exclusive lock for the duration, so no other connection can read or write while it runs. VACUUM cannot run inside an open transaction, and it will change the rowids of any table that lacks an explicit INTEGER PRIMARY KEY — a subtle correctness trap if application code has cached those rowids.

The auto_vacuum modes trade that all-at-once cost for incremental bookkeeping. To support automatic reclamation, SQLite maintains extra pointer-map pages that let it trace any page back to its parent, which is what makes it possible to relocate freelist pages to the end of the file and truncate them off. Those pointer-map pages are the standing overhead of auto-vacuum: roughly one for every page_size/5 pages (about 1 page in 820 at a 4 KB page size, near 0.1%), and they add write traffic because they must be maintained as the b-trees move. This is why auto_vacuum is not free and why it must be decided at database-creation time.

The crash-safety picture is reassuring: none of these operations weaken durability. VACUUM is itself transactional — a power loss mid-VACUUM rolls back to the original database, so you never end up with a half-rebuilt file (you only lose the work). auto_vacuum truncation happens as part of the committing transaction under the same journal or WAL protection as any other write. You never need to lower PRAGMA synchronous to make reclamation cheaper, and doing so to mask its I/O cost trades a space problem for a corruption risk.

Step-by-Step Implementation #

1. Verify Prerequisites & Measure Free Pages #

Before choosing a strategy, measure. Reclamation decisions should be driven by the actual dead fraction, not a hunch. Read the three counters and the current mode:

PRAGMA page_size;       -- bytes per page (usually 4096); converts page counts to bytes
PRAGMA page_count;      -- total pages in the file == file size / page_size
PRAGMA freelist_count;  -- free (dead) pages waiting on the freelist to be reused
PRAGMA auto_vacuum;     -- current mode: 0 = NONE, 1 = FULL, 2 = INCREMENTAL
PRAGMA journal_size_limit;  -- bytes; caps WAL/journal truncation size, independent of VACUUM

Two prerequisites gate a full VACUUM. First, free space: confirm the filesystem has at least the current database size available, or the rebuild will abort with SQLITE_FULL partway through. Second, exclusivity: VACUUM needs the whole database to itself, so it must run when no other connection holds a read or write transaction. On an always-on writer neither prerequisite is reliably met, which is the entire reason incremental_vacuum exists — the continuous-writer pattern is covered in Scheduling Incremental VACUUM for Continuous Writers.

2. Choose an auto_vacuum Mode from the Decision Table #

auto_vacuum has one property that dominates every other consideration: it must be set before any table is created, because the mode determines whether pointer-map pages exist in the file at all. On a freshly created (empty) database, setting the pragma takes effect immediately. On a database that already has tables, changing between NONE and either FULL or INCREMENTAL is inert until you run a full VACUUM — the VACUUM rebuilds the file with (or without) the pointer-map infrastructure. Switching between FULL and INCREMENTAL is the only transition that takes effect at runtime without a VACUUM, because both modes already carry the pointer maps.

For flash-backed targets the INCREMENTAL choice is almost always correct, and the reasons — write amplification and erase-cycle wear — are detailed in Configuring auto_vacuum on Embedded Flash.

3. Apply the Mode with Explicit Verification #

Set the mode at creation time when you can, and always read it back — a mode change that silently failed to apply (because tables already existed) is a classic source of “why is my file still growing.” The routine below creates the database in INCREMENTAL mode, then reclaims a bounded number of pages:

import sqlite3
import logging

logger = logging.getLogger("vacuum_config")


def configure_incremental_autovacuum(db_path: str, reclaim_pages: int = 256) -> None:
    conn = None
    try:
        conn = sqlite3.connect(db_path, timeout=30.0, isolation_level=None)  # autocommit
        # auto_vacuum MUST be set before any table exists; on a new file this
        # takes effect immediately. On a populated file it is inert until VACUUM.
        conn.execute("PRAGMA auto_vacuum=INCREMENTAL;")  # 2 = keep ptrmaps, reclaim on demand
        conn.execute("PRAGMA journal_mode=WAL;")         # concurrent readers during reclamation
        conn.execute("PRAGMA synchronous=NORMAL;")       # fsync at checkpoint, not every commit

        # If this database already had tables, the mode above did nothing yet.
        # A single full VACUUM rewrites the file with pointer maps and makes it stick.
        mode = conn.execute("PRAGMA auto_vacuum;").fetchone()[0]
        if mode != 2:
            logger.warning("auto_vacuum not INCREMENTAL (got %d); running one-time VACUUM", mode)
            conn.execute("VACUUM;")  # exclusive, needs ~2x free space; do this once, offline
            mode = conn.execute("PRAGMA auto_vacuum;").fetchone()[0]

        # Read back and assert the mode actually applied before trusting it.
        if mode != 2:
            raise RuntimeError(f"auto_vacuum still not INCREMENTAL after VACUUM: mode={mode}")

        before = conn.execute("PRAGMA freelist_count;").fetchone()[0]
        conn.execute(f"PRAGMA incremental_vacuum({reclaim_pages});")  # drip up to N pages back
        after = conn.execute("PRAGMA freelist_count;").fetchone()[0]

        # Verify reclamation happened: the freelist must have shrunk (or already been empty).
        if after > before:
            raise RuntimeError(f"freelist grew during reclaim: {before} -> {after}")
        logger.info("auto_vacuum=INCREMENTAL; freelist %d -> %d pages", before, after)
    except sqlite3.Error as e:
        logger.error("space reclamation configuration failed: %s", e)
        raise
    finally:
        if conn:
            conn.close()

incremental_vacuum(N) removes up to N pages from the freelist and truncates the file by that amount; passing 0 (or no argument) reclaims the entire freelist in one shot, which on a large freelist can hold the write lock long enough to stall other connections. Bounding N is how you keep reclamation off the critical path.

Workload Profiles & Threshold Reference #

The right strategy is a function of storage medium, write volume, and whether you can ever take an exclusive lock. These are field-tested starting points; measure freelist_count and file-size trend under real load and adjust.

On the smallest volumes, freelist growth and WAL growth compete for the same scarce bytes, and the WAL side of that trade-off is covered in Handling WAL File Bloat on Constrained Storage.

Failure Documentation & Edge Cases #

VACUUM Aborts with SQLITE_FULL on a Constrained Volume #

Trigger: A full VACUUM on a nearly-full partition fails partway because it needs to materialize a complete second copy of the database plus temporary journal space — roughly twice the current file size — and the volume cannot supply it.

Diagnosis: Compare the database size against filesystem headroom before you start:

import os, shutil
db_bytes = os.path.getsize(db_path)
free_bytes = shutil.disk_usage(os.path.dirname(db_path) or ".").free
needs_vacuum_room = free_bytes > db_bytes * 2  # VACUUM builds a full second copy first

Fallback: Do not run a full VACUUM on the target volume. Use auto_vacuum=INCREMENTAL with bounded incremental_vacuum drips, which reclaim in place with no second copy, or redirect the rebuild’s temporary file to a roomier volume via PRAGMA temp_store_directory. The flash-specific reasoning is in Configuring auto_vacuum on Embedded Flash.

auto_vacuum Silently Ignored on a Populated Database #

Trigger: You set PRAGMA auto_vacuum=INCREMENTAL (or FULL) on a database that already has tables and expect the file to start shrinking. It does not — the mode change is inert until the file is rebuilt.

Diagnosis: Read the mode back immediately after setting it:

PRAGMA auto_vacuum;  -- if this still returns 0 after you set 2, tables already existed

Fallback: Run a single full VACUUM to rewrite the file with pointer-map pages, after which the mode sticks and incremental_vacuum works. Do this once, offline, during provisioning — ideally set the mode at database-creation time so it is never needed in the field.

incremental_vacuum Is a No-Op Unless the Mode Is INCREMENTAL #

Trigger: PRAGMA incremental_vacuum(N) runs without error but reclaims nothing because auto_vacuum is NONE (no pointer maps exist) or FULL (pages were already truncated at commit).

Diagnosis: Assert the mode and watch the freelist across the call:

mode = conn.execute("PRAGMA auto_vacuum;").fetchone()[0]  # must be 2 for incremental_vacuum to act
assert mode == 2, f"incremental_vacuum requires INCREMENTAL mode, got {mode}"

Fallback: If the file must shrink and the mode is NONE, switch to INCREMENTAL and run the one-time VACUUM described above; if the mode is FULL, reclamation is already automatic and incremental_vacuum is simply unnecessary.

VACUUM Invalidates an In-Flight Hot Backup #

Trigger: A full VACUUM rewrites every page of the database. If an online backup is running concurrently, that wholesale rewrite forces the backup to restart from the beginning, and a VACUUM that keeps re-triggering can leave a backup that never converges.

Diagnosis: A backup whose remaining page count resets to the full database size repeatedly, never counting down, is the signature of a concurrent whole-file rewrite.

Fallback: Never schedule VACUUM inside a backup window. Sequence them — quiesce writers, run the backup to completion, then VACUUM — or prefer incremental_vacuum, whose bounded page moves do not thrash a running copy. The backup mechanics are covered in Online Backup API & Hot Copies.

Production Hardening Checklist #

• Set PRAGMA auto_vacuum at database-creation time, before any table exists, so no later VACUUM is required to change it.
• Read PRAGMA auto_vacuum back after setting it and assert it matches the intended mode.
• Monitor the dead fraction from the application via freelist_count / page_count, not by eyeballing file size.
• Confirm free space exceeds 2 × the database size before ever running a full VACUUM.
• Never run a full VACUUM on an always-on writer or during a backup window; use bounded incremental_vacuum instead.
• Bound incremental_vacuum(N) so a single reclamation cannot hold the write lock long enough to starve other connections.
• Set PRAGMA journal_size_limit on constrained volumes so WAL growth does not compete with reclaimed space.
• Never lower synchronous to mask reclamation I/O cost.

Related Pages #

• Configuring auto_vacuum on Embedded Flash — choosing INCREMENTAL at create time to bound growth and spare erase cycles.
• Scheduling Incremental VACUUM for Continuous Writers — draining the freelist in bounded chunks when a full VACUUM can never lock the file.
• Online Backup API & Hot Copies — why a VACUUM must never overlap an in-flight backup.
• Handling WAL File Bloat on Constrained Storage — the WAL side of the space budget on tiny volumes.

For authoritative reference, consult the official SQLite VACUUM documentation and the auto_vacuum pragma reference.