The Distributed Identifier Challenge
In a single-instance relational database, generating unique primary keys is trivial via auto-incrementing sequences (e.g., PostgreSQL BIGSERIAL or MySQL AUTO_INCREMENT). However, in horizontally partitioned distributed architectures, generating unique IDs across dozens of database shards introduces severe architectural bottlenecks:
- Single Point of Failure (SPOF): A centralized database generating auto-increments creates a throughput ceiling and a single point of failure.
- Information Leakage: Monotonically incrementing sequential IDs (1, 2, 3...) expose sensitive business intelligence (competitors can monitor order ID changes to calculate daily revenue). Time-ordered IDs such as Snowflake and UUIDv7 also leak each record's creation timestamp.
- Cross-Shard Uniqueness: When independent database shards insert records concurrently without central coordination, duplicate IDs will corrupt data.
Core Approaches to Distributed ID Generation
Detailed Trade-offs of Generation Patterns
1. Multi-Master Increment with Offsets (MySQL Pattern)
If you have k database instances, instance 1 generates 1, 1+k, 1+2k, instance 2 generates 2, 2+k, 2+2k, etc. While simple, this does not scale dynamically when adding servers, nor does it guarantee time-ordering.
2. UUIDv4 (Universally Unique Identifier)
Generates 128-bit pseudo-random values. Generation requires zero network coordination (O(1) locally). However, UUIDv4 values are completely random, causing severe B-Tree Index Page Splitting and high storage overhead (16 bytes vs 8 bytes for BIGINT).
3. Time-Sorted 64-Bit IDs (Twitter Snowflake)
Encodes millisecond timestamps, physical node identifiers, and in-millisecond sequence numbers into a single 64-bit integer. It fits directly into standard BIGINT fields, requires zero network coordination during generation, and sorts naturally in B-Tree indexes.