Minh Tri Le · i use sqlite all the time and i realised i couldn’t draw what’s inside the file. i knew the words: pages, b-trees, “it’s just one file”. but if you’d handed me a hex dump i’d have been lost.
so i built a page that hands you the hex dump, and then explains it: what’s inside a sqlite file.
it’s real sqlite, not a drawing
the page loads sqlite compiled to webassembly (sql.js, served from this site), creates a small notes table, and then calls db.export() to get the actual bytes of the database file. a ~200-line parser, written against the official file format doc, turns those bytes into everything you see: the 100-byte header, page types, cell pointers, varints, serial types, overflow chains, the freelist.
nothing is canned. when you press ”+ row”, sqlite does the insert, the page re-reads the file, and the figures redraw from whatever the bytes now say.
one cheat, and it’s a visible one: the demo uses PRAGMA page_size = 512, the smallest sqlite allows. the default is 4096. with 512-byte pages a leaf fills up after about a dozen short rows, so you get to watch a split without inserting thousands of rows first. there’s a switch for 4096 in the playground if you want to see how flat the tree gets.
things i didn’t know before building it
the root page never moves. sqlite_schema stores each table’s root page number. so when the root leaf fills up, sqlite doesn’t make a new root above it. it moves the root’s rows down into a fresh page and turns the root itself into the signpost. same page number, new job.
appending doesn’t split 50/50. every b-tree diagram i’d seen splits a full node down the middle. with ascending ids (the normal case for an integer primary key), sqlite usually leaves the full leaf alone and starts a new right-hand page with just the new row. btree.c calls this fast path balance_quick. the result is leaves that end up packed full, instead of half-empty. i only noticed because the bytes refused to match my first animation.
your id column is stored as NULL. an INTEGER PRIMARY KEY is the rowid, and the rowid already sits in front of every cell. so the record itself stores serial type 0 (NULL) for that column. no point storing it twice.
small integers are free. serial types 8 and 9 mean “the integer 0” and “the integer 1”. zero bytes of body. the type code is the value.
big rows spill. a table leaf keeps at most U − 35 bytes of a row’s payload on the page (477 with 512-byte pages). past that, only a prefix stays and the rest goes through a chain of overflow pages, each starting with the 4-byte number of the next one.
the chapters
- the file: the 100-byte header, field by field. hover a byte.
- pages: every page has one job: schema, interior, leaf, overflow, freelist.
- inside a page: header and cell pointers grow from the top, cells from the bottom, free space in the middle.
- splits: before and after an insert that doesn’t fit, computed from the real bytes, in four steps.
- a query’s path:
where id = 6, root to leaf, one page per level. - playground: insert, bulk insert, a huge row, delete, vacuum, add an index, change the page size.
every chapter has its own link, down to the state: /sqlite#split/2 opens the split at step 2, /sqlite#page/3@3 selects byte 3 of page 3. made for pointing at one exact thing in a reply.
it works on a phone, which matters because that’s where most people will open it.
if something on it is wrong, tell me on x. the whole point is that it matches the bytes.