跪拜 Guibai
← All articles
Database

uuidv7 Stops Random UUID Primary Keys From Killing InnoDB Write Performance

By 唐悦玮 ·
Read original on juejin.cn ↗ Google Translate ↗ Alt translation

Switching from v4 to v7 can halve insertion latency in production, as Shopify reported, making it a low-cost win for systems that already use UUIDs. The catch is that v7 encodes creation time, so public-facing IDs need additional encoding to avoid leaking business volume.

Summary

Random UUID v4 primary keys cause InnoDB to scatter new rows across its clustered B+ tree, forcing page splits that drop fill rate to 50-60%, churn the buffer pool, and inflate WAL. The slowdowns only become visible after the table outgrows memory; before that, the buffer pool masks the cost. UUID v7 from RFC 9562 embeds a millisecond timestamp in the high bits, turning random placement into near-sequential appends, with write throughput reaching 85-89% of BIGINT auto-increment versus v4's 36% on MySQL.

PostgreSQL 18 ships native uuidv7() and a timestamp extraction function. MySQL 8.4 has no native v7, so you generate it in the application and store as BINARY(16) to avoid the char overhead. The trade-offs: v7 exposes the creation time, same-millisecond values aren't strictly ordered, and migrating a v4 table with foreign keys means rebuilding the clustered index and all FKs, a multi-hour lock operation. BIGINT auto-increment is still leaner and faster for single-database cases without external ID exposure.

Takeaways
— UUID v4 is 122 random bits, causing InnoDB to split pages at random locations across the clustered index.
— Page splits drop fill rate to 50-60% for random writes, vs 90%+ for sequential writes.
— WAL amplification from full-page writes can push WAL volume above 20GB when pages split.
— UUID v7 (RFC 9562) uses 48-bit Unix milliseconds in the high bits, making values roughly time-ordered.
— v7 write throughput is 85-89% of BIGINT auto-increment; v4 drops to about 36% on MySQL.
— Shopify halved insertion latency by switching from random to ordered UUIDs.
— PostgreSQL 18 provides native uuidv7() and uuid_extract_timestamp().
— MySQL 8.4 lacks native v7; generate in application and store as BINARY(16), not CHAR(36).
— Percona Server 8.4 ships a UUID_VX component with native v1-v7 support.
— v7 IDs expose creation time; public IDs need irreversible encoding.
— Same-millisecond v7s aren't strictly ordered due to trailing random bits.
— Migrating an existing v4 table with foreign keys rebuilds the clustered index and all FKs, locking for hours.
Conclusions

The three-month quiet period before slowdowns appears is a memory threshold: once the hot portion of the B+ tree no longer fits in the buffer pool, page splits and cold-page evictions become visible. That makes the problem easy to miss until it's severe.

v7 is not a free upgrade; the timestamp prefix is a privacy leak. Systems that expose IDs in URLs or APIs must either stick with v4 or encode the ID before public release.

The migration cost described for existing tables is a strong argument for choosing the right key format at schema creation, because retrofitting a clustered index with foreign keys is a multi-hour lock operation.

Concepts & terms
Clustered index
In InnoDB, the primary key index is a clustered index where data rows are physically stored in the leaf nodes of a B+ tree ordered by the primary key. Insert order determines physical placement, so random keys force page reorganizations.
Page split
When a new row needs to be inserted into a full 16KB page of the B+ tree, InnoDB splits the page into two and moves half the rows, causing extra IO and leaving the page underfilled.
WAL amplification
With PostgreSQL's full-page writes, each page split causes the entire old page image to be written to the WAL, inflating its size significantly.
Source: juejin.cn ↗ Google Translate ↗ Backup ↗