Chapter 18 · Performance Engineering, PRAGMAs, Maintenance, and Benchmarking
synchronous, journal_mode, cache_size, temp_store, mmap_size, and Other PRAGMAs
Classify SQLite performance-related PRAGMAs by the guarantee or resource they change, measure them one at a time, and refuse unsafe performance recipes that silently weaken correctness.
Learning outcomes
A copied “fast SQLite PRAGMA pack” is dangerous because PRAGMAs do unrelated things. Some alter durability; some alter concurrency; some change memory hints; some affect temporary work; some are diagnostic. Performance engineering begins by naming the category and failure consequence before measuring speed.
Classify major PRAGMAs by durability, journaling/concurrency, cache/memory, temporary storage, locking, planner/maintenance, and diagnostics.
Explain current synchronous guarantees in rollback and WAL modes without recommending OFF generically.
Interpret cache_size positive pages versus negative kibibytes and its session-scoped nature.
Treat temp_store and mmap_size as workload/platform choices rather than magic speed values.
Explain why journal_mode and locking_mode changes can alter concurrency semantics, not merely latency.
Build a one-variable-at-a-time configuration experiment and a decision table tied to failure tolerance.
Classify before changing
| Category | Examples | Question before change |
|---|---|---|
| Durability | synchronous | What survives application crash, OS crash, and power loss? |
| Journal/concurrency | journal_mode, WAL checkpoint controls | How do readers/writers coordinate and where is committed state stored? |
| Page cache / memory | cache_size | Is the workload actually cache-miss bound, and what memory budget is safe? |
| Memory-mapped I/O | mmap_size | Does this VFS/platform/build benefit, and what address-space policy is acceptable? |
| Temporary work | temp_store | Are sorts/materializations spilling, and what memory pressure follows? |
| Lock policy | locking_mode | Can this connection monopolize file access without harming other processes? |
| Planner/maintenance | optimize, analysis_limit | Are planner statistics stale or missing? |
| Diagnostics | page_count, freelist_count, wal_checkpoint(NOOP) | What state can we observe before acting? |
synchronous changes failure guarantees
PRAGMA synchronous controls when SQLite asks the VFS to synchronize data to stable storage. The current documentation distinguishes OFF, NORMAL, FULL, and EXTRA. The effect depends on journal mode. In WAL mode, NORMAL remains consistent but a recently committed transaction may be lost after power/OS failure; FULL adds a WAL synchronization after each commit to improve durability. In rollback mode, weaker synchronization can also affect consistency guarantees.
| Setting | Use this chapter teaches | Production judgment |
|---|---|---|
EXTRA | Stronger rollback-mode synchronization around journal deletion in addition to FULL behavior. | Measure only if its stronger guarantee matches the risk model. |
FULL | Durability-oriented baseline; in WAL adds sync after each transaction commit. | Good comparison baseline when committed transactions must survive power loss. |
NORMAL | Fewer sync points; WAL remains consistent but may lose recent commits after power loss. | Often a deliberate WAL tradeoff, never an invisible “speed flag.” |
OFF | SQLite continues ordering logically but does not issue synchronization requests. | Not a generic tuning recommendation. Appropriate only for rebuildable/disposable data when corruption/loss risk is accepted. |
“wal-normal” and “wal-full” are different durability experiments. If a report only says “WAL was faster,” it is missing the variable that may explain the result.
journal_mode changes I/O and concurrency architecture
The common modes include DELETE, TRUNCATE, PERSIST, MEMORY, WAL, and OFF. You already studied their mechanics in Chapters 8–9. Here the key performance rule is: do not switch journal mode solely because a blog calls one mode fast. WAL can improve reader/writer overlap but retains one writer, requires checkpoints, uses companion files, and has local-filesystem/shared-memory assumptions.
PRAGMA journal_mode;PRAGMA synchronous;PRAGMA wal_autocheckpoint;PRAGMA wal_checkpoint(NOOP);WAL mode is persistent for the database once successfully enabled, so changing it is not merely a transient benchmark knob. Use a disposable copy when experimenting.
cache_size is a suggestion, and its sign matters
PRAGMA cache_size is a suggested maximum page-cache size per open database. A positive value means pages. A negative value means approximately that many kibibytes, converted to pages using the current page size. The default built-in suggestion is commonly -2000, but builds can override it.
PRAGMA page_size;PRAGMA cache_size; -- current suggestion-- Example experiment only: approximately 8 MiB suggestion.PRAGMA cache_size = -8192;-- Observe the workload, then restore/reopen as appropriate.The setting is not a promise that SQLite immediately allocates that amount, and an application-defined page cache may interpret or ignore it. The cache_size PRAGMA setting reverts when the database connection closes.
temp_store: memory can trade I/O for RAM
SQLite may create temporary b-trees for sorts, GROUP BY, DISTINCT, materialization, and other work. temp_store influences whether many temporary tables/indexes use file or memory storage, subject to the SQLITE_TEMP_STORE compile option. Transaction journals/WAL are not converted into RAM by this PRAGMA.
EXPLAIN QUERY PLANSELECT metric, value_realFROM perf_readingORDER BY value_real DESC;PRAGMA temp_store; -- 0 DEFAULT, 1 FILE, 2 MEMORYPRAGMA compile_options; -- inspect TEMP_STORE build option if presentIf the plan does not create significant temporary work, changing temp_store may have nothing useful to optimize. MEMORY can also increase process memory pressure. Measure the real sort/materialization workload.
mmap_size is a platform/VFS-dependent ceiling
PRAGMA mmap_size=N changes the maximum bytes SQLite may access through memory-mapped I/O for a database. It is not “reserve N bytes of faster RAM.” The effective limit can be capped by compile/start-time settings and the VFS, and the PRAGMA can be a no-op in some situations.
PRAGMA mmap_size;-- Example candidate for a disposable benchmark, not a recommendation:PRAGMA mmap_size = 67108864; -- 64 MiB ceilingPRAGMA mmap_size; -- read back effective valueCompare elapsed time, page-cache behavior if you can observe it, process address-space constraints, and deployment portability. A setting that helps one desktop workload may be inappropriate on a memory-constrained embedded runtime.
locking_mode is not a throughput shortcut for shared applications
locking_mode=EXCLUSIVE can allow one connection to retain file locks and can reduce lock setup work in narrow single-owner workloads. But the cost is architectural: other connections/processes may be unable to access the database as expected. Do not use it to hide write-contention design problems.
PRAGMA locking_mode; -- normally NORMAL-- Only in a disposable, deliberately single-owner benchmark:PRAGMA locking_mode=EXCLUSIVE;-- measure, then close/reopen/restore NORMAL policy as appropriateOne-variable-at-a-time experiment
Baseline: journal_mode = WAL synchronous = FULL cache_size = -2000 (observed) temp_store = DEFAULT mmap_size = observed default locking_mode = NORMALCandidate A: change ONLY cache_size -> -8192 run same read benchmark 10 times verify same result + same planCandidate B: restore baseline change ONLY mmap_size -> 64 MiB ceiling repeatSeparate durability experiment: WAL FULL versus WAL NORMAL label as a FAILURE-GUARANTEE tradeoff, not a pure speed tweak.Decision table: tie setting to workload and risk
| Observation | Candidate | What must be checked before keeping it |
|---|---|---|
| Commit sync dominates WAL FULL workload | Consider WAL NORMAL only if loss of recent commits after power/OS failure is acceptable. | Durability requirement, storage/VFS behavior, restore/retry semantics. |
| Read workload repeatedly misses page cache | Test a larger cache_size. | Process memory budget and measured hit/latency change. |
| Large temp b-tree sort spills and RAM is available | Test temp_store=MEMORY. | Peak memory, compile option, actual elapsed improvement. |
| Read-heavy local file, supported VFS | Test mmap_size. | Platform/build effective limit and deployment consistency. |
| Single dedicated process owns DB permanently | Maybe measure EXCLUSIVE locking. | No other process/connection access requirement. |
| Many writers block each other | Do not expect cache/mmap PRAGMAs to solve serialization. | Shorten transactions, writer queue/batching, or reassess architecture. |
Checkpoint
Reject the performance pack
Explain what is wrong with this recipe: journal_mode=WAL; synchronous=OFF; temp_store=MEMORY; cache_size=-200000; mmap_size=30GB; locking_mode=EXCLUSIVE.
- Which setting directly weakens crash/power guarantees?
- Which settings can create memory/address-space pressure?
- Which setting can interfere with other connections/processes?
- Why is WAL itself not proof of better performance?
- What should be measured first?
Review the answers
synchronous=OFF is the major durability/corruption tradeoff. Large cache_size and mmap_size can pressure memory/address space; temp_store=MEMORY can raise peak RAM. EXCLUSIVE locking changes access semantics. WAL helps certain concurrency patterns but still has one writer and checkpoint cost. First measure the actual bottleneck and record the baseline workload/plan/configuration.
Bridge to maintenance
Configuration is only one part of long-lived performance. Statistics age, freelist space accumulates, WAL checkpoints can be delayed, and file compaction may occasionally be justified. Lesson 4 turns those observations into a maintenance policy that avoids unnecessary work.