How do you design a distributed unique ID generator (Twitter Snowflake vs. UUIDv7)?

In distributed databases spanning multiple server nodes, relying on a central database AUTO_INCREMENT column creates a fatal single point of failure and write throughput bottleneck. Generating unique, time-ordered 64-bit or 128-bit IDs across hundreds of machines requires dedicated architectural patterns.\n\n### 1. Requirements for Distributed IDs\n1. Uniqueness: Zero collisions across millions of nodes.\n2. Roughly Time-Sortable (Monotonic): IDs must sort chronologically so database B-Tree indexes remain compact and append-friendly.\n3. Compact Size: Prefer 64 bits to fit into standard integer database primary keys.\n4. High Throughput: Capable of generating 50,000+ IDs per second per node.\n\n### 2. The Twitter Snowflake Architecture (64-Bit ID)\nSnowflake divides a 64-bit integer into 4 distinct segments:\n\n\n[1 bit: Sign] [41 bits: Millisecond Timestamp] [10 bits: Machine/Worker ID] [12 bits: Sequence Number]\n\n* 1 Bit: Unused sign bit (always 0 to keep integers positive).\n* 41 Bits: Milliseconds since a custom epoch (e.g., Nov 4, 2010). $2^{41} \text{ ms} \approx 69 \text{ years}$ of unique IDs.\n* 10 Bits: Configured Worker/Datacenter ID ($2^{10} = 1,024$ distinct worker nodes).\n* 12 Bits: Sequence counter ($2^{12} = 4,096$ unique IDs per millisecond per node = 4.09M IDs/sec per machine).\n\n### 3. The Clock Skew Challenge\nBecause Snowflake relies on local system clocks, Network Time Protocol (NTP) adjustments can move the clock backward:\n* Mitigation: If the clock moves backward, the worker either waits until the clock catches up to the last recorded timestamp or rejects generation and alerts operations.\n\n### 4. UUIDv4 vs. UUIDv7 in Production Databases\n* UUIDv4 (Completely Random): Random bits cause massive B-Tree index page splitting in PostgreSQL/MySQL, shattering cache locality and bloating index size by up to 300%.\n* UUIDv7 (Unix Timestamp + Random): Embeds a 48-bit millisecond timestamp in the high bits, guaranteeing chronological ordering while preserving 128-bit uniqueness without a central generator service.\n\n### Key Architectural Comparison\n| ID Generator | Bit Length | Chronologically Sortable? | Coordination Required | B-Tree Index Health |

| Twitter Snowflake | 64 bits | Yes (Millisecond prefix) | Machine ID assignment | Optimal (Fits in standard bigint) |\n