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.
Over the last few posts, we slowly peeled back how SQLite keeps its promises.
We went from journals, to transactions, to system vs user transactions, then deep into locking, deadlocks. By the end of that stretch, one thing became clear:
Locks decide who can act.
Journals decide what survives a crash.
But something still has to decide how bytes move safely between memory and disk.
That “something” is the Pager.
Over the next few days, we’ll shift focus to the internal transaction manager that quietly orchestrates all of this behind the scenes.
Why the Pager Exists at All
Databases don’t live in RAM.
They live on disks which maybe slow, byte-oriented, crash-prone disks.
SQLite can’t work directly on disk data.
Every read or write would be painfully expensive. So instead, it follows a simple rule:
Read data into memory → work on it → write it back only when needed.
But memory is limited, and databases are usually much larger than RAM. That’s where caching comes in.
SQLite reserves a small region of memory to hold recently used database content.
This region is called the page cache, and the module that manages it is called the pager.
Pages: SQLite’s View of Storage
At the filesystem level, SQLite sees ordinary files made of bytes.
The pager transforms that low-level view into something far more useful:
- A database becomes a logical array of fixed-size pages
- Pages are addressed by page number, not file offsets
- Each database file can have its own page size
From this point upward, SQLite no longer thinks in terms of files and bytes, it thinks in pages.
And crucially:
Higher layers never touch the database file directly.
The B-tree layer (and everything above it) always goes through the pager.
The Lowest Layer in SQLite
The pager sits at the very bottom of SQLite’s architecture.
It is the only module that talks to the OS, reads-write DB files and manages journal files
It doesn’t understand tables, rows, indexes, or SQL.
It doesn’t interpret data.
It doesn’t “optimize” anything.
Its job is brutally narrow and incredibly important:
Whatever bytes are written must be retrievable later, unchanged.
That’s it.
More Than Just a Cache Manager
Calling the pager a “cache manager” undersells it.
Yes, it moves pages between disk and memory.
But it also quietly takes on multiple DBMS responsibilities:
Transaction manager
Handles atomic commit and rollback
Log manager
Decides when and how journal records are written
Lock manager
Ensures correct file-level locking before page access
Data manager
Coordinates page reads, writes, and space management
Locks and journals exist — but the pager decides when they are used, and in what order.
The Illusion It Creates
The pager’s greatest trick is the abstraction it provides.
To higher layers, it looks like:
- The entire database lives in memory
- Pages can be accessed randomly
- Reads and writes are cheap and safe
Underneath, it’s carefully juggling:
- Limited RAM
- Slow disks
- Crashes
- Partial writes
- Concurrent access
All while never letting higher layers see the mess.
What’s Coming Next
Today was about what the pager is and why it exists.
In the next post, we’ll get concrete:
- The pager interface
- The page access protocol
- How higher layers interact with it
- And how pager states change across transactions
That’s where things start getting really interesting.
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
SOCIAL SHARE CARD GENERATOR