SQLite WITHOUT ROWID saves a third until rows pass a kilobyte, then costs 3x

A table keyed by a UUID string normally stores its rows in a hidden rowid b-tree and keeps the key in a second index. WITHOUT ROWID stores the rows in the key's own b-tree instead. The docs say it suits small rows, so I looked for where "small" ends. On a UUID-text primary key it saved 20 to 35% of the file up to about 800 bytes of payload, and at 1000 bytes the file jumped to 3.3 times the size of the ordinary table.

The test

60,000 rows, key a 36-character UUID string, value a blob of the stated size, 4096-byte pages, file vacuumed before measuring, 20,000 random point lookups. SQLite from the Python 3.14 standard library, in a Linux container.

The saving is the second copy of the key that the rowid table's index carries (about 45 bytes), so it shrinks in relative terms as rows grow and is gone by 500 bytes or so. Lookup is a little faster for small rows, one b-tree descent instead of two, and about even by 400 bytes.

The cliff

In a WITHOUT ROWID table the whole row lives in an index-style b-tree, and those pages cap how much of one cell stays on the page: about a quarter of the page, roughly 1000 bytes at 4096. Past that, the rest goes to an overflow page, and an overflow page is never shared. Each row then costs a leaf cell plus a whole page, which is the 4608 bytes per row in the last two lines, and a lookup reads one more page. The ordinary rowid table keeps payloads of up to about 4000 bytes on its leaf, so its 1000-byte rows stay compact.

Update, same day: the line can be computed exactly. SQLite's file format puts the largest payload an index-style page keeps local at ((U−12)×64/255)−23 bytes, where U is the page size: 1002 at 4096. The payload here is the whole record: a 4-byte header, the 36-character key and the value. So the cliff should sit at a 963-byte value. A byte-by-byte sweep put it there: 962 bytes costs 1025 bytes per row, 963 costs 4609. With 8192-byte pages the formula says 2030, so 1991 bytes of value, and the sweep jumped at 1991, from 2051 to 9218 bytes per row. Small pages make it worse: at 1024 the limit is 230, and a 205-byte value already costs 1139 bytes per row against 303 for the rowid table.

A 100,000-row run with 1000-byte payloads, inserting in random key order, took 1.3 s against 0.19 s for the rowid table, and the file came out at 461 MB against 141 MB. Same shape as the table.

So what

Use WITHOUT ROWID for a key-value table whose rows stay well under a kilobyte, and it is a free third off the file. If the value can grow, such as JSON documents or serialised objects, the rowid table is the safe default, because the penalty for crossing the line is large and silent. A larger page size moves the line up in proportion: work out key + value + a few header bytes against ((page−12)×64/255)−23 and keep a margin.

Method: one machine, one run per size, random UUID keys inserted in random order, other jobs running on the host so timings are ±10%. Sizes are file size after VACUUM divided by rows. The boundary sweep used 20,000 rows per size, one byte at a time across 955–970 (4096) and 1985–2005 (8192).