The performance cost of random UUID primary keys in SQLite and clustered indexes
By
emschwartz
24d ago· 4 min readenInsight
Summary
This article examines the performance drawbacks of using random UUIDs (specifically UUID4) as primary keys in SQLite databases. It explains how the unordered nature of random UUIDs causes excessive paging and B-tree rebalancing in clustered indexes, leading to significant performance costs. The author aims to help readers develop a visceral understanding of these costs, noting that the problem extends beyond SQLite to other databases using clustered indexes.
Source
Hacker NewsThe performance cost of random UUID primary keys in SQLite and clustered indexesandersmurphy.comKey quotes
· 3 pulledOne 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.
A blog mostly about Clojure programming
Comments
Sign in to join the conversation.
No comments yet. Be the first.