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.
- Generate RFC 9562 UUIDs and cryptographically secure identifiers in browser RAM with our Free Client-Side UUID Generator.
- Decode OAuth2/OIDC JWT claims (`sub`, `jti`, `exp`) offline via the Client-Side JWT Decoder.
- Format and validate API JSON payloads locally using the Offline JSON Formatter & Validator.
- Flatten nested JSON arrays into Excel CSV spreadsheets with the JSON to CSV Converter.
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.







