Cache, Memory Mapping, Temporary Storage, and the Pager Layer

Learning outcomes

If a query returns 10,000 rows, SQLite does not necessarily issue 10,000 operating-system reads. SQL executes above a pager and page cache. Pages that are already cached can serve many row operations, and the operating system may cache filesystem pages again below SQLite. Temporary sort/index structures can also live in memory or spill depending on build/runtime decisions.

The pager: SQLite’s page-oriented storage coordinator

SQLite’s architecture separates SQL execution from file-system details. The parser/code generator produces bytecode for the VDBE (Virtual DataBase Engine). VDBE instructions operate B-tree cursors. The B-tree layer requests pages through the pager, which loads database pages into memory, participates in transaction control, and works with rollback journals/WAL. Below that, the VFS (Virtual File System) abstracts OS-specific open/read/write/lock/sync operations.

SQL text + bound values
        |
        v
parser / planner / code generator
        |
        v
VDBE bytecode execution
        |
        v
B-tree cursors / records / indexes
        |
        v
Pager + SQLite page cache
        |
        v
VFS (OS abstraction: files, locks, sync, mmap...)
        |
        v
OS filesystem cache / storage device
        |
        v
main .db  (+ journal or WAL/SHM when applicable)

This is a mental model, not a promise that every internal call follows a simple one-direction pipeline. It is enough to explain why page caching, locks, temporary structures, and VFS behavior affect execution beneath SQL.

Three different caches people often confuse

A cache hit in one layer does not imply a hit in another. Likewise, changing PRAGMA cache_size does not configure your web application cache or the kernel’s cache.

Observe cache_size—do not copy a magic number

PRAGMA cache_size queries or changes the suggested maximum pages kept in SQLite’s page cache for one database. A positive value means pages; a negative value expresses an approximate KiB budget that SQLite converts to pages based on page size. The setting is connection/session scoped.

PRAGMA page_size;PRAGMA cache_size;PRAGMA mmap_size;PRAGMA temp_store;PRAGMA compile_options;

The built-in default cache implementation honors the suggestion, but SQLite permits application-defined caches to interpret or ignore it. The default is often -2000 (about 2 MiB), but compile-time options can change that. Record the current value; performance tuning belongs in Chapter 18 with measured workloads.

Memory-mapped I/O is another access mechanism, not “load DB into RAM”

PRAGMA mmap_size controls the maximum number of database-file bytes SQLite may access using memory-mapped I/O on that connection/database. A value of zero disables mmap access. The effective maximum is capped by compile/start-time limits, and changing the setting can be a no-op while mapped memory is actively used by running statements.

PRAGMA mmap_size;-- Do not set a copied value merely because another machine used it.-- If you experiment later, record baseline + workload + platform first.

Memory mapping still goes through virtual memory and the operating system. It does not mean the entire database is resident in physical RAM or that all queries become faster.

Temporary b-trees: sorting also needs storage structures

Chapter 10’s EQP output sometimes showed USE TEMP B-TREE FOR ORDER BY. When SQLite cannot obtain the required order from an existing access path, it can build a transient B-tree to sort rows. Similar transient structures may support GROUP BY, DISTINCT, compound queries, materialized subqueries, and other operations.

DROP TABLE IF EXISTS temp_sort_probe;CREATE TABLE temp_sort_probe(  id INTEGER PRIMARY KEY,  category TEXT NOT NULL,  score INTEGER NOT NULL,  note TEXT NOT NULL);WITH RECURSIVE seq(x) AS (  VALUES(1) UNION ALL SELECT x+1 FROM seq WHERE x<10000)INSERT INTO temp_sort_probe(category,score,note)SELECT printf('C%02d',x%25), x%997, printf('note-%d',x)FROM seq;EXPLAIN QUERY PLANSELECT category, score, noteFROM temp_sort_probeORDER BY score, category;-- Expect a SCAN plus a USE TEMP B-TREE FOR ORDER BY-style detail-- when no suitable ordering index exists.

temp_store controls some temporary structures—not transaction journals

PRAGMA temp_store selects DEFAULT, FILE, or MEMORY behavior for temporary tables and indices, subject to the build’s SQLITE_TEMP_STORE compile-time choice. This is not a blanket “keep all SQLite temporary activity in RAM” switch. Transaction-control files such as rollback journals and WAL have their own durability requirements and are not governed by this PRAGMA in the same way.

Temporary storage may never hit disk for small work

Even when temporary structures are file-backed in principle, SQLite uses a page cache for them. If the structure stays small enough, pages can remain in memory and no actual temporary file needs to be opened. If the structure outgrows its cache budget, it may spill. This is another reason SQL-row counts do not translate directly into disk-I/O counts.

The VFS boundary: where SQLite meets the operating system

The VFS is SQLite’s abstraction for OS services: opening/closing files, reading/writing, obtaining file locks, syncing, randomness/time, shared memory and memory mapping as supported. Standard SQLite ships VFS implementations for major platforms, and specialized environments can register alternative VFSes.

Application developers normally do not write a VFS. But knowing it exists helps explain platform differences, network-filesystem cautions from Chapter 9, URI vfs=... connection options in Chapter 12, and why a storage bug can live below SQL semantics.

Observation lab: relate a temp sort to cache controls

This lab does not try to benchmark FILE versus MEMORY temp storage. It records the current state, proves a temporary sort structure is needed, and shows which settings/build facts would have to be documented before any tuning experiment.

SELECT sqlite_version();PRAGMA page_size;PRAGMA cache_size;PRAGMA mmap_size;PRAGMA temp_store;SELECT compile_optionsFROM pragma_compile_optionsWHERE compile_options LIKE 'TEMP_STORE%'   OR compile_options LIKE '%MMAP%'   OR compile_options LIKE '%CACHE%'ORDER BY compile_options;EXPLAIN QUERY PLANSELECT category, score, noteFROM temp_sort_probeORDER BY score, category;

Failure cases and safer reasoning

Chapter 11 recap diagram

query / write
    |
    +--> VDBE instructions
            |
            +--> table B-tree (rowid key) / index B-tree (ordered index record)
                    |
                    +--> leaf/interior cells
                    |       +--> local payload
                    |       +--> overflow page chain for large payload
                    |
                    +--> pager + SQLite page cache
                            |
                            +--> freelist / page allocation
                            +--> rollback journal or WAL transaction support
                            |
                            +--> VFS
                                  |
                                  +--> OS filesystem cache / disk

Database file = fixed-size pages
  page 1: 100-byte database header + B-tree content
  other pages: B-tree / overflow / freelist / pointer-map / special pages

Final verification and chapter checkpoint

PRAGMA integrity_check;PRAGMA foreign_key_check;PRAGMA page_size;PRAGMA page_count;PRAGMA freelist_count;PRAGMA cache_size;PRAGMA mmap_size;PRAGMA temp_store;DROP TABLE IF EXISTS temp_sort_probe;

Production judgment and Chapter 12 bridge

Storage internals should improve diagnosis, not invite arbitrary tuning. You now have enough page-level understanding to interpret file growth, overflow, free space, VACUUM, cache settings, and temporary sorting behavior. Chapter 12 climbs back to schema-level application features: views, triggers, attached databases, URI connection options, and database-as-file-format metadata.

Authoritative references

• SQLite — Architecture.
• SQLite — VFS.
• SQLite — Temporary Files Used By SQLite.
• SQLite — PRAGMA cache_size, mmap_size, temp_store.