Hello, I'm Maneshwar. I'm working on FreeDevTools online currently building "one place for all dev tools, cheat codes, and TLDRs" — a free, open-source hub where developers can quickly find and use tools without any hassle of searching all over the internet.
So far, we’ve talked around the pager what it represents, how higher layers interact with it, and why it exists at all.
Today we finally look at the actual doorway.
The pager doesn’t expose its internals freely. Instead, it offers a tight, carefully designed set of interface functions that higher layers (mainly the tree module) are allowed to use.
These functions live in pager.c, are all prefixed with sqlite3Pager*, and are strictly internal.
Application developers never touch them, and that’s very much intentional.
Each function enforces a rule. Break the rule, and SQLite’s correctness guarantees collapse.
Let’s walk through the most important ones.
sqlite3PagerOpen: Creating a Storage Universe
This is where everything begins.
sqlite3PagerOpen:
- Allocates a new Pager object
- Opens the database file
- Creates an empty page cache
- Initializes pager state
At this stage, no locks are taken, no journal is created and no recovery* is performed
That last part is subtle and important.
SQLite uses deferred recovery.
It doesn’t eagerly inspect or replay journals when the database is opened. Recovery only happens when a page is actually accessed.
This keeps startup cheap and avoids unnecessary work.

At this moment, the pager exists, but it’s still passive.
sqlite3PagerClose: Tearing Everything Down Safely
Closing a pager is not just “close the file”.
sqlite3PagerClose:
- Destroys the Pager object
- Closes the database file
- Frees the entire page cache
- Releases all OS resources
But there’s a sharp edge here.
If a transaction is still in progress when this function is called SQLite forces an abort and all changes are rolled back immediately
There is no “best effort” here. Consistency wins over convenience.
Once this function returns:
- All cached pages are invalid
- All pointers to page memory are dangling
- Touching them is undefined behavior (often a crash)
This strict cleanup is what allows SQLite to stay memory-safe without reference counting everywhere.
sqlite3PagerGet: Turning a Page Number into Memory
This is the most frequently used pager function.
sqlite3PagerGet:
- Takes a page number
- Returns a pointer to the in-memory page image
- Pins the page in the cache so it won’t be recycled
Behind the scenes, a lot happens:
First Access Responsibilities
On the first call:
- The pager acquires a shared lock on the database file
- If the lock cannot be obtained →
SQLITE_BUSY
Cache Validation
Before returning the page:
- The pager checks the database file change counter
- If it has changed, the entire cache is purged
This prevents one connection from using stale pages after another connection commits changes.
Deferred Recovery
If a hot journal is detected:
- Recovery is performed right here
- Not at open time, but exactly when it becomes necessary
Page Loading Rules
- If the page exists → read it from disk into cache
- If the file is smaller than the requested page → return a zero-filled page
- For in-memory databases → no disk I/O at all
By the time sqlite3PagerGet returns, the caller is holding a valid, safe, in-memory page.
sqlite3PagerWrite: Declaring Intent to Modify
This function is the gatekeeper for correctness.
Before the tree module modifies any byte of a page, it must call:
sqlite3PagerWrite
This call tells the pager:
“I’m about to change this page. Prepare accordingly.”
What the pager does next depends on state:
Transaction Setup
If not already done:
- Acquire a reserved lock
- Create the rollback journal
- Enter a write transaction
If the lock can’t be obtained → SQLITE_BUSY
Journaling Discipline
- If the page has not yet been logged:
- Its original contents are written to the rollback journal
- If it has already been logged:
- No additional journal write occurs
Either way:
- The page is marked dirty
Statement Journaling
If:
- The update is happening inside a user transaction
- And the page was already journaled earlier
Then:
- A statement journal record may be written
- This allows SQLite to roll back just one statement without aborting the full transaction
Crucially, this function does not write to the database file.
It only prepares the ground so that writing later is safe.
sqlite3PagerLookup: Cache, If You Have It
This function is intentionally limited.
sqlite3PagerLookup:
- Returns a pointer to a cached page only if it already exists
- Pins the page if found
- Returns
NULLif the page is not cached
It does not do any disk I/O or locking
This is used as a fast-path optimization when the caller knows the page might already be resident.
A Pattern Worth Noticing
Across all these functions, a consistent philosophy emerges:
- Page access is explicit
- Write intent must be declared
- Journaling happens before mutation
- Locks are acquired before danger
- Disk I/O is deferred until unavoidable
The tree module never “accidentally” modifies persistent state.
The pager forces discipline.
What Comes Next
Today we focused on the front door of the pager:
- Opening and closing
- Reading pages
- Declaring write intent
- Cache lookups
In the next post, we’ll cover the functions that control lifecycle and boundaries:
sqlite3PagerRefsqlite3PagerUnrefsqlite3PagerBeginsqlite3PagerCommitPhaseOnesqlite3PagerCommitPhaseTwosqlite3PagerRollback- Savepoint-related pager calls
That’s where we’ll finally trace an entire transaction, from first page touch to durable commit or clean rollback.
My experiments and hands-on executions related to SQLite will live here: lovestaco/sqlite
References:
SQLite Database System: Design and Implementation. N.p.: Sibsankar Haldar, (n.d.).
👉 Check out: FreeDevTools
Any feedback or contributors are welcome!
It’s online, open-source, and ready for anyone to use.
⭐ Star it on GitHub: freedevtools



Top comments (0)