UUID v7 vs. UUID v4 (RFC 9562 Guide): Why Time-Sorted UUIDv7 Beats Random UUIDv4 for PostgreSQL & MySQL B-Tree Indexes

WhatsApp
Telegram
Facebook
Twitter
LinkedIn
UUID v7 VS. UUID v4 (RFC 9562) - Trencada Client-Side Tool Guide

For over fifteen years, software architects building distributed microservices, SaaS multi-tenant databases, and public REST APIs defaulted to UUID v4 (`RFC 4122`) as their primary key format so application nodes could generate globally unique 128-bit identifiers without coordinating on a centralized `AUTO_INCREMENT` / `BIGSERIAL` database sequence. Yet as soon as a PostgreSQL, MySQL (InnoDB), or SQL Server table grows past a few million rows, random UUID v4 primary keys trigger a notorious database performance cliff: B-Tree index fragmentation spikes past 50%, random disk I/O thrashing exhausts the buffer pool (`shared_buffers`), and Write-Ahead Log (`WAL`) volume balloons by 300% due to Full-Page Writes (`FPW`).

In May 2024, the Internet Engineering Task Force (IETF) published RFC 9562 (superseding RFC 4122) and officially standardized UUID Version 7 (`UUIDv7`)—a 128-bit identifier that combines a 48-bit big-endian Unix Epoch millisecond timestamp with 74 bits of cryptographic randomness. As detailed in RFC 9562 UUID v7 database locality benchmarks, UUIDv7 is 100% wire-compatible with every existing `uuid` column type while restoring sequential append-only B-Tree index locality! This 2026 backend engineering guide compares the exact bit layouts of UUIDv4, UUIDv7, and ULID, explains the math behind B-Tree page splits, and shows how to generate and inspect UUIDs offline in your browser.

Instant 100% Offline Developer & Security Utility Suite (Zero Server Logging):

Quick Jump Navigation (Table of Contents)

1. Inside the 128 Bits: UUID v4 vs. UUID v7 (`RFC 9562`) Bit-Level Anatomy

Both UUID v4 and UUID v7 are 128-bit (16-byte) numbers canonically formatted as a 36-character hexadecimal string (`8-4-4-4-12`, e.g., 019324e8-7a4b-7c12-9a3f-8b4d2e1c90f4). Notice the 13th hex character (`7` vs `4`), which encodes the 4-bit version field (`ver`), and the 17th hex character (`8`, `9`, `a`, or `b`), which encodes the 2-bit RFC variant (`10xx`):

// UUID v4 (RFC 9562 Section 5.4): 122 Purely Random Bits
[ 48 bits: random_a ] - [ 4 bits: ver (0100 = 4) ] [ 12 bits: random_b ] - [ 2 bits: var (10) ] [ 62 bits: random_c ]
Example: f47ac10b-58cc-4372-a567-0e02b2c3d479  (Random prefix -> Scatters across entire B-Tree!)

// UUID v7 (RFC 9562 Section 5.7): 48-Bit Unix Epoch ms Timestamp + 74 Random/Counter Bits
[ 48 bits: unix_ts_ms (Big-Endian Milliseconds since 1970-01-01) ] - [ 4 bits: ver (0111 = 7) ] [ 12 bits: rand_a ] - [ 2 bits: var (10) ] [ 62 bits: rand_b ]
Example: 01932510-4b20-7f3a-8c91-3d5e7a9b1c2d  (Monotonic prefix -> Appends to rightmost B-Tree leaf!)

2. Why Random UUID v4 Wrecks PostgreSQL & MySQL InnoDB B-Tree Performance

Relational databases store primary key indexes inside B-Tree (or B+Tree) pages—typically `8 KB` pages in PostgreSQL and `16 KB` clustered index pages in MySQL InnoDB. In a B-Tree, leaf pages must remain physically or logically sorted by key value at all times:

  • The Random Page Split Problem: Because UUID v4’s leading 48 bits are uniformly random (`0x000000000000` to `0xffffffffffff`), every single `INSERT` statement targets a completely random `8 KB` / `16 KB` leaf page across the entire multi-gigabyte index. When that target page is full, the database must pause, allocate a new page, move 50% of the keys to the new page (a 50/50 Page Split), and leave both pages half-empty—wasting 30% to 50% of RAM and disk space!
  • Buffer Pool Cache Thrashing: Once your B-Tree index exceeds available RAM, every random UUID v4 `INSERT` requires fetching a cold page from SSD into memory and evicting another page—dropping throughput from 25,000 inserts/sec to under 2,500 inserts/sec.
  • PostgreSQL `WAL` Full-Page Write (`FPW`) Amplification: After every checkpoint in PostgreSQL, the first modification to any `8 KB` page writes the entire 8 KB page image into the Write-Ahead Log (`WAL`) to prevent torn pages. Because UUIDv4 touches thousands of distinct pages per second, WAL disk writes explode.
  • How UUID v7 Solves All Three: Because UUID v7 starts with a 48-bit millisecond timestamp (`unix_ts_ms`), every new row generated today has a higher numeric value than rows generated a millisecond ago. Every `INSERT` lands on the ** exact same rightmost leaf page** currently hot in CPU L3 cache / RAM!

3. Master Comparison: `BIGINT` vs. `UUID v4` vs. `UUID v7` vs. `ULID`

Architecture Metric BIGINT / BIGSERIAL UUID v4 (Random) UUID v7 (RFC 9562) ULID
Storage Size (Binary) 8 Bytes (64-bit) 16 Bytes (128-bit) 16 Bytes (128-bit) 16 Bytes (128-bit)
B-Tree Insert Locality Excellent (Append-only) Poor (Random page splits) Excellent (Time-ordered append) Excellent (Time-ordered)
Decentralized Client Generation No (Requires DB sequence) Yes Yes (74 random bits / ms) Yes (80 random bits / ms)
Native SQL `uuid` Type Support Native `bigint` Native `uuid` 100% Native `uuid` (PG 18 `uuidv7()`) Requires custom extension or `CHAR(26)`
Competitor Enumeration Security Weak (`/orders/1001`, `1002`) Maximum (Zero timestamp leak) Unguessable ID (Reveals creation ms) Unguessable ID (Reveals creation ms)

4. Security Trade-Off: When Should You Still Use UUID v4 Instead of UUID v7?

While UUID v7 is the clear winner for internal database primary keys, high-volume event logs, and Kafka/Supabase streams, remember that the first 12 hex characters of a UUID v7 encode the exact UTC millisecond the record was created (`019325104b20` hex = `1731493546784` ms Epoch).

  • Use UUID v7 When: You are indexing high-write database tables (`orders`, `audit_logs`, `messages`, `transactions`, `iot_events`) where chronological sorting and B-Tree locality save massive RAM and IOPS—or when you want `ORDER BY id DESC` to match `ORDER BY created_at DESC` without maintaining a second index!
  • Use UUID v4 When: You are generating password reset links, invitation tokens, API keys, or public resource URLs where you do not want external users or competitors to see the exact millisecond a user account or document was created. Generate both formats anytime in our Client-Side UUID Generator.

Frequently Asked Questions (FAQ)

Can I store UUID v7 in an existing PostgreSQL `uuid` column without a schema migration?

Yes! Because UUID v7 follows the exact same 128-bit `8-4-4-4-12` hexadecimal representation as UUID v4, PostgreSQL’s native `uuid` data type, MySQL’s `BINARY(16)` (`UUID_TO_BIN()`), and SQL Server’s `uniqueidentifier` accept UUID v7 immediately with zero `ALTER TABLE` changes.

What is the collision probability of UUID v7 within the same millisecond?

Even if two distributed servers generate UUID v7 values during the exact same millisecond (sharing the same 48-bit timestamp prefix), the remaining 74 random bits provide `2^74` (`1.88 × 10^22` or 18.8 sextillion) possible values per millisecond—making collisions astronomically improbable, especially when paired with RFC 9562’s optional monotonic sub-millisecond counter in `rand_a`.

Why shouldn’t I store UUIDs as `VARCHAR(36)` in MySQL or PostgreSQL?

Storing a UUID as a 36-character ASCII string (`VARCHAR(36)` or `TEXT`) consumes 36–37 bytes per row (plus collation comparison overhead) instead of 16 bytes in native binary (`uuid` in PostgreSQL or `BINARY(16)` in MySQL)—more than doubling your primary key and foreign key index size in RAM.

How does Trencada generate UUIDs in the browser?

Trencada’s UUID Generator uses your browser’s hardware-seeded Web Crypto API (`crypto.randomUUID()` and `crypto.getRandomValues()`) locally in RAM, ensuring cryptographic randomness with zero server logging.

How can I inspect a UUID inside a JWT token (`jti` or `sub` claim)?

Paste your JSON Web Token into Trencada’s offline JWT Decoder to inspect the `sub` (Subject UUID) and `jti` (JWT ID) claims locally without transmitting your token over the network.

Trustpilot

Run 450+ client-side PDF, image, developer & calculator utilities in your browser (Zero Cloud Upload):

Launch Free Browser Tools →