Menu

UUIDv4 Is Not Free: Why Random Primary Keys Are Silently Crushing Your Write Performance
📰

UUIDv4 Is Not Free: Why Random Primary Keys Are Silently Crushing Your Write Performance

DEV Community: postgres·𝗝𝗼𝗵𝗻·6 days ago
#w6vJLSMc
#devtopostgresql#devto#dev#uuidv7#uuidv4
Reading 0:00
15s threshold

When my database write latency spiked by 340% after crossing 50 million rows, my CPU wasn't maxed out. My queries weren't missing indexes. Instead, my storage IOPS was completely saturated by a single architectural default I set up on day one: using UUIDv4 as my primary key.

Most backend engineers avoid auto-incrementing integers ( BIGINT / SERIAL ) in public-facing systems for good reasons. Sequential IDs leak business metrics, enable enumeration attacks ( /api/orders/1042 , /api/orders/1043 ), and make distributed ID generation across microservices painful.

So I reached for the industry standard: UUIDv4 . It is 128 bits of randomness, requires zero coordination between nodes, and guarantees global uniqueness.

What looks like a clean architectural win in code review turns into an I/O nightmare inside your storage engine.

The Problem: Randomness vs. B-Tree Locality

Relational databases like PostgreSQL, MySQL (InnoDB), and SQL Server use B-Tree (or B+Tree) structures to maintain primary key indexes.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.