UUIDs and Database Indexing

Universally unique identifiers (UUIDs) are a popular choice for primary keys in distributed systems. They eliminate the need for central coordination, and every node can generate IDs independently without collision risk. But when you use random UUID version 4 as a database primary key, you pay a hidden cost: index fragmentation. Understanding why this happens — and how UUID version 7 addresses it — helps you make better schema decisions.

How B-Tree Indexes Work

Most relational databases (PostgreSQL, MySQL, SQL Server) use B-tree structures for primary key indexes. B-trees maintain sorted order: each new value is inserted at the correct leaf position.

When primary keys are sequential (like auto-incrementing integers), new values always go to the rightmost leaf. The tree grows in one direction, and pages fill efficiently:

Page 1: [1, 2, 3]  →  Page 2: [4, 5, 6]  →  Page 3: [7, 8, 9]  →  New

When primary keys are random (like UUID v4), new values land anywhere in the tree. This causes:

• Page splits: When a target page is full, the database splits it into two half-empty pages, doubling the storage needed for that part of the tree.
• Random I/O: Inserts touch disk pages scattered across the index, defeating sequential write patterns and filling the buffer cache with rarely-used pages.
• Fragmentation: Over time, the index becomes physically disordered, slowing range scans and increasing disk usage.

UUID v4: The Fragmentation Problem

UUID v4 generates 122 random bits. The probability of two values being adjacent is negligible, so every insert is effectively random from the B-tree's perspective.

Measuring the Impact

On a PostgreSQL table with 10 million rows:

The index for UUID v4 is roughly 3x larger because each UUID is 16 bytes (vs. 4 bytes for INT) and because page splits leave half-empty pages throughout the tree.

Write Amplification

Every page split writes two pages instead of one. For a write-heavy workload, this doubles WAL (Write-Ahead Log) volume and increases checkpoint I/O. On SSDs, it accelerates write wear. On HDDs, random I/O creates seek storms that tank throughput.

UUID v7: Time-Ordered and Index-Friendly

UUID version 7, defined in RFC 9562, addresses the fragmentation problem by making the first 48 bits a Unix timestamp in milliseconds. The remaining bits are random, ensuring uniqueness.

UUID v7 layout:
| 48 bits timestamp | 4 bits version | 12 bits rand_a | 2 bits variant | 62 bits rand_b |

Example UUID v7 values generated 1ms apart:

0192d6c4-a800-7000-8000-000000000000  (timestamp: 1716900000000)
0192d6c4-a801-7000-8000-000000000001  (timestamp: 1716900000001)
0192d6c4-a802-7000-8000-000000000002  (timestamp: 1716900000002)

Because the leading bits are time-ordered, UUID v7 values are monotonically increasing (with rare sub-millisecond exceptions). New rows always insert near the end of the B-tree, just like auto-increment keys.

Performance Comparison

UUID v7 eliminates fragmentation while keeping the benefits of UUIDs: global uniqueness, no coordination, and 128-bit collision resistance.

Practical Recommendations

For PostgreSQL

Use uuid-ossp or pg_uuidv7 extension:

CREATE EXTENSION IF NOT EXISTS pg_uuidv7;

CREATE TABLE users (
    id UUID DEFAULT uuid_generate_v7() PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

For Application-Level Generation

// Using the uuid package (v10+)
import { v7 as uuidv7 } from 'uuid';
const id = uuidv7();

# Using uuid7 package
from uuid_extensions import uuid7
id = uuid7()

When to Stick with INT

Auto-incrementing integers are still the best choice when:

• Your system is single-database (no distributed writes)
• Storage efficiency matters (4 bytes vs. 16 bytes per key)
• You need maximum foreign key join performance (integer comparison is faster than UUID comparison)

Key Takeaways

• UUID v4's randomness causes B-tree page splits, leading to 2–3x index bloat and reduced insert throughput.
• UUID v7 prefixes a millisecond timestamp, making values monotonically increasing and eliminating fragmentation.
• UUID v7 gives you the best of both worlds: globally unique, coordination-free IDs with sequential insert performance.
• Auto-incrementing integers remain optimal for single-database systems where storage size and join speed matter most.

Try It Yourself

Generating UUIDs for your database? Use the UUID Generator to create v4 or v7 UUIDs and compare their structure — notice how v7 values sort chronologically while v4 values are randomly ordered.