PRAGMA Optimization Guide #

SQLite’s factory PRAGMA defaults optimize for universal compatibility across decades-old platforms, not for throughput or durability on modern edge hardware. On Edge/IoT gateways, desktop applications, Python automation workers, and embedded controllers, that conservative posture becomes a direct liability: unbounded Write-Ahead Log (WAL) growth, one fsync() on every commit, tiny page caches that thrash against constrained RAM, and SQLITE_BUSY errors the moment two threads contend. As part of the WAL Optimization & Concurrency Tuning reference, this page defines the connection-scoped PRAGMA baseline every other subsystem builds on — journaling modes, checkpoint cadence, connection pooling, and memory-mapped I/O all assume the values established here are already in place. Get the initialization order wrong, or leave a single PRAGMA at its default, and the failure surfaces silently: latency creeps, the -wal file balloons, and the database corrupts only when power is pulled at exactly the wrong moment.

The core principle is that PRAGMAs are infrastructure code, not runtime tuning knobs. They are applied deterministically at connection open, verified by read-back, and versioned alongside the schema — never guessed at, never left implicit, never assumed to have “taken” without an assertion.

Core Mechanism & Crash-Safety Defaults #

Every PRAGMA in this guide is connection-scoped, not database-scoped, with two structural exceptions. journal_mode=WAL writes a persistent flag into the database header, so it survives across connections and process restarts once set. wal_autocheckpoint is stored per connection but effectively governs the shared WAL because any connection can trigger the checkpoint. Everything else — synchronous, cache_size, mmap_size, busy_timeout, foreign_keys, temp_store — resets to its default on every fresh handle. That single fact drives the entire discipline: if a pooled connection skips initialization, it silently diverges from its siblings in cache size, sync policy, and lock-wait behavior, producing failures that reproduce on one thread and vanish on the next.

Ordering matters because some PRAGMAs are ignored inside a transaction or after the journal file already exists. journal_mode must be set before any write opens the legacy rollback journal; cache_size and mmap_size should be established before the first query populates the page cache; foreign_keys is a no-op if changed mid-transaction. The safe rule is to apply the entire baseline immediately after sqlite3.connect() returns and before any BEGIN, migration, or prepared statement runs.

The crash-safety contract hinges on synchronous. In WAL mode, NORMAL is the correct production default: commits do not call fsync() on every write, but the database can never corrupt on power loss — at worst, the last few transactions committed since the previous checkpoint are rolled back. FULL adds an fsync() at each commit so every acknowledged transaction is guaranteed durable, at real latency cost. OFF removes the durability barrier entirely and risks corruption, not just data loss. The full trade-off matrix — and how it maps to specific filesystems and battery-backed storage — lives in Configuring synchronous PRAGMA for Crash Safety; treat that page as required reading before you deviate from NORMAL.

Figure — The hardened connection initialization path: PRAGMAs are applied in dependency order immediately after open, each result is read back and asserted, and only a fully verified handle is released to application or pool code.

The property to internalize: setting a PRAGMA and reading it back are two different operations. A read-only mount, an open reader pinning the legacy journal, or a compile-time option that omits WAL support can all cause a journal_mode=WAL request to silently return delete. The verification step is not optional ceremony — it is the only thing that converts a silent misconfiguration into a loud, early failure.

Step-by-Step Implementation #

1. Verify Prerequisites and PRAGMA Baselines #

Before selecting any values, confirm the environment can honor them. WAL mode requires a filesystem that supports the shared-memory (-shm) file and byte-range locking; on some network filesystems and on certain FAT32 vs ext4 lock semantics, WAL silently degrades. Inspect the live state first:

PRAGMA compile_options;      -- confirm ENABLE_MEMSYS / that WAL + mmap are compiled in
PRAGMA journal_mode;         -- current mode; anything but 'wal' after you set it is a hard failure
PRAGMA synchronous;          -- 0=OFF, 1=NORMAL, 2=FULL, 3=EXTRA
PRAGMA cache_size;           -- pages if positive, KiB if negative (default -2000 = ~2 MiB)
PRAGMA mmap_size;            -- bytes of the DB mapped into the process address space
PRAGMA wal_autocheckpoint;   -- WAL page threshold that schedules a PASSIVE checkpoint (default 1000)
PRAGMA busy_timeout;         -- ms SQLite retries internally before returning SQLITE_BUSY
PRAGMA page_size;            -- needed to convert page-based thresholds into bytes

Record page_size — every page-based figure (cache_size positive form, wal_autocheckpoint) only means something once multiplied by it. The common default is 4096 bytes, so 1000 WAL pages is roughly 4 MiB.

2. Calculate and Select the Target Values #

Size each PRAGMA from the device budget rather than copying a value from a blog post. The formulas below turn a RAM figure into concrete settings:

The negative-KiB form of cache_size is strongly preferred for portability: -8192 always means 8 MiB regardless of page_size, whereas the positive form silently changes meaning if the page size differs between build and deploy.

3. Apply the Configuration with Verification #

The hardened initializer applies the baseline atomically, then reads back every setting and asserts on it. A connection that cannot prove its own configuration is treated as a fatal error, not handed to a worker pool where the misconfiguration would surface later as data loss:

import sqlite3
import logging

logger = logging.getLogger("sqlite.pragma")

# Target baseline for a 512 MiB-RAM edge device (page_size assumed 4096).
BASELINE = {
    "journal_mode":       "wal",     # persistent header flag; decouples readers from the single writer
    "synchronous":        1,         # 1 = NORMAL: fsync deferred to checkpoint; no corruption on power loss
    "cache_size":         -51200,    # negative = KiB -> 50 MiB page cache (~10% of RAM), page_size-independent
    "mmap_size":          134217728, # 128 MiB memory-mapped read window; set 0 on 32-bit targets
    "wal_autocheckpoint": 1000,      # ~4 MiB soft trigger at 4 KiB pages; bounds -wal growth
    "busy_timeout":       5000,      # ms: retry internally, absorbing checkpoint pauses before SQLITE_BUSY
}

def open_hardened(db_path: str) -> sqlite3.Connection:
    # isolation_level=None -> autocommit, so PRAGMAs never sit inside an implicit transaction.
    conn = sqlite3.connect(db_path, timeout=5.0, isolation_level=None)
    cur = conn.cursor()

    # Apply in dependency order: journal_mode first, then durability, cache, checkpoint, lock-wait.
    cur.execute("PRAGMA journal_mode=WAL;")          # returns the resulting mode
    cur.execute("PRAGMA synchronous=NORMAL;")        # WAL-safe durability, no per-commit fsync
    cur.execute("PRAGMA cache_size=-51200;")         # 50 MiB; negative = KiB
    cur.execute("PRAGMA mmap_size=134217728;")       # 128 MiB mapped read window
    cur.execute("PRAGMA wal_autocheckpoint=1000;")   # ~4 MiB WAL soft trigger
    cur.execute("PRAGMA busy_timeout=5000;")         # 5 s internal retry window

    # Verify: read every value back and assert it matches. A silent 'delete' journal_mode
    # (read-only mount / open legacy reader) or an ignored PRAGMA fails loudly here.
    for pragma, expected in BASELINE.items():
        actual = cur.execute(f"PRAGMA {pragma};").fetchone()[0]
        if isinstance(expected, str):
            actual = str(actual).lower()
        if actual != expected:
            raise RuntimeError(
                f"PRAGMA {pragma}: expected {expected!r}, got {actual!r} "
                f"(check filesystem, open readers, and compile_options)"
            )

    logger.info("SQLite PRAGMA baseline verified for %s", db_path)
    return conn

Two Python-specific traps are handled here. First, isolation_level=None puts the driver in autocommit mode so PRAGMAs never execute inside the implicit transaction the default driver would otherwise open — an implicit transaction can cause journal_mode=WAL to be deferred or ignored. Second, the assertion loop runs after every apply, so a partially-applied baseline can never escape into the pool. For the pool-wide contract that every handle must pass this same check, see Connection Pooling Strategies.

Workload Profiles & Threshold Reference #

The same six PRAGMAs resolve to very different values across deployment classes. The table maps each target profile to a recommended baseline and the reasoning behind it:

These are starting points, not endpoints. Measure p99 commit latency and observed -wal size on the real device, then adjust wal_autocheckpoint and cache_size first — they have the largest effect on the storage-versus-latency trade-off.

Failure Documentation & Edge Cases #

WAL bloat from an ignored or oversized autocheckpoint #

Trigger: wal_autocheckpoint set to 0 (disabled) or far too high, or a long-lived reader that pins an old snapshot so the checkpoint cannot truncate. The -wal file grows without bound and every read must scan it, degrading query latency linearly.

Diagnose: PRAGMA wal_checkpoint(PASSIVE); returns (busy, log_pages, checkpointed_pages) — a non-zero first field means a reader blocked truncation; compare log_pages against your threshold and check the on-disk -wal size.

Fallback: enforce a hard ceiling with PRAGMA journal_size_limit, run an explicit PRAGMA wal_checkpoint(TRUNCATE) during a maintenance window, and cap reader lifetimes. Full mitigation is in Handling WAL File Bloat on Constrained Storage.

Silent corruption from synchronous=OFF #

Trigger: OFF is chosen to hide checkpoint latency. A power cut or kernel panic mid-write reorders or drops buffered writes, corrupting the main database — not merely losing the last transaction.

Diagnose: PRAGMA integrity_check; after an unclean shutdown; a healthy database returns a single ok. Log the startup PRAGMA synchronous; value to catch drift into OFF.

Fallback: return to NORMAL (or FULL where every acknowledged commit must survive). Reserve OFF for disposable scratch databases or battery-backed RAM only; the reasoning is detailed in Configuring synchronous PRAGMA for Crash Safety.

mmap_size exhausting the address space #

Trigger: a large mmap_size on a 32-bit target or a memory-pressured device. Mapping competes with heap and thread stacks and raises SQLITE_NOMEM or an mmap failure under burst load.

Diagnose: read back PRAGMA mmap_size; — a value smaller than requested means SQLite silently clamped it; correlate SQLITE_NOMEM with resident-set growth in your process monitor.

Fallback: set mmap_size=0 on 32-bit builds and cap it at 25% of RAM elsewhere. The interaction with page cache is covered in Memory-Mapped I/O Configuration.

SQLITE_BUSY despite a configured busy_timeout #

Trigger: busy_timeout reset to 0 on a pooled connection that skipped initialization, or a writer holding BEGIN IMMEDIATE longer than the timeout window. Threads see SQLITE_BUSY even though the intended baseline set a multi-second wait.

Diagnose: PRAGMA busy_timeout; on the offending handle (not a fresh one) — a 0 confirms the connection never ran the baseline. The dedicated busy_timeout configuration page covers tuning the window itself.

Fallback: make PRAGMA initialization a mandatory, verified step on every checkout, and keep write transactions short. Structural mitigation is in Reducing Lock Contention in Multi-Threaded Apps.

Production Hardening Checklist #

• journal_mode=WAL is set and read back as wal — never trusted without verification
• synchronous is NORMAL in WAL mode (FULL only for audit-critical durability); OFF is banned outside scratch databases
• cache_size is expressed in the negative-KiB form so it is independent of page_size
• mmap_size is 0 on 32-bit targets and capped at ~25% of RAM elsewhere
• wal_autocheckpoint is sized from the target -wal byte budget, with journal_size_limit as a hard ceiling
• busy_timeout is at least 3000 ms and re-applied on every pooled connection
• The initializer asserts every value by read-back and refuses to release an unverified handle
• PRAGMAs are applied in autocommit (isolation_level=None) before any transaction or migration
• compile_options, synchronous, and cache_size are logged at startup to catch configuration drift
• The baseline dictionary is versioned in source control alongside the schema

Related Pages #

• Configuring synchronous PRAGMA for Crash Safety
• Checkpoint Frequency Tuning
• Connection Pooling Strategies
• Memory-Mapped I/O Configuration
• Threshold Tuning for High-Write Workloads