Chapter 11 · Database Files, Pages, B-Trees, Freelist, and Storage Internals
The SQLite Database File: Header, Page Size, Schema Cookie, and File Format
Open SQLite’s documented file format without turning administration into byte editing: inspect the 100-byte header, fixed-size pages, page counts, encoding, schema cookie, application metadata, and measured file growth.
Learning outcomes
A SQLite database looks deceptively simple in a file browser:
usually one .db file. Chapter 8 explained how
transactions protect that file, Chapter 9 explained how
concurrent connections coordinate around it, and Chapter 10
showed how tables and indexes create different access paths.
This chapter now asks a lower-level question:
what is actually inside the file, and which parts of that
knowledge help an application developer make safer
decisions?
Explain why SQLite’s documented cross-platform database format is useful without treating it as a file format you should edit by hand.
Define fixed-size database pages and connect page_size × page_count to the main file’s allocated size.
Identify the 100-byte database header and several practical fields: page size, file page count, schema cookie, encoding, user_version, application_id, and last-writing SQLite version.
Use PRAGMA page_size, page_count, encoding, user_version, application_id, and safe file-size observation instead of byte manipulation.
Read the first 100 bytes in a read-only script and correlate selected documented offsets with PRAGMA results.
Measure how page_count and file size change as a disposable database grows.
A stable file format is one of SQLite’s deployment strengths
SQLite is not merely a library that happens to write opaque implementation files. Its main database file format is publicly documented and has been used across SQLite 3 releases for decades. That makes SQLite valuable as an application file format: a file produced on one supported platform can normally be moved to another and opened by SQLite without a server-side export/import ceremony.
That portability does not turn direct byte editing into normal administration. The file contains B-trees, free-page structures, overflow chains, schema metadata, and transactional invariants. A hex editor can observe the documented header safely on a closed or copied file, but ordinary changes belong through SQL or documented APIs so SQLite can maintain every dependent structure consistently.
The goal is operational reasoning: understand page growth, free space, large payloads, VACUUM, and cache behavior. Directly patching header bytes, B-tree cells, freelist links, or record payloads is not a supported substitute for SQL migrations or recovery tooling.
Pages: the allocation unit of the main database file
SQLite divides the main database into equal-sized pages. A page is the unit that the pager loads, writes, journals, and caches. Current SQLite permits page sizes that are powers of two from 512 through 65,536 bytes. The long-standing default for new databases is commonly 4,096 bytes, but a build/platform can choose differently, so observe rather than assume.
SELECT sqlite_version();PRAGMA page_size;PRAGMA page_count;PRAGMA freelist_count;PRAGMA encoding;PRAGMA application_id;PRAGMA user_version;
For the main database,
page_count × page_size normally matches the
database file’s logical length in bytes once the file has been
flushed to disk. That arithmetic includes pages currently on the
freelist; free pages are still pages in the file.
The first 100 bytes: the database header
Page 1 is special because its first 100 bytes are a
database-wide header. Every valid SQLite 3 database begins with
the 16-byte magic string SQLite format 3