SQLite has long been seen as an embedded database for mobile, IoT, and local dev, but the rise of high‑speed NVMe SSDs and single‑tenant edge deployments has made network latency a bottleneck for traditional client‑server databases.

Running SQLite directly inside the app process eliminates network overhead, turning reads into memory‑mapped file operations with sub‑millisecond latency. However, out‑of‑the‑box SQLite prioritizes safety over throughput. To unlock performance, you must tweak Write‑Ahead Logging (WAL), locking, cache, and the Virtual File System (VFS).

By default SQLite uses a rollback journal that blocks reads/writes. Enabling WAL (PRAGMA journal_mode=WAL) allows concurrent reads and writes; writers append to a separate .sqlite‑wal file while readers continue to read the main database. Checkpointing merges WAL pages back; four modes—PASSIVE, FULL, RESTART, TRUNCATE—determine how the merge occurs. In production, schedule PASSIVE or RESTART checkpoints to avoid indefinite growth.

SQLite still enforces a single‑writer model; a second writer returns SQLITE_BUSY. Use a busy timeout (PRAGMA busy_timeout=5000) and BEGIN IMMEDIATE to avoid deadlocks. Increase cache size (PRAGMA cache_size=-64000 for ~64MB) and use mmap (PRAGMA mmap_size=2147483648 for 2GB) to map the database into memory, turning disk reads into pointer arithmetic. Custom VFS layers like Litestream or LiteFS enable replication and distributed storage.