Floe Notes

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.