Unique ID Generation: Snowflake, ULID & KSUID

Ordering fifty writers' IDs without one database bottleneck

A 5KB sketch plus a 100-entry heap finds every key above 990k of 100M events, yet every estimate assumes keys already arrive distinct and comparable. You create orders in 50 services at once. Auto-increment needs one database, which bottlenecks. UUID v4, meaning 122 random bits needing no coordination, is unique but random, so B-tree writes scatter and time scans fail. You need unique without coordination, roughly time-ordered, and index-friendly. Snowflake IDs, meaning 64-bit time-ordered numbers packing milliseconds plus worker plus sequence, sort with worker coordination; ULID, meaning 128-bit lexicographically sortable strings with time prefix plus randomness, sorts coordination-free; KSUID adds larger randomness for collision-proofing. Like mail rooms stamping time plus counter plus branch code, time orders while branch plus counter disambiguate.

K-sorted, meaning nearly time-ordered with small cross-writer skew, keeps B-tree appends near the right edge. Node assignment via ZooKeeper or etcd, both coordination services leasing worker slots, is Snowflake's only coordination.

Packing sign, time, worker, and sequence into 64 bits

Snowflake internals are one sign bit plus 41 milliseconds since a custom epoch, plus 10 worker bits, plus 12 sequence bits. Time moving forward keeps IDs roughly sorted for clustered-index locality, meaning sequential B-tree appends. Ten bits yield 1024 workers split datacenter plus worker; twelve bits yield 4096 IDs per millisecond per node near 4.1M per second, times 1024 nodes near 4B fleet-wide. Backward clocks stall or wait, covered next.

64-bit Snowflake:  0 | 41 bits millis since epoch | 10 bits node | 12 bits seq
  Time moves forward → IDs roughly sorted (good for clustered index locality)
  Node = datacenter + worker (10 bits → 1024 nodes)
  Seq increments per ms, wraps at 4096/ms → ~4M IDs/sec per node

If clock moves backwards: stall or use sequence overflow → wait for next ms

// ULID: 01H3... monotonic
const a = ulid(); // 01H3...
const b = ulid(); // 01H3... +1 lexicographically even if same ms

Unpacking 41 plus 10 plus 12 into years, workers, and throughput

Splitting one Snowflake integer shows every limit with one division. Forty-one bits hold 2 to the 41 milliseconds near 69.7 years, so a 2025 custom epoch lasts to 2094. Ten bits hold 1024 workers such as 5 plus 5 datacenter-worker splits. Twelve bits hold 4096 per millisecond per node near 4.1M per second. Lifetime volume near 8.7 times 10 to the 15 fits 2 to the 63 with room. ULID contrasts with 48-bit millisecond prefix in Crockford base32 plus 80 random bits: lexicographic sort, no assignment, longer strings.

Layout: 0 | 41 time | 10 node | 12 seq
  41 bits ms: 2^41 ms ≈ 69.7 years (custom epoch ~2025 → good until ~2094)
  10 bits node: 2^10 = 1024 workers (datacenter + worker split, e.g. 5+5)
  12 bits seq: 2^12 = 4096 IDs per ms per node
Throughput: 4096/ms = ~4.1M IDs/sec per node; 1024 nodes → ~4B IDs/sec fleet-wide
Lifespan check: 4M/sec × 69 years ≈ 8.7 × 10^15 < 2^63; fits with room.

ULID contrast: 48-bit ms (same Crockford base32 prefix) + 80-bit random
  → sorts lexicographically, needs NO node assignment, 1.5x the string length.

Name discipline: Snowflake-the-format means Parquet-style columnar files in lakehouses, Snowflake IDs from X-the-social-service mean 64-bit time-ordered integers, and Snowflake-the-warehouse means the cloud analytics service. Three different things sharing one word, so disambiguate every time.

Stalling on purpose when clocks rewind or milliseconds overflow

Snowflake assumes forward time and 4096 IDs per millisecond, while production violates both: NTP, the standard sync protocol, steps boxes 200ms backward after pauses, and flash sales fire 8,000 orders in one millisecond on one node. Both mint duplicates unless generators stall. Small skew under 10ms waits for wall time to catch the last timestamp; large jumps refuse to mint and page, since duplicates outrank downtime for order IDs; hardened Sonyflake or HLC hybrids, meaning logical counters folded into timestamps, order monotonically through steps.

Assigning 1024 worker slots without minting twin streams

Ten bits mean 1024 slots where two boxes in slot 7 mint identical streams forever. Workers claim ephemeral nodes, meaning auto-deleted lease entries, under paths like /snowflake/workers/007 in ZooKeeper or etcd, both coordination services, held by session and recyclable on expiry. The hazard is stale sessions: a partitioned old box still minting as 7 plus a reassigned new 7 duplicates until epoch fencing, meaning a generation bump killing the elder, intervenes. Scope reuse per datacenter with 5 plus 5 splits, consider Sonyflake 16-bit machine IDs rebuilt from private-IP hash with startup checks, and beyond 1024 writers switch to ULID, KSUID, or UUIDv7, all coordination-free sortable forms, instead of wider slots.

Boot: worker requests slot → ZK creates /snowflake/workers/007 (ephemeral)
  session alive → slot 7 owned; session dies → node deleted, slot recyclable
  stale-session hazard: old box (network-partitioned, still minting as 7) +
    new box (reassigned 7) → duplicate streams until fencing (epoch bump) kills one
Safer splits: 5 bits datacenter + 5 bits worker → slot reuse scoped per DC
  Sonyflake: 16-bit machine id (rebuild per deploy from private IP hash + startup check)
  boundary: >1024 writers → ULID/KSUID/UUIDv7 (no slots at all) instead of wider slots

Sorting UUIDv7 with standard UUID shape and no coordination

UUIDv7, meaning the time-ordered UUID variant with a 48-bit Unix-millisecond prefix, keeps 128-bit UUID shape every client library accepts while ordering like ULID. Layout is 48-bit unix_ms plus version plus 12-bit rand_a plus variant plus 62-bit rand_b, where rand_a increments monotonically same-millisecond and rand_b stays random. Seventy-four random bits need 2 to the 37 IDs per millisecond before 50% collision, so it never stalls. Trade is 16B versus Snowflake 8B for zero worker slots, and UUID hyphens versus ULID strings for legacy columns. Migrate new tables to v7 while leaving old v4 primary keys, since rewriting reorders whole tables.

UUIDv7 layout: 48-bit unix_ms | 4-bit ver(0111) | 12-bit rand_a | 2-bit var | 62-bit rand_b
  same-ms ordering: rand_a increments monotonically (like ULID), rand_b stays random
  1ms collision: 74 random bits → ~2^37 IDs per ms before 50% collision; never stalls
  vs Snowflake: no worker slots, no coordination, 2x the bytes (16B vs 8B)
  vs ULID: same time-prefix idea, UUID-shaped (hyphens, 36 chars) for legacy columns

Migration path: new tables default v7; old v4 PKs stay (rewriting PKs reorders the table).

Hiding order numbers competitors count and attackers enumerate

Order 10432 yesterday plus 10789 today publishes growth, while incrementing IDs enumerates every order. Sortable IDs leak order by design, so separate database keys from public identifiers: Snowflake or UUIDv7 inside for sortable joins and scans, 128-bit random tokens outside mapped in lookup columns. Rotating leaked externals never touches foreign keys. Hashids, meaning reversible obfuscating encodings, hide counts from casual eyes but not decoders, so authorization-adjacent identifiers use random indexed tokens rather than salted reversible codes.

Ordering cross-writer clicks before purchases with stamps beside IDs

Two servers minting ULIDs in one millisecond face 80 random bits that prevent collision but order randomly rather than causally. When feeds need click-before-purchase across writers, attach HLC stamps, meaning Hybrid Logical Clock triples of wall plus counter plus node, as secondary sort keys while ULIDs stay primary keys. Sort by wall, logical, id: click at (100, 4) precedes purchase at (100, 5) because the purchase carried the click's stamp plus 1. Identity stays coordination-free at 16 extra bytes per row; causal order rides alongside. IDs identify while clocks order, since one field doing both does neither well.

Same-ms ULIDs from servers A and B: 01H3…a9f2 vs 01H3…31c7; order is luck.
  feed sort key: (hlc_pt, hlc_l, ulid), click (100, 4, …a9f2) < purchase (100, 5, …31c7)
  causal because HLC traveled with the request (purchase carried click's stamp + 1)
  cost: 16 extra bytes per row, zero coordination; monotonicity without a master.
Rule: IDs identify, clocks order; one field doing both jobs does neither well.

Leasing thousand-ID ranges when one minter outruns its millisecond

Four thousand IDs per millisecond per node fails a single hot writer needing forty thousand. Segment allocation as in Leaf from Meituan, a database-backed range service, leases ranges such as 1000 to 1999 in one row update, mints locally with no clocks or slots until draining, prefetches next ranges async, and skips leased-but-unminted gaps on restart. Rejected alternative: one global Redis INCR holding every order ID. Each mint then costs a round trip near 0.5ms, one hot key caps near 100k IDs per second, and a Redis outage halts minting fleet-wide. The database sees one write per thousand IDs rather than one per ID, and NTP steps cannot duplicate leased integers. IDs trend upward with restart jumps: the price of clock independence.

Leaf segment mode: UPDATE segments SET max_id = max_id + 1000 WHERE biz='orders'
  minter holds [5000, 6000) → mints locally, no clock, no worker slots, no stalls
  range drains → next lease [6000, 7000); double-buffer: prefetch next range async
  trade: IDs trend upward but jump on restart (leased-but-unminted range is skipped)
  clock independence is the win; NTP steps cannot duplicate a leased integer range.

Migrating a live orders table from UUIDv4 to UUIDv7 without rewriting history

The orders primary key is UUIDv4, meaning random identifiers scattering B-tree writes, and insert throughput now fragments pages faster than autovacuum heals. The naive migration rewrites the primary key in place, which reorders the whole table, rebuilds every foreign key, and locks writes for hours. The working path leaves history alone: new rows mint UUIDv7, meaning time-ordered UUIDs appending near the right edge, old v4 rows stay where they are, and range scans over recent time read sequentially while old ranges stay scattered but cold. Backfill only when a hot old partition proves worth it, since rewriting primary keys reorders tables and the cure must beat the disease. Index fragmentation concentrates in the v4 era and stops growing the day v7 ships.

Table 500M rows, PK UUIDv4: inserts dirty random leaves, write amp ~2.5x, p99 climbing
  naive: ALTER PK to v7 → rewrite 500M rows + all FKs → hours locked, replicas lag
  forward migration: default v7 for new rows; old v4 rows untouched and cold
    recent-time scans (hot 10%) sequential; old-time scans scattered but rare
  backfill rule: rewrite a partition only if its query share × fragmentation cost
    exceeds rewrite cost; usually just the last quarter, never the whole table.

Stop auto-incrementing across datacenters

Snowflake packs 41 plus 10 plus 12 into 69 years and 4B IDs per second, and UUIDv7 keeps the sort with zero worker slots at 16 bytes. Yet every scheme sorts one dimension. What encodes two dimensions so drivers within 2km answer from 9 prefix scans?