Skip to Content

SQLite Page Cache Architecture

An evergreen technical guide explaining SQLite's page cache design, including PgHdr, PCache1, cache groups, content‑addressed reads, and the pin/unpin contract.
1 February 2026 by
Suraj Barman

Overview

SQLite stores database pages in an in‑memory cache to avoid costly disk I/O. The cache is deliberately small (default 500 pages for a temporary database) and is designed to keep lookups fast while preventing unbounded memory growth.

What Is a PgHdr?

A PgHdr (page header) is the pager’s internal representation of a cached page. It is invisible to higher layers such as the B‑tree module, which sees only the raw page bytes.

  • pgno: the logical database page number.
  • dirty: flag indicating the page has been modified.
  • needSync: indicates the journal must be flushed before the page is written back.
  • nRef: reference count (pin count) that prevents eviction while in use.
  • pDirtyNext / pDirtyPrev: links forming the dirty‑page list.

How PCache1 Implements the Cache

SQLite’s default cache implementation, PCache1, adds a thin layer on top of the raw page memory. Each hash‑table slot is a PgHdr1 object that contains:

  • The PgHdr header.
  • The page image (raw database bytes).
  • Private space used by the tree module for per‑page state.
  • Optional recovery metadata for in‑memory databases.

The entire block is zero‑initialized when a page enters the cache, eliminating stale state.

All slots are reachable via the PCache1.apHash array, where each entry points to a singly‑linked bucket. This simple hash‑table design provides fast lookups without complex tricks.

Why Cache Groups Exist

When many database connections run in the same process, each connection normally has its own PCache1 instance. Cache groups allow multiple instances to share unpinned pages, providing global memory‑pressure handling.

  • Reduces overall memory consumption.
  • Allows one cache to recycle pages evicted from another.
  • Pinned pages remain exclusive to their owner, preserving correctness.

How Cache Reads Are Performed

SQLite treats the cache as content‑addressed, not as a simple array. Pages are requested by logical page number, not by memory address.

When the B‑tree module needs a page, it calls sqlite3PagerGet(). The pager looks up the page number in the hash table, loads it from disk if absent, and returns a pointer to the page image.

Pin, Use, Unpin: The Core Contract

After sqlite3PagerGet() returns a page, the pager no longer tracks its usage. The caller must obey a simple contract:

  • Pin: The page is considered pinned while the caller holds a reference (reference count > 0).
  • Use: The caller may read or modify the page.
  • Unpin: The caller calls sqlite3PagerUnref() to decrement the reference count, making the page recyclable.

Pinned pages cannot be evicted. To avoid deadlock, SQLite enforces a minimum cache size (10 pages as of version 3.7.8), guaranteeing that at least some pages remain evictable.

Why This Design Balances Performance and Safety

The strict fetch‑on‑demand policy eliminates speculative reads and read‑ahead, reducing unnecessary I/O. By separating dirty‑page tracking, replacement policies, and memory sharing into distinct components, SQLite achieves:

  • Predictable memory usage.
  • Fast, deterministic cache lookups.
  • Robust handling of concurrent connections.

Understanding these mechanisms helps developers tune SQLite for their workloads and diagnose performance issues related to caching.