UUIDv4 vs UUIDv7 vs ULID: Distributed Primary Keys, B-Tree Index Fragmentation & Monotonic Ordering

The Primary Key Dilemma in Distributed Architectures

In monolithic architectures, monotonically increasing 64-bit integer sequences (such as PostgreSQL's BIGSERIAL or MySQL's AUTO_INCREMENT) have traditionally served as default primary keys. However, in modern distributed microservices, multi-region database replicas, and client-side offline workflows, centralized auto-increment sequences introduce significant architectural friction:

• Single Point of Failure: Centralized ID generation creates database roundtrips and synchronization bottlenecks during distributed ingestion.
• Data Enumeration Security Risks: Sequential IDs reveal total customer counts and allow attackers to easily crawl resource endpoints.
• Multi-Master Replication Clashes: Merging records generated across disconnected nodes requires distributed coordination or range reservation schemes.

The Hidden Cost of Random UUIDv4: B-Tree Index Fragmentation

To eliminate synchronization, teams frequently adopt UUIDv4 (RFC 4122), which allocates 122 bits to pure pseudorandomness and 6 bits to version and variant metadata. While UUIDv4 guarantees a vanishingly small collision probability (less than one in a billion across billions of entries), its completely uniform random distribution creates severe performance degradation in relational databases utilizing B+ Tree clustered indexes.

Relational databases like PostgreSQL, MySQL InnoDB, and SQLite organize tables as clustered B+ Trees ordered strictly by primary key. When inserts arrive sequentially (monotonically), records are efficiently appended to the right-most leaf pages. However, because UUIDv4 values are completely disordered, consecutive writes are scattered randomly across the entire index space:

// Comparison of Index Locality:
// Monotonic (Append-Only):
Page 1: [1, 2, 3] -> Page 2: [4, 5, 6] -> Page 3: [7, 8, 9] (100% cache friendly)

// Random UUIDv4 (Random Insertion):
Insert 9f2c... -> Splitting Page 4 -> Moving 50% data -> Disk I/O & WAL Thrashing
Insert 1a0b... -> Splitting Page 1 -> Moving 50% data -> Disk I/O & WAL Thrashing

This disorder forces database storage engines to perform frequent B-Tree page splits. Instead of leaf pages remaining 90-100% full, average page density collapses to 50-60%, doubling disk space requirements, evicting active cache blocks from the database buffer pool, and causing severe write latency spikes.

Enter RFC 9562: The UUIDv7 Specification

Recognizing the operational costs of random identifiers, the IETF ratified RFC 9562 in 2024, introducing UUIDv7 as the official standard for time-ordered distributed identifiers. UUIDv7 combines a high-resolution millisecond Unix timestamp with cryptographically random bits:

• Bits 0-47 (48 bits): Unix timestamp in milliseconds since epoch (covers dates until the year 10889).
• Bits 48-51 (4 bits): Version flag (binary 0111 for version 7).
• Bits 52-63 (12 bits): Sub-millisecond sequence counter or high-entropy random data.
• Bits 64-65 (2 bits): Variant flag (binary 10).
• Bits 66-127 (62 bits): Pseudorandom entropy bits.

Because the most significant 48 bits encode time, UUIDv7 values sort naturally in chronological order when stored as binary or canonical 36-character hexadecimal strings. When inserted into a B+ Tree index, UUIDv7 yields 98%+ cache locality, virtually eliminating page splits and matching the insert throughput of auto-incrementing integers while retaining decentralized generation.

UUIDv7 vs ULID: Architectural Comparison

Prior to RFC 9562, developers widely adopted ULID (Universally Unique Lexicographically Sortable Identifier). Both formats provide 128-bit time-ordered identifiers, but feature distinct encoding and ecosystem trade-offs:

Feature	UUIDv7	ULID
Standardization	Official IETF RFC 9562	De facto community spec
Canonical Format	36-character hyphenated hex	26-character Crockford Base32
Native DB Support	Directly compatible with native UUID columns	Stored as BYTEA/BINARY(16) or VARCHAR(26)
URL Safety	Requires 36 chars (hyphens)	Case-insensitive, no vowels (avoids profanity)

Production Recommendations

For modern application development, follow these architectural guidelines:

1. Default to UUIDv7 for relational database primary keys in PostgreSQL and MySQL to ensure optimal index performance, compatibility with native UUID types, and zero generation coordination.
2. Use ULID when identifier strings must be compact and easily read or typed by human operators in customer-facing URLs, support tickets, and invoices.
3. Reserve UUIDv4 exclusively for ephemeral secrets, session tokens, CSRF protection, and non-indexed cryptographic handles where temporal correlation is explicitly undesirable.