Primary Key Clustering Cost Lab (Interactive)
Insert 2,000 rows with BIGINT, UUIDv4, or UUIDv7 keys and watch page splits pile up. Simulate a real clustered B+Tree page cache under random vs sequential primary keys to see fragmentation, page splits, write amplification, and cache-hit collapse.
Clustered Index Insert Physics: Pick Your Key
Insert 2,000 rows per click into a simulated B+Tree with an LRU buffer pool — random keys pay in splits.
Random inserts read 0 cold pages into the 512-page buffer pool and forced 0 50/50 page splits → fill factor 100%. Half-empty pages = 2× the disk, 2× the tree height.
How It Works Under the Hood
In a clustered table the rows are physically stored in primary-key order, so the key you choose dictates write locality. Sequential auto-increment BIGINTs always append to the right-most leaf page: full pages, few splits, warm cache. Random UUIDv4 inserts land in effectively random leaves, spreading every burst across the whole tree — each write into a full page splits roughly 50/50, halving fill factor and thrashing the page cache. UUIDv7 and Snowflake IDs prefix a millisecond timestamp, restoring time-ordered appends while keeping distributed uniqueness.
Core Architectural Principles
- A clustered index means the primary key determines physical row order; inserts into full leaf pages force roughly half-and-half page splits.
- Random UUIDv4 keys scatter writes across thousands of hot pages, tanking fill factor, cache hit rate, and write amplification.
- UUIDv7 and Snowflake IDs embed a leading timestamp, giving globally unique, generation-local keys with near-sequential append behavior.
When choosing a key for a high-write table, quantify: random UUID inserts cause page splits on most writes and bloat every secondary index, since those store the PK as their row pointer. Auto-increment is compact but bottlenecks distributed sharding; UUIDv7 gives local generation and locality. Mention secondary-index PK storage cost to show you understand the full cascade.
Compact sequential keys maximize B+Tree locality but centralize generation; random global keys enable distributed inserts at the price of fragmentation.