SQLite WAL Configuration & Online Backups
Production connection defaults so concurrent readers never block writers and backups capture consistent state.
Connection Initialization PRAGMAs
Run per-connection pragmas immediately after opening each SQLite handle rather than once at database creation:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA temp_store = MEMORY;
Avoiding Silent File Copy Corruption
Never use plain cp or tar directly on a live SQLite database under WAL mode: copying the main file out of sync with its -wal and -shm companions leaves uncommitted transactions stranded or breaks B-tree pointers. Use the built-in online backup command instead:
sqlite3 /var/lib/app/state.sqlite3 ".backup /var/backups/state.snap.sqlite3"
Checkpoint Starvation
Long-lived read transactions pin the WAL index frame and cause the -wal file to grow indefinitely. Keep reader queries short, commit idle transactions promptly, and schedule periodic passive checkpoints with PRAGMA wal_checkpoint(TRUNCATE); during low traffic windows.