what's inside
a sqlite file

it's just one file

no server, no folder of mystery. a sqlite database is a single file on disk, and the first 100 bytes tell sqlite how to read the rest.

the first 16 bytes literally spell "SQLite format 3", plus a zero byte. right after sits the page size: how big each chunk of the file is. sqlite's default is 4096. this demo uses 512, the smallest allowed, so things fill up after a handful of rows. you can switch to 4096 in the playground.

this file is … pages × 512 bytes = … bytes. hover a byte, or tap it.

#file copy link · #file@28 selects byte 28

cut into pages

sqlite never reads "a row". it reads a whole page, then finds the row inside it. every page has exactly one job.

interior pages are signposts. leaf pages hold your rows. overflow pages catch rows too big to fit. freelist pages are empty, waiting to be reused.

page 1 is special: the file header, then sqlite_schema, the little table that lists every table and index and the page where its tree starts. our demo table is notes(id, title, stars, body) with 30 short notes and one long one.

#pages copy link · #pages/3 highlights page 3

a page, up close

a page fills from both ends. a small header (8 bytes on a leaf, 12 on an interior page) and a list of 2-byte cell pointers grow from the top. the cells, your actual rows, are packed in from the bottom.

the gap in the middle is unallocated space. pointers are kept sorted by rowid, but cells can sit anywhere. so an insert writes the new cell at the edge of the gap and slots a pointer into place. when the two ends meet, the page is full.

inside a cell: the payload size and the rowid, both varints (7 bits per byte, the high bit means "more"). then the record: a header with one serial type per column, then the values. id is stored as NULL: an integer primary key is the rowid, so sqlite doesn't store it twice.

#page copy link · #page/3@3 selects byte 3 of page 3

when a page fills up

one more row than the page can hold…

a page can't stretch. when a new row doesn't fit, sqlite rebalances: it takes new pages, moves rows into them, and writes a divider key into the parent. the parent is a signpost: "rowids up to here live on the left".

two things the bytes show that diagrams usually don't. the root keeps its page number: sqlite_schema points at it, so when the root fills, its rows move down and the root becomes the signpost. and with ascending ids, sqlite usually doesn't split 50/50: it puts just the new row on a fresh right-hand page and leaves the full one alone.

stack enough signposts and you get a b-tree: short, wide, and quick to walk.

#split copy link · #split/2 = step 2

finding one row

to find a row by id, sqlite doesn't scan the file. it starts at the root page (its number comes from sqlite_schema), binary-searches the divider keys, follows one pointer, and repeats until it lands on a leaf.

one page per level. an interior cell is just a 4-byte page number plus a varint key, so a 4096-byte signpost has room for hundreds of children, and a tree a few levels deep holds a lot of rows.

try an id that doesn't exist, or switch the playground to 4096-byte pages and watch the tree flatten.

#query copy link · #query/6 looks up id 6

an agent remembers, then recalls

an ai agent forgets everything when the chat ends, unless it writes things down. plenty of agents write them into one sqlite file: a row for each memory, a full-text index for keywords, and a vector, a list of numbers that encodes what the memory means, so it can find things by meaning too.

here's a tiny one: 41 memories in a second real database, 4096-byte pages, running in this tab. follow one memory in, then watch it come back out two ways.

to be straight about the setup: real agent stores usually use fts5 and a vector index like sqlite-vec. sql.js ships fts4 and no vector extension, so the vector search here is a plain full scan with a small cosine function. same idea, same kind of pages.

the vectors come from all-minilm-l6-v2 (384 numbers each), computed ahead of time and shipped as a 28 KB file. this page can't embed your own text, so typed words only run the keyword search.

#memory copy link · #memory/4 = recall by keyword