Explain the internal working of SQLite's B-tree storage structure?

Every table and index in SQLite is stored as a separate B-tree within the single database file — a balanced tree structure where each node is one fixed-size page (typically 4096 bytes), organized so that lookups, insertions, and range scans all stay efficient even as the table grows large.

flowchart TD
    A[Root page] --> B[Interior page]
    A --> C[Interior page]
    B --> D[Leaf page: rows 1-50]
    B --> E[Leaf page: rows 51-100]
    C --> F[Leaf page: rows 101-150]

Table B-trees are keyed by rowid and store the actual row data in their leaf pages; index B-trees are keyed by the indexed column's value and store a reference back to the corresponding rowid, which is then used to fetch the full row from the table's own B-tree. A lookup by primary key (rowid) is a single B-tree traversal; a lookup via a secondary index requires first traversing the index's B-tree, then a second traversal into the table's B-tree to fetch the actual row — which is why a "covering index" (one that includes every column a query needs) can skip that second traversal entirely.