KitWharfFREEFast & Private Web Apps

UUID v7 vs. UUID v4 vs. ULID vs. NanoID: Choosing the Right Identifier for Databases & Distributed Systems

November 19, 202612 min readKitWharf Team
Interactive Online Tool Available

UUID v1/v4/v7, ULID & NanoID Generator

Bulk-generate cryptographically random UUID v4, sortable UUID v7, UUID v1, ULID, and NanoID identifiers via WebCrypto and decode embedded timestamps.

Launch Free Tool

The B-Tree Index Death Spiral: Why Random UUID v4 Destroys Write Throughput

For two decades, application developers used auto-incrementing serial integers (1, 2, 3...) as primary keys in relational databases. While integers offer blistering B-tree insert speed, they introduce fatal architecture flaws:

  • Security Vulnerability: Enumeration attacks allow scraping entire customer directories (/api/users/1024).
  • Business Intelligence Leakage: Competitors can estimate your monthly transaction velocity simply by creating an account on the 1st and 30th of the month.
  • Distributed Merge Collisions: Offline mobile apps or multi-region database masters cannot generate IDs independently without a centralized coordination lock.

To solve this, developers adopted UUID v4 (RFC 4122). However, as tables grow past 10 million rows, UUID v4 causes a catastrophic database performance collapse known as B-tree Page Splitting.

How B-Tree Storage Engines Fail with Random Keys

In PostgreSQL, MySQL (InnoDB), and SQLite, primary keys are indexed in a balanced tree (B-tree) of fixed-size disk blocks (typically 8KB or 16KB). When keys are monotonically increasing, new records append cleanly to the rightmost leaf block. Each block fills to 100% capacity before allocating the next sequential block.

With UUID v4, every insert generates 122 bits of uniform entropy:

  1. An incoming UUID lands on an arbitrary, randomly distributed leaf page in the index tree.
  2. Once the index exceeds the database RAM buffer pool, the database engine must issue a synchronous, high-latency random read to bring that cold page into memory from SSD/NVMe.
  3. If that 8KB page is full, the engine must perform a Page Split: allocate a new 8KB disk page, move half the rows over, and update the parent branch node.
  4. Massive Fragmentation: Page splits leave B-tree leaf blocks only 50% to 60% full, doubling your index size on disk.
  5. WAL Amplification: Full page splits write entire 8KB pages into the Write-Ahead Log (WAL) or MySQL redo log, saturating storage I/O bandwidth.

Enter Time-Ordered 128-Bit Identifiers: RFC 9562 UUID v7

Approved as an official IETF Proposed Standard in RFC 9562 (May 2024), UUID v7 solves the B-tree fragmentation dilemma by placing a 48-bit big-endian Unix Epoch millisecond timestamp in the high-order bits, followed by 74 bits of cryptographic entropy.

B-Tree Engine Architecture (RFC 9562)

Monotonic B-Tree Right-Append vs. Random UUID v4 Thrashing

How 48-bit time-ordered prefixes eliminate buffer pool thrashing, prevent 50% leaf page splits, and maximize write I/O throughput.

⚡ Monotonic Index Engine Pipeline (RFC 9562) Unix Epoch 48-bit • CSPRNG Entropy • Sequential B-Tree Right-Append • Zero Page Splits High Write Throughput 1 INGESTION Time & Entropy • Unix Epoch ms (48 bits) • Valid through 10,889 AD • WebCrypto CSPRNG • 74 bits hardware entropy Source: Hardware Clock 2 BIT PACKING RFC 9562 Struct • Version 0111 (4 bits) • Variant 10 (2 bits) • rand_a (12 bits counter) • rand_b (62 bits entropy) Spec: 128-Bit Wire 3 TRAVERSAL B-Tree Lookup • Monotonic ordering • Targets active right page • In-RAM Buffer Pool hit • Zero random disk seeks Cache Hit: > 99% 4 PAGE FILL Sequential Fill • 8KB block fills to 100% • Zero mid-tree splits • 50% index storage saving • Compact physical footprint Fill Factor: 100% 5 STORAGE WAL & Redo Logs • Minimal sequential WAL • 4x to 10x faster inserts • Native 16-byte uuid type • Zero cloud leak Target: Postgres/MySQL ✓ Database Performance Benchmark: UUID v7 Outperforms UUID v4 by 4x to 10x By eliminating random B-tree page splits, UUID v7 provides the security of UUIDs with the insertion efficiency of auto-incrementing integers.

Complete Comparison Matrix: UUID v7 vs. UUID v4 vs. ULID vs. NanoID

Engineering Metric RFC 9562 UUID v7 RFC 4122 UUID v4 Crockford Base32 ULID NanoID Legacy UUID v1
Total Binary Size 128 bits (16 bytes) 128 bits (16 bytes) 128 bits (16 bytes) 126 bits (21 chars) 128 bits (16 bytes)
String Output Length 36 characters 36 characters 26 characters 21 characters 36 characters
Lexicographically Sortable? Yes (K-Sortable) No (Pure random) Yes (Strictly sortable) No (Pure random) Partial (Gregorian)
Timestamp Precision 48-bit Unix ms None 48-bit Unix ms None 60-bit 100ns intervals
Max Timestamp Year 10,889 AD N/A 10,889 AD N/A 3400 AD
Entropy Bits 74 bits 122 bits 80 bits ~126 bits 14-bit clock + 48-bit node
Native SQL uuid Storage Yes (Postgres/MySQL) Yes (Postgres/MySQL) Requires conversion Stored as string (varchar) Yes (Postgres/MySQL)
URL & Slug Friendliness Moderate (36 chars) Moderate (36 chars) High (Clean 26 chars) Highest (21 chars) Moderate (36 chars)
Primary Industry Role Relational DB Primary Keys Ephemeral Session Tokens NoSQL, JSON & Distributed Events Public Links & React Keys Legacy Active Directory

Anatomy & Bit Layout Deep Dive

1. RFC 9562 UUID v7 (128-Bit Visual Breakdown)

RFC 9562 · 128-Bit Structure Example: 0193449a-7c10-7b32-9a14-e8f2109c4401
unix_ts_ms (48 bits)
Bits 0–47 · Unix Epoch ms (to 10,889 AD)
ver
0111 (7)
rand_a
12 bits
var
10
rand_b (62 bits)
WebCrypto CSPRNG
Monotonic Sorting: Leading 48-bit timestamp keeps B-tree inserts sequential.
74 Bits of Entropy: rand_a + rand_b backed by crypto.getRandomValues().

2. Crockford Base32 ULID (26-Character Layout)

Timestamp · 10 chars (48 bits)
01JCYQ8M2K · Unix ms precision
Randomness · 16 chars (80 bits)
9X7V2P4W8N1M6Q3R · Crockford Base32 (excludes I, L, O, U)

Production Database Migration Recipes

Migrating an existing application from UUID v4 to UUID v7 requires zero schema alterations in PostgreSQL, because both identifiers occupy exactly 16 bytes binary in a native uuid column!

PostgreSQL 16 & 17 DDL

In PostgreSQL 17, UUID v7 is supported natively. For PostgreSQL 14–16, you can use the lightweight pg_uuidv7 extension or a pure SQL function:

-- Schema definition with UUID v7 primary key
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  customer_id UUID NOT NULL,
  total_amount NUMERIC(10, 2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

-- Indexing note:
-- Because UUID v7 is monotonically increasing,
-- a standard B-tree index achieves maximum packing density!
CREATE INDEX idx_orders_customer ON orders (customer_id);

MySQL 8.0 InnoDB Clustered Index Strategy

In MySQL InnoDB, the primary key dictates the physical ordering of the table on disk (Clustered Index). Using UUID v4 in MySQL causes catastrophic disk thrashing. With UUID v7, store as BINARY(16):

CREATE TABLE audit_logs (
  id BINARY(16) NOT NULL,
  user_email VARCHAR(255) NOT NULL,
  action VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- Inserting UUID v7 formatted as binary:
INSERT INTO audit_logs (id, user_email, action)
VALUES (UNHEX(REPLACE('0193449a-7c10-7b32-9a14-e8f2109c4401', '-', '')), 'alice@corp.internal', 'LOGIN_SUCCESS');

Decoding Embedded Timestamps During Live Incident Triage

When debugging customer webhook failures or distributed log streams, UUID v7 and ULID provide an unexpected superpower: instant chronological extraction without a database query.

  1. Extract the first 48 bits (the first 12 hex characters in UUID v7, or the first 10 characters in ULID).
  2. Convert that hex value to a standard integer: parseInt("0193449a7c10", 16) = 1731998743568.
  3. Construct a standard JavaScript date: new Date(1731998743568).toISOString() → 2024-11-19T06:45:43.568Z.

In the KitWharf UUID & ULID Generator Studio:

  • Paste any ID into the Timestamp Decoder to inspect the detected RFC version, variant, and exact UTC millisecond timestamp.
  • Batch generate up to 500 IDs formatted as lines, JSON arrays, CSV, or ready-to-run SQL INSERT statements—100% locally via WebCrypto.
Found this guide helpful? Share it with your team:

Ready to put this into practice?

Launch our free UUID v1/v4/v7, ULID & NanoID Generator — instant client-side execution in your browser with zero registration required.

Open Tool