Connection Pragmas and Configuration

~12 min · python, pragma, configuration

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The 'production-ready opener' worth memorizing

Most production Python apps using SQLite end up with a small connection-opening helper that sets a fixed list of PRAGMAs every time. The list, with rationale:

journal_mode = WAL — concurrent readers/writers (track 4).
synchronous = NORMAL — safe with WAL, faster than FULL.
foreign_keys = ON — actually enforce FKs (track 4).
busy_timeout = 5000 — wait briefly on transient locks instead of failing.
temp_store = MEMORY — keep temp B-trees in RAM rather than disk.
cache_size = -64000 — 64 MB page cache for read-heavy workloads.
mmap_size = 134217728 — 128 MB memory-mapped I/O for fast reads on large files.

Self-reference: Pippa's backend/store/conversations.py opens every connection with this exact set (with project-tuned cache and mmap sizes). The fact that the WebUI feels instant on a 100k-row JSONL+SQLite history is largely down to these seven settings.

Code

The opener you'll copy into every project·python

import sqlite3, atexit

def open_db(path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(path, timeout=30.0, isolation_level=None)
    conn.row_factory = sqlite3.Row
    for pragma in (
        'PRAGMA journal_mode = WAL',
        'PRAGMA synchronous = NORMAL',
        'PRAGMA foreign_keys = ON',
        'PRAGMA busy_timeout = 5000',
        'PRAGMA temp_store = MEMORY',
        'PRAGMA cache_size = -64000',     # 64 MB
        'PRAGMA mmap_size = 134217728',   # 128 MB
    ):
        conn.execute(pragma)
    atexit.register(lambda: (conn.execute('PRAGMA optimize'), conn.close()))
    return conn

External links

PRAGMA Statements

Exercise

Take an existing Python script of yours that uses sqlite3 with default settings. Replace the connection opener with the production-ready one above. Re-run any benchmarks you have. Note which PRAGMAs gave the biggest wins in your specific workload.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousCustom SQL Functions and Aggregates Next →Error Handling — sqlite3 Exception Classes

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.