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.…