Mastering SQLite PRAGMA: Optimize Performance and Persistence Across Linux & Windows

What are PRAGMA commands?

PRAGMA statements are SQLite‑specific SQL extensions that let you query or modify internal database settings such as journal mode, synchronization level, page size, and application identifiers. They are essential for fine‑tuning performance and durability.

Synchronous Modes

SQLite offers four synchronous settings that balance speed and safety:

OFF (0) : No fsync – fastest but data can be lost on power failure.

NORMAL (1) : WAL‑layer fsync is deferred – good compromise for most workloads.

FULL (2) : Every transaction commit performs fsync – reliable but slower.

EXTRA (3) : fsync plus metadata fsync – safest, lowest performance.

Choosing FULL is recommended for critical business data.

WAL Checkpoint and Autocheckpoint

The Write‑Ahead Log (WAL) improves concurrency. You can control checkpoint frequency with PRAGMA wal_autocheckpoint (e.g., set to 200 pages) and manually trigger a checkpoint using PRAGMA wal_checkpoint(TRUNCATE). Example session:

[root@localhost data]
# sqlite3 test.db
SQLite version 3.47.1 2024-11-25 12:07:48
sqlite> PRAGMA synchronous = FULL;
sqlite> PRAGMA wal_autocheckpoint = 200;
sqlite> PRAGMA wal_checkpoint;
0|0|0
sqlite> PRAGMA wal_checkpoint(TRUNCATE);
0|0|0

Persistent vs. Session‑Level PRAGMA

Persistent pragmas are written to the database file and affect all future connections. Session‑level pragmas only affect the current connection or transaction.

Persistent PRAGMAs : journal_mode, auto_vacuum, encoding (new DB only), application_id, page_size, user_version.

Non‑persistent (session) PRAGMAs : synchronous, wal_autocheckpoint, checkpoint_fullfsync, analysis_limit, cache_size, cache_spill, busy_timeout, automatic_index, foreign_keys, foreign_key_check, integrity_check, defer_foreign_keys, incremental_vacuum, data_version, database_list, index_list / index_info / index_xinfo, and all deprecated PRAGMAs.

Linux vs. Windows Differences

Most PRAGMA behavior is identical across platforms, but a few nuances exist:

Encoding : Both default to UTF‑8; can be set only on a newly created empty database.

Page size : Default 4096 bytes on both; configurable range 512–65536 before tables are created.

Application ID : Supported and persisted on both OSes.

File locking : Linux uses POSIX fcntl, Windows uses LockFileEx, leading to slightly weaker concurrency on Windows.

WAL behavior : Windows’ FlushFileBuffers makes FULL sync slower than Linux’s fsync.

Overall, SQLite’s core behavior is consistent; the main practical difference is Windows’ default console encoding (GBK) which requires chcp 65001 for UTF‑8.

Typical Initialization Script

PRAGMA journal_mode = WAL;
PRAGMA synchronous = FULL;
PRAGMA wal_autocheckpoint = 200;

Save these statements in a .sql file and execute it when the database starts.

Step‑by‑Step Configuration Checklist

Set database encoding (only on empty DB): PRAGMA encoding = 'UTF-8'; Set page size (only on empty DB): PRAGMA page_size = 4096; Define an application identifier: PRAGMA application_id = 0x1234ABCD; Set schema version for application use: PRAGMA user_version = 20240101; Choose auto‑vacuum mode: PRAGMA auto_vacuum = FULL; (or NONE/INCREMENTAL).

Enable WAL journaling: PRAGMA journal_mode = WAL; Configure synchronization level: PRAGMA synchronous = FULL; Adjust WAL autocheckpoint interval (session‑level): PRAGMA wal_autocheckpoint = 200; Optionally enforce strict tables: CREATE TABLE t(a INTEGER, b TEXT) STRICT; Optionally run manual vacuum:

PRAGMA incremental_vacuum;