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 NULL if 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:

• sqlite3PagerRef
• sqlite3PagerUnref
• sqlite3PagerBegin
• sqlite3PagerCommitPhaseOne
• sqlite3PagerCommitPhaseTwo
• sqlite3PagerRollback
• 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