UUID v4 vs UUID v7: Database Indexing, B-Tree Fragmentation, and Storage Strategies

For decades, relational databases relied on auto-incrementing integer primary keys. As distributed architectures and microservices proliferated, central sequence coordination became a scaling bottleneck, driving widespread adoption of Universally Unique Identifiers (UUIDs). However, the standard UUID v4 introduces catastrophic write degradation as datasets scale into millions of rows. The new UUID v7 standard (RFC 9562) solves this fundamental limitation.

The Problem with Random UUID v4

UUID v4 consists of 122 bits of pseudo-random entropy and 6 fixed version/variant bits. Because each generated identifier is completely non-sequential, new rows must be inserted at statistically random positions within a database's primary key index:

1. B-Tree Page Splits and Random I/O

Relational databases (including PostgreSQL, MySQL InnoDB, and SQLite) store primary keys using B-Tree (or B+Tree) indices. In a B-Tree, keys are ordered sequentially across fixed-size disk pages (typically 8KB in PostgreSQL and 16KB in MySQL).

When inserting sequential keys (such as auto-incrementing integers), new writes consistently append to the rightmost leaf page. Once that page fills, a clean split occurs, leaving the preceding page 100% full and cached in memory.

With random UUID v4, every insert targets an arbitrary page across the entire index tree:

• If the target leaf page is already full, the engine must perform a mid-tree page split, moving half the keys to a newly allocated page.
• This causes 50% internal page fragmentation, cutting effective page storage in half and inflating total index size on disk by roughly 2×.
• Once the working index outgrows the database buffer pool (RAM), every single row insert requires a random physical disk read to load the target page, followed by a random disk write to flush it. Write throughput drops by 80% to 95%.

Enter UUID v7 (RFC 9562)

Approved in 2024 as part of RFC 9562, UUID v7 introduces a standardized, time-ordered layout that combines millisecond-precision Unix timestamps with cryptographically strong pseudo-random bits:

Why UUID v7 Restores B-Tree Performance

Because the most significant 48 bits contain a monotonically increasing Unix timestamp, consecutive UUID v7 values sort in chronological order:

018f4a38-3482-7000-84a1-43281984210a  (Created at T0)
018f4a38-3482-7001-92b3-54910293481b  (Created at T0 + 1ms)
018f4a38-3490-7000-a112-984120938411  (Created at T0 + 15ms)

In B-Tree indices, inserts consistently land on the latest pages. Buffer pool hit ratios remain close to 99%+, page fragmentation is virtually eliminated, and write throughput scales linearly alongside auto-incrementing integers while retaining all distributed generation benefits.

Database Storage: String vs Binary Format

Regardless of whether you choose v4 or v7, how you store the 128-bit number in your schema is equally decisive:

PostgreSQL

PostgreSQL includes a native UUID data type. Internally, PostgreSQL stores the 128-bit value as a compact 16-byte structure, regardless of whether you input canonical hyphenated strings or compact hex strings:

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- v4 native
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

MySQL (InnoDB)

MySQL lacks a dedicated 16-byte UUID data type. Storing UUIDs as VARCHAR(36) wastes 36 bytes per row plus index overhead. The industry standard is BINARY(16):

CREATE TABLE orders (
  id BINARY(16) NOT NULL,
  total_cents INT NOT NULL,
  PRIMARY KEY (id)
);

-- Storing v7 in MySQL
INSERT INTO orders (id, total_cents)
VALUES (UUID_TO_BIN('018f4a38-3482-7000-84a1-43281984210a', 0), 4999);

-- Querying as canonical string
SELECT BIN_TO_UUID(id, 0) AS id, total_cents FROM orders;

Practical Migration Path

1. Dual Generation in Application Layer: Update your API services to generate UUID v7 for newly inserted records. Because both v4 and v7 occupy identical 128-bit space and follow RFC 4122 canonical parsing, your database schemas require zero DDL alterations.
2. Historical Records Coexist: Old v4 keys remain completely valid and queryable. New records will cluster at the end of the B-Tree index, restoring cache performance immediately for all fresh inserts.
3. Remove Hyphens for Wire Transport: For microservice RPCs or URL slugs, stripping hyphens yields a clean 32-character hexadecimal string without altering the underlying byte representation.