The Desi Architect
Notes / Docker Sandboxes / UUIDv4 vs UUIDv7: B-Tree Index Fragmentation & WAL Bloat
Docker Sandboxes · Tier 2: Data & Scale · Lab 66

UUIDv4 vs UUIDv7: B-Tree Index Fragmentation & WAL Bloat

Watch 60,000 random UUIDv4 keys fracture your B-Tree index into half-empty leaf pages and flood the Write-Ahead Log. Switch to monotonic UUIDv7 keys to pack pages to 90 percent density, reclaim 21 percent of your index memory, and eliminate page splits.

Time30 minutes
StackPostgreSQL 16Ports 15455
MemoryAbout 120 MB
Before thisLab 15Database Indexing, Query Optimizat
Download lab (.zip) View steps
Verified runWindows, Mac, LinuxBash and PowerShell
  1. 1PrepareWhat you will learn
  2. 2PredictGuess before you run
  3. 3RunStep by step
  4. 4ObserveWhat you should see
  5. 5Break itChange one thing
  6. 6Clean upLeave nothing running
On this page
  1. 00 / What You Will Learn
  2. 01 / Why It Matters
  3. 02 / What We Are Building
  4. 03 / Words You Need
  5. 04 / Predict First
  6. 05 / Step by Step
  7. 06 / What You Should See
  8. 07 / Look Inside
  9. 08 / Break It, Fix It
  10. 09 / In Real Projects
  11. 10 / Interview Corner
  12. 11 / Clean Up

00 /What You Will Learn

By the end you will be able to
  • Explain how random UUIDv4 keys cause B-Tree leaf page splits.
  • Measure leaf page density and fragmentation using the PostgreSQL pgstattuple extension.
  • Compare Write-Ahead Log (WAL) volume generated by random vs monotonic insertions.
  • Understand how RFC 9562 UUIDv7 encodes timestamps and randomness to keep sequential insert order.
  • Articulate the production trade-off between right-edge latch contention and random page thrashing.

Before you start

Complete Lab 15 (Database Indexing) and Lab 28 (PostgreSQL MVCC Dead Tuples) first. You should know what a primary key index does and how PostgreSQL stores data on 8KB disk pages. Docker Desktop must be running.