Tools: Structure of a Cell in SQLite

Structure of a Cell ## Four Variations of a Cell ## 1. Table Tree — Internal Page ## 2 Table Tree — Leaf Page ## Index Tree — Internal Page ## 4. Index Tree — Leaf Page ## Variant Integers: SQLite’s Compact Encoding ## Payload Restrictions and Embedded Fractions ## Overflow Pages ## The Overflow Calculation Rules ## The Big Picture ## HexmosTech / git-lrc ## Free, Unlimited AI Code Reviews That Run on Commit ## git-lrc ## See It In Action Hello, I'm Maneshwar. I'm working on git-lrc: a Git hook for Checking AI generated code. AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production. git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free. Today we finally reach the most granular unit of the B+-tree: The cell — the actual byte-level representation of keys and rows. Everything we’ve studied so far page headers, pointer arrays, overflow chains, balancing — exists to manage these variable-length byte strings safely and efficiently. A cell is a variable-length byte string stored inside the cell content area of a tree page. Each cell represents (a part of) a single payload. That payload is composed of: a key and an optional data portion The exact layout of a cell depends on: The generic structure is shown here: One of the most important things to notice is that several fields are marked as var. These are variable-length integers, called variants. Cells are not fixed-size structures. Their layout adapts depending on what kind of tree they belong to. Let’s connect the abstract structure to SQLite’s four tree page types. There is no payload stored here. Internal table nodes only route searches based on rowid. This is where actual table rows live. Internal index pages route based on key values rather than rowid. Leaf index pages store only keys. No separate data portion exists. Notice that keys or data may be partially stored in the cell, with the remainder spilling to overflow pages. SQLite uses a variable-length integer encoding to represent: A variant integer uses between 1 and 9 bytes. Each byte contributes: The integer is formed by reading consecutive bytes whose high bit is set, stopping at the first byte whose high bit is clear. The most significant byte appears first. As a special case, if 9 bytes are used, all 8 bits of the 9th byte are part of the value This encoding allows: It is essentially a Huffman-style encoding, optimized for common small values while still supporting full 64-bit range. This design dramatically reduces space overhead for rowids and payload sizes. You might assume that if a page has enough free space, a payload would always be stored fully inside it. SQLite does not do that. Instead, it enforces maximum embedded payload fractions, which are stored in the file header: These values control how much of a payload is allowed to reside directly on the page. If a payload exceeds the allowed embedded fraction: Even if there is technically enough space, SQLite may still spill data to overflow to maintain page balance and future insertion flexibility. Large payloads are stored across overflow pages, which form a singly linked list. Overflow pages never mix content from multiple payloads. The chain continues until the entire payload is stored. SQLite uses specific formulas to decide how much payload to keep on the page and how much to spill. For example, on a leaf table-tree page: The formulas are carefully chosen to: Internal table-tree pages never store payload or overflow. Index trees follow slightly different thresholds, tuned for key storage. At this point, we have reached the lowest structural level of SQLite’s B+-tree system: Every SQL operation — insert, update, delete — eventually becomes: The abstraction layers now fully connect: We’ve now finished dissecting the physical structure of B+-tree pages. In the next post, we step back up one level and examine: The Tree Module Functionalities That’s where the runtime control layer meets the structural layout we’ve just explored. 👉 Check out: git-lrc Any feedback or contributors are welcome! It’s online, source-available, and ready for anyone to use. ⭐ Star it on GitHub: Free, Unlimited AI Code Reviews That Run on Commit AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production. git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free. See git-lrc catch serious security issues such as leaked credentials, expensive cloud operations, and sensitive material in log statements Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse - Whether the page is internal or leaf - Whether the tree is a table tree or an index tree - 4-byte left child page number - Variant storing the rowid value - Variant for total length of the row record - Variant for rowid - Part of the row record - 4-byte overflow page number (if needed) - 4-byte left child page number - Variant for total key length - Part of the key - 4-byte overflow page number (if needed) - Variant for total key length - Part of the key - 4-byte overflow page number (if needed) - Rowid values - 7 bits for the integer value - 1 bit (the highest bit) indicating continuation - Small integers (very common) to be stored in 1 or 2 bytes - Large 64-bit integers to be stored in at most 9 bytes - Offset 21 → max embedded payload fraction - Offset 22 → min embedded payload fraction - Offset 23 → min leaf payload fraction - SQLite stores part of it in the cell - The remainder spills into overflow pages - Uses its first 4 bytes to store the next overflow page number - Uses the rest of the page for payload data - Except the last page, which may contain unused trailing space - Large rows do not monopolize tree pages - Tree pages remain balanced and efficient - Insert/delete operations remain predictable - If payload size p < (u - 36), store it fully on page - Otherwise compute a minimum local payload M - Store a calculated portion locally - Spill the rest to overflow pages - Maintain minimum occupancy - Avoid pathological fragmentation - Keep cells within allowed bounds - Pointer arrays - Free blocks - Modify a cell - Update pointer arrays - Possibly allocate overflow pages - Mark pages dirty in the pager - Journal changes for recovery - Control data structures - The Btree structure - The BtShared structure - 🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production. - 🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong. - 🔁 Build a habit, ship better code. Regular review → fewer bugs → more robust code → better results in your team. - 🔗 Why git? Git is universal. Every editor, every IDE, every AI…