Configuring auto_vacuum on Embedded Flash

Flash media punishes the wrong reclamation strategy in a way that spinning disks and enterprise SSDs hide. An eMMC chip, a raw NAND partition, or a consumer SD card reclaims space in erase blocks of tens of kilobytes to a few megabytes, and every one of those blocks tolerates only a bounded number of erase cycles before it wears out. A SQLite database that truncates and relocates pages on every single commit therefore does not just cost I/O time — it consumes the finite life of the device. The right decision on flash is almost always auto_vacuum=INCREMENTAL, set at the moment the database file is created, so that space can be reclaimed but only when you choose to pay for it. This is the flash-specific corner of VACUUM & Space Reclamation within the broader Backup, Recovery & Data Integrity guide, and the create-time timing matters as much as the mode itself, because getting it wrong later forces a full rewrite that is exactly the wear event you were trying to avoid.

Figure — At create time you commit to a mode before the first table exists; on flash, FULL truncates every commit and churns erase blocks, while INCREMENTAL defers reclamation to a single bounded drip per idle window.

Choosing auto_vacuum mode at create time for flash media A create-time decision node sits at the top: auto_vacuum must be set before the first CREATE TABLE. The left branch, marked avoid on flash, selects auto_vacuum=FULL, which truncates the freelist at every commit and churns erase blocks. The right branch, marked prefer, selects auto_vacuum=INCREMENTAL, which reclaims space in one bounded drip per idle window and minimizes flash wear. Create-time decision set before first CREATE TABLE avoid on flash prefer auto_vacuum=FULL truncate freelist every commit Erase-block churn write amplification, wear ↑ auto_vacuum=INCREMENTAL reclaim only on demand Bounded growth one drip per idle tick, wear ↓

Diagnosis

Establish two things before you touch the setting: which mode the file is currently in, and whether it is actually growing. Both are cheap to read and together they tell you whether you have a create-time decision still available or a rewrite already on your hands.

PRAGMA auto_vacuum;     -- 0 = NONE, 1 = FULL, 2 = INCREMENTAL; decided when the first table was made
PRAGMA freelist_count;  -- dead pages that a reclaim could return to the filesystem
PRAGMA page_count;      -- total pages; file size = page_count * page_size
PRAGMA page_size;       -- bytes per page (commonly 4096) to convert counts to bytes

If auto_vacuum returns 0 and the file has already accumulated tables, you are past the free decision point: the mode can only be changed by rewriting the whole file, which on flash is the costly path you want to avoid. If it returns 2, you are already in the right mode and only need a reclamation cadence. The growth signal is the file-size trend: sample page_count * page_size over time, or watch the file directly, and compare it against freelist_count. A file whose byte size keeps climbing while freelist_count is also large is a database that is reusing some freed pages but never returning the surplus to the card:

import os
size_bytes = os.path.getsize(db_path)  # sample this on a timer; a monotonic climb is the symptom

A steadily rising size_bytes with a persistently high freelist_count is the exact condition auto_vacuum=INCREMENTAL plus periodic reclamation is designed to bound.

Solution

Create the database with the mode set before any table exists, verify it took, then reclaim a small, bounded number of pages. The order is the whole point: on a brand-new file the PRAGMA applies immediately and no rewrite is ever needed.

import sqlite3
import logging

logger = logging.getLogger("flash_autovacuum")


def create_flash_database(db_path: str, drip_pages: int = 128) -> None:
    conn = None
    try:
        conn = sqlite3.connect(db_path, timeout=15.0, isolation_level=None)  # autocommit for PRAGMAs

        # Set auto_vacuum FIRST, before creating any table, so it applies without a rewrite.
        conn.execute("PRAGMA auto_vacuum=INCREMENTAL;")  # 2 = keep ptrmaps, reclaim only on demand
        conn.execute("PRAGMA journal_mode=WAL;")         # readers stay live during a drip
        conn.execute("PRAGMA synchronous=NORMAL;")       # fsync at checkpoint, not per commit (spares flash)

        # Now it is safe to create tables; the ptrmap infrastructure already exists.
        conn.execute("CREATE TABLE IF NOT EXISTS readings(ts INTEGER, sensor TEXT, value REAL);")

        # Read the mode back and assert it stuck. On a pre-populated file this would be 0.
        mode = conn.execute("PRAGMA auto_vacuum;").fetchone()[0]
        if mode != 2:
            raise RuntimeError(f"auto_vacuum=INCREMENTAL not active (got {mode}); file had tables already")

        # A small, bounded reclamation drip: never incremental_vacuum(0) on flash — that
        # reclaims the entire freelist at once and can truncate a large span in one commit.
        before = conn.execute("PRAGMA freelist_count;").fetchone()[0]
        conn.execute(f"PRAGMA incremental_vacuum({drip_pages});")  # up to N pages back to the card
        after = conn.execute("PRAGMA freelist_count;").fetchone()[0]

        if after > before:
            raise RuntimeError(f"freelist grew during drip: {before} -> {after}")
        logger.info("flash DB ready: auto_vacuum=INCREMENTAL, freelist %d -> %d", before, after)
    except sqlite3.Error as e:
        logger.error("flash auto_vacuum setup failed: %s", e)
        raise
    finally:
        if conn:
            conn.close()

Choosing INCREMENTAL over FULL is the write-amplification decision. FULL moves freelist pages to the end of the file and truncates them off at every commit, so a workload that inserts and deletes steadily rewrites and shortens the file continuously — many small erase-block-touching operations. INCREMENTAL keeps the same pointer-map bookkeeping but performs zero truncation until you call incremental_vacuum, letting you collapse all that churn into one drip during an idle window sized to your I/O budget, as covered in Scheduling Incremental VACUUM for Continuous Writers.

Verification

Prove three things: the mode is active, a drip actually returns space, and the file is bounded rather than monotonically climbing.

conn = sqlite3.connect(db_path, isolation_level=None)
mode = conn.execute("PRAGMA auto_vacuum;").fetchone()[0]  # must be 2 for any of this to work
assert mode == 2, f"auto_vacuum must be INCREMENTAL (2), got {mode}"

before = conn.execute("PRAGMA freelist_count;").fetchone()[0]
conn.execute("PRAGMA incremental_vacuum(64);")            # reclaim a bounded slice
after = conn.execute("PRAGMA freelist_count;").fetchone()[0]
assert after <= before, f"freelist did not shrink: {before} -> {after}"

If freelist_count was above your drip size and the file shrank by roughly reclaimed_pages * page_size bytes afterward, reclamation is working. Track file size across a full retention cycle; the number should oscillate within a band instead of trending upward.

Failure Modes & Gotchas

Switching mode on a populated database forces a full rewrite that hammers flash. You cannot move a file from NONE to INCREMENTAL (or between NONE and FULL) by setting the pragma alone once tables exist — the change only lands when a full VACUUM rebuilds the file with pointer-map pages. On flash that VACUUM rewrites every live page and needs roughly twice the file size in free space, precisely the large erase-cycle event you are trying to avoid. Decide at provisioning time; treat a field-side mode change as a last resort run offline, not a routine operation.

FULL mode’s per-commit truncation is invisible write amplification. auto_vacuum=FULL looks attractive because the file shrinks with no operator involvement, but on a continuous writer it relocates and truncates pages on every transaction, multiplying the physical writes behind each logical commit. On a card rated for a limited number of program/erase cycles, that difference is measured in device lifetime. Prefer INCREMENTAL and control the cadence yourself.

Pointer-map pages are a small standing cost you accept deliberately. Enabling any auto-vacuum mode adds pointer-map pages — about one page per page_size/5 pages, near 0.1% of the file at a 4 KB page size — plus the write traffic to maintain them as b-trees move. That overhead is the price of being able to reclaim space at all; on flash it is easily justified by avoiding an eventual full VACUUM, but it means auto-vacuum is never truly free, and a read-mostly database that never deletes may be better left at NONE. Sizing the page cache around this footprint is covered in Tuning cache_size for Embedded Linux.

For authoritative reference, see the official auto_vacuum pragma documentation.