The perils of UUID primary keys in SQLite

The perils of UUID primary keys in SQLite 05 Jun 2026

It's common to use random UUIDs as a primary key in databases. One of the known downsides of random UUIDs is that their unordered nature (UUID4) can cause a lot of extra paging for the clustered index because you are inserting rows randomly into the Btree and having to re-balance it. This post tries to help us develop a more visceral understanding of the performance cost of all that extra paging.

While this post is about SQLite specifically, the problem of random UUIDs also extends to other databases that use clustered indexes.

What is a clustered index?

A clustered index determines the physical storage order of the rows in a table. The table's data rows are stored in the index's leaf pages, sorted by the indexed key. Because of this:

There can be only one clustered index per table (rows can only be physically sorted one way).

A clustered index is the table. The leaf nodes contain the full row data

A non-clustered index, by contrast, stores only the indexed columns plus a pointer to the actual row data, which lives elsewhere.

Rowid

Every ordinary SQLite table has an implicit 64-bit integer primary key called rowid. The table's data is stored in a B-tree ordered by rowid. This is effectively SQLite's clustered index. The physical storage order of rows follows rowid sequence.

... continue reading