跪拜 Guibai
← Back to the summary

uuidv7 Stops Random UUID Primary Keys From Killing InnoDB Write Performance

uuidv7 is here: why you should stop using uuidv4 as your primary key

You use UUID.randomUUID() as your primary key. For the first three months, writes are normal. After that, inserts get slower and slower, yet the slow SQL log stays empty. The problem isn't SQL. The random primary key shatters InnoDB's clustered B+ tree with fragments, page splits, buffer pool misses, and WAL bloat.

You casually set the primary key to UUID.randomUUID() when creating the table. For three months, everything is calm: writes and queries behave. But three months later, inserts become slower, disk IO quietly climbs in monitoring, and you search the slow SQL log to find nothing: no statement times out, CPU isn't high. You might think: a primary key is just an ID, how can it affect write speed?

It sounds absurd, but the root lies in InnoDB's storage structure. Using a UUID as a primary key isn't impossible, but it and the database's clustered index have two different mindsets, and when put together, they slowly grind away performance.

The primary key isn't just "any non-repeating value"

First, let's clarify how InnoDB stores data. Its primary key index is a clustered index: data rows themselves are sorted by primary key order into the leaf nodes of a B+ tree with 16KB pages. A newly inserted row is placed at the position where the primary key "should be". If the primary key is an auto-increment BIGINT, new rows always land on the right edge of the tree; when a page fills, a new page is opened, almost pure append. But when a UUID is the primary key, where is that "should be" position?

UUID v4 is 122 bits of pure randomness, and each generated value has no order relative to others. The new row's "should be" position is some random corner of the tree; that page is likely already full, so the database has to split the page: split it into two pages, each moving half the data. That's called a page split.

image

What does a page split cost? First, the fill rate drops: pages written randomly average only 50% to 60% space usage, while sequential writes can reach over 90%. You need nearly twice the storage for the same data. Second, the buffer pool gets flushed by cold pages: new rows are scattered everywhere, so pages read in are evicted after one use, and the hit rate drops sharply. Third, WAL amplification: when PostgreSQL enables full-page writes, page splits produce full-page images that can push WAL volume above 20GB. After three months, when the table is larger, these symptoms all appear.

Wait, why did the first three months pass without issue? Because when the table is small, the entire B+ tree fits in memory, and the cost of page splits is masked by the buffer pool. Once the data volume crosses that threshold, the cost of splits and cold pages starts to visibly slow down writes.

Why uuidv7 can save you

The problem isn't "using UUID", it's "using a random UUID". UUID v7, defined in RFC 9562 (finalized May 2024), places a 48-bit Unix millisecond timestamp in the high bits and leaves the remaining 74 bits random. That means v7 values are roughly time-increasing: newly generated IDs are naturally larger than older ones.

What does v7 add over v4, being also a UUID? It just puts the timestamp in the high bits, making values increase roughly over time. Insert back into InnoDB's clustered index, and new v7 rows land back on the right edge of the tree, becoming append writes. Page splits nearly disappear, and fill rate returns to over 90%. In public benchmarks, v7 write throughput reaches 85% to 89% of BIGINT auto-increment; v4 drops to around 36% on MySQL. Shopify, in real production, replaced random UUID primary keys with ordered UUIDs and cut insertion latency in half.

The following is the chain of improvements brought by timestamp ordering: one time prefix turns "random scattering" into "sequential append":

image

How the two databases handle it

PostgreSQL 18 (released September 2025) directly includes uuidv7(), so you don't need to build it yourself, and it even provides a function to extract the timestamp back out:

-- PostgreSQL 18: native uuidv7(), also can reverse-extract creation time from ID
CREATE TABLE orders (
  id   uuid PRIMARY KEY DEFAULT uuidv7(),
  customer text NOT NULL
);

SELECT uuid_extract_timestamp(id) AS created_at
FROM orders
LIMIT 1;

Why can't MySQL do the same and simply provide a uuidv7() like PostgreSQL? Because MySQL 8.4 has no native implementation; it only has UUID() (v1, with MAC and time) and gen_random_uuid() (v4, pure random). To use v7, you generate it in the application layer and store it as BINARY(16), not as CHAR(36) text. Text takes twice the space and carries character set overhead:

-- MySQL 8.4: no native uuidv7(), generate in application layer, store as fixed-length binary
CREATE TABLE orders (
  id BINARY(16) PRIMARY KEY,
  customer VARCHAR(255) NOT NULL
);
-- Generate with a uuidv7 library in the application, convert to 16-byte binary and write.
-- If you can only use v1 for now, UUID_TO_BIN(UUID(), 1) swaps the time fields,
-- making v1 ordered by time when stored (essentially the ordered-UUID trick, not v7).

One more note: Percona Server 8.4 provides a UUID_VX component with native implementations of v1 through v7. If you're on the Percona branch, you can directly use uuid_v7().

The cost of switching to uuidv7 must be calculated

Don't switch blindly just because it's faster. The most obvious cost is exposing creation time: v7 puts milliseconds in the high bits of the ID, which is equivalent to laying out "when this record was created" directly. If your ID appears in public URLs or external APIs, users can guess your daily registration count or order volume by order. In such scenarios, you'd rather use v4, or encode the ID irreversibly before exposing it.

Multiple v7s generated in the same millisecond are not strictly ordered; they rely on trailing random bits. Under high concurrency, it's normal for a later-created value to occasionally sort earlier. Don't treat it as an exact time ordering.

Old tables must not be migrated blindly. Changing a table that already uses v4 as its primary key and is referenced by many foreign keys to v7 means rebuilding the entire clustered index, and also rebuilding all foreign keys. On a large table, this is a lock operation measured in hours. You need to pick a low peak, use online DDL or gray dual-write to migrate, and not ALTER on a whim.

You also need to compare the ledger with BIGINT: uuidv7 takes 16 bytes, twice the size of 8 bytes. For a single database, IDs not exposed externally, and no need to worry about traversability, BIGINT auto-increment is still the smallest and fastest. v7's value lies in scenarios that are distributed, multi-service, need merging, and don't want auto-increment.

Extend the lesson: these pitfalls are the same

Anyone who has stepped on the random primary key trap has likely also stepped on its relatives. Using MD5 or another random hash as a primary key has exactly the same consequences: random scatter, page splits. Some people, for convenience, store UUID text as VARCHAR(36), which takes twice the space of BINARY(16) and has character set conversion overhead. That's entirely self-inflicted.

Foreign keys also bloat. A primary key of 16 bytes means every referencing table's foreign key field is also 16 bytes, and indexes grow accordingly. In systems with many tables and dense foreign keys, primary key selection directly affects overall storage and cache hit rate. It's not just one table's issue. Have you calculated this chain before?

Recap

Using a UUID as a primary key isn't wrong; the mistake is using completely random v4. InnoDB's clustered index requires data to land in pages ordered by primary key. Random keys force many page splits, lower fill rate, flush the buffer pool, and amplify WAL, so writes slowly collapse. uuidv7 uses a timestamp prefix to turn "random" into "roughly ordered", giving append writes back to the database. PostgreSQL 18 supports it natively; MySQL 8.4 requires application-layer generation plus BINARY(16) storage. For selection: single database and not exposed externally, use BIGINT; distributed or needing merge, use v7; use v4 for public IDs where you want to hide time. Before migrating an old table, factor the migration cost into the ledger.

References:

Author: 唐悦玮 | An engineer starting from backend, using AI to extend to full stack.