Learn & Understand

The Hidden Cost of UUID Primary Keys, and Why UUIDv7 Exists

The companion calculator generates UUIDs, including the popular random version 4. UUIDs are excellent identifiers, letting independent systems mint unique IDs without coordination, which is why they are everywhere in distributed systems. But using a random UUID as a database primary key carries a real, often-unnoticed performance cost, and understanding that cost, and the newer UUID version designed to eliminate it, is important knowledge for anyone choosing how to identify their records. The best distributed identifier and the best database key are not always the same thing.

Why Randomness Hurts Databases

Databases store their primary key in a sorted structure, typically a B-tree index, and that structure performs best when new keys arrive in roughly increasing order, appending near the end. A random version 4 UUID defeats this entirely: each new key lands at an unpredictable position scattered throughout the index, forcing the database to insert into the middle of the structure repeatedly.

The scattered inserts cause page splits and fragment the index, and they thrash the cache because the relevant index pages are all over the place rather than concentrated at the hot end. Under high insert volume, this can meaningfully slow a database compared to sequential keys, a cost invisible until the table grows large.

The Storage Overhead

There is a second, simpler cost: size. A UUID is a large value compared to a compact integer, and because the primary key is duplicated into every secondary index that references it, a large key inflates storage and memory across the whole table's index structures. More bytes per key means fewer keys fit in each cache page, compounding the performance effect. For a small table this is negligible; for a large, heavily indexed one, the overhead of a bulky random key is real. This is why the choice between a UUID and a compact auto-incrementing integer is a genuine engineering trade-off, not a mere preference.

The Trade-off UUIDs Solve

None of this means UUIDs are wrong, they solve problems sequential integers cannot. They let multiple services or database shards generate IDs independently without a central counter, which is essential for horizontally scaled and distributed systems. They avoid the enumeration risk of sequential integers, where an attacker can guess adjacent record IDs by incrementing a number in a URL. The question is whether you can get these benefits without the database performance penalty of randomness, and for a long time the answer was an awkward compromise.

Why UUIDv7 Exists

The newer UUID version 7 is designed precisely to resolve this tension. It is time-ordered: it embeds a timestamp in the leading bits so that newly generated UUIDs are roughly sortable by creation time, while still including randomness for uniqueness. Because they increase over time, version 7 UUIDs insert near the end of a B-tree index like sequential keys, avoiding the fragmentation and cache-thrashing that plague random version 4 keys, yet they retain the distributed, coordination-free generation and non-enumerable quality that make UUIDs valuable. It is, in effect, the best of both worlds: a UUID that behaves well as a database key. This is why version 7 has become the recommended choice for UUID primary keys, superseding the older approaches.

Choosing an Identifier Wisely

Use the calculator to generate UUIDs, and choose the version with the database cost in mind: random version 4 is fine as a general identifier but can fragment indexes and slow inserts when used as a primary key at scale, its size adds storage overhead, and a compact integer avoids both but sacrifices distributed generation and invites enumeration. Where you want UUIDs as keys, prefer the time-ordered version 7, which inserts efficiently while keeping the benefits. The calculation produces the ID; understanding the key-performance trade-off is what makes it the right ID.