SQLite PRAGMA Settings: Tuning Connections for Production

PRAGMA Is How You Talk to the Engine

A PRAGMA is a SQLite-specific statement that reads or changes how the engine behaves. You run them like any other SQL — but instead of touching your data, they touch the database's configuration.

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA page_size;

Run as a query, a PRAGMA returns the current value. Run as an assignment, it changes the value:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

The mental model: most PRAGMAs are per connection. Open a new connection and the defaults are back. That's why production code typically has a small block of PRAGMA statements that run immediately after every connection is established.

The Production Baseline

If you only remember five PRAGMAs, remember these:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;

SELECT 'configured' AS status;

That's a sensible default for almost any application using SQLite as its primary store. Each one is worth understanding individually — the rest of this page walks through them.

journal_mode = WAL

The journal mode controls how SQLite makes writes durable. The default, DELETE, uses a rollback journal: writers block readers and readers block writers. Fine for a CLI tool, painful for a web app.

WAL (Write-Ahead Logging) flips that around. Readers and writers don't block each other — readers see a consistent snapshot while a writer is committing. You still get one writer at a time, but reads stay fast under load.

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

A few things to know:

• journal_mode is persistent — once set, it stays that way for the database file. You don't need to set it on every connection, but it doesn't hurt.
• WAL creates two extra files alongside your .db: a -wal and a -shm. Don't delete them while the database is open.
• WAL doesn't work well over network filesystems (NFS, SMB). Keep the database on local disk.

There's a separate doc on WAL mode and concurrency that goes deeper. For now: turn it on.

synchronous = NORMAL

synchronous controls how aggressively SQLite flushes to disk. The trade-off is durability vs speed.

• FULL (default) — flush after every commit. Maximum durability. Slower.
• NORMAL — flush at safe checkpoints. Safe with WAL. Faster.
• OFF — let the OS decide. Fast but risks corruption on power loss.

PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

The integer in the result (1) corresponds to NORMAL. With WAL mode, NORMAL is the recommended setting — you don't lose committed transactions on a crash, you only risk losing the most recent ones on a power failure. For most apps that's the right balance.

Don't use OFF unless you're populating a throwaway database and can recreate it from scratch.

foreign_keys = ON

This one trips people up. SQLite supports foreign keys, but enforcement is off by default, and it's a per-connection setting:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO books (title, author_id) VALUES ('Ghosts', 999);

With foreign_keys = ON, that last insert fails — no author with id 999 exists. Without the PRAGMA, SQLite happily writes the orphan row and you discover the mess months later.

Run PRAGMA foreign_keys = ON; as the very first statement on every new connection. Most ORMs do this automatically; if you're using the raw driver, it's on you.

busy_timeout = 5000

SQLite allows one writer at a time. If a second connection tries to write while the first is mid-transaction, it gets SQLITE_BUSY and bails out immediately — by default.

busy_timeout tells SQLite to wait and retry instead:

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

The value is in milliseconds. 5000 means "wait up to 5 seconds for the lock before giving up." Combined with WAL, this eliminates most spurious database is locked errors in concurrent applications.

If you find yourself raising this above 30 seconds, the real fix is probably shorter transactions, not a longer timeout.

cache_size

cache_size sets how many database pages SQLite keeps in memory. More cache means fewer disk reads, which means faster queries on hot data.

The value has two forms:

• Positive number — pages. With the default 4 KB page size, 2000 is 8 MB.
• Negative number — kibibytes. -20000 is 20 MB regardless of page size.

PRAGMA cache_size = -20000;
PRAGMA cache_size;

The negative form is easier to reason about — you're saying "give me 20 MB of cache" rather than doing arithmetic with page size. For a small app, 20–50 MB is plenty. For a read-heavy workload on a bigger database, push it higher. Like synchronous, cache_size is per-connection.

mmap_size

Memory-mapped I/O lets SQLite read parts of the database file directly from the OS page cache, skipping a copy. It can speed up reads on large databases:

PRAGMA mmap_size = 268435456;
PRAGMA mmap_size;

That's 256 MB. SQLite will map up to that much of the database into memory if there's room. The OS handles paging, so you're not actually allocating 256 MB upfront — you're allowing it to map up to that.

mmap_size shines on read-heavy workloads. It's also harmless on small databases. The defaults are conservative, so bumping it is usually a win.

PRAGMA optimize

The query planner uses statistics to pick indexes. Stale statistics mean bad plans. PRAGMA optimize updates those statistics cheaply:

PRAGMA optimize;

The recommended pattern is to run it just before closing long-lived connections — application shutdown, end of a request handler that holds a connection for a while. It's fast (usually milliseconds) and only does work when something actually needs updating.

It's not the same as ANALYZE, which is a full statistics rebuild. optimize is the lightweight, run-it-often cousin.

Reading All the Settings

If you want to see what a connection is currently configured with, query the PRAGMAs without an assignment:

PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA foreign_keys;
PRAGMA busy_timeout;
PRAGMA cache_size;
PRAGMA page_size;
PRAGMA temp_store;

Useful when debugging — connecting from a different driver and wondering why behavior changed is almost always a PRAGMA difference.

There's also PRAGMA pragma_list; which dumps every PRAGMA the build supports:

PRAGMA pragma_list;

Not something you'd memorize, but handy when you need it.

Settings That Belong in CREATE, Not at Runtime

A couple of PRAGMAs configure the database file itself and only take effect before any tables are created:

• PRAGMA page_size = 8192; — page size on disk. Default is 4096, which is fine for most workloads. Larger pages help with large rows.
• PRAGMA encoding = 'UTF-8'; — text encoding.

PRAGMA page_size = 8192;
PRAGMA encoding = 'UTF-8';
CREATE TABLE ...

If you change page_size on an existing database, you need to VACUUM for it to take effect. Set these once, at creation time, and forget them.

A Real Connection-Setup Snippet

In application code, this usually lives in whatever opens the connection. Conceptually:

-- Run once on every new connection:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

-- Run periodically, or before close:
PRAGMA optimize;

temp_store = MEMORY keeps temporary tables and indexes in RAM, which speeds up queries that need to sort or aggregate without an index.

That's the whole production checklist. Half a dozen lines, and SQLite goes from "fine for development" to "appropriate for a real workload."

Next: Common Errors

Even with good PRAGMAs, you'll run into the usual cast of SQLite errors — database is locked, disk I/O error, constraint failed. The next page walks through what each one actually means and how to fix it.