Choosing TRUNCATE vs PERSIST Journal on Flash

You have a SQLite database on an SD card or eMMC module running the default DELETE rollback journal, and each commit is quietly burning flash: the engine creates a <db>-journal file, writes it, fsyncs it, fsyncs the directory to record the new inode, and then deletes the file and syncs the directory again. On spinning disks that directory churn is invisible; on wear-levelled flash with a coarse erase block it multiplies every small transaction into a fistful of metadata writes. If you are staying on a rollback journal — because a network mount, a FAT32 partition, or a strict durability requirement rules out Write-Ahead Logging — the lever you have is PRAGMA journal_mode, choosing TRUNCATE or PERSIST over DELETE. This page is the narrow decision within the Journaling Modes Deep Dive guide, part of the broader SQLite Architecture & Production Hardening discipline: given that you are on a rollback journal on flash, which of the three modes minimises the per-commit metadata cost, and how do you prove the switch took.

The three modes differ only in what happens to the journal file after a transaction commits successfully. DELETE unlinks the file, which forces the filesystem to update and sync the containing directory. TRUNCATE instead truncates the journal to zero bytes and zeroes its header, leaving the inode allocated so no directory update is needed. PERSIST leaves the file at full size and merely overwrites its header with zeroes, so the next transaction sees an invalid header and reuses the existing allocation. Fewer directory operations mean fewer small synchronous writes, and on flash that is the whole game.

Figure — The same COMMIT on a rollback journal, resolved three ways: DELETE unlinks and syncs the directory, TRUNCATE zeroes the header and keeps the inode, PERSIST leaves the file and only rewrites the header.

How DELETE, TRUNCATE, and PERSIST finalise the rollback journal at commit A single COMMIT on a rollback-journal database branches to three outcomes. DELETE unlinks the journal file, forcing a directory update and a second directory fsync, the most metadata-heavy path. TRUNCATE truncates the journal to zero bytes and zeroes its header while keeping the inode, so no directory sync is needed. PERSIST leaves the file at full size and only overwrites the header with zeroes, reusing the allocation on the next transaction — the fewest metadata operations, at the cost of a stale journal file left on disk. COMMIT · rollback journal journal replayed into the DB, now to finalise it DELETE DEFAULT unlink the journal file directory entry removed + directory fsync most metadata ops per commit on flash clean: no stray file TRUNCATE MIDDLE GROUND truncate journal to 0 B header zeroed, inode kept no directory update still fsyncs the file unless synchronous OFF no stray file PERSIST FEWEST WRITES zero the header only file left at full size allocation reused next txn fewest metadata ops stale file confuses naive backups

Diagnosis

First confirm you are actually on a rollback journal and paying the deletion cost. PRAGMA journal_mode reports the current mode without changing anything:

PRAGMA journal_mode;   -- 'delete' here means every commit unlinks the -journal and syncs its directory

If this returns delete, truncate, or persist, you are on the rollback family (not WAL) and the finalisation behaviour above applies. The symptom that brings you here is disproportionate write activity for a modest commit rate — a logger inserting a few rows a second showing thousands of block writes a minute in the kernel’s I/O accounting, or an SD card reporting media wear far ahead of the data volume you actually store.

Make the cost concrete by counting the syscalls one commit issues. On Linux, strace on the metadata operations tells you which mode you are in and what it costs — DELETE shows unlink plus a directory fsync, TRUNCATE shows ftruncate with no unlink, PERSIST shows neither:

# Count the journal-finalisation syscalls for a workload that does N commits.
# DELETE -> unlink + fsync(dir); TRUNCATE -> ftruncate; PERSIST -> only header write.
strace -f -e trace=unlink,unlinkat,ftruncate,fsync,fdatasync \
  -c python3 write_batch.py 2>&1 | tail -20

A DELETE-mode run dominated by unlink/unlinkat and directory fsync calls, scaling linearly with commit count, is the signature of avoidable metadata churn. Watch the journal file itself flicker to corroborate — under DELETE it appears and vanishes each transaction, under TRUNCATE it stays at zero bytes between commits, under PERSIST it stays at its high-water size:

# Poll the journal size while the writer runs:
watch -n0.2 'ls -la /var/lib/logger/events.db-journal 2>/dev/null || echo "no journal (deleted)"'

Solution

The switch is one PRAGMA, but journal-mode changes on a rollback journal are not persisted in the database header — they are per-connection and must be re-applied on every fresh handle, so the right place for this is your connection factory. Set the mode, then, for PERSIST, bound the leftover file with journal_size_limit so a one-off large transaction cannot leave a permanently oversized journal squatting on flash. Read every value back and assert it before returning the connection.

import sqlite3
import logging

logger = logging.getLogger("flash_journal")


def open_rollback_flash(db_path: str, mode: str = "TRUNCATE") -> sqlite3.Connection:
    """
    Open a rollback-journal connection tuned to minimise per-commit metadata
    churn on flash. `mode` is 'TRUNCATE' or 'PERSIST'. Every PRAGMA is read
    back and asserted before the handle is returned.
    """
    if mode not in ("TRUNCATE", "PERSIST"):
        raise ValueError("mode must be 'TRUNCATE' or 'PERSIST'")

    conn = sqlite3.connect(db_path, timeout=5.0)
    try:
        # journal_mode is per-connection for rollback modes; it RETURNS the
        # resulting mode and does not raise if the switch is refused.
        got = conn.execute(f"PRAGMA journal_mode={mode};").fetchone()[0]  # returns e.g. 'truncate'

        # FULL keeps crash-safety on a rollback journal: the journal is synced
        # before the DB is touched, so a power cut mid-commit rolls back cleanly.
        conn.execute("PRAGMA synchronous=FULL;")            # durable rollback journal; do not weaken on removable media

        # Bound the PERSIST journal so a big transaction can't leave a huge file
        # parked on flash forever; 64 KiB caps the reused allocation.
        conn.execute("PRAGMA journal_size_limit=65536;")    # cap leftover -journal at 64 KiB; -1 = no limit

        # --- read-back verification (assert against SQLite, not our variables) ---
        mode_now = conn.execute("PRAGMA journal_mode;").fetchone()[0]
        limit    = conn.execute("PRAGMA journal_size_limit;").fetchone()[0]
        if mode_now.lower() != mode.lower():
            raise RuntimeError(f"journal_mode is {mode_now!r}, expected {mode.lower()!r}")
        if limit != 65536:
            raise RuntimeError(f"journal_size_limit is {limit}, expected 65536")

        logger.info("rollback journal ready: mode=%s size_limit=%s", mode_now, limit)
        return conn
    except sqlite3.Error:
        logger.exception("failed to configure rollback journal on %s", db_path)
        conn.close()
        raise

TRUNCATE is the pragmatic default here: it removes the directory unlink/fsync that dominates DELETE’s cost while leaving no stale file to confuse tooling, so it is the safest reduction for most flash loggers. Reach for PERSIST only when you have measured that the residual file truncation under TRUNCATE is still your bottleneck and you can guarantee the leftover journal is handled correctly by everything that touches the directory. Note journal_size_limit is meaningful for both TRUNCATE and PERSIST (it caps the size the file is allowed to keep), but it is PERSIST where an unbounded limit actually hurts, because the file is never shortened otherwise.

Verification

Prove the mode took, then prove it changed the syscall profile. First, from the same connection and a fresh one, the mode must read back as chosen:

import sqlite3

with sqlite3.connect("/var/lib/logger/events.db") as check:
    try:
        mode = check.execute("PRAGMA journal_mode=TRUNCATE;").fetchone()[0]  # re-apply on this handle
        assert mode.lower() == "truncate", f"expected truncate, got {mode!r}"
        integ = check.execute("PRAGMA integrity_check;").fetchone()[0]        # must be 'ok' before trusting writes
        assert integ == "ok", f"integrity_check returned {integ!r}"
        print("verified: TRUNCATE journal active, integrity ok")
    except sqlite3.Error as exc:
        raise SystemExit(f"verification failed: {exc}")

Then re-run the strace -c count from the diagnosis step under the new mode and compare. A correct TRUNCATE configuration shows unlink/unlinkat dropping to zero while ftruncate appears once per commit; a correct PERSIST configuration shows neither unlink nor ftruncate after the journal reaches steady state, only the header rewrite folded into the ordinary page writes. If unlink is still present per commit, the switch did not take — most often because a pooled connection was handed back without the factory re-applying the mode.

Failure Modes & Gotchas

PERSIST leaves a real file that naive backups and health checks mistake for corruption or an unclean shutdown. Because the journal stays on disk with a zeroed header, a backup script that copies events.db-journal alongside events.db, or a monitor that treats “a -journal exists” as “a transaction is in flight,” will misread a perfectly healthy database. Never copy the stale journal into a backup as if it were live state; if you snapshot a PERSIST database, copy only the main file after confirming no transaction is open, or run PRAGMA wal_checkpoint-equivalent care by simply issuing a PRAGMA journal_mode read to confirm quiescence. This is the strongest argument for defaulting to TRUNCATE, where no such file lingers.

TRUNCATE still fsyncs the (now zero-length) journal unless you weaken synchronous, so it is not free. The mode removes the directory operation, not the file sync — with synchronous=FULL you still pay one fsync on the truncated journal to guarantee the rollback boundary. Dropping to synchronous=NORMAL or OFF to eliminate that sync trades away crash safety on a rollback journal in a way that WAL does not, and on removable media a mid-commit power loss can then leave the database inconsistent. Keep synchronous=FULL on rollback modes and accept the one sync; if that sync is genuinely the bottleneck, the answer is a different architecture, not a weaker durability setting.

This whole decision is orthogonal to WAL — and WAL is usually the better answer. DELETE, TRUNCATE, and PERSIST are all rollback journals that serialise readers behind the writer and pay a journal write per commit; they only differ in cleanup cost. Write-Ahead Logging changes the model entirely: commits append to a -wal file and readers proceed concurrently, which on flash typically means far less write amplification than any rollback mode. Choose among these three only when WAL is genuinely unavailable — a network filesystem without working shared-memory locking, or a hard requirement that the database be a single self-contained file at rest. If WAL is on the table, weigh it directly against DELETE in the WAL vs DELETE Journal Mode for IoT comparison, and if you decide to move, follow the verified procedure in switching from DELETE to WAL mode safely rather than flipping the mode blind.