WAL vs DELETE Journal Mode for IoT #

An IoT gateway that logs a few hundred sensor rows a minute and serves a local status dashboard has exactly the workload where the journal-mode choice is decided wrong most often. Reach for Write-Ahead Logging because a blog said it is faster, and you inherit two extra files, a shared-memory requirement, and a checkpoint you now have to schedule — on a device with a 4 GB eMMC and a read-only rootfs image. Keep the DELETE rollback default and your dashboard query freezes every time the ingestion loop commits. This decision sits at the center of the SQLite Architecture & Production Hardening discipline because it is made once, persists in the database header or your provisioning code, and quietly shapes durability, latency, and flash lifetime for the life of the fleet. This guide frames the trade-off for edge and IoT hardware specifically: what each mode actually does on power loss, which filesystems refuse WAL, how the fsync accounting differs, and how to pick with a repeatable decision rather than a preference. The mechanics of each mode in isolation are worked through in the journaling modes deep dive; here the goal is only to choose between the two for a device.

Core Mechanism & Crash-Safety Defaults #

The two modes differ in where the new data goes first. In DELETE — the historical default and still the mode SQLite falls back to when WAL is unavailable — a writer first copies the original page images into a -journal file, then overwrites pages in the main database in place, then deletes the journal on commit. Atomicity comes from the saved originals: if power fails mid-write, the next open finds the journal, copies those pages back, and unwinds the interrupted transaction. The cost is serialization. The writer holds an EXCLUSIVE lock over the entire database file for the whole commit, so every reader blocks and may see SQLITE_BUSY. Every commit also drives at least two fsync() barriers — one on the journal, one on the main file — which on flash means scattered, in-place rewrites that age the medium faster.

WAL inverts this. Committed changes are appended as frames to a separate -wal file while a -shm shared-memory file coordinates which frames each connection may read. The main database is not touched at commit time at all; it is updated later, in bulk, during a checkpoint. Readers keep serving a consistent snapshot — the main file plus the WAL frames that existed when their transaction began — so one writer and many readers proceed at once without blocking. Because frames are appended sequentially and carry per-frame checksums, WAL is gentler on flash wear-leveling and lets synchronous=NORMAL be safe: the fsync can be deferred to checkpoint time without risking corruption, only the loss of the last few pre-crash commits.

There are two costs that matter on an edge device. First, WAL needs three files where DELETE needs one — the database, -wal, and -shm — and it needs to mmap the -shm file into shared memory. Filesystems that do not implement POSIX advisory locking and shared memory correctly (NFS, SMB, many FUSE overlays) cannot host a WAL database safely, and a read-only rootfs cannot create the -shm file at all. Second, the WAL grows until a checkpoint folds it back; without a bounded checkpoint cadence it can fill a small partition and raise SQLITE_FULL. Both costs are absent in DELETE, which is precisely why DELETE remains the right answer for a meaningful slice of edge deployments rather than a legacy default to be migrated away from.

Side-by-Side Comparison #

The table below compares the two modes on the axes that decide an IoT deployment. Read it as the payload behind the decision diagram: the first three rows gate the choice, the rest quantify what you trade.

Two rows deserve emphasis for IoT. The filesystem requirements row is a hard gate, not a preference: if the medium cannot host WAL, no amount of tuning changes that, and the safe response is DELETE, covered further in managing file locks on FAT32 vs ext4. The fsync cost row is where WAL earns its keep on write-heavy loggers, because deferring the barrier is what the synchronous pragma configuration for crash safety makes safe in WAL and dangerous in a rollback journal.

How to Choose #

The choice is a three-step filter: measure the workload, test the constraints that can veto WAL, then apply and prove the mode actually took. Do not skip the third step — a mode switch that silently no-ops is invisible until a crash exposes it.

1. Profile the workload #

Answer four questions with numbers, not intuition. How many processes or threads open the database, and do any of them read while another writes? If nothing ever reads concurrently with the writer — a single-process logger flushing to disk — WAL’s headline feature is unused and DELETE’s single-file simplicity is a net win. What is the commit rate? A high-frequency logger committing many times per second benefits sharply from WAL’s deferred fsync; a device that commits once a minute barely notices. How much storage headroom exists for a growing -wal before a checkpoint reclaims it? On a small partition this bounds how large wal_autocheckpoint can safely be. Finally, how wear-sensitive is the medium — a raw SD card ages far faster under in-place rewrites than a managed eMMC with over-provisioning. Match the answers to the profile table below before touching a PRAGMA.

2. Check filesystem and power constraints #

Two constraints can veto WAL outright regardless of the workload. First, the filesystem: WAL requires POSIX advisory locking and a shared-memory mapping for the -shm file. Confirm the database lives on a local, writable filesystem (ext4, f2fs, or similar) — not on an NFS/SMB mount and not on a read-only rootfs image where the -shm file cannot be created. Second, power and durability posture: if an acknowledged commit must survive an abrupt power cut with zero loss (a metering or billing ledger), you need per-commit fsync, which means either WAL with synchronous=FULL or DELETE with synchronous=FULL; on the flash-wear axis, WAL’s sequential appends usually still win. If the device tolerates losing the last second of telemetry on a brownout, synchronous=NORMAL is correct and only WAL makes it both fast and safe. Route any filesystem that fails the first check to DELETE.

3. Apply the chosen mode and verify the read-back #

Set the mode explicitly and read it back and assert it. In WAL, journal_mode=WAL silently returns the old mode if another connection holds the file; in DELETE, a stray earlier connection can have left the database in WAL in the persisted header. Neither failure is visible without an assertion.

import sqlite3
import logging

logger = logging.getLogger(__name__)


def configure_journal_mode(db_path: str, mode: str = "WAL") -> sqlite3.Connection:
    """
    Apply the chosen journal mode for an IoT deployment and verify it took.
    mode is "WAL" for a shared dashboard+logger, "DELETE" for a lone logger
    or an exotic/read-only filesystem. Fails loudly on any mismatch.
    """
    mode = mode.upper()
    if mode not in ("WAL", "DELETE"):
        raise ValueError(f"unsupported journal mode: {mode!r}")

    conn = sqlite3.connect(db_path, timeout=5.0, isolation_level="DEFERRED")
    try:
        applied = conn.execute(f"PRAGMA journal_mode={mode};").fetchone()[0]  # persistent in the DB header; returns the effective mode
        if mode == "WAL":
            conn.execute("PRAGMA synchronous=NORMAL;")           # fsync at checkpoint, not per commit; safe only in WAL
            conn.execute("PRAGMA wal_autocheckpoint=256;")       # fold WAL back every ~1 MB (256 * 4 KiB) to bound growth on flash
        else:
            conn.execute("PRAGMA synchronous=FULL;")             # rollback needs per-commit fsync to keep acknowledged commits
        conn.execute("PRAGMA busy_timeout=5000;")                # wait up to 5000 ms on a locked write before SQLITE_BUSY
        conn.execute("PRAGMA foreign_keys=ON;")                  # enforce referential integrity (OFF by default)

        # --- Mandatory read-back verification ---------------------------------
        effective = conn.execute("PRAGMA journal_mode;").fetchone()[0]
        sync = conn.execute("PRAGMA synchronous;").fetchone()[0]  # 1 == NORMAL, 2 == FULL
        if effective.lower() != mode.lower():
            raise RuntimeError(
                f"journal_mode is {effective!r}, expected {mode!r}; "
                "another connection may hold the file, or the filesystem rejects WAL"
            )
        expected_sync = 1 if mode == "WAL" else 2
        if sync != expected_sync:
            raise RuntimeError(f"synchronous is {sync}, expected {expected_sync}")
        logger.info("journal mode verified: mode=%s synchronous=%s", effective, sync)
        return conn
    except sqlite3.Error:
        logger.exception("journal-mode configuration failed")
        conn.close()
        raise

If step 3 raises when you asked for WAL, the read-back has caught exactly the case the decision was meant to prevent — the filesystem quietly refused WAL — and the correct response is to fall back to DELETE rather than run a database that thinks it is in a mode it is not. The safe live migration in the other direction is documented in switching from DELETE to WAL mode safely.

Workload Profiles & Threshold Reference #

There is no single correct answer; the mode follows the device class. Match the deployment to the nearest row, then confirm it against a real power-cycle test on the exact media the fleet runs.

The threshold that most often bites is wal_autocheckpoint on a small partition. A logger that commits faster than the checkpointer folds frames back — often because a long-lived reader pins the WAL — will grow -wal until the partition fills and writes fail with SQLITE_FULL. Bounding it is the subject of checkpoint frequency tuning; on constrained storage, pair a low autocheckpoint with a bounded read-transaction lifetime.

Failure Documentation & Edge Cases #

WAL on a read-only rootfs #

Trigger: the database sits on an immutable or read-only root image, or a squashfs overlay, and code opens it in WAL. SQLite must create db-wal and db-shm alongside the database and cannot.

Diagnosis: the open raises SQLITE_CANTOPEN or SQLITE_READONLY on the -shm file, or PRAGMA journal_mode=WAL reports a non-wal mode on an idle database. The read-back assertion in step 3 catches this at startup.

Fallback: relocate the database to a writable partition (/data, a tmpfs for ephemeral state, or a mounted flash volume), or if the database is genuinely query-only, ship it in DELETE mode and open it read-only so no journal is ever needed.

Orphaned -wal after an unclean shutdown #

Trigger: the process is killed mid-checkpoint, leaving a large -wal and a stale -shm. Committed but un-checkpointed frames still live in the WAL.

Diagnosis: the -wal file persists after all connections close; PRAGMA integrity_check; returns anything other than ok, or the trailing frame fails checksum validation.

Fallback: never delete -wal/-shm by hand while any connection is open — that discards committed transactions. Instead reopen the database (SQLite auto-replays checksum-valid frames), run PRAGMA integrity_check before accepting writes, then a PRAGMA wal_checkpoint(TRUNCATE) in a quiet window to reset the WAL.

DELETE reader-writer stalls #

Trigger: a DELETE-mode database is opened by both an ingestion loop and a dashboard query; the writer’s EXCLUSIVE lock blocks the reader on every commit, surfacing as UI freezes or SQLITE_BUSY.

Diagnosis: readers return SQLITE_BUSY clustered around commit times; raising busy_timeout masks latency but does not remove the serialization.

Fallback: this is inherent to rollback journaling. If the workload genuinely needs concurrent reads, the fix is to migrate to WAL — provided the filesystem supports it — via switching from DELETE to WAL mode safely; a larger busy_timeout is only a stopgap.

WAL on a network filesystem #

Trigger: the database is placed on an NFS, SMB, or FUSE mount to centralize storage, and opened in WAL. Shared memory and advisory locking are not implemented correctly across the mount.

Diagnosis: intermittent SQLITE_IOERR, -shm creation failures, or — worst case — silent inconsistency between nodes that no error surfaces.

Fallback: keep the live database on local disk and replicate or back it up over the network instead; if it must reside on the mount, use DELETE with synchronous=FULL and accept single-writer serialization. The filesystem-locking specifics are in managing file locks on FAT32 vs ext4.

Frequently Asked Questions #

Is WAL always faster than DELETE on IoT hardware? #

Not universally. WAL wins when readers and a writer overlap and when synchronous is NORMAL, because it defers the fsync to checkpoint time. For a single-process logger with no concurrent readers, DELETE with synchronous=FULL can be competitive and avoids the extra -wal and -shm files and the checkpoint bookkeeping.

Can I run WAL on a read-only root filesystem? #

No. WAL needs to create and write the -wal and -shm files and a shared-memory mapping next to the database. On a read-only rootfs or immutable image the -shm creation fails and the open falls back or errors with SQLITE_CANTOPEN or SQLITE_READONLY. Put the database on a writable partition, or ship a query-only database in DELETE mode.

Does WAL lose data on power loss with synchronous=NORMAL? #

It cannot corrupt the database, but the last few transactions committed just before the power cut may roll back because their fsync had not yet fired. DELETE with synchronous=FULL fsyncs every commit, so an acknowledged commit survives, at the cost of far more write and erase cycles on flash.

Why does my writer block all readers even though I expected concurrency? #

That is DELETE (rollback) behaviour: the writer holds an EXCLUSIVE lock over the whole database file for the duration of the commit, so readers stall and may return SQLITE_BUSY. Concurrent readers alongside one writer require WAL mode, where readers are served from a consistent snapshot.

Production Hardening Checklist #

• Decide the mode from the workload profile and filesystem test, not from a default — record the reasoning next to the provisioning code.
• Confirm the database lives on a local, writable filesystem before selecting WAL; route read-only images and network mounts to DELETE.
• Set journal_mode explicitly and assert the read-back equals the intended mode — never trust the persisted header.
• Use synchronous=NORMAL with WAL for telemetry; use FULL (either mode) only where an acknowledged commit must survive a brownout.
• With WAL, cap wal_autocheckpoint to the partition headroom (128–256 pages on tight flash) and schedule a periodic PRAGMA wal_checkpoint(PASSIVE).
• With DELETE, verify concurrent readers are genuinely absent; if they appear later, migrate to WAL rather than raising busy_timeout indefinitely.
• Run PRAGMA integrity_check after any suspected unclean shutdown before resuming writes; never hand-delete -wal/-shm with a connection open.
• Restrict the database and any -wal/-shm artifacts with strict filesystem permissions under a dedicated service account.
• Rehearse recovery against a simulated power cut on the exact media the fleet runs — desk testing on NVMe hides eMMC and SD failure modes.

Related Pages #

• Journaling Modes Deep Dive — the full mechanics of every rollback and WAL mode this decision draws on.
• Switching from DELETE to WAL Mode Safely — the zero-downtime migration once you have chosen WAL.
• Configuring the synchronous PRAGMA for Crash Safety — why NORMAL is safe under WAL and risky under a rollback journal.
• Checkpoint Frequency Tuning — bounding -wal growth once WAL is in production.
• Managing File Locks on FAT32 vs ext4 — the filesystem-locking constraints that can veto WAL entirely.

For authoritative detail, consult the official SQLite Write-Ahead Logging documentation.