UUIDv7 vs ULID vs UUIDv4: Database Primary Key Performance Guide

Choosing the right primary key strategy is one of the most critical architectural decisions for scalable databases.

While traditional sequential auto-incrementing integers (BIGINT AUTO_INCREMENT / SERIAL) offer excellent insertion locality, they expose business volume metrics to the public (/orders/102 vs /orders/103) and complicate distributed multi-master writes.

To solve this, developers historically adopted random UUIDv4. However, inserting random UUIDs into high-volume relational databases causes severe B-tree index fragmentation, cache thrashing, and degraded write throughput.

The introduction of RFC 9562 (UUIDv7) and ULID resolves this dilemma by combining millisecond-level time ordering with cryptographic randomness.

---

1. Why Random UUIDv4 Degrades Database Performance

Relational databases like PostgreSQL (B-tree) and MySQL InnoDB (Clustered Index) organize primary key indexes in balanced trees where data is stored sorted by key value.

Sequential / Time-Ordered Inserts (UUIDv7 / ULID):
[Page 1: 00:01, 00:02, 00:03] -> [Page 2: 00:04, 00:05] -> New pages appended to end (Append-Only)

Random Inserts (UUIDv4):
[Page 1: 0a..., 3f...] <--- Insert 1c... forces Page 1 to SPLIT in the middle!
[Page 2: 8b..., c4...] <--- Insert 9a... forces Page 2 to SPLIT!

The Cost of Randomness:

1. Frequent Page Splits: When inserting a random value into an already-full 8KB/16KB index page, the database must split the page into two half-empty pages.
2. Buffer Pool Thrashing: Because new rows scatter randomly across all leaf pages in the tree, the database cannot keep active pages in RAM cache, causing constant disk I/O reads.
3. Index Bloat: Clustered tables with random keys consume up to 40–60% more disk space due to half-empty split pages.

---

2. Deep Dive: UUIDv7 vs ULID Specifications

UUIDv7 (RFC 9562 Standardized)

UUIDv7 is an official IETF standard (RFC 9562 released in 2024) designed specifically for database locality.

• Bit Layout (128 bits):
  • 48 bits: Unix epoch timestamp in milliseconds (covers dates through year 10,889).
  • 4 bits: Version (0111 for v7).
  • 12 bits: Sub-millisecond sequence counter or random bits.
  • 2 bits: RFC variant (10).
  • 62 bits: Cryptographically secure pseudo-random entropy.
• Format: Standard UUID hyphenated hex string: 0191c49b-7e61-7000-8c29-d58ef06e41b2.

ULID (Universally Unique Lexicographically Sortable Identifier)

ULID is a popular community specification created to provide URL-safe, compact sortable identifiers.

• Bit Layout (128 bits):
  • 48 bits: Unix epoch timestamp in milliseconds.
  • 80 bits: Cryptographic randomness.
• Format: 26-character Crockford's Base32 string: 01ARZ3NDEKTSV4RRFFQ69G5FAV.
• Properties: Case-insensitive, no hyphens, human-readable.

---

3. Comparison Matrix: UUIDv7 vs ULID vs UUIDv4

Feature	UUIDv4	UUIDv7 (RFC 9562)	ULID
Standardization	RFC 4122 (Classic)	RFC 9562 (Modern IETF)	Community Spec
Time-Sortable?	❌ No (Pure Random)	✅ Yes (48-bit timestamp)	✅ Yes (48-bit timestamp)
B-Tree Index Friendly	❌ Severe fragmentation	✅ Excellent (Sequential)	✅ Excellent (Sequential)
Native DB UUID Type	✅ Native 16-byte storage	✅ Native 16-byte storage	⚠️ Needs VARCHAR(26) or BYTEA
String Representation	36 chars (hyphenated hex)	36 chars (hyphenated hex)	26 chars (Crockford Base32)
Collision Resistance	$2^{122}$ entropy	$2^{62}\text{--}2^{74}$ per ms	$2^{80}$ per ms
Public Information Leak	Zero metadata	Creation timestamp exposed	Creation timestamp exposed

---

4. Code Implementations Across Stacks

TypeScript / Node.js (UUIDv7 & ULID)

// Modern native Node.js crypto supports UUIDv7:
import { randomUUID } from 'node:crypto';

// In modern runtimes or using the 'uuid' library:
import { v7 as uuidv7 } from 'uuid';
import { ulid } from 'ulid';

const newOrderId = uuidv7(); 
// => "0191c49b-7e61-782a-a92c-5b23d5a4ecb1"

const userEventId = ulid(); 
// => "01J70GZ9B8V1W2X3Y4Z5A6B7C8"

PostgreSQL Schema Migration (UUIDv7)

PostgreSQL 17+ and standard extension functions support UUIDv7 natively:

-- Create table using native 16-byte UUID column:
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- or uuidv7()
    customer_id UUID NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Extract timestamp directly from UUIDv7 in SQL:
-- Bytes 0-5 contain the 48-bit millisecond timestamp

---

5. Architectural Recommendation

1. For Relational Databases (PostgreSQL, MySQL, SQLite): Choose UUIDv7. It stores in native 16-byte UUID columns, eliminates B-tree index page splits, and preserves standard UUID formatting across all ORMs (Drizzle, Prisma, TypeORM).
2. For URLs and Public API IDs: Choose ULID. Its 26-character Base32 representation is shorter, cleaner, and avoids URL-encoding issues.
3. For Ephemeral Nonces & Cryptographic Tokens: Stick with UUIDv4 or raw CSPRNG bytes where timestamp metadata should not be leaked.

---

Frequently Asked Questions

Can someone extract the creation time from a UUIDv7 or ULID?

Yes. The first 48 bits encode the Unix epoch timestamp in milliseconds. Never use UUIDv7 or ULID for sensitive reset tokens or session keys where generation time must remain private.

How do I generate and inspect UUIDv7 and ULID tokens?

Generate batch IDs and inspect timestamp components in real-time with the UUID Generator Tool and verify Unix timestamps with the Timestamp Converter Tool.