Course contentsShow
← System Design
Lesson 1520 of 1,91937. Real-World System: URL ShortenerFree lesson

Primary Key Selection: Auto-Increment vs UUID

Compare auto-increment IDs versus UUIDs for the URL table, considering distributed writes and collision avoidance.

Primary Key Selection: Auto-Increment vs UUID

You'll learn: How to choose between auto-increment integers and UUIDs as primary keys for your URL shortener's database, considering distributed systems and collision risks.

The Core Tradeoff

In a URL shortener, every URL mapping needs a unique identifier. You have two main options: auto-increment IDs (1, 2, 3, ...) or UUIDs (universally unique identifiers like 550e8400-e29b-41d4-a957-146564174000).

Think of auto-increment as a ticket dispenser at a deli counter—one machine issues sequential numbers. UUIDs are like everyone generating their own random ticket numbers so unique that collisions are astronomically unlikely.

Auto-Increment Advantages

  • Compact: Small integer keys (4-8 bytes) mean less storage and faster indexes
  • Ordered: New entries append to the end, improving B-tree index performance
  • Human-friendly: Easy to read and debug

Challenges: In distributed systems, coordinating a global counter becomes a bottleneck. You need distributed ID generation (like we covered earlier with Snowflake-style approaches) to avoid every write hitting a single coordination point.

UUID Advantages

  • Zero coordination: Any server can generate UUIDs independently
  • Truly distributed: No single point of contention for ID generation
  • Collision-free: Practically guaranteed uniqueness across all nodes

Challenges: UUIDs are 128 bits (16 bytes), causing index bloat and random insertion patterns that fragment B-tree indexes, hurting performance.

For URL Shorteners

Most URL shorteners prefer auto-increment or Snowflake-style IDs because the short URL itself is derived from this ID via Base62 encoding. Sequential IDs keep short URLs compact and predictable in length. UUIDs would produce unnecessarily long encoded strings, defeating the "short" part of URL shortener.

Key Takeaway: Choose auto-increment (with distributed coordination like Snowflake) for URL shorteners to keep encoded URLs short and indexes efficient; use UUIDs when true decentralized generation without coordination matters more than storage efficiency.