Connection pragmas in SQL

A handful of pragmas decide how a SQLite connection trades safety for speed.

PRAGMA journal_mode = WAL;

PRAGMA synchronous = NORMAL;

PRAGMA busy_timeout = 5000;

PRAGMA cache_size = -20000;

PRAGMA optimize;

How it works

1. journal_mode = WAL lets readers and one writer work at the same time.
2. synchronous = NORMAL is the usual WAL companion: fast, still crash-safe.
3. busy_timeout makes a blocked writer wait instead of failing immediately.

The run, in numbers

Lines

9

Characters to type

132

Tokens

24

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 15 seconds.

Type this snippet

Step 5 of 5 in Transactions, step 15 of 17 in Plans & performance.

← Previous Next →