UUIDv7 vs UUIDv4: B-Tree Index Performance

This article examines why UUIDv7 significantly outperforms UUIDv4 as a primary key in relational database management systems. While UUIDv4 generates completely random 128-bit identifiers that degrade storage and retrieval mechanisms, UUIDv7 incorporates a high-precision timestamp prefix to produce time-ordered values. By switching to UUIDv7, web applications maintain the globally unique, distributed generation benefits of UUIDs while eliminating the severe B-tree index fragmentation, frequent page splits, and cache invalidation typically caused by random UUIDv4 keys.

The Structural Limitation of UUIDv4

UUIDv4 consists of 122 pseudo-random bits. Because these values have no sequential order, each newly generated key lands at an unpredictable location in a database index.

Relational databases like PostgreSQL, MySQL (InnoDB), and SQL Server rely on B-tree (or B+ tree) data structures to maintain primary key indexes. A B-tree keeps its keys in sorted order across fixed-size disk blocks called pages.

When you insert a random UUIDv4:

  • The database must insert the record into an arbitrary leaf page somewhere in the tree.
  • If the target leaf page is full, the engine must perform a page split, allocating a new page and moving roughly half of the data to make room.
  • As the dataset grows larger than the available RAM (the database buffer pool), nearly every insert requires loading a random page from disk into memory, causing heavy random I/O bottlenecks.

This behavior leads to heavily fragmented indexes, low page density (often hovering around 50% capacity), and degraded write throughput.

How UUIDv7 Works

Defined in RFC 9562, UUIDv7 solves the ordering problem by replacing the pseudo-random prefix with a time-based component.

A UUIDv7 layout consists of:

  1. Timestamp (48 bits): A Unix timestamp in milliseconds, providing natural chronological sorting.
  2. Version and Variant (6 bits): Standard UUID metadata.
  3. Sub-millisecond Precision / Counter / Randomness (74 bits): Guarantees uniqueness even when generating thousands of IDs within the same millisecond on distributed nodes.

Because the leading 48 bits represent time, UUIDv7 values are naturally monotonic (k-sortable). Newer records will always have lexicographically higher values than older records under normal clock conditions.

B-Tree Performance Benefits of UUIDv7

1. Elimination of Mid-Tree Page Splits

Because UUIDv7 keys are monotonically increasing, new entries are consistently appended to the right-most edge of the B-tree index. Instead of splitting existing leaf pages throughout the tree, the engine fills leaf pages to capacity before allocating a new one at the end. This keeps leaf page density close to 90–100%, significantly reducing storage overhead.

2. Optimized Buffer Pool and Cache Locality

B-tree insertions with UUIDv7 exhibit high temporal locality. The database only needs to keep the most recent index leaf pages in the memory buffer pool to handle new writes. With UUIDv4, every insert targets a random page, requiring the database to cycle pages in and out of RAM constantly. UUIDv7 allows the working set for writes to stay compact and resident in memory.

3. Reduced Write Amplification and Disk I/O

When page splits occur frequently with UUIDv4, the database writes excessive amounts of data to the transaction log (Write-Ahead Log / WAL) and marks multiple pages as dirty. UUIDv7 dramatically cuts write amplification by confining page allocations and modifications to a predictable, linear path.

4. Faster Range Queries and Lookups

Because chronological order is embedded in the key, queries filtering or sorting by creation time can often leverage the primary key index directly, eliminating the need for secondary indexes on created_at columns in certain write-heavy tables.

Conclusion for Web Development

For modern web applications requiring distributed ID generation without central coordination, UUIDv4 introduces severe scalability limits in relational databases once data exceeds memory limits. UUIDv7 provides the exact same benefits—decentralized generation, collision resistance, and non-enumerable obscurity—while behaving like an auto-incrementing integer at the storage layer. Adopting UUIDv7 allows relational databases to maintain predictable write throughput, compact index sizes, and optimal cache utilization.