SQLite pragmas for better performance

SQLite default settings prioritize safety over speed. For many applications, better performance is possible with safe pragma changes.

Apply these pragmas at connection time:

import sqlite3

conn = sqlite3.connect('app.db')

# WAL mode - allows concurrent reads during writes
conn.execute('PRAGMA journal_mode=WAL')

# Synchronous NORMAL - safe with WAL, much faster than FULL
conn.execute('PRAGMA synchronous=NORMAL')

# Larger cache - default is 2MB, increase for read-heavy workloads
conn.execute('PRAGMA cache_size=-64000') # 64MB (negative = KB)

# Memory-mapped I/O - faster reads
conn.execute('PRAGMA mmap_size=268435456') # 256MB

# Foreign keys (off by default!)
conn.execute('PRAGMA foreign_keys=ON')

# Temp store in memory
conn.execute('PRAGMA temp_store=MEMORY')

# Busy timeout instead of immediate SQLITE_BUSY errors
conn.execute('PRAGMA busy_timeout=5000') # 5 seconds

# For read-only workloads:
# conn.execute('PRAGMA query_only=ON')

# Periodic optimization:
# conn.execute('PRAGMA optimize') # Run occasionally

WAL mode alone can give 10x write performance improvement. These pragmas are safe for most applications and widely recommended.