Improving Primary Key Performance: Alternatives to UUID v4

In many applications, a primary key must be unique and hard to guess. Developers often choose UUID v4 for these benefits. However, there are trade-offs in performance.

Pros and Cons of Using UUID v4 as a Primary Key

UUID v4 gives global uniqueness and improves security because its random nature makes it hard to predict record order. Clients can even generate IDs without extra database calls. However, this randomness harms performance. The non-sequential order leads to poor index locality, increased disk I/O, and frequent page splits, especially during writes. In addition, the 128-bit size uses more storage than regular integers.

Why MySQL Suffers More: Index and Data Structure Differences

MySQL’s InnoDB engine uses a clustered B+Tree where table data is stored along with the primary key. With sequential keys, new records are added to the end of the tree, keeping data on the same pages and improving cache hit rates. UUID v4, being random, scatters inserts over many pages. This causes frequent page splits and increases disk I/O, which degrades performance. PostgreSQL, by contrast, stores data separately from the index, so it suffers less from this issue.

A Better Alternative: Binary Storage and Ordered ID Schemes

One solution is to store IDs in binary form (BINARY(16)) rather than as strings, which improves storage efficiency and reduces comparison overhead. Even better, using an ordered ID—such as a time-ordered UUID (v6 or v7) or alternatives like Snowflake IDs or ULIDs—can improve performance. Both Snowflake IDs and ULIDs include a timestamp that makes new records nearly sequential. Note that NanoIDs, by default, are generated randomly and do not embed a time component (see Why we chose NanoIDs for PlanetScale’s API for more details).

4. Conclusion

While UUID v4 offers many advantages in security and distributed systems, its randomness can degrade performance, especially in MySQL’s clustered index environment. Storing IDs in binary and adopting an ordered ID scheme—whether a time-ordered UUID (v6/v7) or an alternative like Snowflake IDs or ULIDs—can achieve near-sequential order, improving index efficiency and overall database performance in both MySQL and PostgreSQL.