Random UUID primary keys in InnoDB: what actually hurts

Switching an AUTO_INCREMENT key to a UUID is common, and on a big MySQL table it can turn a fast insert path into a slow one. The reason is the way InnoDB lays out data.

The clustered index

In InnoDB, the table itself is the primary key index: rows are stored in primary key order in a B-tree. Two consequences follow.

1. Inserts with increasing keys append to the end of the tree, so pages fill up neatly and the working set is the last few pages. Inserts with random keys land anywhere. Each insert may hit a page that is not in memory and is often full, and the full page has to split, which leaves two half-empty pages. On a large table the result is more disk reads, more writes and a table that is bigger than it needs to be.
2. Every secondary index entry contains the primary key. A 16-byte key, or worse a 36-character string, is copied into each index. A table with five secondary indexes stores the key six times, and each copy takes room in memory.

Reduce the cost

• Store the UUID as BINARY(16), not CHAR(36). That is 16 bytes instead of 36 (more with a multibyte character set). MySQL 8 has UUID_TO_BIN() and BIN_TO_UUID() for the conversion.
• Use a time-ordered UUID. UUIDv7, standardised in RFC 9562, puts a millisecond timestamp in the high bits, so new keys are mostly increasing and inserts append like an auto-increment key while the ids remain globally unique and unguessable enough for most uses. For version 1 UUIDs, UUID_TO_BIN(uuid, 1) swaps the time fields so that they sort by time.
• Keep a compact internal key. An AUTO_INCREMENT bigint as the primary key, with a separate unique UUID column for external use, gives you a small clustered key and a stable public identifier. The cost is one extra unique index.

What about PostgreSQL

PostgreSQL heap tables are not clustered on the primary key, so the table does not split in this way, but the B-tree index on a random UUID still receives inserts in random places and suffers similar page-split and cache effects, only less severely. PostgreSQL 18 adds a built-in function for generating UUIDv7 values, which removes the need for an extension or application-side generation.

Do not optimise it prematurely

For a table with thousands of rows, none of this matters. Random UUIDs solve real problems: keys generated by clients, merging data from several systems, ids that do not reveal order or volume. The effect shows up when the table and its indexes no longer fit in memory, and it is easy to measure with insert throughput and buffer pool hit rate. Decide when the table is heading there, and before it is in production, because changing a primary key later means rewriting the table.

ULID, a format that predates the UUIDv7 standard

Before UUIDv7 was formally standardised in RFC 9562, a community format called ULID (Universally Unique Lexicographically Sortable Identifier) solved the same problem: a 128-bit value with a 48-bit millisecond timestamp in the high bits followed by 80 bits of randomness, so IDs generated later sort after IDs generated earlier, exactly like UUIDv7. ULIDs are conventionally represented as a 26-character Crockford base32 string rather than the hyphenated hexadecimal of a UUID, which is shorter to display and avoids characters that are easy to confuse when read aloud or typed by hand, but the same BINARY(16) storage advice applies once you decode it to raw bytes for the column. Libraries exist for generating and converting ULIDs in most languages. Since UUIDv7 now covers the same need as a standardised, widely supported format, new systems generally have less reason to reach for ULID specifically, though plenty of existing systems already use it and there is no pressing reason to migrate away from a working one.