| name | sqlite-wal |
| description | Deep SQLite operational intuition — WAL mechanics, checkpointing, fsync semantics, multi-process locking, FTS5 internals, replication (Litestream/LiteFS/rqlite). Load when diagnosing WAL, SQLITE_BUSY, multi-writer concurrency, network-FS corruption, pragma tuning, or backup/replication. Skip for ordinary SQL, schema design, or basic indexing. Triggers on: "WAL mode", "wal_checkpoint", "SQLITE_BUSY", "busy_timeout", "BEGIN IMMEDIATE", "sqlite over NFS", "FTS5 tokenizer", "Litestream", "LiteFS".
|
SQLite Operational Guide
Concise operational pointers for deep SQLite tuning, WAL diagnosis, and multi-process correctness.
Assumes you already know SQL and basic SQLite usage (.dump, .schema, EXPLAIN QUERY PLAN). This skill covers the operational layer — WAL internals, fsync semantics, lock states, pragma defaults, FTS5/JSON1 quirks, network-FS hazards.
When to use
Load when the question is about:
- WAL mechanics, checkpointing,
-wal/-shm file behavior
SQLITE_BUSY / SQLITE_BUSY_SNAPSHOT diagnosis, busy_timeout tuning- Multi-process or multi-host SQLite (and why most attempts corrupt)
- Pragma-level perf tuning (
synchronous, cache_size, mmap_size, temp_store)
- FTS5 index design (tokenizers, contentless tables, vocab tables)
- JSON1 indexing via generated columns; JSONB choice (3.45+)
- Backup correctness (
VACUUM INTO, backup API, sqlite3_rsync)
- Replication patterns: Litestream / LiteFS / rqlite tradeoffs
Do NOT load for: ordinary SELECT/INSERT writing, schema-first design, "what index should I add", basic CREATE TABLE syntax — those don't need this skill.
WAL mechanics
- Enable:
PRAGMA journal_mode=WAL returns wal on success. Persists across connections in the file header — unlike every other journal mode. To revert: PRAGMA journal_mode=DELETE (default).
- Three files:
name.db, name.db-wal (append-only log of new page versions), name.db-shm (mmap'd wal-index, ephemeral). All three are required while connections are open. Lost -shm is recoverable on next open.
- Concurrency model: many readers + one writer concurrent. Writers append to WAL; readers see a snapshot at txn start (their "end mark") and consult WAL pages newer than the main DB but ≤ their end mark. Rollback journal mode serializes everything; WAL does not.
- Checkpoint = fold WAL pages back into main DB. Not the same as commit. Commit just appends a frame to
-wal.
-shm is mmap-only — never required to be on disk. If the FS doesn't support shared mmap (most network FS), WAL fails. Workaround: PRAGMA locking_mode=EXCLUSIVE before first WAL access — -shm is then never created, but the connection holds the file exclusively.- Format version bump: opening a WAL DB with an SQLite older than 3.7.0 fails — WAL bumps the file-format version from 1 to 2.
Checkpointing
- Auto-checkpoint default:
PRAGMA wal_autocheckpoint=1000 (pages, ~4 MB at the default 4096-byte page size). Triggered at end of any commit that grows WAL ≥ N pages. Setting 0 disables.
- Manual modes:
PRAGMA wal_checkpoint(MODE):
PASSIVE (default): does whatever it can without blocking; may not finish if readers/writers are active.FULL: waits for writers, then checkpoints all committed frames; may block briefly.RESTART: like FULL, then waits for readers past the checkpoint to finish; next writer starts at WAL offset 0 (file size unchanged).TRUNCATE: like RESTART, then truncates -wal to zero bytes.
- Checkpoint starvation: a long-running read txn pins its end mark, blocking the checkpointer from advancing past unread frames.
-wal grows unbounded. Symptom: -wal >> .db. Fix: kill the long reader; use PRAGMA journal_size_limit=N (default -1 = no limit) to bound the file post-checkpoint (does not prevent growth between checkpoints).
- Litestream/LiteFS pattern: app sets
PRAGMA wal_autocheckpoint=0 and lets the replicator drive checkpoints, so frames aren't dropped before being shipped.
- WAL-reset bug (3.7.0–3.51.2): two concurrent connections checkpointing/writing could corrupt. Fixed in 3.51.3 / backported 3.50.7, 3.44.6. Verify version on production.
Synchronous and durability
- Defaults:
PRAGMA synchronous=FULL (2) for rollback journal. WAL mode's effective default depends on the binary; always set explicitly.
- Recommended for WAL:
PRAGMA synchronous=NORMAL (1). fsync's -wal only at checkpoint boundaries (not every commit). Power-loss safe because WAL is append-only and torn appended frames are detected and discarded on recovery. Throughput: typically 2–10× over FULL.
FULL (2) under WAL: fsync after every commit and at checkpoint. Worst-case durability, lowest throughput. Use only if you cannot tolerate losing the last few committed transactions on power loss.OFF (0): no fsync ever. Unsafe under power loss — both rollback and WAL modes can corrupt. Acceptable only for ephemeral/derived data.- macOS specific: stock
fsync() does NOT flush the disk write cache. Set PRAGMA fullfsync=1 (default OFF, macOS-only) to use F_FULLFSYNC. Cost: large; benefit: actual durability on consumer SSDs.
- Linux ext4: default fsync is sufficient when the FS is mounted with
barrier=1 (default since ~2010). USB sticks and consumer SD cards routinely lie about sync — corruption on power loss is the storage's fault, not SQLite's.
- Crash recovery: hot WAL is detected on next open; uncommitted frames are discarded. Do NOT delete
-wal or -shm after a crash — it destroys committed-but-not-checkpointed transactions. Safe deletion requires a clean shutdown (last connection closes, checkpoint runs).
Locking and concurrency
- Rollback-journal lock states (
UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE): WAL skips most of these. WAL writers acquire a write lock on a byte range in -shm; readers acquire a per-reader byte-range lock recording their end mark. Many readers + one writer truly concurrent.
SQLITE_BUSY (5): another connection holds an incompatible lock right now. Resolved by waiting/retrying.SQLITE_BUSY_SNAPSHOT (517): WAL-specific. A read txn began with end-mark E; another connection wrote and committed beyond E; this connection now tries to upgrade its read txn to a write txn. Cannot proceed without restarting the txn — busy_timeout does not retry this. Fix: re-issue BEGIN IMMEDIATE and replay.PRAGMA busy_timeout=N (ms). Default 0 (return BUSY instantly). Production minimum: 5000. Implementation: short polling sleeps internally, retries on SQLITE_BUSY only — not on BUSY_SNAPSHOT.BEGIN modes:
BEGIN / BEGIN DEFERRED (default): acquires no lock until first statement. Read becomes write on first DML — that upgrade is where BUSY_SNAPSHOT happens.BEGIN IMMEDIATE: acquires the write lock at BEGIN. If another writer is active, fails with BUSY now (recoverable via busy_timeout). Use this for any txn that will write.BEGIN EXCLUSIVE: in WAL mode, identical to IMMEDIATE. In rollback mode, blocks readers too.
- Single-writer rule: SQLite serializes writes per database file. Multi-process writers ⇒ funnel writes through a single process or accept BUSY/SNAPSHOT retry storms. Keep write txns short (sub-second).
- Threading:
SQLITE_THREADSAFE defaults to 1 (serialized) when compiled without flags. Mode 2 (multi-thread) is safe across threads only if each connection/prepared statement is touched by one thread at a time. Prepared statements are NOT thread-safe regardless of mode.
Network filesystems = corruption
- NFS, SMB, FUSE, sshfs, GCS Fuse, S3FS, EFS: do not host an active SQLite DB on these. POSIX advisory locking is buggy or unimplemented;
-shm mmap does not synchronize across hosts; fsync() semantics are unreliable. Multi-host writes corrupt within minutes.
- Single-host on NFS: marginally safer with
PRAGMA locking_mode=EXCLUSIVE (avoids -shm), but fsync reliability is still the FS's problem. Treat as best-effort, never authoritative.
- Hard/symlinks: opening the same DB through two distinct names yields two distinct
-wal/-shm pairs ⇒ corruption.
- Backup-while-open:
cp / rsync of an open DB captures inconsistent state. Always use VACUUM INTO, the backup API, or sqlite3_rsync (3.47+).
Pragma tuning
FTS5 quirks
- Content modes:
- Default: full row stored in FTS5 shadow tables. Largest, simplest.
content=t1, content_rowid=a: external content. Index only; query joins back to source. Requires triggers (AFTER INSERT/UPDATE/DELETE) to keep in sync — easy to drift.content='': contentless. Smallest. No UPDATE/DELETE (use INSERT INTO ft(ft, rowid) VALUES('delete', N)). 3.43+ adds contentless_delete=1 for true DELETE/INSERT-OR-REPLACE.
- Tokenizers:
unicode61 (default): case-insensitive, strips diacritics by default (remove_diacritics=1). Set 0 to preserve.trigram: required for substring (%foo%) / LIKE / GLOB acceleration. 3-grams as tokens.porter: English stemming wrapper (tokenize='porter unicode61').ascii: ASCII-only fast path; non-ASCII becomes token chars.
- MATCH syntax:
thr* (prefix), ^one (initial token), col : "phrase" (column filter), NEAR(a b, 5) (within N tokens, default 10), - (NOT). Implicit AND beats OR.
detail knob (size vs query power):
full (default): rowid + col + offset. Supports NEAR, phrase, snippets.column: rowid + col. ~50% smaller; no NEAR/phrase.none: rowid only. ~80% smaller; no NEAR/phrase/column filter.
- Maintenance:
INSERT INTO ft(ft) VALUES('optimize') merges all segments — slow; one-shot. INSERT INTO ft(ft, rank) VALUES('merge', N) does incremental work. automerge defaults to 4, crisismerge to 16.
fts5vocab: virtual table exposing term/col/doc/cnt. Useful for token-distribution analysis and detecting tokenizer mismatches.
JSON and JSONB
Backups and replication
VACUUM INTO 'file.db': atomic, online, transaction-consistent. Reads with shared lock; writes a fresh DB file with no -wal artifacts. Recommended default for app-level backups.- Backup API (
sqlite3_backup_init/step/finish): copies pages with concurrent writes allowed; restarts pages that change mid-copy. Library-level only.
sqlite3_rsync (3.47+): rsync-style delta copy of an open DB. Designed for low-bandwidth replication.- Plain file copy is safe ONLY when:
journal_mode=DELETE and no connection is open. With WAL, copying just .db produces a stale snapshot — must copy .db + .db-wal + .db-shm atomically, which a plain cp cannot guarantee.
- Litestream: tails the WAL by reading
-wal frames; ships them to S3/blob storage. Requires wal_autocheckpoint=0 so app-driven checkpoints don't truncate frames before shipment.
- LiteFS: FUSE FS that intercepts SQLite writes at the page level; multi-node primary-replica with per-DB leadership. Constraint: one writer per DB, replicas read-only.
- rqlite / dqlite: SQLite behind Raft. Each write is a Raft log entry; SQL is replayed on every node. Different correctness model — multi-master at the cost of write latency.
Common pitfalls
INTEGER PRIMARY KEY IS the rowid — alias for the hidden 64-bit rowid. INT PRIMARY KEY is NOT (subtle: INT ≠ INTEGER in this one place). Inserting NULL auto-generates.- Type affinity is hint-level in default tables —
CREATE TABLE t(n INTEGER); INSERT INTO t VALUES('hello') succeeds. Use STRICT tables (3.37+, 2021-11-27) to enforce: CREATE TABLE t(n INTEGER) STRICT — only INT/INTEGER/REAL/TEXT/BLOB/ANY allowed; mismatches raise SQLITE_CONSTRAINT_DATATYPE.
WITHOUT ROWID: requires explicit PRIMARY KEY (NOT NULL enforced). Single B-tree (no rowid index). Best for non-integer PKs and small rows (< 1/20 of page size, ~200 B at 4 KB pages). INTEGER PRIMARY KEY stops being a rowid alias here. No AUTOINCREMENT.ATTACH DATABASE: cross-DB queries work, but only the main DB's WAL handles atomicity. Multi-DB writes are NOT atomic across files in WAL mode — use rollback journal if cross-file atomicity is required.PRAGMA foreign_keys is per-connection: your migration tool may have it ON, your app OFF, your repl shell different again. Pin it on every open.- Triggers: per-row, fire after each affected row's modification.
recursive_triggers=OFF by default ⇒ a trigger that re-modifies the same table won't cascade. Surprising for ORM-style soft-delete patterns.
- Single-writer constraint: queue all writes through a dedicated process or a per-DB mutex in your app. Long write txns (> ~100 ms) starve readers via SNAPSHOT, even though "readers don't block writers." Break large writes into chunks.
Authoritative references
Official SQLite docs (sqlite.org):
Replication and operator deep-dives:
- Litestream tips — WAL settings, busy_timeout, autocheckpoint disable
- Fly.io: SQLite Internals: WAL — Ben Johnson on checkpoint dynamics
- LiteFS docs (
fly.io/docs/litefs/) — FUSE-level page replication
- rqlite / dqlite project docs — Raft-over-SQLite tradeoffs
Guardrails
Before recommending a non-trivial pragma change or replication topology:
- Quote the exact pragma name and its default value (per
pragma.html).
- Cite the official SQLite doc section.
- Make the recommendation conditional on observed metrics —
-wal size, SQLITE_BUSY count, txn duration.
- Verify SQLite version (
SELECT sqlite_version()). Many features (JSONB 3.45, STRICT 3.37, contentless_delete 3.43, sqlite3_rsync 3.47, WAL-reset fix 3.51.3) are version-gated.
Tuning without measurement, or porting a Postgres mental model wholesale to SQLite, is worse than defaults.
