Sqlite Database File Structure

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

Picking up from where we left off, we now move deeper—past naming and connections—into the physical anatomy of an SQLite database file.

At this level, SQLite stops looking like a relational system and starts behaving like a carefully managed byte array, layered with structure and intent.

Except for pure in-memory databases, every SQLite persistent or temporary, it lives entirely inside one file.

That file grows and shrinks over its lifetime, constrained only by what the operating system and filesystem allow.

To the OS, this file is nothing special: just a sequence of bytes.

It does not understand tables, rows, or indexes.

SQLite builds all of that meaning on top of basic read, write, and sync primitives.

The rest of this section explains how SQLite imposes order on that raw byte stream.

The Page Abstraction: SQLite’s Fundamental Unit

To manage space efficiently and keep I/O predictable, SQLite divides the database file into fixed-size chunks called pages.

A few key properties follow from this design:

• Every database file size is always a multiple of the page size
• Pages are numbered sequentially, starting from page 1
• Page number 0 is special, it represents “no page” and never exists physically
• Page 1 starts at file offset 0, followed by page 2, page 3, and so on

You can think of the database file as a dynamic array of pages, where the page number acts as the array index.

This abstraction is provided by SQLite’s pager module, which sits between higher level logic and the operating system’s file APIs.

Page Size: Fixed, Powerful, and Flexible

SQLite’s default page size is 1024 bytes, but this is not a hard rule.

Important constraints:

• Page size must be a power of two
• Valid range: 512 bytes to 65,536 bytes
• Upper bound exists because page size is stored in 2-byte unsigned integers internally

The theoretical maximum database size is enormous, on the order of 140 terabytes, derived from:

• Maximum page count: 2,147,483,647 pages
• Maximum page size: 65,536 bytes

In practice, filesystem limits usually come first.

Changing the Page Size

Once a database file is created, it adopts the default page size used by the SQLite library that created it. However, you can override this before creating the first table using:

PRAGMA page_size = 4096;

SQLite stores the chosen page size in the database file itself. From that point on:

• The database is permanently tied to that page size
• It will work correctly even if opened later by a SQLite library compiled with a different default

This is a critical design decision: page size is a property of the database file, not the library.

Page Types: Nothing Floats, Nothing Leaks

SQLite tracks every page in the database file. There is no garbage collection and no orphaned space drifting around. Each page always belongs to exactly one category.

Broadly, pages fall into four types:

1. Free pages
   • Currently unused
   • Tracked explicitly, ready for reuse
2. Tree pages
   • Form B-tree or B+-tree structures
   • Subtypes:
     • Internal pages (navigation)
     • Leaf pages (actual data)
     • Overflow pages (spillover for large records)
3. Pointer-map pages
   • Used by auto-vacuum and incremental vacuum
   • Track parent–child relationships between pages
4. Lock-byte pages
   • Used internally for concurrency control

Tree pages dominate most databases. Internal pages guide searches, while leaf pages store real content, table rows or index entries.

When a record grows too large to fit entirely inside a leaf page, SQLite stores part of it inline and spills the rest into overflow pages, linked together as needed.

Nothing is implicit. Nothing is guessed. SQLite always knows where every page is and why it exists.

Page 1: The Anchor of the Entire Database

SQLite allows almost any page to serve any purpose, with one critical exception.

Page 1 is special.

It always serves two roles:

1. A B+-tree internal page, acting as the root of sqlite_master (or sqlite_temp_master)
2. The home of the 100-byte database file header, starting at file offset 0

This page is the database’s anchor point. From it, SQLite can discover:

• The schema
• The page size
• The file format
• The total database size
• And how to interpret every other byte in the file

If Page 1 is unreadable, the database is effectively lost.

Database Metadata: The File Header

The first 100 bytes of Page 1 form the file header. This header defines the structural rules of the database. Some of the most important fields include:

Header String

A fixed 16-byte UTF-8 string:

SQLite format 3

This acts as a magic signature. If it doesn’t match, SQLite refuses to open the file.

Page Size

Stored directly in the header. This tells SQLite how to slice the rest of the file into pages. A value of 1 represents a page size of 65,536 bytes.

File Format Version

Two bytes indicate:

• Read format version
• Write format version

Values:

• 1 → legacy rollback journal format
• 2 → WAL format

If SQLite encounters a higher version than it understands, it will refuse to read or write the database.

Reserved Space

Up to 255 bytes at the end of each page may be reserved. Normally this is 0.

A non-zero value is used when SQLite’s built-in encryption is enabled, leaving room for per-page nonces.

The remaining part of each page is the usable space, which must be at least 480 bytes.

Payload Fractions

Several header fields control how much data can be embedded directly inside tree pages before spilling into overflow pages:

• Maximum embedded payload fraction
• Minimum embedded payload fraction
• Minimum leaf payload fraction

These values enforce balance ensuring tree nodes don’t degenerate into a single massive record.

While their original tuning purpose is mostly historical today, they remain part of the on-disk format for compatibility.

File Change Counter

This counter increments on every successful write transaction.

It allows the pager to detect when cached pages are stale and must be reloaded.

Database Size

Stored as the current number of pages in the database.

This lets SQLite know where the file logically ends, even if the OS file size is larger due to preallocation or leftovers.

Why All This Matters

At this layer, SQLite reveals its true nature: not a lightweight toy database, but a precisely engineered storage engine.

Every byte has a reason.

Every page has an owner.

Nothing is left to chance or background cleanup.

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