AI summary by TwinMind

The Problem With UUIDs

Theo - t3․gg

25m 53s

Video Introduction & Context

  • Third attempt at filming UUID video over 2 years
  • Previous attempts (couch filming, random stream) weren't satisfactory due to complexity
  • PlanetScale sponsored video but provided no input on content
  • Using PlanetScale blog post "The problem with using a UUID primary key in MySQL" as starting point

UUID Version History & Complexity

  • Currently 5 official versions (V1-V5) and 3 proposed versions (V6-V8)
  • V1 (Time-based):
    • Uses Gregorian calendar start date (October 10, 1568) instead of Unix Epoch (January 1, 1970)
    • Components: Time Low, Time Mid, Time High, Clock Sequence, Node
    • Timestamp grows in 100 nanosecond increments
    • Node identifies system generating UUID
  • V2:
    • Replaced low time segment with POSIX user ID for traceability
    • Increased collision chances, rarely used
  • V3 & V5:
    • Deterministic generation using namespace (itself a UUID) + name
    • V3 uses MD5 hashing, V5 uses SHA-1 hashing
  • V4 (Random):
    • Almost entirely random except version identifier
    • Most commonly used version
  • V6:
    • Nearly identical to V1 but flips timestamp bits (most significant first)
    • Attempt to make UUIDs sortable - failed
  • V7:
    • Time-based using Unix Epoch instead of Gregorian calendar
    • Replaces node with randomness for less trackability
  • V8:
    • Permits vendor-specific implementations
    • Barely a standard, more of a "vibe"

UUID Collision Reality

  • Universal uniqueness promise has failed multiple times
  • Random number generators aren't always truly random
  • Collision risk increases with poor randomness

MySQL Performance Issues with UUIDs

Insert Performance Problems

  • MySQL uses B+ tree indexes for primary keys
  • Sequential values (auto-incrementing integers) allow predictable tree balancing
  • Random UUIDs cause frequent page splitting and rebalancing
  • High-volume databases experience significant performance degradation

Storage Utilization Issues

  • Size comparison:
    • Auto-incrementing integer: 32 bits
    • UUID binary format: 128 bits (4x larger)
    • UUID string format: 288 bits (20x larger than integer)
  • Secondary indexes also consume more space (use primary key as pointer)
  • Page splitting with random keys reduces page utilization from 94% to as low as 50%

Best Practices for UUID Usage

Binary Storage

  • Store UUIDs in BINARY(16) format instead of CHAR(36)
  • Reduces storage from 36 characters to 16 bytes
  • Still larger than 32-bit integer but significantly better

Ordered UUID Variants

  • Use time-based versions (V6, V7) for better sequential ordering
  • Avoid V1 due to least significant timestamp portion being first
  • Sequential ordering mitigates page splitting issues

MySQL Built-in Functions

  • UUID_TO_BIN() function with swap flag
  • Reorders timestamp portion for better sequential behavior

Security Considerations

  • Sortable/incremental IDs create security vulnerabilities:
    • Unauthorized password resets via guessable IDs
    • Unauthorized access through ID enumeration
  • Counter-argument: If system can be compromised through ID knowledge alone, fix the authorization system, not the ID system

Alternative ID Systems

  • Snowflake IDs: Twitter's distributed ID system
  • ULIDs: Universally Unique Lexicographically Sortable Identifiers
  • Nano IDs: Smaller, customizable character sets
  • CUID V2: Collision-resistant unique identifiers (presenter's previous preference)

CUID V2 Concerns

  • Originally recommended for sortability and security
  • CUID V2 maintainers removed sortability feature citing security concerns
  • Presenter's stance changed during video after learning about performance implications

Performance vs Security Trade-offs

  • Sequential IDs: Better database performance, potential security risks
  • Random IDs: Better security, worse database performance
  • Need to balance based on specific use case requirements

TwinMind

Get summaries like this for your own meetings

TwinMind takes notes in the background during meetings, lectures, and calls, then writes the summary for you.

Free plan to get started

No bot joins your calls

Phone, desktop & Chrome apps

Your next meeting, summarized like this

TwinMind listens in the background and turns meetings, lectures, and calls into searchable notes you can chat with. Start free.

Available on phone, desktop & Chrome

Your next meeting, summarized like this

TwinMind listens in the background and turns meetings, lectures, and calls into searchable notes you can chat with. Start free.

Available on phone, desktop & Chrome

Your next meeting, summarized like this

TwinMind listens in the background and turns meetings, lectures, and calls into searchable notes you can chat with. Start free.

Available on phone, desktop & Chrome