Database Indexing easy
Why can inserting random UUIDs as Primary Keys be slower than inserting sequential Integers?
- A UUID generation requires significantly more CPU clock cycles and complex mathematical entropy than simple auto-incremented integers.
- B UUIDs are stored as variable-length string literals, which are natively incompatible with the database's internal B-Tree comparison logic.
- C Random UUIDs cause 'Page Splits' and 'Cache Thrashing' as new rows are inserted into arbitrary locations in the B-Tree rather than at the end.
- D Standard relational databases do not natively support the indexing of 128-bit identifiers, forcing the use of inefficient and slow custom hash-functions.
Answer: C. Random UUIDs cause 'Page Splits' and 'Cache Thrashing' as new rows are inserted into arbitrary locations in the B-Tree rather than at the end.
Sequential IDs (like integers) allow the database to always append new data to the right-hand edge of the index, which is usually already loaded in the memory cache. In contrast, random UUIDs force the database to insert data into random 'pages' on the disk. If a page is full, the database must perform an expensive 'Page Split' to make room. This leads to massive disk I/O, index fragmentation, and poor memory utilization as the database constantly swaps pages in and out of RAM.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- Z-Order Curves (Geohashing) map 2D data to 1D strings for indexing. What is their primary weakness?
- In-Memory databases (Hyper, HANA) often favor Adaptive Radix Trees (ART). Why?
- Log-Structured Merge (LSM) Trees (RocksDB, Cassandra) optimize for Write Throughput. How does their disk IO pattern differ from B-Trees?
- In LSM compaction strategies, 'Universal' (Tiered) compaction prioritizes what over 'Leveled' compaction?
- In LSM trees, what is 'Space Amplification'?
- Why is it generally ineffective to index a 'Low Cardinality' column like `Gender` ('Male'/'Female')?