SQLite

On-disk layout

A SQLite file begins with a 100-byte header that contains the magic string "SQLite format 3", the page size, the schema cookie, and pointers to the freelist and the most recently committed transaction. The rest of the file is a sequence of equally-sized pages.

Pages are typed: lock-byte, freelist, B-tree interior, B-tree leaf, payload overflow, and pointer-map. The B-tree interior pages form a navigational index; B-tree leaf pages hold actual row data. Pages that are too small to fit a row spill into a chain of overflow pages linked by 4-byte next-page pointers.

• A 4 KiB page can hold 2-3 typical rows, or 40+ small rows.
• Schema changes bump the schema cookie so prepared statements from older schemas are re-prepared automatically.
• PRAGMA integrity_check walks every page to detect corruption.

Concurrency model

SQLite serializes writers and parallelizes readers. In rollback-journal mode, a writer takes an EXCLUSIVE lock, while readers use SHARED locks. In WAL mode, writers append to the WAL file and readers see the most recently committed snapshot plus the WAL tail, which lets readers and one writer run simultaneously without blocking.

WAL mode is the default for new databases that opt in via PRAGMA journal_mode = WAL. It is faster under mixed workloads and survives crashes because the WAL is replayed or rolled back at next open.

              PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
            

Query evaluation

SQLite uses a flat-bytecode VM. The query planner builds a tree of operations -- open-read, open-write, sort, filter, aggregate, join -- and the executor dispatches each op in turn. There is no JIT, no vectorized engine, no parallelism. For the workloads SQLite targets (embedded, low-concurrency, single-user) this trade-off wins: a 600 KB library that boots in microseconds and never pages out.