When rows are deleted and pages fall empty, SQLite does not shrink the file. The
pages go on the freelist and are used again before the file grows. The
database header gives the first
trunk page at offset 32 and the total number of freelist pages, trunks and leaves
together, at offset 36. VACUUM, or
auto-vacuum, gives free
pages back by making the file smaller.
Trunk pages
The freelist is a chain of trunk pages, each listing some leaf pages. A trunk page is an array of 4-byte big-endian numbers filling the usable space of the page:
| Offset | Size | Type | Name | Description |
|---|---|---|---|---|
| 0 | 4 | u32 | Next trunk page | The next trunk page of the chain, or 0 on the last. |
| 4 | 4 | u32 | Leaf count | L, the number of leaf page numbers that follow. |
| 8 | 4 × L | u32[L] | Leaf pages | The page numbers of the leaf pages. |
A trunk page has room for (U ÷ 4) − 2 leaf numbers, where U is the usable size, but SQLite fills at most (U ÷ 4) − 8. Versions before 3.6.0 (July 2008) reported a database as corrupt if any of the last six entries of a trunk page was in use. Later versions read such pages, but still leave those six entries unused so that older versions can read the file. The explorer accepts up to (U ÷ 4) − 2.
| Usable size | Leaf numbers SQLite writes, at most |
|---|---|
| 1,024 | 248 |
| 4,096 | 1,016 |
The trunk pages themselves are free pages too, and are counted at header offset 36.
Leaf pages
A leaf page is a free page listed on a trunk. Its content means nothing: it is
whatever the page held before it was freed, or zeros. SQLite avoids reading or writing
leaf pages, so freeing a page costs no write to that page. The old content of a
deleted row can therefore survive on a leaf page until the page is reused, unless
PRAGMA secure_delete is on.