UUID v4 vs UUID v7: B-Tree Index Fragmentation & Primary Key Architecture

A Universally Unique Identifier (UUID) is a 128-bit value engineered to guarantee global uniqueness without requiring coordination with a central authority or database sequence. UUID version 4 (UUIDv4) is constructed almost entirely from pseudo-random entropy (122 random bits and 6 version/variant bits). Because collision probability is mathematically negligible across billions of records, UUIDv4 became the standard choice for distributed microservices and offline-first client ID generation.

However, when UUIDv4 is adopted as the clustered primary key in relational database engines (such as PostgreSQL with B-Tree indexes or MySQL with InnoDB clustered indexes), significant write latency bottlenecks emerge as tables scale into millions of rows.

Relational databases organize primary key indexes using B-Tree (or B+Tree) data structures, which require sorted keys on disk:

• Sequential Integer Keys (AUTO_INCREMENT / BIGSERIAL): Monotonically increasing numbers always append to the rightmost leaf page of the B-Tree. When a page fills up, a clean new page is allocated at the end, maintaining near-optimal page fill factors (~90-95%).
• Random UUIDv4 Keys: Uniformly distributed random hashes insert unpredictably across arbitrary leaf pages throughout the entire index tree. When a record lands in a page that is already full, the engine must execute an expensive Page Split—splitting the disk block into two halves, migrating records, and rewriting parent pointers.
• Buffer Pool Thrashing: As the index size eclipses available RAM, random inserts trigger severe random I/O: the database must evict active pages to read target blocks from disk, leading to high write amplification and latency spikes.

Approved in 2024, RFC 9562 introduces UUIDv7 to unite the collision resistance of random identifiers with the sequential append characteristics of auto-incrementing integers:

• Bits 0–47 (48-bit Big-Endian Unix Timestamp): Millisecond-precision timestamp representing elapsed time since Unix Epoch (valid beyond the year 10,800 AD).
• Bits 48–51 (4-bit Version): Set to binary 0111 (decimal 7).
• Bits 52–63 (12-bit rand_a): Contains a fractional timestamp extension or monotonic sequence counter to preserve strict ordering when multiple IDs are generated within the exact same millisecond.
• Bits 64–65 (2-bit Variant): Standard variant marker 10.
• Bits 66–127 (62-bit rand_b): Cryptographically secure random entropy.

Because UUIDv7 IDs sort chronologically by default, database engines insert new records sequentially into the trailing leaf pages of the B-Tree index, drastically reducing page splits and maintaining high page density.

• PostgreSQL 18+: Features native built-in uuidv7() function support.
• PostgreSQL 14–17: Utilize the high-performance pg_uuidv7 native C extension which provides uuid_generate_v7().
• Node.js / TypeScript: Use the official uuid package (import { v7 as uuidv7 } from "uuid").
• Python: Native support added in Python 3.14 via uuid.uuid7().
• Test and benchmark UUIDv4 versus UUIDv7 generation directly within the browser using the DevUtils UUID Generator tool.

-- PostgreSQL 18 schema utilizing native UUIDv7 for optimal B-Tree clustering
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    customer_id UUID NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

-- Enables sequential index insertions and efficient time-range index scans

Transitioning an existing high-throughput production database from UUIDv4 to UUIDv7 requires careful architectural orchestration to avoid exclusive table locks:

1. Add Shadow Column: Create a new nullable id_v7 column alongside the existing id column.
2. Application-Layer Dual-Writing: Update application code to generate UUIDv7 for all newly inserted rows while queries continue reading via the legacy id column.
3. Asynchronous Backfill: Run a background worker job during off-peak hours to backfill historical rows with deterministic UUIDv7 values derived from each record's original created_at timestamp.
4. Concurrent Index Creation: Build a unique index on id_v7 using CREATE UNIQUE INDEX CONCURRENTLY in PostgreSQL to avoid write locks.
5. Constraint Swap & Cutover: Within a brief transactional window, swap primary key constraints and deprecate the legacy column.

-- PostgreSQL zero-downtime UUIDv7 index cutover pattern
ALTER TABLE orders ADD COLUMN id_v7 UUID DEFAULT uuidv7();

-- Populate historical rows asynchronously without locking writes
UPDATE orders SET id_v7 = uuidv7() WHERE id_v7 IS NULL;

-- Create index concurrently without blocking read/write traffic
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_id_v7 ON orders(id_v7);