SQLite Performance Tips for Web Applications

SQLite is increasingly used in production web applications, but its performance characteristics are different from client-server databases. Here are the optimization techniques I've learned running SQLite in production at DailyWatch . Essential PRAGMAs These should be set every time you open a connection: $db = new PDO ( 'sqlite:data/videos.db' ); $db -> setAttribute ( PDO :: ATTR_ERRMODE , PDO :: ERRMODE_EXCEPTION ); // Critical PRAGMAs for web applications $db -> exec ( 'PRAGMA journal_mode = WAL' ); // Write-Ahead Logging $db -> exec ( 'PRAGMA synchronous = NORMAL' ); // Safe balance of speed/durability $db -> exec ( 'PRAGMA cache_size = -20000' ); // 20MB page cache $db -> exec ( 'PRAGMA busy_timeout = 5000' ); // Wait 5s for locks $db -> exec ( 'PRAGMA foreign_keys = ON' ); // Enforce FK constraints $db -> exec ( 'PRAGMA temp_store = MEMORY' ); // Temp tables in RAM $db -> exec ( 'PRAGMA mmap_size = 268435456' ); // 256MB memory-mapped I/O WAL Mode: The Single Most Important Setti