Choosing the primary key strategy for your relational database tables is one of the most critical architectural decisions in system design. Historically, software teams defaulted to sequential auto-incrementing integers (INT or BIGINT). However, as systems transitioned to distributed microservices, multi-region database clusters, and client-heavy single-page applications, sequential integers introduced severe limitations: centralized ID allocation bottlenecks, predictability vulnerabilities (IDOR), and complex cross-database merge conflicts.
To overcome these challenges, many engineering teams switched wholesale to 128-bit Universally Unique Identifiers (UUIDs). Yet, many teams soon discovered an unpleasant surprise: after table sizes surpassed several million rows, database insert performance plummeted and memory usage spiked dramatically. In this architectural guide, we dissect why random UUIDs degrade relational databases and how modern time-ordered UUIDv7 resolves the problem.
The Mechanics of Clustered B-Tree Indexes
In relational database storage engines (such as MySQL's InnoDB), table data is physically organized on disk according to the table's primary key inside a B+ Tree clustered index.
- Sequential Integers (Optimal Insertion): When inserting sequential IDs (
1, 2, 3...), new rows are always appended to the very end of the right-most leaf page in the B+ Tree. Pages fill up compactly, disk I/O is sequential, and cache locality remains high. - Random UUIDv4 (Random Insertion Penalty): UUIDv4 consists of 122 bits of pseudo-random entropy. When inserting a new random UUID, the database must place it in a random leaf page in the B+ Tree. If that page is already full, the database engine must execute an expensive B-Tree Page Split, moving half the rows to a new page and rewriting index pointers. Over time, page splits create massive disk fragmentation, blow up memory buffers, and degrade query throughput by up to 80%.
| Primary Key Strategy | Storage Size | Insertion Order | B-Tree Index Fragmentation | Distributed Safety |
|---|---|---|---|---|
| BIGINT Auto-Increment | 8 bytes | Strictly Sequential | Near Zero Fragmentation | Poor (Requires central coordinator) |
| UUIDv4 (Random) | 16 bytes (BINARY) / 36 bytes (CHAR) | Completely Random | Catastrophic Page Splits (> 50%) | Excellent (Decentralized) |
| UUIDv7 (Time-Ordered) | 16 bytes (BINARY) | Monotonically Increasing | Minimal (Similar to BIGINT) | Excellent (Decentralized) |
Storage Efficiency: Binary(16) vs. CHAR(36)
Storing UUIDs as formatted strings (e.g., '3b241101-e2bb-4255-8caf-4136c566a962') in a CHAR(36) column consumes 36 bytes per row. When multiplied across millions of rows and multiple secondary foreign key indexes, string UUIDs consume 4.5x more memory than an 8-byte BIGINT. In MySQL 8 and PostgreSQL, always store UUIDs as native 16-byte binary types (BINARY(16) in MySQL or native UUID in PostgreSQL):
-- MySQL 8: Optimal UUID Storage Pattern
CREATE TABLE users (
id BINARY(16) NOT NULL,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB;
-- Inserting a UUID as 16 raw bytes
INSERT INTO users (id, email) VALUES (UUID_TO_BIN(UUID(), 1), 'user@example.com');UUIDv7: The Modern Salvation (RFC 9562)
Ratified in 2024, RFC 9562 introduced UUIDv7, designed specifically to solve the database index fragmentation penalty. A UUIDv7 identifier begins with a 48-bit Unix millisecond timestamp followed by 74 bits of cryptographically random entropy. Because the timestamp prefix naturally increases over time, new rows are always appended to the end of B-Tree indexes, achieving the insert speed of auto-incrementing integers while retaining decentralized global uniqueness.
Generate compliant UUIDv4 and UUIDv7 identifiers instantly using our free client-side UUID Generator.
