Part 158: SQLite Deep Dive: WAL Mode, In-Memory DBs & PRAGMA Tuning

Unlocking extreme performance from the world's most deployed database engine.

1. Enabling Write-Ahead Logging (WAL Mode)

By default, SQLite uses a rollback journal which blocks concurrent readers while writing: - WAL Mode allows concurrent readers and a single writer simultaneously! - Readers read the base database file + WAL file without acquiring table locks.

-- Enable WAL mode (persists across connections in the database header)
PRAGMA journal_mode = WAL;

-- Set synchronous mode to NORMAL (safe against application crashes, drastically faster writes)
PRAGMA synchronous = NORMAL;

2. Performance PRAGMA Presets for High-Throughput SQLite

Execute these commands when establishing every connection:

-- Store temporary tables and indices in RAM instead of disk
PRAGMA temp_store = MEMORY;

-- Increase memory page cache size (e.g. 64MB cache)
PRAGMA cache_size = -64000;

-- Enable Memory-Mapped I/O (reads bypass kernel buffers)
PRAGMA mmap_size = 268435456; -- 256 MB

-- Enforce Foreign Key constraints (disabled by default in SQLite!)
PRAGMA foreign_keys = ON;

3. In-Memory and Shared In-Memory Databases

-- Private in-memory database (destroyed when connection closes)
ATTACH DATABASE ':memory:' AS temp_db;

-- Shared in-memory database across multiple threads via URI
-- sqlite3_open("file:shared_mem?mode=memory&cache=shared", &db);

Key Takeaways

• Always switch SQLite to journal_mode = WAL and synchronous = NORMAL for production web servers.
• Always verify PRAGMA foreign_keys = ON; in application connection initialization.

In Part 159, we explore Latches vs Locks: Buffer Latches, Spinlocks & Page Splits!