UUID & ULID Studio
Bulk-generate up to 500 UUID v1/v4/v7, ULID & NanoIDs and decode embedded UTC timestamps.
Password & Diceware Tool
Generate CSPRNG API keys, Diceware passphrases, and Shannon entropy audits locally.
JWT Debugger
Inspect JWT jti UUID claims, verify HMAC signatures, and audit token expiry.
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:
- An incoming UUID lands on an arbitrary, randomly distributed leaf page in the index tree.
- 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.
- 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.
- Massive Fragmentation: Page splits leave B-tree leaf blocks only 50% to 60% full, doubling your index size on disk.
- 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.
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.
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)
2. Crockford Base32 ULID (26-Character Layout)
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.
- Extract the first 48 bits (the first 12 hex characters in UUID v7, or the first 10 characters in ULID).
- Convert that hex value to a standard integer:
parseInt("0193449a7c10", 16) = 1731998743568. - 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
INSERTstatements—100% locally via WebCrypto.