Why is using UUIDs as primary keys bad for clustered index performance in SaaS?

All answers to this question.

Using completely random UUIDv4 strings as primary keys on clustered indexes is an incredibly common performance killer. Because the values are entirely random, new rows cannot simply be appended to the end of the physical disk storage. Instead, the database engine is forced to constantly insert records right into the middle of existing pages, causing frequent page splits, massive disk I/O, and severe index fragmentation. This completely tanks write throughput as your tables grow. You should switch to sequentially ordered identifiers like UUIDv7 or ULIDs, which preserve spatial locality while remaining globally unique.

   Answered 2025-01-10 by Diana Prince

Does moving from a standard relational engine to a NoSQL database like MongoDB eliminate this specific indexing bottleneck when handling completely random unique identifiers?

   Answered 2025-01-13 by Arthur Pendelton

• Arthur, switching to a NoSQL engine doesn't completely fix the underlying issue of un-ordered keys. While Document stores handle high write volumes differently than strict B-Tree relational engines, random keys still force index nodes to be constantly re-shuffled in memory. This leads to inefficient RAM utilization and slower query lookups over time, meaning sequential ordering remains a best practice across almost all database types.

     Commented 2025-01-14 by Bruce Wayne

Random keys ruin your cache locality. The database server will waste massive amounts of CPU and memory constantly swapping data pages in and out of RAM just to execute basic inserts.

   Answered 2025-01-18 by Harvey Dent

• Exactly, Harvey. We ran into this exact wall where our write latency quadrupled after hitting ten million rows. Migrating our primary keys to time-sorted sequential identifiers instantly brought our disk write I/O back down to normal levels.

     Commented 2025-01-19 by Jeremy Vance