UUIDs as primary keys: index fragmentation, storage, and why UUIDv7 helps

A UUID primary key buys you one real thing: the ability to generate a globally unique id on the client, before the row ever reaches the database. Everything else about it is a tradeoff, and the most expensive tradeoff is invisible until your table is large.

The locality problem

Relational databases store rows in a B-tree ordered by the primary key, and in some engines, notably InnoDB, the entire row lives inside that tree as the clustered index. Inserts go wherever the key sorts.

A UUIDv4 is random. Every insert lands at an unpredictable point in the tree, so the database constantly writes into the middle of pages rather than appending to the end. When a target page is full, it performs a page split: allocating a new page and moving half the rows across. The result is fragmentation, pages that are half empty, and a working set that no longer fits in memory because inserts touch pages scattered everywhere instead of a hot tail.

An auto increment integer, by contrast, always appends. Every insert hits the same rightmost page until it fills, then moves on. No splits, no fragmentation, excellent cache behaviour.

Storage cost

UUIDs are also simply bigger.

The 16 versus 8 bytes is only the start. The primary key is copied into every secondary index as the row pointer, so a wide key inflates every index on the table, not just the primary one. Storing a UUID as a 36 character string makes this dramatically worse and should never be done; store it as a 16 byte binary type.

How UUIDv7 and ULID fix locality

The random part is the cause, so the fix is to make the key sortable by time while keeping it unique. UUIDv7 puts a millisecond Unix timestamp in the leading bits, followed by random bits for uniqueness within that millisecond. ULID does the same thing with a different text encoding.

Because the high bits now increase with time, new inserts sort near each other at the tail of the tree, just like an auto increment key. You get append like insert behaviour, far fewer page splits, and a hot working set, while keeping the client side generation and global uniqueness that made you reach for UUIDs in the first place.

UUIDv4:  9b1deb4d-3b7d-4bad-9bdd-2b0d7b3dcb6d   fully random, scatters inserts
UUIDv7:  018f4e2a-1c40-7b3e-8a2f-6d9c1e5b7a90   time-ordered prefix, inserts cluster

The tradeoff you accept with v7

Time ordered ids leak information. Anyone holding two UUIDv7 values can tell which row was created first and roughly when, and a sequence of them can reveal creation rate. For internal keys this is usually fine. For anything exposed publicly where creation order or volume is sensitive, that leak may matter, and a random UUIDv4 or a separate opaque public id is the better choice.

A reasonable default for most systems: use UUIDv7 for internal primary keys to keep the database happy, and only reach for UUIDv4 where unpredictability genuinely matters more than insert performance.

Generate UUIDv4 and UUIDv7 values, in bulk if you need them, with the UUID Generator, and compare how the two look before you commit a schema to one.