Why UUID Primary Keys Slow Down Your Database — B-Tree Page Splits and the v7/ULID Fix
"Use a UUID for your primary key, it's safer" is true, but there's a nuance people often miss: the specific UUID version you pick changes the outcome completely. In particular, using the most common variant — UUID v4 (fully random) — as the primary key of a relational database like MySQL InnoDB or PostgreSQL frequently causes insert performance to degrade noticeably as the table grows. This guide breaks down why at the level of index internals, why the recently standardized UUID v7 and ULID emerged as alternatives, and when you'd still want plain random UUIDs anyway.
1. The clustered index and the assumption of "stacking in sorted order"
InnoDB uses the primary key as a clustered index. That means the actual row data is physically stored in B-tree leaf pages in primary-key order. When you use an auto-increment integer as the primary key, every new row's value is always larger than everything stored so far, so it always gets appended to a single page at the far right end of the index (the most recent page). That page was just written to, so it's very likely already sitting in the buffer pool (memory cache), and the insert completes quickly with no disk access. Sequential keys perform well on inserts precisely because they "always touch the same end."
2. How random UUIDs break locality — page splits and cache misses
UUID v4's 122 bits are statistically fully random. That means there's no way to predict where a newly inserted value will land in the sort order of already-stored values. As a result, inserts get scattered evenly across the entire key space of the index, and each one has to squeeze into the middle of an already-full leaf page. When a page is full, InnoDB performs a page split — splitting it in two to make room for the new page — and this process leaves pages roughly half-full on average (index fragmentation), meaning more pages and more disk space are needed to store the same amount of data. Worse, once the table grows past the buffer pool's size, a randomly located page is increasingly likely to not be in cache, so more and more inserts require reading a page in from disk. Sequential keys keep reusing a single "hot page," while random keys keep touching cold pages across the whole table, which structurally drives up I/O load. On top of that, secondary indexes internally store the primary key value alongside their own data, so a larger primary key (a UUID is 16 bytes, four times a typical integer key) also bloats every secondary index.
3. UUID v7 and ULID — the "sortable uniqueness" compromise
UUID v7, formally standardized by the IETF as RFC 9562 in 2024, puts a millisecond-precision Unix timestamp in the top 48 of its 128 bits and fills the remaining bits with random data (plus version/variant bits). Because the high-order bits increase over time, a newly generated UUID is generally sorted after previously generated ones. This creates insert locality almost identical to auto-increment, so new rows mostly append sequentially at the right end of the index, sharply cutting down page splits. ULID follows the same idea: it combines a 48-bit timestamp with 80 bits of randomness and encodes the result as a 26-character Base32 string (unlike UUID, though, it isn't an IETF standard — it's a separate open spec). Both solve the problem the same way: "keep guaranteeing uniqueness with randomness, but layer on the regularity of creation-time order to restore index locality."
4. So why still use UUID (v4) at all?
That said, a sequential integer primary key isn't always the right answer. In distributed environments — multiple servers, microservices, or database shards that each need to issue IDs simultaneously with no central sequence counter — relying on a single auto-increment column creates bottlenecks and collisions. UUIDs (v4 or v7 alike) have the fundamental advantage of producing effectively collision-free unique values anywhere, with no coordination required. They can also be finalized in application code before the row is ever inserted into the database, which is useful for complex transactions or event-sourcing architectures that need referential integrity ahead of time, and — unlike sequential integer IDs — the value itself doesn't leak business information like signup counts or daily order volume (though note that UUID v7 does expose its timestamp, so it doesn't fully hide creation-order information). Ultimately, the choice is a trade-off between "index performance lost to randomness" versus "coordination-free distributed ID issuance and no leaked information," and the current trend is converging on the middle ground: UUID v7 and ULID.
Frequently Asked Questions
Q. Why does using UUID v4 as a primary key slow things down?
A. UUID v4's 122 random bits mean every newly inserted value lands at an unpredictable position in the index's sort order. Since engines like InnoDB use the primary key as the clustered index, this random insertion keeps splitting already-full B-tree leaf pages (a page split), and it also raises the odds that the target page isn't already in the buffer pool, which drives up disk I/O.
Q. How does UUID v7 solve this problem?
A. UUID v7 puts a millisecond-precision Unix timestamp in the top 48 of its 128 bits and fills the rest with random data, so the value itself is roughly sorted by creation time. That means new rows mostly get appended sequentially at the rightmost end of the index (the most recent page), which greatly reduces page splits — similar to an auto-increment integer primary key.
Q. How is ULID different from UUID?
A. ULID is also a 128-bit identifier, structured as a 48-bit timestamp plus 80 bits of randomness, represented as a 26-character Base32-encoded string. Like UUID v7, it sorts chronologically, which solves the index insert-locality problem, and its defining trait is that string sort order matches creation-time order exactly. Unlike UUID, though, it isn't an IETF standard format — it's maintained as a separate open spec.
Q. So why do people still use UUID as a primary key at all?
A. In distributed environments where multiple servers, services, or shards must each generate unique IDs simultaneously without central coordination, you need something like UUID with a near-zero collision probability rather than an auto-increment column that relies on a single sequence counter. UUIDs can also be generated on the application side before the row is ever inserted into the database, and unlike sequential integer IDs, the value itself doesn't leak business information such as signup counts or daily order volume.
Q. How do I improve a table that already uses UUID v4?
A. Keeping the column type as-is only offers limited relief, so the fundamental fix is switching new inserts to UUID v7 or ULID generation going forward. If changing the schema right away isn't feasible, a common compromise is to keep a separate auto-increment integer column as the clustered index and demote the UUID to a regular unique-constrained column (a secondary index) to reduce insert load.