UUID v7 vs. UUID v4: Why Sequential Identifiers Prevent B-Tree Index Fragmentation

When designing a relational database schema, choosing the primary key type is one of the most consequential decisions an engineering team will make.

For years, developers faced an uncomfortable tradeoff:

• Sequential Auto-Increment Integers (BIGINT): Extremely fast for database indexes and compact on disk, but vulnerable to sequential enumeration attacks, leaky business metrics (e.g., competitors estimating your order volume by looking at invoice #1042), and incapable of offline generation in microservices without central coordination.
• Random UUIDs (UUIDv4): Secure against enumeration, universally unique across independent microservices, and easily generated client-side, but notoriously damaging to relational database write performance at scale.

With the ratification of UUIDv7 under RFC 9562, this compromise is no longer necessary.

To understand why UUIDv7 is rapidly replacing UUIDv4 across modern database architectures, we need to inspect how relational database storage engines actually write data to disk.

---

The Root Problem: How B-Tree Indexes Work

Most relational database management systems—including PostgreSQL, MySQL (InnoDB), and SQLite—organize primary key indexes using B+Trees.

A B+Tree is a self-balancing search tree where:

• Keys are maintained in strict ascending sorted order.
• Data is divided into fixed-size disk blocks called pages (typically 8KB in PostgreSQL and 16KB in MySQL InnoDB).
• Fast lookups ($O(\log N)$) rely on navigating internal branch pages to find the exact leaf page holding the requested row.

                  [ Root Page: Keys 100, 500 ]
                         /            \
       [ Branch Page: 100-499 ]    [ Branch Page: 500-999 ]
             /          \                /          \
       [ Leaf Page A ] [ Leaf Page B ] [ Leaf Page C ] [ Leaf Page D ]
       (Ordered Rows)  (Ordered Rows)  (Ordered Rows)  (Ordered Rows)

As long as new keys are added sequentially in increasing order, the database engine simply appends records to the end of the current right-most leaf page. When a page fills up, a brand new page is allocated, and subsequent inserts continue smoothly.

What Happens When You Insert Random UUIDv4 Keys?

Because UUIDv4 relies on pure pseudo-random entropy across all 122 bits, every newly generated key is scattered randomly throughout the keyspace:

Insert 1: 3f8a12bc-...  -> Lands in Leaf Page B
Insert 2: a7c901ee-...  -> Lands in Leaf Page D
Insert 3: 04b281f0-...  -> Lands in Leaf Page A
Insert 4: 9811c0de-...  -> Lands in Leaf Page C

When your dataset is small enough to fit completely inside the database’s in-memory cache (PostgreSQL’s shared_buffers or InnoDB’s buffer_pool), you may not notice an issue. But once the table and its primary key index exceed available RAM, three critical performance bottlenecks emerge:

1. Frequent Page Splits

When a newly generated UUIDv4 key needs to be inserted into a leaf page that is already 100% full, the database cannot simply append the record. It must perform a page split:

1. Allocate a new disk page.
2. Move approximately 50% of the existing rows from the full page to the new page.
3. Insert the new row into the appropriate half.
4. Update the parent branch node to reference the new page.

Page splits are expensive: they consume CPU cycles, generate extensive write-ahead log (WAL) records, and introduce write locks that stall concurrent queries.

2. Index Fragmentation & Bloat

Because page splits divide existing pages in half, many pages across your B-Tree index end up with low fill factors (often only 50% to 60% capacity).

As a result, the physical size of your index on disk balloons—often becoming 30% to 50% larger than an index populated with sequential keys. A larger index means fewer keys fit into RAM, accelerating cache eviction.

3. Buffer Pool Thrashing & Disk I/O

In a high-throughput system, every random UUIDv4 insert likely touches a different, arbitrary leaf page. If that page is not currently in memory, the database engine is forced to perform a random read from persistent storage (SSD or NVMe) to load the page into RAM, write the key, and mark the page as dirty.

This causes continuous churn in the buffer pool, displacing active cache pages and drastically lowering database throughput.

---

The Solution: How UUIDv7 Restores Sequential Performance

UUIDv7 eliminates B-Tree fragmentation by placing a 48-bit millisecond Unix timestamp at the very beginning of the 128-bit identifier:

|---- 48 bits (Timestamp) ----|-- 4b --|-- 12b (Entropy) --|-- 2b --|-- 62b (Entropy) --|
| Unix Epoch in Milliseconds  | Ver(7) | Random Counter    | Var(2) | Cryptographic Random |

Because time moves forward monotonically:

• Every newly generated UUIDv7 is naturally greater than any UUIDv7 created in preceding milliseconds.
• The database engine always writes new records to the right-most edge of the index.
• Leaf pages are filled to near 100% capacity before a clean, orderly page allocation occurs.
• The buffer pool stays hot because the most recent page remains resident in memory for all subsequent inserts during that time window.

---

Architectural Comparison: PostgreSQL vs. MySQL InnoDB

The performance impact of UUIDv7 depends heavily on how your database engine implements primary keys.

MySQL (InnoDB): The Clustered Index Hazard

In MySQL’s InnoDB storage engine, the table is the primary key. This structure is called a clustered index: the actual row columns (name, email, metadata) live directly inside the leaf nodes of the primary key’s B-Tree.

When you use random UUIDv4 as an InnoDB primary key:

• A page split doesn’t just shuffle lightweight index pointers—it physically moves entire multi-kilobyte data rows around on disk.
• Secondary indexes in InnoDB reference the primary key. When the primary key is bloated, every secondary index on that table becomes significantly larger and slower.

Switching from UUIDv4 to UUIDv7 in MySQL InnoDB typically results in a 3x to 5x boost in sustained write throughput for large tables.

PostgreSQL: Heap Storage & Index Bloat

PostgreSQL stores table data in a separate unstructured heap file, while primary keys are managed via secondary B-Tree index files.

While PostgreSQL avoids shuffling table rows during an index page split, random UUIDv4 keys still cause massive WAL (Write-Ahead Log) amplification, heavy index fragmentation, and high checkpoint overhead. UUIDv7 allows PostgreSQL’s B-Tree vacuuming routines to operate with minimal rebalancing overhead.

---

Benchmark Summary

Metric	Random UUIDv4	Sequential UUIDv7	Impact
Insert Pattern	Dispersed randomly	Monotonically increasing	Eliminates random disk seeks
B-Tree Page Fill Factor	~50% – 60%	~90% – 95%	Up to 40% less disk footprint
Page Splits per 100k Inserts	High (frequent)	Near Zero	Vastly reduced WAL write volume
Buffer Cache Hit Ratio	Drops as index grows	Consistently high	Keeps working set in RAM
Enumeration Attack Risk	Zero (impossible)	Low (sub-second randomness)	74 bits of entropy per timestamp
Distributed Generation	Client-side / Offline	Client-side / Offline	Zero network coordination needed

---

Are There Any Tradeoffs With UUIDv7?

While UUIDv7 is overwhelmingly superior for primary keys, two architectural considerations should be noted:

1. Timestamp Exposure: Because the first 48 bits encode the creation time down to the millisecond, someone inspecting the raw UUID can determine when the row was created. If creation timestamps are considered sensitive operational data in your threat model, consider obfuscating public API routes or pairing the internal UUIDv7 key with a separate public token.
2. Sub-millisecond Collisions in Tight Loops: If a single node generates thousands of UUIDv7 values in the same millisecond, the 74 remaining bits of entropy guarantee statistical uniqueness, but strict microsecond ordering within that single millisecond may require a monotonic counter (supported by the RFC 9562 spec).

---

Conclusion & Recommendation

If you are starting a new project in 2026, do not use UUIDv4 for database primary keys.

UUIDv7 provides the best of both worlds: the operational freedom and privacy of distributed, client-generated identifiers, coupled with the blazing-fast sequential write characteristics of traditional integers.

You can generate and inspect valid RFC 9562 UUIDv7 identifiers directly in your browser using our Online UUIDv7 Generator.