Why UUID v4 Is Killing Your Database Indexes (And How UUID v7 Fixes It)

If you have ever built a distributed system or microservice architecture, you have likely used Universally Unique Identifiers (UUIDs) for database primary keys. They solve the coordination problem instantly: any service can generate a unique key locally without talking to a central database or sequence generator.

However, as your tables grow into millions of rows, you might notice a sudden degradation in insert performance, rising disk I/O, and aggressive memory consumption. The root cause is often the very ID strategy that enabled your distributed setup: standard UUID v4.

The Problem: B-Tree Page Splits and Random Writes

Most relational database engines, including PostgreSQL (B-Tree) and MySQL (InnoDB clustered indexes), store primary key indexes in balanced tree structures. These structures are optimized for sequential or near-sequential inserts.

When you use an auto-incrementing integer (SERIAL or BIGINT), each new row is appended to the rightmost leaf node of the B-Tree index. This operation is fast and predictable:

1. The engine checks the rightmost page in buffer pool memory.
2. If space exists, it writes the new index entry.
3. Disk pages remain packed at near 100% fill factor.

UUID v4, by design (RFC 4122), consists of 122 bits of pseudo-random data. When inserting rows with UUID v4 primary keys, new values are scattered randomly across the entire range of the B-Tree index.

This randomness triggers frequent page splits:

• When an insert hits a full index page (typically 8KB or 16KB), the database engine must allocate a new page.
• It moves half of the entries from the existing page into the new page to maintain sorting order.
• Both modified pages must be written back to disk, along with updates to parent node references in the tree.

At scale, this results in severe write amplification, index fragmentation (pages sitting 50% empty), and poor cache locality because working sets no longer fit in memory.

How UUID v7 Solves the Indexing Bottleneck

Published in RFC 9562, UUID v7 introduces a time-ordered structure designed specifically for modern database workloads:

• First 48 bits: Big-endian Unix timestamp in milliseconds.
• Next 12 bits: Sub-millisecond precision or random counter bits.
• Remaining 68 bits: Cryptographically strong pseudo-random data.

Because the leading 48 bits represent monotonically increasing time, UUID v7 values are lexicographically sortable. For database indexes, a UUID v7 behaves almost identically to an auto-incrementing integer during inserts: new keys arrive at the rightmost edge of the B-Tree.

Here is a comparison of key layouts:

UUID v4: 9b1deb4d-3b7d-4149-9cc6-84776ad0c4e7  (Purely random)
UUID v7: 018f4a12-68b3-7649-b570-3d84931a7890  (Time-ordered prefix + random)

By switching to UUID v7, benchmarks across PostgreSQL and InnoDB show up to 8x higher insert throughput and a 40–50% reduction in index size compared to UUID v4.

When prototyping schemas or testing migrations locally, you can quickly generate and compare different UUID formats using the free Nutilz UUID Generator, which generates v4, v7, and namespace-based UUIDs directly in your browser.

Privacy and Security Tradeoffs

While UUID v7 provides superior indexing performance, it is important to consider the trade-offs:

1. Information Leakage: Because the first 48 bits encode a timestamp, anyone observing a UUID v7 can extract the exact millisecond the record was created. In public-facing URLs or API tokens, this could leak sensitive operational metrics (such as daily user signup volume).
2. Clock Drift Sensitivity: If your server clocks drift backwards or experience NTP adjustments, monotonic ordering can be affected. Modern UUID v7 implementations handle this by incrementing counter bits during time collisions.

Summary of Best Practices

• Use UUID v7 for internal database primary keys: Enjoy distributed ID generation without sacrificing B-Tree index performance.
• Use UUID v4 for public tokens and external IDs: Keep creation timestamps hidden where privacy matters.
• Store as Native UUID or Binary(16): Avoid storing 36-character string representations (CHAR(36)), which double storage overhead and slow down comparisons.

For quick testing, mock data generation, or formatting checks without writing local scripts, utilities like Nutilz offer browser-based tools with zero data collection.