Why UUIDv7 Beats UUIDv4 for Database Primary Keys

Choosing a primary key format used to force an awkward architectural compromise. If you chose auto-incrementing integers (BIGINT), you gained compact B-tree indexes and lightning-fast sequential writes, but exposed sequential record counts to the public and struggled with offline, client-side, or multi-region generation. If you opted for standard random UUIDv4, you gained decentralized generation and unguessable IDs at the cost of crippling your database index performance once tables grew past RAM.

Standardized in RFC 9562, UUIDv7 completely resolves this dilemma. By combining a millisecond-precision Unix timestamp in the most significant bits with strong cryptographic randomness, UUIDv7 brings sequential append locality back to standard 128-bit identifiers.

The Silent Cost of Random UUIDv4 Inserts

Relational databases like PostgreSQL, MySQL (InnoDB), and SQLite store primary key indexes in B-tree structures. A B-tree organizes records in fixed-size pages—typically 8 KB or 16 KB. When you insert keys that increase monotonically, such as 1, 2, 3..., every new entry lands neatly on the right-most leaf page of the tree. When that page fills up, the engine allocates a new page at the end. The hot working set remains concentrated in memory cache (shared_buffers), disk I/O stays minimal, and pages achieve close to 90–100% fill factor.

UUIDv4 shatters this optimization. Because 122 of its 128 bits are purely pseudo-random, each new key lands at an arbitrary memory address inside the B-tree:

Sequential (BIGINT):  [1, 2, 3] -> [4, 5, 6] -> [7, 8, 9] (appends to right edge)
Random (UUIDv4):      [0x1a, 0x8f] <- insert 0x4c splits node -> [0x4c, 0xf2]

When a newly generated UUIDv4 targets a leaf page that is already full, the database must execute a page split. It allocates a new page, moves roughly 50% of the existing rows over, and updates parent pointers.

This behavior triggers three compounding performance penalties:

1. Severe Write Amplification: Splitting an 8 KB page to insert a single 16-byte key forces the engine to dirty and write multiple full pages to the Write-Ahead Log (WAL) and disk.
2. Cache Churn: As the index outgrows available RAM, random inserts touch cold pages constantly. The database repeatedly evicts cached data to read old index pages from disk.
3. Bloated Storage: Continuous 50/50 splits leave leaf pages half-empty on average, inflating the final index size on disk by 25% to 40% compared to sequential keys.

How UUIDv7 Restores Index Locality

UUIDv7 eliminates random page scattering by redesigning the internal 128-bit layout defined by RFC 9562:

• 48 bits: Unix epoch timestamp in milliseconds.
• 4 bits: UUID version (0111 for version 7).
• 12 bits: Sub-millisecond precision or monotonic counter.
• 2 bits: UUID variant (10 for RFC 9562/4122).
• 62 bits: Cryptographically secure pseudo-random entropy.

 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                           unix_ts_ms                          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          unix_ts_ms           |  ver  |       rand_a          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|var|                        rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                            rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Because the Unix millisecond timestamp occupies the most significant bits, any UUIDv7 created after another UUIDv7 is naturally greater in lexicographical and byte sort order. In PostgreSQL, MySQL, or SQLite, new inserts immediately land at the right-most edge of the primary key B-tree index.

You retain all the perks of UUIDs—independent client generation, zero coordination between distributed nodes, and non-enumerable record IDs—while reclaiming the write throughput of standard auto-increment integers.

Using UUIDv7 in Modern Applications

Because UUIDv7 conforms strictly to the standard 128-bit UUID format, it is a drop-in binary replacement for UUID columns without altering database schemas.

In PostgreSQL 17 and newer, you can use native generation directly in your DDL:

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    customer_id UUID NOT NULL,
    total_amount NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

In application code (Node.js/TypeScript), client-side generation requires only standard libraries:

import { v7 as uuidv7 } from 'uuid';

// Generates time-ordered UUIDv7: e.g., '018f4a3b-c210-7abc-b456-426614174000'
const orderId = uuidv7();

Practical Trade-offs to Keep in Mind

While UUIDv7 is an easy default for new relational tables, two trade-offs warrant consideration:

1. Timestamp Leakage: Because the first 48 bits encode the creation timestamp, anyone who inspects the ID knows the exact millisecond it was generated. If the exact creation time of a resource represents sensitive business intelligence, avoid using the raw UUIDv7 in public URL paths or mask it behind an unlinked token.
2. Sequential Batch Pagination: While UUIDv7 is ordered by time, multiple keys generated in the same millisecond across distributed nodes will have random relative ordering within that millisecond window. For strict deterministic pagination, pair UUIDv7 keyset conditions with explicit tie-breakers if sub-millisecond precision matters.

For nearly all production web workloads, transitioning new tables from UUIDv4 to UUIDv7 is one of the highest-leverage schema decisions you can make to protect long-term write performance.

Sources

1. vertexaisearch.cloud.google.com
2. vertexaisearch.cloud.google.com
3. vertexaisearch.cloud.google.com
4. vertexaisearch.cloud.google.com
5. vertexaisearch.cloud.google.com
6. vertexaisearch.cloud.google.com