SQLite performance tuning for applications

SQLite is slow for write-heavy workloads or concurrent access in application use.

Essential SQLite pragmas and patterns:

-- Apply on every connection open
PRAGMA journal_mode = WAL;          -- Write-Ahead Logging (concurrent reads)
PRAGMA synchronous = NORMAL;        -- Safe with WAL, much faster
PRAGMA busy_timeout = 5000;         -- Wait 5s on lock instead of failing
PRAGMA cache_size = -64000;         -- 64MB cache (negative = KB)
PRAGMA foreign_keys = ON;           -- Enforce FK constraints
PRAGMA temp_store = MEMORY;         -- Temp tables in memory

-- For write-heavy workloads
PRAGMA mmap_size = 268435456;       -- Memory-map 256MB of DB
PRAGMA page_size = 4096;            -- Match filesystem block size

# Python: apply pragmas on connect
import sqlite3

def get_connection(db_path):
    conn = sqlite3.connect(db_path)
    conn.execute('PRAGMA journal_mode = WAL')
    conn.execute('PRAGMA synchronous = NORMAL')
    conn.execute('PRAGMA busy_timeout = 5000')
    conn.execute('PRAGMA cache_size = -64000')
    conn.execute('PRAGMA foreign_keys = ON')
    return conn

# Batch inserts: use transactions
conn = get_connection('app.db')
with conn:
    conn.executemany(
        'INSERT INTO items (name, value) VALUES (?, ?)',
        [(f'item_{i}', i) for i in range(10000)]
    )  # 100x faster than individual inserts

Key insights:

• WAL mode: 1 writer + N readers concurrently
• Batch inserts in transactions (not autocommit)
• Create indexes after bulk insert
• Use EXPLAIN QUERY PLAN to verify index usage

Default SQLite settings prioritize safety over speed. WAL mode and proper pragmas can improve performance 10-100x for common application patterns.

Applications using SQLite as their database