Distributed ID Generation: Snowflake, ULID, and the Clock-Skew Problem

AUTO_INCREMENT is one of those features you only appreciate after you lose it. The moment you shard a database or accept writes in two regions, a single counter that hands out 1, 2, 3 stops existing — and it turns out an ID scheme has to satisfy more constraints than "unique": generation must not require coordination on the hot path, IDs should sort roughly by creation time (your indexes and your pagination care), they must fit in 64 bits if you want cheap joins, and they should not leak business intelligence. That last one is real: sequential order IDs let a competitor place one order per week and read your growth rate off the IDs — the classic German tank problem.

Why not just UUIDv4?

Random UUIDs are coordination-free and collision-proof, which is why they are everywhere. Two problems at scale:

Index locality. A B-tree clusters nearby keys on the same pages. Random 128-bit keys scatter every insert to a random page — every insert touches a cold page, the working set becomes the whole index, page splits multiply, and your buffer cache stops caching anything useful. On a table doing thousands of inserts per second, switching the primary key from UUIDv4 to a time-ordered ID is one of the highest-leverage index optimizations available.

Size. 128 bits versus 64 doubles the footprint of every foreign key and every secondary index entry that carries the primary key.

Snowflake: 64 bits, three fields

Twitter's Snowflake layout is the canonical answer, and every large company runs some variant of it:

 63          62........22  21........12  11...........0
+---+---------------------+-------------+---------------+
| 0 |  timestamp (41 bit) | worker (10) | sequence (12) |
+---+---------------------+-------------+---------------+
  41 bits of milliseconds since a custom epoch  → ~69 years
  10 bits of worker ID                          → 1,024 generators
  12 bits of per-millisecond sequence           → 4,096 IDs/ms/worker

Timestamp in the high bits makes IDs sort by creation time — new rows append to the right edge of the B-tree, range scans by time become primary-key scans, and cursor pagination gets a natural key. Each worker generates locally with zero coordination: read the clock, bump the sequence, done. Ceiling: about 4 million IDs per second per worker.

The two problems Snowflake creates:

Worker ID assignment. Two generators sharing a worker ID silently mint duplicates. You need each instance to hold a unique 0–1023 identity: leased from etcd/ZooKeeper at startup, derived from a StatefulSet ordinal, or assigned from instance metadata. This is the operational cost of the scheme — uniqueness now depends on ops discipline rather than math.

Clock skew. The generator trusts the machine clock, and NTP can step a clock backwards. A generator that already issued IDs for time T, then sees T minus 30ms, will re-issue colliding IDs. Standard defenses: track the last timestamp used and refuse to go backwards (wait out small regressions, alarm and halt on large ones), and run clock-sanity checks against peers. Sonyflake and others trade bit widths for more tolerance. If you remember one failure mode from this post, make it "NTP stepped the clock back and the ID generator kept going."

ULID and UUIDv7: the same idea, standardized

ULID (48-bit timestamp + 80 random bits, Crockford base32) and UUIDv7 — now standardized in RFC 9562 — apply the identical trick at 128 bits: time in the high bits for sort order and index locality, randomness in the low bits for coordination-free uniqueness with no worker registry at all.

Scheme	Bits	Sorted?	Coordination	Notes
Auto-increment	64	Yes	Single node	Dies at sharding; leaks volume
UUIDv4	128	No	None	Index locality pain
Snowflake	64	Yes	Worker registry	Clock skew is your problem
ULID / UUIDv7	128	Yes	None	The easy default in 2026
DB ticket server	64	Roughly	Central service	Flickr-style; SPOF to manage

My default advice: UUIDv7 unless you have a reason — native support landed in Postgres 18, and it deletes the worker-ID problem entirely. Reach for Snowflake when 64-bit keys measurably matter (storage-heavy tables, join-heavy workloads) and you are prepared to operate worker identity properly. The ticket-server pattern (a MySQL instance whose only job is REPLACE INTO on an auto-increment, made HA with two servers issuing odd/even) is a legitimate low-tech middle ground.

Two fine-print items

Time-ordered IDs recreate hot partitions. If the ID is also your shard key under range partitioning, all new writes land in the newest range — the exact skew problem time-ordered keys cause everywhere. Hash the ID for placement, or shard on a different key.

Rough order, not order. Snowflake IDs order by generator clock, not by any global truth; two IDs minted 2ms apart on different workers can invert. Fine for feeds and pagination; not fine as a causality or concurrency-control mechanism. If "which write happened first" has correctness consequences, you need versions or consensus, not timestamps in IDs.

Takeaways

ID generation is a miniature distributed-systems problem: uniqueness without coordination, ordering without a global clock. Time-in-high-bits is the trick that makes IDs both unique and index-friendly, whether spelled Snowflake, ULID, or UUIDv7. Choose UUIDv7 by default, Snowflake when 64 bits pay rent, and in either case decide before the incident what happens when the clock goes backwards — because eventually it will.