What is inside a SQLite database file: what does its 100-byte header record, and how are tables and indexes stored in its pages?
answer
- a magic string first
- page size at offset 16
- page 1 roots the schema
- one B-tree per table and index
- rowid keys, rows in leaves
basics
~20 sThe file is a run of equal-size pages, 4096 bytes by default since 3.12.0. Page 1 opens with a 100-byte header (magic string, page size, counters, freelist head) and roots sqlite_schema, which records the root page of every table and index B-tree.
solid answer
~50 sEvery SQLite database starts with the 16 bytes `SQLite format 3` plus a nul, then header fields such as the page size at offset 16, the file-format version bytes (2 in WAL mode), the file change counter, the size in pages, the first freelist trunk page, the schema cookie, the text encoding and `user_version`. After that the file is pages of one size, a power of two from 512 to 65536 bytes, 4096 by default since 3.12.0 (1024 before). Each rowid table is a **table B-tree** keyed by its 64-bit rowid: interior pages hold only keys and child pointers, and the rows live in the leaves. Each index, and each `WITHOUT ROWID` table, is an **index B-tree**. Page 1 roots `sqlite_schema`, which records every other B-tree's root page. Large payloads spill into overflow pages, and unused pages sit on the freelist.
code
sql · 3 linesPRAGMA page_size;
PRAGMA page_count;
SELECT type, name, rootpage FROM sqlite_schema;go deeper
Recall that a SQLite database is one file of equal-size pages, 4096 bytes by default, starting with a header that names the format.
Explain the two B-tree kinds, rowid tables keyed by a 64-bit integer with rows in the leaves and index B-trees for indexes and WITHOUT ROWID tables, and page 1 rooting sqlite_schema.
Connect the format to operations: page size fixed in WAL mode, the freelist behind file size, overflow pages for large values, and header bytes 18 and 19 marking WAL.
Use the stable, documented format as an argument when choosing SQLite as an application file or interchange format, and know which settings, page size and auto-vacuum, are hard to change later.
## One file, one page size The complete state of a SQLite database normally lives in a single file, the **main database file**, joined during and after transactions by a rollback journal or a WAL file. The main file is a sequence of **pages**, numbered from 1, all the same size within one database. The page size is a power of two from 512 to 65536 bytes. SQLite chooses it for a new database based on the platform and filesystem; the default has been 4096 bytes since 3.12.0, and for many years before that it was almost always 1024. All reads and writes start at a page boundary and cover whole pages, with one exception: when a database is first opened, the first 100 bytes, the header, are read on their own. At any moment every page has exactly one job: - a **table B-tree** page, interior or leaf; - an **index B-tree** page, interior or leaf; - a **freelist** page, trunk or leaf, tracking unused pages; - a **payload overflow** page, holding the tail of a cell too large for its page; - a **pointer-map** page, present only in auto-vacuum databases; - the **lock-byte page**, which exists only in files larger than 1,073,741,824 bytes and which SQLite itself never reads or writes. ## The 100-byte header Page 1 begins with the database header. Every multi-byte field is big-endian. A selection: | Offset | Size | Field | |---|---|---| | 0 | 16 | The string `SQLite format 3` followed by a nul byte | | 16 | 2 | Page size in bytes; the value 1 stands for 65536 | | 18, 19 | 1 each | File-format write and read versions: 1 for legacy, 2 for WAL | | 24 | 4 | File change counter | | 28 | 4 | Size of the database in pages | | 32, 36 | 4 each | First freelist trunk page, and total number of freelist pages | | 40 | 4 | Schema cookie, which changes whenever the schema changes | | 56 | 4 | Text encoding: 1 UTF-8, 2 UTF-16le, 3 UTF-16be | | 60 | 4 | The user version, read and set with `PRAGMA user_version` | | 68 | 4 | The application ID, set with `PRAGMA application_id` | The magic string is how any tool recognises a SQLite file, and the user version is a common place for an app to record which schema migration it has reached. ## Table B-trees and index B-trees SQLite stores everything in two kinds of B-tree, and each complete B-tree is identified by the page number of its root. - A **table B-tree** holds one rowid table. Each entry has a 64-bit signed integer key, the rowid, and the row's content as its data. Interior pages hold only keys and pointers to child pages; all the row content lives in the leaves. - An **index B-tree** holds one index, including the implicit indexes created by `UNIQUE` and `PRIMARY KEY` constraints. Each entry is a key of arbitrary length with no separate data; besides the indexed columns, the key usually carries columns that point back to the table row. - A **WITHOUT ROWID** table uses an index B-tree rather than a table B-tree. - A virtual table has no B-tree of its own. The schema table, `sqlite_schema` (the older name `sqlite_master` still works), is always a table B-tree rooted at **page 1**, directly after the header. Each of its rows describes a table, index, view or trigger, holds its SQL text and, for tables and indexes, the root page of its B-tree. Opening a database therefore means reading page 1 and following root pages from there. When a cell's payload exceeds a threshold, only its first bytes stay on the B-tree page and the rest goes into a linked list of overflow pages. Table B-tree keys are integers and never need overflow, so key overflow happens only in index B-trees. ## Changing the page size `PRAGMA page_size = N` does not change an existing file immediately. The value is remembered and used when the database is first created, or at the next `VACUUM` on that connection while the database is not in WAL mode. A WAL-mode database cannot change its page size at all, not by `VACUUM` and not by restoring through the backup API; switch to a rollback-journal mode first. ## Why the format matters to you - The format is documented, cross-platform and has been used by every release since 3.0.0, so a file can be copied between machines and architectures freely, which is what makes a SQLite file usable as an application file format. - The header lets a tool check a file cheaply, and `PRAGMA page_count` times `PRAGMA page_size` gives the size of the database. - Knowing that an ordinary table is a B-tree keyed by rowid explains why `VACUUM` may renumber the rows of tables without an explicit `INTEGER PRIMARY KEY`, and why a freelist is needed at all. ```sql SELECT type, name, rootpage FROM sqlite_schema; ```
- How does SQLite store a row too large to fit on one page?Only the first part of the cell's payload stays on the B-tree page; the rest goes into a linked list of payload overflow pages. Table B-tree keys are integer rowids and never overflow, so overflow carries row content in table B-trees and keys in index B-trees.
- How do you change the page size of an existing SQLite database?Set PRAGMA page_size = N, a power of two from 512 to 65536, then run VACUUM on the same connection while the database is not in WAL mode; the rebuild writes the file with the new size. A WAL-mode database must first switch to a rollback-journal mode such as DELETE, then VACUUM, then switch back to WAL.
- SQLite keeps each table in its own file inside a database directory.
- Each table in a SQLite file can use its own page size.
- Rows of a rowid table are stored in interior pages as well as leaves.
- The SQLite schema lives in a separate catalog file next to the database.
- SQLite's default page size has always been 4096 bytes.
Assumes SQLite 3.53; the default page size for new databases rose from 1024 to 4096 bytes in 3.12.0, so files created by older libraries often use 1024-byte pages.