synchronous=NORMAL vs FULL Trade-off Matrix #

PRAGMA synchronous decides how aggressively SQLite forces data to stable storage, and in WAL mode the choice between NORMAL and FULL is the sharpest durability-versus-throughput lever you have. It is not a performance micro-optimization: it sets exactly how much of the most recent work can evaporate when the power drops mid-write, and it directly governs flash wear on the constrained devices this material targets. This decision guide belongs to the WAL Optimization & Concurrency Tuning reference and drills into one line of the broader PRAGMA optimization baseline. The default is FULL (level 2); the WAL-mode recommendation for most deployments is NORMAL (level 1). Understanding precisely what each fsyncs, and when, is the difference between a defensible durability posture and either silent data loss or needlessly incinerated flash endurance.

The decision is framed by one question — how much of the last transaction may you lose on sudden power loss — and answered by matching that tolerance to your journal mode and storage. Everything else is a consequence of where and how often SQLite calls fsync.

Figure — From a durability requirement to a synchronous level: a tolerable rollback window selects NORMAL, a zero-loss requirement selects FULL, and both converge on verifying the read-back value.

Core Mechanism & Crash-Safety Defaults #

PRAGMA synchronous controls when SQLite issues an fsync (or the platform equivalent) to force the operating system’s write cache down to the storage medium. It has four levels: OFF (0), NORMAL (1), FULL (2), and EXTRA (3). What each level costs and protects depends entirely on the active journal_mode, because the sync points differ between the rollback journal and the write-ahead log.

In the classic DELETE or TRUNCATE rollback journal, FULL fsyncs the journal before the page cache is modified and fsyncs the database file before the journal is deleted, so a committed transaction is on stable storage when the commit returns. NORMAL in a rollback journal weakens that: it skips one of the syncs, and on some historical configurations a badly timed power loss could, in principle, leave the database recoverable but with a narrow risk that is materially worse than the WAL case. This is the crucial asymmetry — NORMAL means something safer in WAL than it does in DELETE.

In WAL mode the accounting changes. Commits append frames to the -wal file; the main database file is only touched during a checkpoint, which copies committed frames back into it. Under synchronous=FULL, SQLite fsyncs the WAL on every commit, guaranteeing each committed transaction is durable the instant the commit returns. Under synchronous=NORMAL, SQLite does not fsync the WAL on every commit; it syncs the WAL at each checkpoint and syncs the database file as part of that checkpoint. The consequence is a bounded durability window: after a power loss, any transactions committed since the last WAL sync may roll back, but the database is guaranteed to remain consistent — no torn pages, no corruption — because the WAL’s frame checksums let recovery discard the unsynced tail cleanly. You lose the newest commits, not the database.

That is the entire trade in one sentence: in WAL mode, NORMAL trades a small, bounded loss of the most recent transactions on power failure for far fewer fsyncs and far less flash wear, while FULL closes that window at the cost of an fsync on every single commit. Keep journaling in WAL mode and this trade is favorable for the overwhelming majority of edge, desktop, and automation workloads.

Side-by-Side: The Trade-off Matrix #

The matrix makes the two anti-patterns obvious. synchronous=OFF is the only row where power loss can corrupt the database rather than merely roll back recent work, and FULL in DELETE mode is the slow default you most often want to move away from — but toward NORMAL-in-WAL, not toward NORMAL-in-DELETE, which is a distinctly weaker guarantee.

How to Choose #

1. Classify the durability requirement #

State, in plain terms, what a sudden power loss is allowed to cost you. There are three honest answers. First, “losing the last few seconds of writes is fine as long as the database opens clean” — the common case for telemetry, caches, logs, and most desktop state. Second, “no committed transaction may ever be lost” — financial ledgers, audit records, anything where an acknowledged write is a promise. Third, “the data is disposable and rebuildable” — scratch tables and derived caches. The first answer points at NORMAL, the second at FULL (or EXTRA), and only the third can justify even considering OFF, and only on data you can regenerate.

2. Match the requirement to storage and journal mode #

A durability level is only as strong as the storage honoring the fsync. Confirm you are in WAL mode first, because NORMAL is meaningfully safer there than in DELETE. Then account for the medium: a device with a volatile write cache that lies about fsync completion — many cheap SD cards and some consumer SSDs without power-loss protection — undermines even FULL, because SQLite issued the sync but the hardware buffered it. On such media, either enable the drive’s write-cache barrier honestly, add a supercapacitor-backed controller, or accept that your true durability floor is set by hardware, not by the PRAGMA. Where power is stable (mains with a UPS, or a device that flushes on a clean shutdown signal) and the workload is write-heavy, NORMAL is the correct default; where power loss is abrupt and every commit is a promise, choose FULL.

3. Apply and verify the read-back #

Set the PRAGMA and immediately read it back — remembering it returns the integer level, not the keyword. PRAGMA synchronous reports 0, 1, 2, or 3, so assert against the integer.

import sqlite3

# 1 == NORMAL. In WAL mode this defers the WAL fsync to checkpoint time.
EXPECTED_SYNC = 1

def configure(db_path: str) -> sqlite3.Connection:
    try:
        conn = sqlite3.connect(db_path, timeout=5.0)
        conn.execute("PRAGMA journal_mode=WAL;")      # -- WAL: NORMAL is durable + consistent here
        conn.execute("PRAGMA synchronous=NORMAL;")    # -- fsync WAL at checkpoint, not per commit
        conn.execute("PRAGMA wal_autocheckpoint=1000;") # -- checkpoint every ~1000 pages; bounds the loss window
        # Verify: synchronous reads back as an INTEGER level (0/1/2/3), not a word.
        mode = conn.execute("PRAGMA journal_mode;").fetchone()[0]
        sync = conn.execute("PRAGMA synchronous;").fetchone()[0]
        assert str(mode).lower() == "wal", f"journal_mode not WAL: {mode!r}"
        assert sync == EXPECTED_SYNC, f"synchronous expected {EXPECTED_SYNC}, got {sync!r}"
    except sqlite3.Error as exc:
        raise RuntimeError(f"durability configuration failed: {exc}") from exc
    return conn

# For a zero-loss requirement, set FULL and assert 2 instead:
def configure_full(db_path: str) -> sqlite3.Connection:
    try:
        conn = sqlite3.connect(db_path, timeout=5.0)
        conn.execute("PRAGMA journal_mode=WAL;")      # -- keep WAL for concurrent readers
        conn.execute("PRAGMA synchronous=FULL;")      # -- fsync WAL on EVERY commit; no rollback window
        sync = conn.execute("PRAGMA synchronous;").fetchone()[0]
        assert sync == 2, f"synchronous expected 2 (FULL), got {sync!r}"
    except sqlite3.Error as exc:
        raise RuntimeError(f"FULL durability configuration failed: {exc}") from exc
    return conn

Because the durability window under NORMAL is bounded by how often the WAL is synced, the checkpoint cadence is part of this decision — a shorter wal_autocheckpoint interval narrows the window at the cost of more frequent syncs, which is exactly the ground covered in checkpoint frequency tuning.

Workload Profiles & Threshold Reference #

Failure Documentation & Edge Cases #

synchronous=OFF corruption on power loss #

Trigger: synchronous=OFF set to chase write throughput; a power loss lands mid-checkpoint while the database file is partially rewritten. Diagnosis: next open returns SQLITE_CORRUPT or SQLITE_NOTADB, or PRAGMA integrity_check reports errors. Fallback: never use OFF on data you cannot rebuild; move to NORMAL in WAL, which keeps the database consistent across power loss and only risks the unsynced tail of recent commits.

FULL needlessly destroying flash endurance and throughput #

Trigger: synchronous=FULL left as the default on a write-heavy embedded device where losing the last second of samples is acceptable. Diagnosis: commit latency dominated by fsync; flash write-amplification and wear metrics climbing far faster than the logical data rate. Fallback: switch to NORMAL under WAL and tune the checkpoint interval to bound the loss window; reserve FULL for data where every commit is a promise.

NORMAL in DELETE mode assumed as strong as in WAL #

Trigger: synchronous=NORMAL set without first confirming WAL mode, so the weaker rollback-journal semantics apply. Diagnosis: PRAGMA journal_mode returns delete, not wal, while durability was reasoned about as if WAL. Fallback: set WAL first and verify both PRAGMAs together; the NORMAL guarantee you want exists only in WAL mode. See switching from DELETE to WAL mode safely.

Volatile drive write cache lying about fsync #

Trigger: a cheap SD card or consumer SSD without power-loss protection acknowledges fsync while the data still sits in a volatile buffer. Diagnosis: corruption or lost commits after power loss despite FULL; the PRAGMA was honored by SQLite but not by the hardware. Fallback: treat storage as the durability floor — enable honest write barriers, use media with power-loss protection, and confirm ownership and mount options via file system permissions and ownership.

Frequently Asked Questions #

What does synchronous=NORMAL actually fsync in WAL mode? #

In WAL mode, NORMAL syncs the WAL file at each checkpoint rather than on every commit, so many commits share one fsync. A commit appends frames to the WAL but does not force them to disk; the database file is synced when the checkpoint transfers those pages into it.

Can NORMAL corrupt the database on power loss? #

No. In WAL mode NORMAL keeps the database consistent across a power loss; the only risk is that the last few transactions committed since the previous sync may roll back. That is a bounded durability window, not corruption — WAL frame checksums let recovery discard the unsynced tail cleanly.

When is FULL actually required? #

When you cannot tolerate losing any committed transaction on sudden power loss and the storage cannot be trusted to preserve recently written WAL frames. FULL fsyncs the WAL on every commit, closing the rollback window at the cost of throughput and additional flash wear.

Why is synchronous=OFF dangerous? #

OFF issues no syncs at all, so a power loss mid-checkpoint can leave the database file partially written and corrupt, returning SQLITE_CORRUPT or SQLITE_NOTADB on the next open. It is defensible only for disposable data you can rebuild.

Production Hardening Checklist #

• Confirm WAL mode before reasoning about NORMAL; its guarantee is stronger in WAL than in DELETE.
• Default to synchronous=NORMAL for telemetry, caches, and desktop state where a bounded loss window is acceptable.
• Use synchronous=FULL only where every committed transaction is a promise, and accept the per-commit fsync cost.
• Never ship synchronous=OFF on data you cannot rebuild from another source.
• Verify the setting by reading PRAGMA synchronous back and asserting the integer level (0/1/2/3), not the keyword.
• Bound the NORMAL loss window explicitly by tuning the checkpoint interval alongside the PRAGMA.
• Treat storage as the true durability floor: audit any medium whose write cache may not honor fsync.

For the authoritative semantics, consult the official SQLite PRAGMA synchronous documentation and the Write-Ahead Logging documentation.

Related Pages #

• WAL Optimization & Concurrency Tuning — the parent reference this comparison belongs to.
• PRAGMA Optimization Guide — the full baseline synchronous sits within.
• Configuring the synchronous PRAGMA for Crash Safety — the focused how-to behind this decision.
• Checkpoint Frequency Tuning — bounding the NORMAL loss window by controlling WAL syncs.
• File System Permissions & Ownership — the storage and mount review durability ultimately rests on.