UUID v4 vs v7: Which Should You Use as Your Database Primary Key?

UUID v4 has been the default choice for randomly generated primary keys for over a decade. It's universally supported, collision-resistant, and easy to generate. But UUID v7 — standardized in 2024 — introduces monotonic time ordering that significantly reduces index fragmentation in B-tree databases. If you're building a new system today, the choice matters more than most developers realize.

A brief history of UUID versions

The UUID standard (RFC 9562, formerly RFC 4122) defines multiple versions, each with a different generation strategy:

• v1: Timestamp + MAC address. Sortable by time, but exposes the generating machine's network address — a privacy concern that led to its decline.
• v3 / v5: Name-based, using MD5 (v3) or SHA-1 (v5) hashing. Deterministic — the same name always produces the same UUID. Used for namespace-based identifiers.
• v4: Pure random, except for four bits that identify the version. The most widely used version.
• v7: Timestamp prefix followed by random data. Standardized in 2024. Combines the sortability of v1 with the privacy of v4.

UUID v4: fully random

A UUID v4 looks like this:

122 of the 128 bits are randomly generated (6 bits are reserved for version and variant markers). The probability of two v4 UUIDs colliding is astronomically small — approximately 1 in 5.3 × 10^36 for any single pair. For all practical purposes, v4 UUIDs are unique.

The database performance problem with v4

The problem isn't collision — it's randomness itself. Relational databases store rows in B-tree index structures ordered by the primary key. When a new row is inserted, the database finds the correct position in the B-tree and inserts it there.

With sequential integer IDs (1, 2, 3...), new rows always go at the end of the index. The B-tree's rightmost leaf page grows, which is cache-friendly and requires minimal page splits.

With random v4 UUIDs, new rows are inserted at arbitrary positions throughout the B-tree. This causes:

• Index fragmentation: Pages fill up at random positions, requiring frequent page splits and leaving partially-filled pages throughout the tree.
• Cache misses: Each insert potentially touches a different index page. For large tables, those pages are rarely in the buffer pool, requiring disk reads.
• Write amplification: Page splits require rewriting large blocks of data.

On a small table, this is imperceptible. On a table with millions of rows receiving hundreds of inserts per second, the performance difference is measurable — sometimes dramatically so.

UUID v7: time-ordered randomness

UUID v7 uses the first 48 bits for a Unix millisecond timestamp, followed by random data:

Because the timestamp prefix is monotonically increasing, UUIDs generated later sort after UUIDs generated earlier. New rows are inserted at or near the end of the index — the same locality property as auto-incrementing integers.

The 80-bit random suffix ensures uniqueness even for UUIDs generated within the same millisecond. Multiple systems generating UUIDs simultaneously won't collide because the random portion provides ample collision resistance.

Security considerations

v7 UUIDs embed a millisecond-precision timestamp. This means:

• The approximate creation time of any record is recoverable from its ID alone
• The ordering of records is visible without a separate created_at column
• An attacker who sees multiple UUIDs can infer the rate of record creation

For most applications, this is a non-issue — you likely already expose creation timestamps through your API. But for use cases where the creation time or ordering of records is sensitive, v4's full randomness is preferable.

Using UUIDs as primary keys: the broader case

Whether you choose v4 or v7, UUID primary keys offer advantages over auto-incrementing integers in several scenarios:

Distributed systems

Auto-incrementing IDs require a central authority to assign the next number. In a distributed system with multiple application nodes, this creates a bottleneck — each node must coordinate with a central ID service. UUIDs can be generated independently on any node with no coordination required.

Security through opacity

Sequential integer IDs expose the total number of records in your system and enable trivial enumeration attacks. If your user IDs are 1, 2, 3... an attacker can iterate through every user. A UUID-based URL like /users/018f6db5-d840-7c0f-a40e-a8b9c5d3e1f2 cannot be guessed or enumerated.

Safe for client-side generation

In some architectures, the client generates the ID before sending the create request. This simplifies optimistic UI updates — the client knows the ID immediately without waiting for a server response. UUIDs are safe for this because the collision probability is negligible.

Which should you use?

For new applications starting today:

• Use v7 if you're working with a database that uses B-tree indexes (PostgreSQL, MySQL, SQLite, SQL Server) and you expect significant write volume. The reduced fragmentation is a real performance benefit.
• Use v4 if you need maximum library compatibility, if creation time must not be inferable from the ID, or if you're integrating with systems that expect standard v4 UUIDs.
• Use integers if you're building a simple application without distributed requirements and don't need the security properties of UUIDs.

Migrating from v4 to v7

If you have an existing system with v4 UUIDs and want to switch to v7 for new records:

1. New records use v7 — existing records keep their v4 UUIDs
2. Your application should handle both versions transparently (they're the same 128-bit format)
3. Sorting by primary key will put all old v4 records in a seemingly random order, while new v7 records sort correctly among themselves
4. If true sort order across all records matters, add a separate created_at timestamp column and sort by that