UUID v4 vs UUID v7: Why Database Performance Demands Time-Ordered Keys

The Primary Key Dilemma in Modern Systems

When designing database schemas for modern distributed systems, engineers face a classic trade-off:

1. Auto-Increment Integers (BIGINT):

• Fast B-Tree insertion performance.
• Vulnerable to Enumeration Attacks (e.g. GET /api/user/1042).
• Requires central database sequence locks (hard to shard).

1. Random UUID v4:

• Globally unique across distributed nodes without coordination.
• Impossible to enumerate or guess.
• Disastrous for database index performance at scale.

---

The Hidden Bottleneck of Random UUID v4

Relational databases (PostgreSQL, MySQL, SQLite) store primary keys using B-Tree (Balanced Tree) indexes.

When inserting rows with random UUID v4 keys:

1. Index Fragmentation: New keys are inserted into arbitrary pages across the entire index tree.
2. Frequent Page Splits: When index leaf pages fill up out-of-order, the database engine must split pages and rewrite disk storage blocks.
3. Cache Eviction: Random lookups force disk I/O reads because index nodes no longer fit into RAM (shared_buffers / innodb_buffer_pool).

---

Enter UUID v7: Time-Ordered Universally Unique Identifiers

Standardized under RFC 9562, UUID v7 replaces random entropy with a time-ordered layout:

Binary Structure of UUID v7 (128 bits):

• 48 bits: Unix Epoch Timestamp in milliseconds.
• 4 bits: Version indicator (0111 = Version 7).
• 12 bits: Sub-millisecond sequence counter.
• 2 bits: Variant indicator (10).
• 62 bits: Cryptographically secure random entropy.

Because the leading 48 bits represent a monotonically increasing timestamp, newly created UUID v7 keys are always naturally sorted.

---

Feature Comparison Matrix

---

Generating UUID v7 in Code

package main

import (
	"fmt"
	"github.com/google/uuid"
)

func main() {
	id, _ := uuid.NewV7()
	fmt.Printf("UUID v7: %s\n", id)
}

---

Generate UUID v4 & UUID v7 with Yurlie

Generate, validate, and inspect UUID v4 and time-ordered UUID v7 identifiers instantly using the free Yurlie UUID Generator.