The perils of UUID primary keys in SQLiteThe perils of UUID primary keys in SQLite<br>05 Jun 2026<br>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.<br>While this post is about SQLite specifically, the problem of random UUIDs also extends to other databases that use clustered indexes.<br>What is a clustered index?<br>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:<br>There can be only one clustered index per table (rows can only be physically sorted one way).<br>A clustered index is the table. The leaf nodes contain the full row data<br>A non-clustered index, by contrast, stores only the indexed columns plus a pointer to the actual row data, which lives elsewhere.<br>Rowid<br>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.<br>Without rowid<br>SQLite also supports WITHOUT ROWID tables. These tables have no implicit rowid. Instead, the primary key you declare becomes the clustered index.<br>Baseline<br>Let's establish a performance baseline with regular rowid int primary key. We'll insert 10 million rows in batches of 1 million.<br>(d/q writer<br>["CREATE TABLE IF NOT EXISTS event(id INT PRIMARY KEY, data BLOB)"])

(dotimes [_ 100]<br>(time<br>(d/with-write-tx [db writer]<br>(dotimes [_ 1000000]<br>(d/q db ["INSERT INTO event (data) values (?)" data])))))<br>Results:<br>total rowstime in ms1000000012082000000011023000000011774000000011385000000010866000000011017000000010708000000010699000000010791000000001081Roughly a million inserts per second.<br>UUID4<br>Now lets try UUID4.<br>(d/q writer<br>["CREATE TABLE IF NOT EXISTS event(id BLOB PRIMARY KEY, data BLOB) WITHOUT ROWID"])

(dotimes [_ 10]<br>(time<br>(d/with-write-tx [db writer]<br>(dotimes [_ 1000000]<br>(d/q db ["INSERT INTO event (id, data) values (?, ?)"<br>(random-uuid4-bytes) data])))))<br>Results:<br>total rowstime in ms10000000264920000000564430000000713740000000835250000000935960000000981770000000104908000000011130900000001166810000000012586Oh no! What's happened here 10-12x slower?!<br>Profile<br>That's a big difference. But, lets not guess when we can profile.<br>Below is a normalised diffgraph. A diffgraph compares two profiling snapshots (in this case INT vs UUID4) and displays the differences in a flamegraph structure. Unlike a regular diffgraph that shows absolute changes, a normalised view adjusts the total number of samples between the two compared profiles to be the same. This means we can see the relative differences as a percentage. This matters because our profiles will run for different amounts of time.

The colour signifies the direction of the change: a blue frame means less time was spent in this function in the second profile (UUID4) compared to the first (INT); a red frame means more time was spent in the second profile. The colour intensity indicates the relative change in the number of samples for the frame itself (self time delta).<br>We can see from the diffgraph that we are spending a lot more time balancing the tree, reading and writing. This is because the unordered nature of UUID4 means they are ordered randomly which is forcing SQLite to constantly re-balance the Btree.<br>UUID7<br>We can theoretically fix this with UUID7 which is time ordered eliminating the ordering problem of UUID4. Let's see if this improves things.<br>(d/q writer<br>["CREATE TABLE IF NOT EXISTS event(id BLOB PRIMARY KEY, data BLOB) WITHOUT ROWID"])

(dotimes [_ 10]<br>(time<br>(d/with-write-tx [db writer]<br>(dotimes [_ 1000000]<br>(d/q db ["INSERT INTO event (id, data) values (?, ?)"<br>(random-uuid7-bytes) data])))))<br>Results:<br>total rowstime in ms1000000013722000000012803000000013654000000012505000000012566000000012707000000012468000000012579000000012451000000001258Back to a more reasonable number. Slightly slower than our baseline. UUID blob primary keys are 16 bytes vs int primary keys which are 8 bytes.<br>Conclusion<br>Hopefully, this post helps illustrate some of the pitfalls with UUID primary keys in SQLite and how to navigate them.<br>The full benchmark code can be found here.<br>If you enjoyed this post you might like this one 100000 TPS with SQLite<br>Further reading<br>Clustered Indexes<br>Clustered Indexes and the WITHOUT ROWID Optimization<br>clj-async-profiler<br>Exploring flamegraphs<br>Diffgraphs<br>Thanks to Everyone on the Datastar discord who read drafts of this and gave me feedback.