computer-science
The perils of UUID primary keys in SQLite
Key takeaways
- It's common to use random UUIDs as a primary key in databases.
- While this post is about SQLite specifically, the problem of random UUIDs also extends to other databases that use clustered indexes.
- A clustered index determines the physical storage order of the rows in a table.
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.
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:
Article preview — originally published by Hacker News. Full story at the source.
Read full story on Hacker News →
More top stories
Aggregated and edited by the Scoop newsroom. We surface news from Hacker News alongside other reporting so you can compare coverage in one place.
Editorial policy · Corrections · About Scoop