storage-format
This guide breaks down how SQLite organizes data on disk, from the 100-byte header through page types, B-tree layouts, and cell formats. It covers record encoding with serial types, overflow page chains for large payloads, and freelist structures for space reuse. Includes Turso implementation details and debugging commands.
storage-format explains SQLite's on-disk structure, including headers, B-trees, pages, cells, and overflow handling.
AI-generated summary based on this skill's SKILL.md
Install
tursodatabase/turso/storage-format · repository language: Rust
git clone https://github.com/tursodatabase/turso
cp -r turso/.claude/skills/storage-format ~/.claude/skills/storage-formatnpx skillfed install tursodatabase/turso/storage-formatFrequently asked questions
AI-generated answers based on this skill's SKILL.md and metadata
What is the SQLite file format structure?
storage-format explains how SQLite organizes data on disk starting with a 100-byte header that defines page size, version, and schema metadata. The file is divided into fixed-size pages (typically 4096 bytes), each containing a page header, cell pointers, and cell content. Pages are typed as leaf tables, interior tables, leaf indexes, or interior indexes, arranged in a B-tree hierarchy. The header also stores the freelist root page and total page count, enabling SQLite to manage space and navigate the entire database structure.
How does SQLite store data on disk using B-trees?
storage-format details how SQLite uses B-tree pages to organize table and index storage. Interior pages contain keys and child page pointers for navigation; leaf pages hold actual records. Each page has a page header (8–12 bytes) followed by a cell pointer array and free space. Cells are variable-length records stored from the page end backward. The B-tree structure allows efficient range queries and insertions. Turso's storage engine implements this same layout, enabling compatibility with standard SQLite tools while supporting distributed features.
What are B-tree interior vs leaf pages in SQLite?
storage-format distinguishes interior and leaf pages in SQLite's B-tree. Interior pages store keys and pointers to child pages, directing searches downward through the tree. Leaf pages store actual data cells—for tables, the full record with rowid; for indexes, the indexed columns and rowid. Interior pages have a right-child pointer; leaf pages do not. Both page types share the same header format but differ in cell content. This separation enables efficient tree navigation and keeps leaf pages focused on data storage.
How does SQLite handle overflow pages for large payloads?
storage-format explains SQLite's overflow mechanism for records exceeding available cell space. When a record is too large, the cell stores a pointer to an overflow page chain. The first overflow page contains a link to the next overflow page and partial record data; subsequent pages continue the chain. This design avoids fragmenting leaf pages and keeps them efficient for typical records. The overflow structure is transparent to queries but critical for debugging large-value storage and understanding space usage in real databases.
What is the SQLite record format with serial types?
storage-format describes how SQLite encodes records using serial types. Each record begins with a varint header specifying the number and types of columns. Serial types encode data type (NULL, integer, float, blob, text) and length, allowing SQLite to parse variable-length fields without schema lookups. Text and blob types include length in the serial type; integers use 1–8 bytes depending on value. This compact encoding minimizes storage and enables fast deserialization during query execution.
How can I debug and inspect Turso/SQLite database files?
storage-format provides guidance for inspecting database files using hex editors, the SQLite command-line tool, and Turso-specific debugging commands. You can examine the 100-byte header to verify page size and schema version, then navigate pages by offset to inspect B-tree structure, cell layouts, and freelist chains. Understanding page types, cell pointers, and record formats lets you trace data organization and diagnose corruption or space issues. Turso's storage engine maintains standard SQLite format, so these inspection techniques apply directly.
SKILL.md
rendered from the published skill — quoted content, verbatim
Storage Format Guide
Database File Structure
┌─────────────────────────────┐
│ Page 1: Header + Schema │ ← First 100 bytes = DB header
├─────────────────────────────┤
│ Page 2..N: B-tree pages │ ← Tables and indexes
│ Overflow pages │
│ Freelist pages │
└─────────────────────────────┘
Page size: power of 2, 512-65536 bytes. Default 4096.
Database Header (First 100 Bytes)
| Offset | Size | Field |
|---|---|---|
| 0 | 16 | Magic: "SQLite format 3\0" |
| 16 | 2 | Page size (big-endian) |
| 18 | 1 | Write format version (1=rollback, 2=WAL) |
| 19 | 1 | Read format version |
| 24 | 4 | Change counter |
| 28 | 4 | Database size in pages |
| 32 | 4 | First freelist trunk page |
| 36 | 4 | Total freelist pages |
| 40 | 4 | Schema cookie |
| 56 | 4 | Text encoding (1=UTF8, 2=UTF16LE, 3=UTF16BE) |
All multi-byte integers: big-endian.
Page Types
| Flag | Type | Purpose |
|---|---|---|
| 0x02 | Interior index | Index B-tree internal node |
| 0x05 | Interior table | Table |
(truncated - see the full file via the links below)
Read as markdown · JSON record · Browse the source repository
File tree — 1 file
.claude/skills/storage-format/SKILL.md