skip to content

What is inside a SQLite database file: what does its 100-byte header record, and how are tables and indexes stored in its pages?

level: middleimportance: nice to knowfreq 22%

answer

  1. a magic string first
  2. page size at offset 16
  3. page 1 roots the schema
  4. one B-tree per table and index
  5. rowid keys, rows in leaves

basics

~20 s

The 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 s

Every 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 lines
sql
PRAGMA page_size;
PRAGMA page_count;
SELECT type, name, rootpage FROM sqlite_schema;

go deeper

for a junior

Recall that a SQLite database is one file of equal-size pages, 4096 bytes by default, starting with a header that names the format.

for a middle

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.

for a senior

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.

for a principal

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.