Blaming UUID primary keys on their size misses the real problem. Yes, 128 bits is twice an eight-byte bigint, and yes, comparing them is slower — Percona measured integer comparison at about 28 times faster than comparing identical UUID strings. But neither of those is what makes a busy table with UUIDv4 keys fall off a performance cliff. The killer is where each insert lands, and that is entirely a property of how the key sorts.
A B-tree doesn't care what your key means — only where it sorts
Every B-tree index insert has exactly one legal destination: the leaf page that covers the new key's position in sort order. With a monotonically increasing key — an auto-increment integer, a timestamp — every insert lands on the rightmost leaf. The database appends, fills the page, allocates a fresh one to its right, and moves on. InnoDB's documentation states this precisely: when records are inserted in sequential order, "the resulting index pages are about 15/16 full," because InnoDB deliberately reserves 1/16 of each page for future updates.
A UUIDv4 is 122 bits of randomness. Each new key sorts to a uniformly random position across the entire keyspace, so each insert targets a random leaf page somewhere in the tree. Two things follow:
- Page splits everywhere. A random insert into a full page forces a split in the middle of the tree, not at its right edge. Per the same InnoDB docs, with random insert order "the pages are from 1/2 to 15/16 full." At the bad end of that range you're paying for nearly double the pages — double the disk, and, more importantly, double the cache footprint — to store the same rows. InnoDB will merge a page back only when its fill factor drops below
MERGE_THRESHOLD(50% by default), so the tree tends to settle into this half-empty state rather than recover from it.
- The working set becomes the whole index. With sequential keys, the set of pages an insert can touch is tiny: the right edge of the tree plus its ancestors. With random keys, every leaf page is a candidate on every insert. Your effective working set is the entire index.
In InnoDB the situation is aggravated by the storage layout: the table is the clustered primary key index, so randomly scattered key insertions scatter the row data itself, and every secondary index carries the primary key as its row pointer. PostgreSQL stores rows in a heap, so the table data isn't physically ordered by primary key — but the primary key's B-tree index suffers the same random-insertion scatter, and it's often the hottest structure in the database.
The cliff is the buffer pool boundary
Percona's write-up "UUIDs are Popular, but Bad for Performance" makes the failure mode concrete. The benchmark ran single-threaded inserts on MySQL 5.7 with a 128 MB buffer pool and storage capped at 500 IOPS — a deliberately small setup so the collapse happens quickly enough to graph.
While the index still fit in the buffer pool, insertion was CPU-bound and random key order barely mattered. Once the table grew past the buffer pool, the random-key workload became IO-bound almost immediately: the leaf page a new UUIDv4 needs is almost never cached, so nearly every insert costs two I/O operations — one read to fetch the page, one write to flush a dirty page and make room. At 500 IOPS, that arithmetic caps you around 250 inserts per second, no matter how fast your CPU is.
This is the part teams discover in production rather than in load tests. A staging database whose whole index fits in memory shows no difference between v4 and sequential keys. The gap only opens when the index outgrows RAM — which is to say, precisely when the table has become important.
Percona's mitigation at the time was telling: prefix the UUID with a value that stays constant for a while (their example hashed the year and week), so inserts within any given week cluster into one small region of the tree. That is a hand-rolled version of exactly what UUIDv7 later standardized.
UUIDv7 moves the timestamp to the front
RFC 9562 (published 2024, obsoleting RFC 4122) defines UUIDv7 with this bit layout:
| Bits | Field | Contents |
|---|---|---|
| 48 | unix_ts_ms | Unix timestamp, milliseconds |
| 4 | ver | version, 0111 |
| 12 | rand_a | random, or extra timestamp precision / counter |
| 2 | var | variant, 10 |
| 62 | rand_b | random, or counter for monotonicity |
Because the timestamp occupies the most significant bits, values generated near each other in time sort near each other as raw bytes — the RFC notes that v6 and v7 are designed so that "implementations that require sorting (e.g., database indexes) sort as opaque raw bytes without the need for parsing or introspection." Inserts land at (or near) the right edge of the tree again, and the append-mostly behavior of sequential keys returns while keys remain globally unique and generatable on any client without coordination. The RFC is unusually direct about why this matters: "Time-ordered monotonic UUIDs benefit from greater database-index locality," and the real-world difference versus random inserts "can be one order of magnitude or more."
Within a single millisecond, ordering isn't free — two IDs generated in the same tick would tie on the timestamp and fall back to random order. Section 6.2 of the RFC offers three ways to make generation monotonic: a dedicated counter after the timestamp (recommended length: at least 12 bits, at most 42), treating the random field as a randomly seeded counter that increments per ID, or filling rand_a with sub-millisecond timestamp precision (fractional milliseconds scaled by 4096).
Databases are adopting this natively. PostgreSQL 18 ships a uuidv7() function that uses the sub-millisecond-precision method — the docs describe the timestamp as "UNIX timestamp with millisecond precision + sub-millisecond timestamp + random" — alongside uuidv4() as a clearer-named alias for gen_random_uuid(). It even takes an optional shift interval to offset the embedded timestamp, which must stay within the 48-bit field's range: 1970 to roughly the year 10889.
What you give up
UUIDv7 is not a strict upgrade, and the RFC says so: "If UUIDs are required for use with any security operation within an application context in any shape or form, then UUIDv4 SHOULD be utilized." A v7 value embeds its creation time in cleartext and carries less entropy than v4's 122 random bits. If an identifier ever appears in a URL, an ID that is partially predictable and timestamps its own creation is a real liability — password-reset tokens and invite codes should never be v7.
Marc Brooker's "Fixing UUIDv7" adds a distributed-systems objection: because every generator in every region derives the same leading bits from the wall clock, v7 creates correlated behavior across failure boundaries — synchronized key ranges, synchronized cache patterns, and a higher chance of triggering the same edge case everywhere at once. His proposed middle ground XORs the timestamp with a keyed hash that rotates every 2^N milliseconds (he suggests N ≥ 12 for most installations), keeping locality within each window while decorrelating the global pattern. It's a useful reminder that "timestamp-prefixed" is a tuning knob, not a binary choice.
The practical decision table is short:
- Internal primary keys, high insert volume: UUIDv7 (or a DB-generated sequential key if you don't need client-side generation).
- Anything security-sensitive or exposed where creation time is itself sensitive: UUIDv4, and accept the index cost — or keep a v4 as the public identifier and a sequential key as the internal one.
- Already stuck with v4 keys at scale: the fix isn't a bigger instance forever; it's a migration, because the buffer-pool arithmetic only gets worse as the tree grows.
The one-line summary: your primary key's sort order decides which pages your inserts touch, and which pages your inserts touch decides whether your working set is one page or the whole tree. Choose the key for the tree, not just for uniqueness.