UUID v4 vs. UUID v7: Collision Math, Database Indexing & Performance

Distributed identity: Balancing collision-free randomness with database storage efficiency.

For decades, relational database design followed a simple convention: primary keys were sequential integers (AUTO_INCREMENT or BIGSERIAL). But as software shifted toward distributed microservices, multi-region deployments, and client-heavy single-page applications, sequential IDs broke down. Exposing sequential IDs creates critical security vulnerabilities (allowing attackers to enumerate records by guessing /users/412), while generating them requires centralized database locks that create severe bottlenecks during high-throughput ingestion.

The standard industry solution was UUID v4—a 128-bit identifier generated from pure randomness. Yet, while UUID v4 solved central coordination and security enumeration, it introduced a catastrophic database performance issue: B-Tree index fragmentation.

In May 2024, the IETF published RFC 9562 (formally obsoleting RFC 4122), introducing UUID v7 to resolve this trade-off once and for all.

1. UUID v4: The Mathematics of Pure Randomness

A Version 4 Universally Unique Identifier is constructed almost entirely of pseudo-random or cryptographically secure random bits.

xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx

• Total Length: 128 bits (16 bytes, represented as 32 hex characters + 4 hyphens)
• Version Bits: 4 bits fixed to 0100 (representing version 4)
• Variant Bits: 2 bits fixed to 10 (RFC variant)
• Entropy Bits: 122 bits of pure randomness

Collision Probability & The Birthday Problem

With 122 bits of unconstrained entropy, the total number of possible UUID v4 combinations is:

2¹²² ≈ 5,316,911,983,139,663,491,615,158,242,970,000,000 (~5.3 × 10³⁶)

Due to the Birthday Paradox, the probability of a collision does not require generating all possible values. However, the scale of 122-bit entropy is staggering:

• To have a one-in-a-billion (0.0000001%) chance of a single collision, an application must generate approximately 103 trillion UUIDs.
• If you generated 1 billion UUIDs every single second for approximately 85 years, the probability of generating a single duplicate would still be less than 50%.

To generate cryptographically unbiased, high-entropy RFC 4122 identifiers instantly on your local device, use the Urban Mixo UUID Generator.

2. The Silent Database Killer: B-Tree Index Fragmentation

While UUID v4 is cryptographically sound, using it as a primary key inside clustered B-Tree indexes (such as PostgreSQL's default indexes or MySQL's InnoDB storage engine) severely degrades performance at scale.

How B-Trees Store Records

Relational database engines store index records inside fixed-size disk blocks called pages (typically 8KB in PostgreSQL and 16KB in MySQL InnoDB).

• When using sequential IDs (like auto-increment integers), records arrive in monotonically increasing order. New writes append neatly to the very end of the right-most index page. When a page fills to 100%, the engine smoothly opens a new page on disk.
• With UUID v4, every key is randomly distributed. An incoming insert might belong on page 12, the next on page 84,102, and the third on page 3.

The Three Penalties of Random UUID v4 Primary Keys:

1. Costly Page Splits: When an insert lands on an 8KB index page that is already full, the database must split the page in half, move 50% of the rows to a brand new page, and re-balance the tree. This turns light write operations into heavy, multi-page disk I/O events.
2. Cache Thrashing: As your table exceeds available RAM (the database buffer pool), random inserts force the engine to constantly evict healthy pages from memory to fetch cold pages from disk, causing query latency to spike.
3. Index Bloat: Because frequent page splits leave pages only 50% to 70% full, the physical index file on disk ends up 30% to 50% larger than a clean sequential index.

3. Enter UUID v7: Time-Ordered Sequential Entropy

Published in RFC 9562, UUID v7 solves the B-Tree problem by combining a standardized timestamp with cryptographically secure random bytes.

|--- 48 bits (Timestamp) ---|-- 4 bits --|-- 12 bits --|-- 2 bits --|--- 62 bits ---|
| Unix Epoch Milliseconds | Ver (0111) | Sub-ms/Rand | Var (10) | Random Bytes |

Why UUID v7 Outperforms UUID v4 in Databases:

• 48-Bit Unix Timestamp Prefix: The first 6 bytes encode the current Unix Epoch timestamp in milliseconds. This guarantees that keys generated over time are naturally sequential.
• Append-Only Writes: Inserts naturally land at the trailing edge of the B-Tree index, completely eliminating premature page splits and preserving 95%+ page fill factors.
• 74 Bits of Random Entropy: Even with the time prefix, UUID v7 provides 74 bits of pure entropy (plus sub-millisecond sequence counters), making accidental collisions virtually impossible across multi-node server clusters.
• Decentralized Generation: Mobile apps, background workers, and distributed microservices can generate UUID v7 keys independently without querying the database for the next sequential integer.

4. Architectural Comparison: Choosing Your Identifier

Identifier Type	Total Size	Time-Ordered?	B-Tree Friendly?	Best Use Case
AUTO_INCREMENT	4 or 8 bytes	Yes	Maximum	Single-node internal lookup tables
UUID v4	16 bytes	No (Random)	Poor	API keys, password resets, public session IDs
UUID v7 (RFC 9562)	16 bytes	Yes (Ms precision)	Excellent	Modern distributed database primary keys
ULID	16 bytes (Crockford Base32)	Yes	Excellent	Alternative 26-char string formats

5. Production Implementation Rules

1. Store as Native 16-Byte Binary, Never String: Always store UUIDs in database columns using native types (UUID in PostgreSQL, BINARY(16) in MySQL). Storing UUIDs as formatted text strings (CHAR(36)) wastes more than double the memory, increases disk size, and severely degrades join performance.
2. Keep UUID v4 for Unlinkable Security Credentials: Because UUID v7 embeds an explicit timestamp, anyone inspecting a UUID v7 identifier can instantly extract the exact millisecond the record was created. For password reset tokens, unguessable voucher codes, or anonymous tracking cookies, always use UUID v4 or secure random strings from our Random Number Generator to avoid leaking temporal metadata.
3. Adopt UUID v7 for High-Write Ingestion: For audit logs, telemetry events, billing transactions, and e-commerce orders, standardizing on UUID v7 provides the speed of sequential writes alongside the distributed benefits of unique IDs.

---

Frequently Asked Questions

Are UUIDs guaranteed to be unique across multiple databases?

While UUIDs are mathematically uncoordinated (no central authority verifies uniqueness), the probability of two independent systems generating identical UUID v4 or v7 keys is so infinitesimally low that engineers treat them as universally unique in production systems.

Can an attacker guess the next UUID v7?

No. While the 48-bit timestamp prefix moves forward predictably, the remaining 74 bits consist of cryptographically secure random values. An attacker cannot predict the complete 128-bit key even if they know the exact millisecond it was created.

Why did the IETF replace RFC 4122 with RFC 9562?

RFC 4122 was finalized in 2005. Its older time-based format (UUID v1) leaked physical network MAC addresses and used an awkward 100-nanosecond timestamp starting in the year 1582. RFC 9562 modernized the standard by deprecating insecure legacy formats and introducing clean, epoch-based formats (UUID v6, v7, and v8) built for modern cloud databases.