UUIDv4 vs UUIDv7 as a Primary Key: What Actually Changes

The advice "do not use UUIDs as primary keys" was good advice about a specific UUID version, and it got repeated long enough to become a rule about all of them. The problem was never the 128 bits. It was that UUIDv4 is random, and random insert positions are expensive in a B-tree.

UUIDv7, standardised in RFC 9562 in May 2024, keeps every property that made UUIDs attractive and removes the one that made them slow.

Key takeaways#

• UUIDv4 carries 122 random bits, so consecutive inserts land in unrelated index pages.
• UUIDv7 puts a 48-bit millisecond Unix timestamp in the high bits, so new rows sort to the end of the index.
• The cost of randomness is worst in clustered indexes: InnoDB and SQL Server store the whole row in primary-key order.
• PostgreSQL is affected less, because its tables are heaps, but the index still fragments.
• UUIDv7 leaks creation time. That is a real trade-off, not a footnote.

Why do random UUIDs slow inserts down?#

Because a B-tree stores keys in sorted order, and where a new key sorts decides how much work the insert costs.

With a sequential key, every insert lands at the right edge of the tree. That page is already in memory, it fills up, it splits once, and you move on. With UUIDv4 each new key sorts to an unpredictable position, so the database has to read a different page for nearly every insert. Once the index outgrows the buffer pool, most of those reads hit disk.

The knock-on effect is page splits. Sequential inserts fill a page to capacity before splitting. Random inserts split pages that are half full, all over the tree, so the index ends up larger and sparser than the data warrants. More pages means less of the index fits in memory, which causes more disk reads, which is the same problem feeding itself.

Which databases does this actually hurt?#

It depends on whether the primary key controls physical row storage.

Engine	Primary key is	Impact of random UUID
MySQL / InnoDB	Clustered — the row lives in the index	Severe: the table itself fragments
SQL Server	Clustered by default	Severe, same reason
PostgreSQL	A separate B-tree over a heap	Moderate: the index fragments, rows do not
SQLite	Clustered unless WITHOUT ROWID is off	Moderate to severe

InnoDB is the worst case. Its primary key is clustered, so the leaf pages of that index are the table: a random key does not merely scatter an index, it scatters the rows. Every secondary index also stores the full primary key as its row pointer, so 16 bytes makes each one larger than an 8-byte BIGINT would.

PostgreSQL is the common source of "we use these and it is fine" reports, and those reports are usually honest. Heap storage means row placement is independent of the key, so damage is confined to the index. Confined is not zero, but it is survivable at sizes where InnoDB would be struggling.

What does UUIDv7 change?#

UUIDv7 replaces the leading random bits with a timestamp. The first 48 bits are Unix time in milliseconds, followed by version and variant bits and then randomness.

That single change makes the values monotonically increasing. Consecutive inserts sort next to each other, land in the same right-edge page, and behave like an auto-increment key as far as the B-tree is concerned. You keep the properties you chose a UUID for:

• Client-side generation. An application, a mobile client or a queue consumer can mint an id without a round trip to the database.
• Merge safety. Ids created in different services or shards do not collide.
• No enumeration. The random tail means an id does not reveal how many rows exist, which sequential integers do.

PostgreSQL 18, released in September 2025, added a native uuidv7() function, along with uuidv4() as an alias for gen_random_uuid() and support for UUIDv7 in uuid_extract_timestamp(). Before 18 you need an extension or client-side generation; the standard is the same either way.

What is the catch?#

UUIDv7 embeds a creation timestamp in the identifier, and anyone holding the id can read it. uuid_extract_timestamp() exists precisely because the value is recoverable.

That matters more than it first appears. If ids appear in URLs, an outside observer can tell when an account was created, infer signup rate by comparing two ids, or order records by age without access to any timestamp column you thought was private. For an invoice id this is harmless. For a medical record or a private message it may not be.

The honest framing: UUIDv4 leaks nothing and costs performance. UUIDv7 leaks creation time and costs almost nothing. Pick the one whose cost you can afford.

So which should I use?#

Use UUIDv7 for the primary key of any table that takes sustained inserts, particularly on InnoDB or SQL Server, when ids need to be generated outside the database and a visible creation time is acceptable.

Use UUIDv4 when the identifier is externally visible and creation time is genuinely sensitive, or when the table is small enough that the whole index stays in memory and the difference is unmeasurable.

Use a BIGINT sequence when none of the UUID properties are needed. This is the option people skip past. If ids are only ever created by one database, a sequence is smaller, faster, and simpler, and half the reason UUIDs get chosen is habit rather than a distribution requirement.

A reasonable hybrid, common in practice: a BIGINT primary key for internal joins and a separate indexed public identifier. You get compact internal keys and non-enumerable public ones, at the price of one extra index.

Migrating an existing table#

Changing a primary key type on a large table is not a routine migration. A workable sequence:

1. Add a nullable column and backfill it in batches, avoiding one long-running transaction.
2. Populate new rows on write with uuidv7() or a client-side equivalent.
3. Add a unique index once the backfill completes.
4. Move foreign keys across, one relationship at a time.
5. Promote the column and drop the old key last.

Step 4 is the one people underestimate. Every foreign key referencing the old primary key has to be widened and repointed, and until they are all done both keys must stay valid.

If you just need values to test with, the UUID generator produces them in the browser, and the hash generator is the tool for the adjacent case where what you actually want is a deterministic id derived from content rather than a random one.

The short version#

The old rule against UUID primary keys was really a rule against random primary keys, and it was correct. UUIDv7 keeps the useful properties and drops the randomness where it hurt. On InnoDB the switch is close to free and worth making; on PostgreSQL it is a smaller win; and in both cases the real question is whether you needed a UUID rather than a sequence in the first place. If the id is doubling as a secret, which generator produced it matters more than which version it is.

Sources: RFC 9562, PostgreSQL 18 UUIDv7 support