The effects of using UUIDv4 as primary keys

For a while I’ve seeing posts claiming that UUIDv4 primary keys come with some inherent issues and tradeoffs that, generally, are not taken into consideration when designing database models.

Prior to that my only knowledge was that:

• UUIDv4 take twice the space as bigints
• UUIDv4 are random and help avoiding enumeration attacks

With this in mind, I made the call to use UUIDv4 as primary keys when they are exposed to end users in multiple projects. The price paid for the extra space seems low compared with the benefit of the extra simplicity that allows you to expose the primary key directly to the end users.

Yesterday I came across this essay from Andrew Atkinson who covers in great detail why, in most cases, you should avoid UUIDv4 primary keys.

Turns out that my analysis was extremely naive and incomplete. I really recommend you go read Andrew’s essay to see the full picture. But here’s my own summary:

1. UUIDs take a lot of space. This adds over time when you have a growing number of tables, rows and indexes.
2. UUIDv4 are random so indexes require frequent rebalancing and result in low-density pages.
3. Due to the previous points, index lookups must scan more pages and thus require 40% more I/O operations. Both during insertion and lookup.

His recommendation is to stick to sequence ids if possible or use UUIDv7 if you really need the extra capabilities offered by UUIDs.

Andrew is not the only one explaining this issues. Planetscale also has a very detailed essay about the problems with using UUIDv4 as primary keys in MySQL. They use NanoIDs themselves.

In the same vein, Shopify claim that switching from UUIDv4 to ULIDs resulted in a 50% decrease of INSERT duration.