UUID v4 vs v7 for Database Keys: Why Random Ids Get Slow

What a random key does to a B-tree

Every mainstream database indexes a primary key with a B-tree, a structure that keeps keys sorted so it can find any one in a handful of page reads. Inserting a key means locating the page where it belongs in sort order and writing it there; when that page is full, it is split into two and the parent updated.

With a sequential key — an auto-increment integer, or anything that increases over time — every new row belongs at the far right of the tree. The same rightmost page fills and splits, the pages just before it are finished and never touched again, and the working set is a few hot pages that live comfortably in memory.

With a random key, every new row belongs somewhere different. At a hundred thousand rows the whole index still fits in RAM and nobody notices. At a hundred million rows it does not; a random insert now means reading a cold page from disk, splitting it, and writing two pages back, over and over, for every single row. Write throughput collapses, the index bloats because split pages average half-full, and the cache hit rate falls for reads too. MySQL's InnoDB and SQL Server, which cluster the table itself on the primary key, suffer most, because they are rearranging the rows and not just an index. PostgreSQL's heap tables are less affected but its B-tree indexes bloat the same way.

What v7 changes

A UUID v7, defined in RFC 9562 (May 2024), lays out its 128 bits as:

unix_ts_ms (48 bits) | ver (4) | rand_a (12) | var (2) | rand_b (62)

The first 48 bits are the Unix timestamp in milliseconds, which sorts ids by creation time to the millisecond. The rand_a field is either 12 more random bits or, in the better implementations, a counter that makes ids generated within the same millisecond monotonic. Everything after the variant bits is random — 62 bits, or 74 including rand_a, which is far more than enough to make collisions a non-issue.

The effect on the B-tree is exactly the sequential case above: new ids land at the right edge, the working set stays hot, pages fill completely before splitting. Benchmarks on large InnoDB tables commonly show insert throughput several times higher with v7 than v4, and the index noticeably smaller. You also get a creation timestamp for free, readable from the id without a query — Layerbit's UUID tool decodes it — which is handy in logs and support tickets.

The cost is that the timestamp is readable by anyone who sees the id. For a row id in a database that is usually irrelevant. For a token that gates access, it can matter, which is the one case where v4 remains the right choice.

The other versions, briefly

Do you need a UUID at all?

An auto-increment BIGINT is 8 bytes instead of 16, sorts perfectly, and is the fastest key any database offers. UUIDs justify their extra size in three situations. When ids are minted in several places — mobile clients working offline, sharded services, event producers — and must not collide without coordination. When ids are exposed in URLs or APIs and a sequential integer would let anyone enumerate every record or read your growth rate off the counter. And when data from separate systems will later be merged, and integer ids from two databases would clash.

If none of those apply, an integer key with a separate public UUID column is a reasonable compromise: fast joins internally, opaque ids externally. If they do apply, v7 gives you the coordination-free generation and the opacity while keeping most of the integer's index behaviour.

Storing them properly

The 36-character string form is for humans. In the database, store the 16 raw bytes: PostgreSQL's native uuid type, MySQL's BINARY(16), SQL Server's uniqueidentifier. A VARCHAR(36) key is more than twice the size, compares character by character, and defeats much of the benefit. Two database-specific traps: MySQL's UUID() function returns a v1, which does not sort, so use v7 generated in the application or UUID_TO_BIN(id, 1) to reorder it; and SQL Server sorts uniqueidentifier by byte groups in an unusual order, so a v7 is not automatically sequential there — NEWSEQUENTIALID() or a byte-swapped layout is needed.

Where the random bits come from

The uniqueness of a v4 or v7 rests entirely on the randomness of its bits. JavaScript's Math.random(), Java's java.util.Random and similar general-purpose generators are predictable: an attacker who sees a few outputs can compute the next ones, which turns an "unguessable" invitation link into a guessable one. Use crypto.randomUUID() or crypto.getRandomValues() in the browser and Node, uuid.uuid4() in Python (which uses os.urandom), UUID.randomUUID() in Java (which uses SecureRandom). The same applies to the random part of a v7: the timestamp makes it sortable, but the random bits are still what makes it unique and unguessable within a millisecond.

Choose by what the id has to hide

A primary key has to be unique and cheap to index; it does not have to be secret, and it usually does not matter that its creation time is readable. That describes v7. A capability token — a password-reset link, a share URL — has to be unguessable, and its creation time is one fewer thing to leak. That describes v4. Most schemas have far more of the first kind than the second, which is why v7 is the right default for new work and why the random-key slowdown, once you have seen it explained, is an easy problem to avoid.

Frequently asked questions