SQLite UUIDs tank performance by 300 percent
Random UUIDs as primary keys in SQLite cause page splits that triple insertion time. Integer keys stay fast because SQLite is built for sequential writes.
Anders Murphy benchmarked UUID primary keys in SQLite and found a 300 percent slowdown compared to integer keys. The culprit is not the UUID size. It is the randomness. SQLite stores rows in B-tree order by primary key. Sequential integer keys append cleanly to the end of the tree. Random UUIDs land anywhere, forcing the database to split pages mid-tree, copy data around, and rebalance branches. Each insert triggers more disk writes than it should. The benchmark inserts 100,000 rows. Integer keys finish in one second. UUIDs take four. The gap widens with table size because page splits compound. At a million rows, you are waiting twenty seconds instead of five. PostgreSQL has similar behaviour but hides it better with TOAST and more aggressive caching. SQLite runs leaner, so every page split shows up in wall-clock time. If you need globally unique keys, Murphy suggests ULIDs or timestamp-prefixed UUIDs. Both preserve some sequential ordering so the B-tree does not thrash. I have used UUIDs in Postgres without noticing this because connection pooling and caching smooth it over. SQLite is different. It is a file on disk, not a server process. Every write goes straight to the filesystem. Random primary keys fight the way SQLite is built. The fix is simple. Use integer autoincrement keys for the primary key. Store the UUID in a separate indexed column if you need external references. Sequential writes stay fast. Lookups by UUID stay indexed. The database does not spend half its time splitting pages.