UUID v7 for Primary Keys: Fixing the Index Problem of Random UUID v4

If you have ever designed a database schema, you have probably faced the question: auto-increment integer or UUID? And if you chose UUID, a second question follows: which version? For years the default answer was UUID v4. Since RFC 9562 (published in 2024) standardized UUID v7, many teams are switching. This article explains why.

#What is a UUID?

A UUID (Universally Unique Identifier) is a 128-bit value, usually written as 32 hexadecimal characters in five groups:

f47ac10b-58cc-4372-a567-0e02b2c3d479

Its main promise is that anyone can generate one without coordinating with a central server and still be practically certain it is unique. That makes UUIDs great for distributed systems, offline clients, public IDs in URLs and merging data from multiple sources.

Two small parts of the string carry metadata:

• The first character of the third group is the version (4 in the example above).
• The first character of the fourth group encodes the variant (8, 9, a or b for standard RFC UUIDs).

#How UUID v4 works

UUID v4 is simple: 122 random bits plus 6 fixed bits for version and variant. In JavaScript you can create one with a single call:

crypto.randomUUID(); // "3b241101-e2bb-4255-8caf-4136c566a962"

The chance of a collision is astronomically small. You would need to generate about 2.7 × 10¹⁸ v4 UUIDs to have a 50% chance of a single duplicate.

#The hidden cost: random inserts

The problem with v4 is not uniqueness, it is order. Most relational databases (PostgreSQL, MySQL/InnoDB, SQL Server) store primary keys in a B-tree index. When keys increase over time, new rows are appended to the right-most page of the tree. That is cheap and cache-friendly.

Random v4 keys land anywhere in the index. Each insert may touch a different page, which leads to:

• Page splits and a fragmented index that is larger than necessary.
• Poor cache locality: the "hot" part of the index is the whole index.
• More disk I/O and write amplification as tables grow to millions of rows.

In MySQL/InnoDB the effect is stronger because the table itself is clustered by primary key, so the rows are physically reordered too.

#How UUID v7 works

UUID v7 keeps the same 128-bit format but changes the layout:

Because the timestamp comes first, UUIDs generated later sort after earlier ones. Inserts behave almost like an auto-increment column, while you keep all the benefits of UUIDs: no central coordination and no guessable sequence numbers.

018bcfe5-6800-7xxx-...   ← 2023-11-14 22:13:20 UTC
0192a4b7-1c40-7xxx-...   ← a later moment, sorts after

A nice side effect: you can read the creation time directly from the ID. Paste a v7 UUID into our UUID Generator & Inspector and it will show the embedded timestamp.

#v4 vs v7 at a glance

#When to choose which

Choose UUID v7 when:

• The UUID is a primary key or is indexed in a large table.
• You want rows naturally ordered by creation time (for pagination or debugging).
• You are starting a new project and your language or database has a v7 library.

Keep UUID v4 when:

• The ID must not reveal when something was created, for example password-reset tokens, invitation codes or anything security-sensitive. (For secrets, a long random token is often better than any UUID.)
• The table is small, or the UUID is just a secondary attribute that is rarely queried.
• You need maximum compatibility with older systems.

#What about ULID and NanoID?

• ULID solves the same problem as v7 (time-ordered, 128 bits) but uses a 26-character Crockford Base32 string such as 01HF7YAT004E7D9JPFDJKRFPSQ. It is shorter in URLs and is case-insensitive, but it is not a standard UUID, so native uuid column types will not accept it directly.
• NanoID is a short random ID (21 characters by default). It is great for public URLs, but like v4 it has no ordering.

#Storage tips

1. Store UUIDs as binary, not text. PostgreSQL has a native uuid type (16 bytes). In MySQL use BINARY(16) instead of CHAR(36): it is less than half the size and compares faster.
2. Generate IDs in the application when possible. It lets you know the ID before the insert, which simplifies creating related records in one transaction.
3. Do not rely on the ordering being exact across machines. Clocks drift, and within the same millisecond the order depends on the random part.

#Conclusion

UUID v4 is still perfectly fine for many use cases, but for database primary keys in growing tables, UUID v7 is the better default today. It keeps the uniqueness and decentralization of UUIDs while giving the database the sequential inserts it loves.

You can generate v4, v7, ULID and NanoID values in bulk, and decode any existing UUID, with our free UUID Generator. Everything runs locally in your browser.