Pragmas and WAL

Pragmas are per connection

Almost every pragma applies to one connection and is forgotten when it closes. Only a handful are stored in the database file itself.

PRAGMA foreign_keys;         -- read the current value
PRAGMA foreign_keys = ON;    -- set it, on this connection only
PRAGMA main.cache_size;      -- scope a pragma to one attached database
PRAGMA temp.temp_store;

Because of that split, set the per-connection ones in your pool's on-connect hook, not in a migration. A connection that skips PRAGMA foreign_keys = ON writes orphan rows the other connections cannot prevent.

Every pragma that returns a value also exists as a table-valued function, which is how you read one from application code without a special API.

SELECT * FROM pragma_table_info('users');
SELECT * FROM pragma_index_list('users');
SELECT * FROM pragma_foreign_key_list('posts');
SELECT * FROM pragma_table_list;

Also worth knowing: an unrecognized pragma name is silently ignored rather than raising, so a typo like PRAGMA foriegn_keys = ON does nothing at all and reports success. Read the value back to confirm it took.

journal_mode = WAL

The default journal is a rollback journal, where a writer copies original pages into file.db-journal and every reader is blocked for the duration. WAL inverts that: a writer appends new pages to file.db-wal and readers keep reading the old ones.

PRAGMA journal_mode;         -- delete, on a fresh database
PRAGMA journal_mode = WAL;   -- returns 'wal' on success
PRAGMA journal_mode;         -- wal, and it persists in the file

What WAL buys, and what it costs:

- Readers never block the writer and the writer never blocks readers. Writers still serialize against each other, one at a time. - Commits are usually faster, because they are a sequential append. - It needs three files, file.db, file.db-wal, and file.db-shm, and the -shm is a shared-memory coordination file. - It requires real shared memory, so WAL does not work over NFS or SMB, and it does not work on a read-only filesystem unless the -wal is already checkpointed away. - PRAGMA journal_mode = WAL is a persistent property, so every later connection uses it. - It cannot be set inside a transaction.

-wal and -shm are not optional extras. The -wal file holds committed data that is not yet in the main file, so copying only file.db from a live WAL database loses recent commits and can produce a corrupt copy. Delete the -wal of a cleanly closed database and you lose whatever it held.

ls -la app.db*
# app.db  app.db-shm  app.db-wal

PRAGMA locking_mode = EXCLUSIVE combined with WAL lets a single-process application drop the -shm file entirely, which is how WAL works on a filesystem without shared memory.

synchronous, and the durability tradeoff

synchronous controls how often SQLite calls fsync, which is the only thing standing between a power cut and lost data.

PRAGMA synchronous;           -- 2 on a stock build
PRAGMA synchronous = NORMAL;  -- 1

With WAL, synchronous = NORMAL is the recommended setting and it cannot corrupt the database, only lose transactions committed since the last checkpoint. With a rollback journal, NORMAL can corrupt on power loss. So the pairing matters: WAL plus NORMAL is safe and fast, DELETE plus NORMAL is not.

synchronous = OFF is only for a database you can recreate from scratch, for example a build cache or an import scratch file.

Note that some distributions ship altered defaults. The sqlite3 that comes with macOS, for instance, reports synchronous = 1, secure_delete = 2, and legacy_alter_table = 1, none of which are the upstream defaults. Always read a pragma back rather than assuming.

SELECT sqlite_version(), sqlite_source_id();

Checkpointing

A checkpoint copies pages from the -wal back into the main database file. It happens automatically when the WAL passes wal_autocheckpoint pages, by default 1000 pages, which at a 4096-byte page size is about 4MB.

PRAGMA wal_autocheckpoint;         -- 1000 pages
PRAGMA wal_autocheckpoint = 4000;  -- less frequent, larger WAL
PRAGMA wal_autocheckpoint = 0;     -- disable, you must checkpoint manually
PRAGMA journal_size_limit = 67108864;   -- cap the WAL at 64MB after a checkpoint

PRAGMA wal_checkpoint(PASSIVE);    -- default. Never blocks, may do nothing
PRAGMA wal_checkpoint(FULL);       -- wait for readers, copy everything
PRAGMA wal_checkpoint(RESTART);    -- FULL, then force the next writer to restart the WAL
PRAGMA wal_checkpoint(TRUNCATE);   -- RESTART, then shrink the WAL file to zero
-- returns three integers: busy | wal_pages | pages_checkpointed

The three-column result is busy (1 if it could not finish), the WAL size in pages, and how many pages were moved.

The failure mode to know: a long-running read transaction prevents checkpointing, so the -wal grows without bound. An analytics query that holds a read snapshot for an hour can leave a multi-gigabyte WAL, and disk fills up. Keep read transactions short, and if a long one is unavoidable, run it against a VACUUM INTO copy.

-- What to run before a backup or on a clean shutdown
PRAGMA wal_checkpoint(TRUNCATE);

busy_timeout and SQLITE_BUSY

Only one connection may write at a time. A second writer gets SQLITE_BUSY immediately unless you tell SQLite to wait.

PRAGMA busy_timeout;          -- 0, no waiting at all
PRAGMA busy_timeout = 5000;   -- wait up to 5000 ms for a lock

Error: database is locked

That message is SQLITE_BUSY, and a default-configured connection produces it under the lightest concurrency. Set busy_timeout on every connection, 3000 to 10000 ms is typical.

Two cases busy_timeout does not save you from:

- A deferred transaction upgrading to a write. BEGIN takes a read lock, and if another writer commits before your first UPDATE, the upgrade fails with SQLITE_BUSY_SNAPSHOT and no amount of waiting helps, because waiting would deadlock. The fix is BEGIN IMMEDIATE for any transaction that will write, which takes the write lock up front where the timeout does apply. - A checkpoint blocked by a reader, which returns busy rather than waiting forever.

Error: database is locked (SQLITE_BUSY_SNAPSHOT)

-- Correct shape for a writing transaction
PRAGMA busy_timeout = 5000;
BEGIN IMMEDIATE;
  UPDATE accounts SET balance = balance - 50 WHERE id = 1;
  UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

Also retry on busy at the application level, with a small backoff, because a timeout can still expire under real contention.

Performance and memory pragmas

PRAGMA cache_size = -64000;   -- NEGATIVE means KiB, so 64 MiB. Positive means PAGES
PRAGMA mmap_size = 268435456; -- 256 MiB memory-mapped I/O, 0 disables
PRAGMA temp_store = MEMORY;   -- temp tables and sort files in RAM (2)
PRAGMA page_size = 8192;      -- before the first write, or with a VACUUM
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA threads = 4;           -- worker threads for a large sort
PRAGMA analysis_limit = 400;  -- cap the work PRAGMA optimize will do

auto_vacuum reclaims free pages as rows are deleted, at the cost of extra bookkeeping on every write. INCREMENTAL lets you reclaim on your own schedule.

PRAGMA auto_vacuum = INCREMENTAL;   -- then VACUUM once to apply
PRAGMA incremental_vacuum(100);     -- release 100 free pages now
PRAGMA freelist_count;              -- pages currently free
PRAGMA page_count;                  -- total pages, times page_size is the file size

Statistics, integrity, and introspection

ANALYZE gathers table and index statistics into sqlite_stat1, and the planner uses them to choose between indexes. Without it, the planner guesses.

ANALYZE;                 -- the whole database, can be slow on a large one
ANALYZE users;           -- one table and its indexes
PRAGMA optimize;         -- run the analyses that are actually worth running
PRAGMA optimize(0x02);   -- see the recommended commands without running them
PRAGMA analysis_limit = 400;   -- bound the work, then optimize is cheap

Run PRAGMA optimize before closing every long-lived connection, with analysis_limit set to a few hundred. It is the documented recommendation, it is nearly free, and it keeps sqlite_stat1 fresh without the full cost of ANALYZE.

PRAGMA integrity_check;        -- full check, reports 'ok' or a list of problems
PRAGMA integrity_check(10);    -- stop after 10 errors
PRAGMA quick_check;            -- skips the slow index-content comparison
PRAGMA foreign_key_check;      -- every violating row in the database

integrity_check reads every page, so it is O(database size). quick_check is much faster and catches most real corruption. Neither repairs anything, and the recovery path for a damaged file is .recover.

PRAGMA table_info(users);      -- cid, name, type, notnull, dflt_value, pk
PRAGMA table_xinfo(users);     -- the same, plus hidden and generated columns
PRAGMA table_list;             -- every table in every attached database
PRAGMA index_list(users);      -- seq, name, unique, origin, partial
PRAGMA index_info(idx_name);   -- the indexed columns
PRAGMA index_xinfo(idx_name);  -- plus direction, collation, and key/value role
PRAGMA foreign_key_list(posts);
PRAGMA database_list;          -- seq, name, file, for main and every attachment
PRAGMA compile_options;        -- how this binary was built
PRAGMA function_list;          -- every SQL function available
PRAGMA module_list;            -- every virtual table module, fts5 included

table_xinfo is the one to use when generated columns are involved, since table_info hides them. Its extra hidden column is 0 for a normal column, 2 for a VIRTUAL generated column, and 3 for a STORED one.

user_version for migrations

SQLite has no migration table built in, but it has a 32-bit integer in the file header that is perfect for a schema version.

PRAGMA user_version;        -- 0 on a fresh database
PRAGMA user_version = 3;    -- write it, persists in the file
PRAGMA application_id;      -- another header integer, for a magic number
PRAGMA application_id = 0x48554442;

A complete migration runner is about ten lines.

-- Read the version, then apply only what is missing, bumping as you go
PRAGMA foreign_keys = OFF;
BEGIN IMMEDIATE;
  -- if user_version < 1
  CREATE TABLE users(id INTEGER PRIMARY KEY, email TEXT UNIQUE NOT NULL) STRICT;
  PRAGMA user_version = 1;
  -- if user_version < 2
  ALTER TABLE users ADD COLUMN name TEXT;
  PRAGMA user_version = 2;
COMMIT;
PRAGMA foreign_keys = ON;

PRAGMA user_version is transactional, so a rolled-back migration rolls back its version bump too. Keep PRAGMA foreign_keys outside the transaction, since it is a no-op inside one.

application_id is a separate header field for identifying the file type, so file app.db and other tools can recognize your schema. Pick a four-byte value and register it in the SQLite magic-number list if the format is public.

A recommended block for a server-side app

Run these on every new connection, in this order, before any transaction.

PRAGMA journal_mode = WAL;         -- persistent, so a no-op after the first time
PRAGMA synchronous = NORMAL;       -- safe with WAL, much faster than FULL
PRAGMA foreign_keys = ON;          -- per connection, and it is OFF by default
PRAGMA busy_timeout = 5000;        -- wait for the write lock instead of erroring
PRAGMA cache_size = -64000;        -- 64 MiB of page cache per connection
PRAGMA temp_store = MEMORY;        -- temp b-trees in RAM
PRAGMA mmap_size = 268435456;      -- 256 MiB mmap, for read-heavy work
PRAGMA analysis_limit = 400;       -- bound PRAGMA optimize

And on clean shutdown of a long-lived connection:

PRAGMA optimize;

Four caveats. cache_size is per connection, so a pool of 20 at 64 MiB reserves 1.2 GiB. journal_mode = WAL fails inside a transaction, so run it first. PRAGMA foreign_keys must be outside a transaction to take effect. And for a write transaction always use BEGIN IMMEDIATE, since busy_timeout cannot rescue a deferred transaction that has already taken a read lock.

VACUUM

VACUUM rebuilds the database file from scratch, defragmenting it and returning free pages to the filesystem.

VACUUM;                          -- rebuild main in place
VACUUM INTO 'compact.db';        -- 3.27+, write a fresh compacted copy
PRAGMA page_size = 8192; VACUUM; -- the only way to change page_size after creation
PRAGMA auto_vacuum = FULL; VACUUM;   -- the only way to change auto_vacuum

Costs and constraints: it needs free disk space of roughly twice the database size, it takes a write lock for the whole operation, it cannot run inside a transaction, and it invalidates every open statement. It also changes rowids in a table that has no INTEGER PRIMARY KEY, which is one more reason to always declare one.

VACUUM INTO is the version to reach for. It leaves the original untouched, needs no exclusive lock, and produces a compacted, checkpointed, single-file copy, which makes it both a defragmenter and the modern online backup.