What it is
The moment a design shards its database, AUTO_INCREMENT breaks — two shards will happily issue the same ID. The replacement needs four properties at once: unique across all nodes, roughly sortable by creation time (so cursors and merges work), compact (ideally 64 bits, because IDs end up in every index), and generated without a coordination round trip (because ID generation sits on the write path of everything).
Interviewers reach for this two ways: as an embedded requirement ("you've sharded — how do IDs work now?", short codes in a URL shortener, tweet IDs that must sort by time) and as a standalone question. The scoring is on whether you know the Snowflake bit layout cold and can defend it against its one real weakness: clocks.
I'd use Snowflake-style IDs — 64 bits: 41 of millisecond timestamp, 10 of machine ID, 12 of sequence — generated locally on each node with no coordination, time-sortable so my cursor pagination works, and I'll handle the backwards-clock case explicitly.
The picture
The variants and when to pick each
Database sequences — fine until the shard
A single database issuing auto-increment IDs is simple and perfectly ordered, and it's the right answer for an unsharded system — say that, don't skip past it. It fails as a single point of failure and a write bottleneck, and it cannot span shards.
The historical patch is multi-master increment: k servers each issue IDs congruent to i mod k — Flickr ran two ticket servers, one issuing odd, one even. It scales writes a little, but IDs are no longer time-ordered across servers and adding a server means re-numbering the scheme. Know it as trivia; don't propose it.
UUIDs — v4 is a trap for primary keys, v7 fixes it
UUIDv4 is 128 random bits: zero coordination, generate anywhere including on the client. Two costs: it's twice the storage of a bigint (16 bytes vs 8, multiplied through every index and foreign key), and it's random — inserts land at random positions in a B-tree, wrecking page locality, causing page splits and cache misses. This is why UUIDv4 primary keys are notoriously slow in MySQL.
UUIDv7 (RFC 9562, 2024) fixes the killer problem: a 48-bit millisecond timestamp prefix followed by randomness, so inserts are sequential-ish and B-trees stay happy. If I can afford 128 bits, UUIDv7 is the modern zero-infrastructure default; if I want 64, Snowflake.
Snowflake — the star answer, so know the bits
64 bits: 1 sign bit, 41 bits of millisecond timestamp from a custom epoch (~69 years of range), 10 bits of machine ID (1,024 nodes), 12 bits of sequence (4,096 IDs per millisecond per node, ~4M/s theoretical). Each node generates locally — no network call, no lock, no coordination at generation time. IDs are k-sorted: globally ordered to within clock skew, which is exactly enough for cursor pagination and merging timelines.
The two operational costs: machine IDs must be assigned safely (ZooKeeper, or config management with a startup uniqueness check — two nodes sharing an ID silently produce duplicates), and the whole scheme trusts the clock. Instagram embedded a variant in Postgres (41 time / 13 shard / 10 sequence); MongoDB's ObjectId is the same idea in 12 bytes. Naming a variant shows this is understanding, not memorisation.
Range allocators and the URL-shortener special case
A segment allocator (Meituan's Leaf) has a central store lease blocks of IDs — say 1M at a time — to app servers, which then issue from the block in memory. The database is touched once per million IDs, so it's no longer a bottleneck; the cost is that IDs are only roughly time-ordered and a crashed server burns its unused block. A good answer when you want small dense IDs without per-ID coordination.
For a URL shortener, the ID becomes user-visible, so encode it: base62 over a counter or Snowflake ID gives 62^7 ≈ 3.5 trillion codes in 7 characters. The alternative — hash the URL and handle collisions — costs a read-before-write on every insert. Counter plus base62 is cleaner; the give-up is that sequential codes are enumerable, so add a random offset or bijective scramble if scraping matters.
Numbers worth memorising
| Snowflake layout | 41 time + 10 machine + 12 sequence (+1 sign) |
| Snowflake capacity | 4,096 IDs/ms/node ≈ 4M/s; 1,024 nodes; ~69 yr epoch |
| UUID vs bigint storage | 16 bytes vs 8 (36 chars as text) |
| UUIDv4 collision odds | ~50% only after ~2.7 × 10¹⁸ IDs — ignore it |
| Base62 short codes | 6 chars ≈ 57B, 7 chars ≈ 3.5T combinations |
Where the interviewer pushes
The push is always the clock: "your Snowflake node's clock jumps back 30 seconds — what happens?" The failure is real: the node would re-issue timestamp+sequence pairs it already used, producing duplicate IDs. The strong answer: detect it (last-issued timestamp is remembered), then refuse to generate — either error out, or wait until the clock catches up if the skew is milliseconds. Never generate through it. And prevent it operationally: NTP configured to slew (smear) rather than step the clock backwards.
Second push: "why do tweet IDs need to be time-sortable at all?" Because everything downstream leans on it: cursor pagination pages by ID alone, and merging many users' timelines into one feed becomes a merge of sorted streams by ID — no timestamp column needed, no separate sort. If IDs were random, every feed read would join against a timestamp and re-sort. Sortability isn't a nice-to-have; it's what makes the read path cheap.