Scaling Reads hard
Why does using random UUID v4 as a Primary Key hurt Read performance in a large B-Tree database compared to sequential Integers or ULIDs?
- A UUIDs take up more space (128-bit vs 64-bit).
- B UUIDs cannot be indexed.
- C Random UUIDs cause 'Page Fragmentation' (Random Writes) in the B-Tree, spreading related data across random disk pages. This forces the DB to load more pages into RAM to satisfy queries, lowering the cache hit ratio.
- D UUIDs require encryption.
Answer: C. Random UUIDs cause 'Page Fragmentation' (Random Writes) in the B-Tree, spreading related data across random disk pages. This forces the DB to load more pages into RAM to satisfy queries, lowering the cache hit ratio.
B-Trees love locality. Sequential IDs fill pages 100%. Random IDs result in pages being 60-70% full (fragmentation) and scattered order. Reading 100 random UUID rows might require fetching 100 different disk pages.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- In a caching system, what is a 'Cache Miss'?
- Why is `SELECT * FROM Users` generally discouraged in high-performance applications compared to `SELECT name, email FROM Users`?
- You have users all over the world downloading large images from your server in New York. Users in Australia complain it is slow. What solves this?
- What is the main downside of reading from a Read Replica instead of the Primary database?
- You have maximized the RAM on your single database server, but it's still too slow. You decide to add 10 more servers to share the load. What is this called?
- You have 5 Read Replicas. How does the application know which one to ask for data?