UUID v7: 23x Faster Inserts, PostgreSQL 18

 PostgreSQL 18: 23x Faster Inserts With UUID v7 | Software Engineer, Author, High Performance PostgreSQL for Rails

This article by Andrew Atkinson highlights how migrating database primary keys to UUID v7 in PostgreSQL 18 improved insert performance up to 23x for high-volume tables.

• Performance Improvements: Multi-row insert queries saw dramatic reductions in execution time:
  • Table A: Reduced execution time from 0.7ms to 0.03ms (23x speedup) at 12,000 calls/min.
  • Table B: Reduced from 0.6ms to 0.07ms (9x speedup).
  • Table C: Reduced from 0.5ms to 0.08ms (6x speedup).
    
    
• Why UUID v7 Performs Better: Unlike UUID v4 (purely random) or UUID v1, UUID v7 is time-ordered (monotonically increasing). This keeps the primary key B-Tree index pages "hot" in the Postgres buffer cache, reducing disk I/O, CPU overhead, and index page splits.
  
  
• Migration Strategy & Challenges:
  • Changing column defaults using ALTER TABLE ... ALTER COLUMN ... SET DEFAULT uuidv7() requires an Access Exclusive Lock, which blocks all reads and writes.
  • To avoid downtime on active tables, the author used short lock_timeout values (e.g., 50–100ms) paired with a PL/pgSQL retry loop with jittered backoffs (50–250ms) to acquire the lock during tiny traffic lulls.
    
    
• Trade-offs: UUID v7 embeds a timestamp in the initial bits, meaning record creation times can be decoded from the ID. If hiding record creation timestamps is a strict security requirement, UUID v4 is still preferred.