Deep Dive into SQLite Storage

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.*

Yesterday, we looked at Page 1, the immutable starting point of every SQLite db.

Today, we move forward into parts of the file that feel invisible at the SQL layer but are absolutely central to how SQLite manages space, survives crashes, and keeps itself fast over time.

This post continues the journey by explaining freelists, leaf pages, trunk pages, and then journals the SQLite’s safety net.

Why SQLite Needs a Freelist

SQLite never gives unused pages back to the operating system immediately.

Once a page is allocated to a db file, it stays inside the file unless an explicit shrink operation happens.

When rows are deleted, indexes dropped, or tables removed, pages become inactive.

Instead of discarding them, SQLite places those pages into a structure called the freelist.

The freelist is simply SQLite’s internal inventory of unused pages which are ready to be reused for future inserts without growing the file.

What Is the Freelist?

The freelist is a linked structure embedded directly inside the db file.

Key facts:

• The first freelist trunk page number is stored in the file header at offset 32
• The total count of free pages is stored at offset 36
• All free pages are tracked no garbage collection, no ambiguity

SQLite organizes the freelist as a rooted tree-like list, starting from the file header and branching outward.

Trunk Pages and Leaf Pages (Freelist Pages)

Freelist pages come in two subtypes:

Trunk Pages

A trunk page is a directory of free pages.

Its layout (starting at the beginning of the page):

1. 4 bytes → Page number of the next trunk page (or 0 if none)
2. 4 bytes → Number of leaf pointers stored on this trunk
3. N × 4 bytes → Page numbers of leaf pages

Each trunk page can reference many free pages at once.

Leaf Pages

A leaf page is a free page that contains no meaningful structure. Its content is unspecified and may contain garbage from prior use.

Leaf pages are the actual reusable pages. Trunk pages merely point to them.

How Pages Enter and Leave the Freelist

When a page becomes inactive SQLite adds it to the freelist. The page remains physically inside the db file

When new data must be written:

This explains why databases often grow but don’t shrink automatically.

Shrinking the Database: VACUUM and Autovacuum

If the freelist grows too large, disk usage becomes wasteful. SQLite provides two solutions:

VACUUM

This is a heavyweight but precise operation.

Autovacuum Mode

Autovacuum trades runtime overhead for space hygiene.

Journal Files in SQLite

A journal is a crash recovery file that records db changes so SQLite can roll back incomplete transactions.

It guarantees atomicity and durability, ensuring the db is never left half-written after a failure.

SQLite historically uses legacy journaling, which includes:

1. Rollback journal
2. Statement journal
3. Master journal

From SQLite 3.7.0 onward, databases use either legacy journaling or WAL, never both at the same time.

In-memory databases skip journaling entirely but done in memory itself.

Rollback Journal: SQLite’s Safety Harness

Each db has one rollback journal file:

• Stored in the same directory as the db
• Named by appending -journal to the db file name
• Created at the start of a write transaction
• Deleted (by default) when the transaction finishes

Rollback journals store before images of db pages, allowing SQLite to restore the db if something goes wrong.

Rollback Journal Structure

A rollback journal is divided into log segments.

Each segment consists of:

1. Segment header
2. One or more log records

Most of the time, there is only one segment. Multiple segments appear only in special situations.

Segment Header: The First Line of Defense

Each segment header starts with eight magic bytes:

D9 D5 05 F9 20 A1 63 D7

These bytes exist solely for sanity checks.

The header also stores:

• Number of log records (nRec)
• Random value for checksum calculations
• Original db page count
• Disk sector size
• DB page size

The header always occupies exactly one disk sector, and all values are stored in big-endian format.

Journal Retention Modes

By default, SQLite deletes the journal file after commit or rollback.

You can change this using:

• DELETE (default)
• PERSIST
• TRUNCATE

In exclusive locking mode, the journal file persists across transactions, but its header is invalidated or truncated between uses.

Asynchronous Transactions (Unsafe but Fast)

SQLite supports an asynchronous mode:

• Journal and db files are never flushed
• Faster transactions
• nRec is set to -1
• Recovery relies on file size, not metadata

This mode is not crash safe and is intended mainly for development or testing scenarios, but yeah there is performance gains.

Why This Layer Matters

At this depth, SQLite reveals its philosophy:

• Space is recycled, not discarded
• Safety is achieved with precise, minimal metadata
• Nothing is implicit; everything is tracked
• Recovery logic is encoded directly into file structure

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