Appearance
11.5 — Distributed Unique ID Generator
A team ships a feature and, three weeks later, notices that inserts into their main table have got slower every single day since the deploy. Nothing in the query changed. The table grew, but it grew before too. What changed is that the new table uses random 128-bit identifiers as its primary key, so every insert lands at a random position in the index instead of at the end of it — and now that the index no longer fits in memory, every insert is a disk read.
That is what an identifier choice costs when it is made casually. The identifier decides whether database writes spread out or pile up, whether "newest first" is a cheap index scan or an expensive sort, whether outsiders can read your business volume off your URLs, and whether a clock correction quietly produces duplicate primary keys. It is the smallest system in this book and the one with the largest blast radius, because every other table in the product has a copy of it in a foreign key.
1. Requirements
Functional. Generate unique identifiers at 100,000 a second or more across a fleet of servers. The identifiers should be roughly sortable by creation time, so that "show me the newest" is a range scan rather than a sort. Generation must need no coordination on the hot path.
Non-functional, with numbers.
- Under 1 millisecond to generate, locally. This runs inside every write in the product, so any network call here is a network call added to every write.
- Uniqueness that survives process restarts and clock adjustments. Not "uniqueness in the normal case".
- No identifier reveals business volume to anyone who can see it.
- 64 bits if possible, because the identifier is repeated in every index and every foreign key in the database, and the difference between 8 and 16 bytes is multiplied by every row and every reference.
Out of scope today: total global ordering, which is impossible without coordination and is a different problem (10.3), and identifiers used as security tokens, which is a separate requirement discussed in section 6.
The clarifying questions, and what each answer changes
"Will this identifier be visible to users?" If it appears in a URL or an API response, then whatever it encodes is public. A time-ordered identifier publishes its creation time and, worse, lets anyone compare two of them to estimate how many things you created in between. That is a business-intelligence leak, and it turns one identifier into two — an internal one and a public one.
"Is this the clustered primary key?" In engines where the table is physically stored in primary-key order, a random key does not only fragment an index, it scatters the rows themselves, so fetching the last hundred orders becomes a hundred random disk reads instead of one sequential one. If the answer is yes, monotonicity is not a nice-to-have.
"How will the table be sharded?" This is the question people ask last and should ask first, because the property that makes time-ordered identifiers good for indexes makes them bad for sharding: shard by identifier range and every new write goes to the newest shard.
"Can we operate a small registry that hands out worker numbers?" If yes, 64-bit Snowflake identifiers are available. If no — a small team, no coordination store, no stable replica numbering — then 128-bit time-ordered identifiers are the honest answer and the extra eight bytes are the price of needing no infrastructure.
"What is the peak burst, not the average?" 100,000 a second average tells you nothing about whether a single worker can exhaust its per-millisecond allowance. The arithmetic in section 2 shows why the answer is almost always comfortable, and it is worth doing rather than assuming.
2. Estimation
Throughput per worker. A Snowflake identifier reserves 12 bits for a sequence counter that resets every millisecond, which is 4,096 identifiers per millisecond per worker, or 4.096 million per second per worker. What that forces: nothing. The requirement is 100,000 a second across the entire fleet, which one worker could serve forty times over. This design is not about throughput, it is about correctness — and saying that out loud early stops the conversation from drifting into a scaling discussion that has no content.
The time field. 41 bits of milliseconds is 2⁴¹ = 2.2 × 10¹² milliseconds ≈ 69.7 years from whatever epoch you choose. What that forces: choose a custom epoch near the project's start rather than 1970, or you have already spent 56 of those years. And write the epoch and its exhaustion date in a comment, because the person who has to deal with it has not been born into the company yet.
The worker field. 10 bits is 1,024 distinct workers. What that forces: the registry must cover processes, not machines, if the generator is embedded in a library — twenty services with fifty replicas each is a thousand processes, and you are at the ceiling. This is the arithmetic that decides between the embedded and the sidecar deployment in section 5, and it is usually skipped.
Storage cost of the identifier itself. A table with one billion rows, a primary key and four secondary indexes that each carry the primary key as their pointer, plus three other tables holding it as a foreign key. At 8 bytes that identifier occupies roughly 8 GB per copy, so about 64 GB across all copies. At 16 bytes it is 128 GB. What that forces: the 64 GB difference is not a rounding error — it is memory that the buffer pool no longer has for actual data, which is a latency cost on every query in the database. This is the number that justifies the extra machinery of a worker registry, and if the number is small for your product, so is the justification.
Cost of randomness, which is the number people never compute. Sequential inserts touch one index page repeatedly — the rightmost one — so the working set for writes is a handful of pages regardless of table size. Random inserts touch a different page each time, so the working set for writes is the entire index. Once the index exceeds memory, every insert becomes a disk read, and inserts get slower every week as the table grows. What that forces: monotonicity in the high bits, by whatever mechanism. This is the requirement behind the entire design.
3. The five options, honestly compared
| Approach | Size | Time-sortable | Coordination | The real cost |
|---|---|---|---|---|
| Database auto-increment | 8 B | yes | every insert | one writer; hard to shard; leaks volume |
| UUIDv4 (random) | 16 B | no | none | random index writes; the problem at the top of this page |
| UUIDv7 / ULID | 16 B | yes | none | 16 bytes in every index and foreign key |
| Snowflake | 8 B | yes | worker-number assignment only | depends on the clock; needs a registry |
| Ticket / segment server | 8 B | yes | one batch per N identifiers | a service to run; gaps in the sequence |
Auto-increment is what a single database gives you free. It is compact, perfectly ordered, and it has exactly one writer — so it cannot be sharded without either colliding or reintroducing a central allocator. It also publishes your volume: a customer who creates an order on Monday and another on Friday can subtract the two numbers.
UUIDv4 is 122 random bits. It needs nothing, it will never collide in practice, and it is the wrong choice for a clustered primary key for the reasons in section 2. It remains a fine choice for identifiers that are not the storage key — an idempotency key, a request identifier, a session token.
UUIDv7 and ULID keep the 128-bit shape and universal tool support but put a millisecond timestamp in the high bits, so inserts are sequential again. This is the zero-infrastructure answer, and it is the right default for most teams. What you pay is eight extra bytes, everywhere, forever.
Snowflake packs a timestamp, a worker number and a per-millisecond counter into 64 bits. Half the storage, time-ordered, no coordination at generation time. What you pay is a registry that hands out worker numbers and a dependency on the clock behaving.
A ticket or segment server hands each process a block of, say, 10,000 numbers, which it then issues locally. Coordination drops by the block size, identifiers stay dense and small, and the visible cost is gaps — a process that restarts abandons the rest of its block, so the sequence has holes. That is only a problem if someone believed the numbers were consecutive, which is exactly why the gaps are worth mentioning before someone builds a report on the assumption.
The recommendation: Snowflake-style 64-bit identifiers when index size matters and you can run a registry; UUIDv7 otherwise, because its time-ordered prefix fixes the only fatal problem with UUIDv4 and it needs no infrastructure at all.
4. Snowflake, bit by bit
ts
const EPOCH = 1735689600000; // (1) 2025-01-01, chosen once
class Snowflake {
private seq = 0;
private lastMs = -1;
constructor(private readonly workerId: number) { // (2)
if (workerId < 0 || workerId > 1023) throw new Error('worker id out of range');
}
next(): bigint {
let ms = Date.now();
if (ms < this.lastMs) { // (3)
throw new ClockWentBackwards(this.lastMs - ms);
}
if (ms === this.lastMs) {
this.seq = (this.seq + 1) & 0xfff; // (4)
if (this.seq === 0) ms = this.spinUntilAfter(this.lastMs); // (5)
} else {
this.seq = 0; // (6)
}
this.lastMs = ms;
return (BigInt(ms - EPOCH) << 22n) // (7)
| (BigInt(this.workerId) << 12n)
| BigInt(this.seq);
}
}(1) the epoch is a constant chosen once and never changed. Changing it after the system has issued identifiers reorders history, because old identifiers were built against the old epoch. (2) the worker number arrives from outside, and the constructor refuses an out-of-range value rather than silently masking it — a worker number of 1,024 masked to 10 bits becomes 0, which collides with a real worker and produces duplicates. (3) the one condition that can produce duplicate identifiers is the clock going backwards, so the generator refuses rather than risking it. Section 6.1 argues why refusing is right. (4) the sequence wraps within 12 bits. Two identifiers in the same millisecond differ in this field. (5) 4,096 identifiers inside one millisecond exhausts the sequence, so the generator waits for the clock to advance. That is a bounded wait of under a millisecond, and it is correct. (6) a new millisecond resets the sequence. Some implementations reset to a small random value instead, which spreads identifiers slightly and costs a little of the per-millisecond capacity — worth knowing exists, not worth doing by default. (7) the whole identifier is three shifts and two bitwise ORs on local state. No lock, no allocation, no I/O, well under a microsecond. That is the property that lets this sit inside every write in the product.
5. Deployment, and the only piece that needs coordination
Embedded as a library gives the fastest possible generation — the identifier is produced inside the process that needs it, with no network involved. The cost is that the worker number must be unique per process, so a fleet of twenty services with fifty replicas each needs a thousand distinct numbers, which is most of the 1,024 available.
As a sidecar — a tiny process on each host, called over the local loopback — costs about 0.2 ms per identifier and reduces the registry's job to one number per host. For most fleets that is a far smaller number and a far simpler problem, and 0.2 ms sits comfortably inside the 1 ms budget.
The worker-number registry is the only coordination in the design, and it runs once at startup. Three ways to do it:
A short-lived registration in a small coordination store — a service that holds a key which disappears automatically if its holder stops renewing it. The process claims the lowest free number, renews it while it lives, and the number is released if it dies or stalls.
A stable replica index from the deployment system, if yours gives each replica a fixed number that survives restarts. That index is a worker number, for free, and it is the most elegant option available when it exists.
A lease from a small service, with an expiry the holder must renew.
The failure to design against is two live processes holding the same worker number, because that produces duplicate identifiers silently — no error, no log line, just two rows that eventually collide on a unique constraint somewhere unrelated. This is why the number must be a lease that a stalled node loses, and never a configuration value that a stalled node keeps. A process partitioned from the registry for thirty seconds must stop generating, because the registry has by then handed its number to someone else.
And the corollary that has bitten real systems: if the registry is unreachable at startup, fail to start. Do not default to worker 0. A deployment where the registry was briefly down and three processes all defaulted to 0 is the single most common way this design produces duplicates.
6. Deep dives
6.1 The clock, which is the whole risk
The generator's correctness rests on one assumption: the clock never goes backwards. If it does, the timestamp field repeats a millisecond that has already been used, and because the sequence counter resets whenever the millisecond changes, the generator will re-emit identifiers it has already issued.
That is unrecoverable. Not "a bug" — unrecoverable. Duplicate primary keys mean rows overwriting each other or inserts failing in code paths that have nothing to do with each other, discovered days later, with no way to determine after the fact which identifiers were issued twice.
So the generator refuses. On detecting that now is earlier than the last issued millisecond, it throws, the write fails with a retryable error, and the node raises an alarm. The reasoning is a clean trade stated out loud: a few seconds of failed writes is an incident; duplicate primary keys are data loss.
Prevention, in order of how much it helps. Run time synchronisation in a mode that adjusts the clock's rate rather than stepping it, so a correction plays out as the clock running slightly slow for a while instead of jumping backwards. This alone eliminates the common case. Use a monotonic clock source where the platform offers one, for anything measuring elapsed time (10.3). And be aware that virtual machines can jump clocks in ways no time-synchronisation configuration prevents — a machine suspended and resumed, or migrated between physical hosts, can come back with a clock that moved in either direction. That is a real argument for the sidecar deployment on hosts you control.
Bounded waiting is acceptable for small skews. If the clock is 3 ms behind, spinning until it catches up is fine. If it is five minutes behind, waiting is an outage wearing patience as a disguise. So the wait needs a ceiling, and past the ceiling the answer is to refuse and alarm.
And the defence in depth: put a unique constraint on the identifier column. If every argument above is somehow wrong, the database converts silent corruption into a loud error, which is a much better failure.
6.2 What a time-ordered identifier tells the world
A Snowflake identifier is not opaque. Anyone holding one can extract its creation timestamp by shifting right 22 bits and adding the epoch — and the epoch is guessable from two identifiers with known creation times.
Worse, two identifiers from the same worker reveal how much happened in between. A competitor who signs up on the first of the month, notes their order identifier, signs up again on the last of the month and notes the second one can estimate your monthly order volume from the difference. This is the classic sequential-identifier business leak, and a Snowflake identifier obscures it only slightly, because the worker and sequence fields are in fixed positions.
The fix is to stop asking one identifier to do two jobs. Keep the Snowflake as the internal primary key, where its ordering and size are earning their keep. Add a separate public identifier — a random string, as in 11.1 — for anything that appears in a URL or an API response. It costs one column and one unique index, and it removes the entire class of enumeration and inference problems at once.
And the rule that must be said every time identifiers come up: an identifier is never an authorisation token. Unguessability reduces the attack surface; it does not grant access. Every request still checks that the caller is allowed to see the object, regardless of how they came by its identifier (9.9.5).
6.3 The property that helps indexes hurts sharding
Here is the tension at the centre of this design, and it is worth stating as a tension rather than as two separate facts.
A time-ordered identifier makes index inserts sequential, which is the whole point: every new row goes at the end of the index, one hot page, no fragmentation.
That same property means every new row goes to the same place. Shard a table by identifier range and all writes land on the shard that owns the newest range, while the other shards sit idle holding history. You have built a distributed database with one active node (10.6).
Two fixes, and choosing between them is a real decision.
Shard by a hash of the identifier. Writes spread perfectly. You lose range scans, so "all orders created last Tuesday" becomes a query against every shard.
Shard by something else entirely — user, tenant, account — and keep the time-ordered identifier only for ordering within a partition. This is why every earlier study in this Part partitions by owner rather than by identifier, and it is almost always the right answer, because the queries a product actually runs are scoped to a customer far more often than to a time range.
6.4 What "roughly sortable" actually means
It is worth being precise, because candidates often overclaim here.
Two identifiers from the same worker are strictly ordered: the timestamp is non-decreasing and the sequence breaks ties, so a later identifier is always numerically larger.
Two identifiers from different workers are ordered only as well as their clocks agree. If worker A's clock is 4 ms ahead of worker B's, then an event on B that genuinely happened first can carry a smaller timestamp and therefore a larger... no — carry a smaller timestamp and therefore sort earlier than it should, or sort later, depending on the direction of the skew. The honest statement is: identifiers are ordered to within the clock skew of the fleet, which is a few milliseconds with working time synchronisation.
This matters in exactly one place, and it is a place people trip over: using the identifier as a pagination cursor. WHERE id > :lastSeen ORDER BY id looks like a strict cursor and is not, because an identifier smaller than lastSeen can still be inserted after you read that page — by a worker whose clock was slightly behind. The row is then skipped forever. The fix is the same one pagination always needs: order by a tuple that is genuinely unique and stable, and accept that "recent" is approximate (9.6.2).
6.5 When a ticket server is the better answer
Snowflake removes coordination by giving each worker its own space in the identifier. A ticket server removes coordination a different way: it hands out blocks.
A process asks the ticket server for a range and receives, say, 40,000 to 49,999. It issues those locally, and when it is running low it asks for the next block. Coordination happens once per 10,000 identifiers instead of once per identifier, which is a 10,000-fold reduction and quite enough for most systems.
What you get: identifiers that are dense small integers, no clock dependency whatsoever, and no worker registry — the ticket server is the registry, and it is a much simpler thing to operate than a lease protocol.
What you pay: a service that must be available (though only once per block, so a brief outage is invisible), and gaps. A process that restarts abandons the rest of its block, so the sequence has holes of up to 10,000. That is only a problem if somebody believed the numbers were consecutive — and somebody usually does, which is why "identifiers have gaps and that is by design" belongs in the documentation rather than in a support conversation.
Choose the ticket server when the clock dependency is what worries you, or when you want dense identifiers for a human-facing reference number. Choose Snowflake when you want zero infrastructure on the request path.
7. Decision Ledger
| Decision | Alternatives | Why this | What it costs |
|---|---|---|---|
| 64-bit Snowflake | UUIDv4; UUIDv7; database sequence | half the bytes of a UUID in every index and foreign key; time-ordered; no coordination when generating | a worker-number registry, and a dependency on the clock |
| Time-ordered high bits | random identifiers | sequential index inserts; "recent" becomes a range scan | writes concentrate, so never shard by identifier range |
| Refuse to generate on a backwards clock | wait it out; ignore it | duplicate primary keys are unrecoverable; failed writes are not | brief unavailability during a clock correction |
| Worker number as a lease | a configuration value per host | a stalled or partitioned node loses its claim instead of duplicating | a registry dependency at startup, and fail-to-start if it is down |
| Sidecar rather than embedded | a library in every process | one number per host instead of one per process, well inside 1,024 | ~0.2 ms per identifier |
| Separate public identifier | expose the internal one | removes timestamp and volume disclosure, and enumeration | one more column, one more index, one more lookup |
| Unique constraint on the identifier column | trust the generator | converts silent duplication into a loud error | a small index cost you were paying anyway |
8. Scale and failure
Throughput is not the story. One worker covers 4.1 million identifiers a second against a requirement of 100,000. At ten times the load you still have not exhausted a single worker. What changes with scale is the number of processes, which pushes against the 1,024-worker ceiling and pushes you from the embedded deployment toward the sidecar.
Bit-budget adjustments are the real scaling lever, and they are trades rather than wins. Taking a bit from the sequence and giving it to the worker field doubles the fleet size and halves per-worker throughput. Taking a bit from the timestamp doubles worker capacity and halves the lifetime to 35 years. Write these as a table in the design document, because someone will eventually need to make the trade and will otherwise make it by guessing.
| What breaks | Blast radius | How you find out | What keeps it running | Recovery |
|---|---|---|---|---|
| Two processes on one worker number | silent duplicate identifiers | unique-constraint violations in unrelated tables | leases that expire, not configuration values | find the overlap window; audit rows created in it |
| Registry unreachable at startup | that process cannot start | startup failure, loudly | fail to start — never default to worker 0 | restore the registry; the process retries |
| Clock steps backwards | that node stops issuing identifiers | ClockWentBackwards counter and alarm | refuse and alarm; other nodes are unaffected | wait for the clock to pass the last issued millisecond |
| Clock drifts forward | identifiers from the future; ordering is skewed | clock-offset metric per host | nothing breaks; ordering is approximate anyway | time synchronisation pulls it back by slewing |
| Sequence exhausted in a millisecond | sub-millisecond pause on that worker | a rare-event counter | bounded spin to the next millisecond | none needed |
| Sharded by identifier range | one shard takes every write | write QPS per shard, wildly uneven | shard by owner or by hash instead | re-shard, which is expensive — get this right first |
| Epoch exhausted (~69 years) | no new identifiers | a date in a comment nobody read | document the exhaustion date in code | a new identifier scheme, and years of warning |
The row worth staring at is the first one, because it is the only failure here that is silent. A duplicate worker number does not error at generation time; it errors later, somewhere else, as a constraint violation on a table that has nothing to do with the process that caused it. That distance between cause and symptom is why the lease design matters more than any other decision on this page.
What the interviewer will push on
"Why is UUIDv4 a bad primary key?" They want the mechanism, not the word "random". Give the three consequences: page splits on every insert instead of appends to one hot page, a write working set that becomes the entire index rather than its tail (so inserts get slower as the table grows, and cross a cliff when the index stops fitting in memory), and lost read locality — in an engine that stores rows in primary-key order, "the last hundred orders" becomes a hundred random reads. Then add the flat cost of 16 bytes repeated in every index and foreign key. The tell is naming the cliff, because it explains why the problem appears weeks after the deploy rather than immediately.
"The clock jumps back 2 seconds. What does your generator do?" They are testing whether you will trade correctness for availability under pressure. The answer is refuse and alarm, and the justification must be the asymmetry: failed writes are an incident that ends, duplicate primary keys are corruption that never fully ends because you cannot tell afterwards which identifiers were reused. Then give the prevention ladder — slewing rather than stepping, monotonic clocks for elapsed time, awareness that virtual machine migration jumps clocks regardless — and the backstop of a unique constraint.
"Two processes end up with worker number 7. What happens, and how would you have prevented it?" The tell is that you say silently. Nothing errors at generation. The duplicates surface later as constraint violations in unrelated code, which makes them very hard to trace. Prevention is that the worker number must be a lease the process can lose, not a value it holds — plus fail-to-start when the registry is unreachable, because defaulting to zero is how this happens in practice.
"You've made identifiers sortable by time. Now shard the table." This is the trap, and it is deliberate. Sharding by identifier range puts every new write on one shard, so the property you worked for becomes the problem. Say it as a tension you already knew about, then give the two resolutions — hash the identifier, or shard by owner and use the identifier only for within-partition ordering — and note that the second is why every other study in this Part partitions by owner.
"Can I use the identifier as a pagination cursor?" Almost, and the almost is the answer. Identifiers from one worker are strictly ordered; identifiers across workers are ordered only to within the fleet's clock skew, so a row with a smaller identifier can be inserted after you have already read past that point, and it is skipped forever. Naming the direction of the failure — rows are lost, not duplicated — is what shows you have thought it through rather than heard it.
"What does the identifier leak?" Creation time, directly, by a shift. And business volume, by subtracting two identifiers from the same worker. Then the fix: keep the Snowflake internal, mint a separate random public identifier for URLs, and never treat either one as authorisation.
Volunteer this, because nobody asks: put a unique constraint on the identifier column even though the generator guarantees uniqueness. Every argument on this page is an argument about why duplicates cannot happen, and every one of them rests on an assumption — the clock, the registry, the lease renewal, the worker range check. The constraint costs an index you were mostly paying for anyway, and it converts the one unrecoverable failure mode in the design into an ordinary error you find out about in seconds.
Next: 11.6 — identifiers, storage and caching are settled, so the next study is about getting a message to a person: fan-out across channels nobody controls, delivery you cannot confirm, and the difference between sending a notification twice and charging a card twice.
Recall
- The 64 bits: 1 sign + 41 timestamp (milliseconds since a custom epoch, ~69 years) + 10 worker (1,024) + 12 sequence (4,096 per millisecond) = 4.1 million per worker per second, produced by three shifts on local state with no I/O.
- Why time-ordered: sequential index inserts (random keys make the write working set the whole index, so inserts degrade as the table grows and fall off a cliff when the index leaves memory), "newest first" as a range scan, and 8 bytes instead of 16 in every index and foreign key.
- The five options: auto-increment (one writer, leaks volume) · UUIDv4 (no ordering — the index killer) · UUIDv7/ULID (sortable, zero infrastructure, 16 B) · Snowflake (8 B, needs a registry) · ticket server (blocks, no clock dependency, gaps).
- Backwards clock ⇒ refuse and alarm. Duplicate primary keys are unrecoverable; failed writes are an incident. Slew, never step. Bound any wait.
- Worker number must be a lease, never configuration, because two live processes on one number duplicate silently. Registry down at startup ⇒ fail to start, never default to 0.
- Leaks: creation time directly, and volume by subtracting two identifiers from one worker. Mint a separate random public identifier; no identifier is ever an authorisation token.
- The tension: time ordering helps indexes and hurts sharding. Never shard by identifier range — shard by owner or by hash and use the identifier for ordering within a partition.
- Ordering is only as good as clock skew across workers, which is why an identifier is not a strict pagination cursor.
Self-test: Recite the layout and the per-worker ceiling. Why do inserts with random keys get slower over time rather than being slow immediately? What does the generator do on a backwards clock, and what is the asymmetry that justifies it? Why must the worker number be a lease? Why can you not shard by identifier range?
Quiz Bank
FoundationalWhy does UUIDv4 hurt database performance, and what fixes it?
UUIDv4 is 122 uniformly random bits, so consecutive inserts land at random positions in the index. Three separate consequences follow, and it is worth keeping them separate because they have different symptoms.
Page splits everywhere. With a monotonically increasing key, every insert appends to the rightmost page of the index, which is nearly free. With random keys, each insert may land in the middle of a full interior page and split it, roughly doubling write amplification and leaving the index fragmented — pages half full, so the index occupies more space than its contents need.
A write working set that is the entire index. Sequential inserts touch the same hot page over and over, so the pages a writer needs in memory are a handful regardless of table size. Random inserts touch a different page each time, so the pages a writer needs are all of them. While the index fits in memory this is invisible. The moment it does not, every insert becomes a disk read, and the system falls off a performance cliff weeks after the deploy that caused it — which is why this problem is usually diagnosed as "the database is getting slow" rather than as an identifier choice.
Lost read locality. Rows created together are stored far apart, so "fetch the last hundred orders" is a hundred scattered reads rather than one sequential scan. In an engine that physically clusters the table by primary key, this scatters the row data itself, not just index entries, which makes it much worse.
On top of all three, the flat cost: 16 bytes instead of 8, repeated in the primary key, in every secondary index that carries it as a pointer, and in every foreign key in every other table.
What fixes it. UUIDv7 or ULID keeps the 128-bit shape and all the existing tooling but puts a millisecond timestamp in the high bits, restoring sequential inserts. This is the right default when you want zero infrastructure and can afford the eight extra bytes. Snowflake gets you to 8 bytes when index size genuinely matters and you can operate a worker registry. Auto-increment remains fine when there is exactly one writer.
The rule to state: any identifier used as a clustered primary key must be monotonically increasing, or you are paying for randomness on every write for the life of the system — and the bill arrives later, which is why it is so often paid.
InterviewWhat happens if the clock moves backwards on a Snowflake node, and what should the system do?
What would happen. The timestamp field would repeat a millisecond that has already been used. Because the sequence counter resets whenever the millisecond changes, the generator would start again from sequence zero inside a millisecond where it has already issued identifiers zero, one, two and so on — so it would re-emit identifiers it has already given out.
The consequence is not a bug that can be fixed forward. It is duplicate primary keys entering a system built on the assumption that they cannot exist: rows overwriting each other in some places, insert failures in others, all of it surfacing days later in code paths unrelated to the machine whose clock moved, with no way to determine after the fact which identifiers were reused.
So the generator refuses. On detecting that the current millisecond is earlier than the last one it issued from, it throws. The write fails with a retryable error, the caller retries, and the node raises an alarm. The reasoning is a trade you should deliver explicitly rather than defend when challenged: a few seconds of failed writes is an incident that ends; duplicate primary keys are data loss that does not.
Prevention, in order of effect. Configure time synchronisation to slew rather than step — correcting the clock by running it slightly slow for a while rather than jumping it — which removes the common case entirely. Use a monotonic clock source for anything measuring elapsed time (10.3). And know that virtual machines can jump clocks in ways no synchronisation setting prevents: a machine suspended and resumed, or migrated between physical hosts, can return with a clock that moved either way. That is a genuine argument for running the generator as a sidecar on hosts whose behaviour you understand.
Bounded waiting for small skews is acceptable and often better: if the clock is a few milliseconds behind, spin until it catches up. But the wait needs a ceiling, because waiting out a five-minute correction is an outage with a patient name.
And the defence in depth: a unique constraint on the identifier column. Every argument above rests on an assumption, and the constraint means that if any of them is wrong, you get a loud error rather than silent corruption.
InterviewWhy must the worker number be a lease rather than configuration, and what happens if it is not?
What goes wrong. Two live processes holding worker number 7 will, within the same millisecond, both issue sequence 0, then both issue sequence 1, and so on. Every identifier they produce in overlapping milliseconds is a duplicate. Nothing errors at generation time — the arithmetic is perfectly valid — so the failure travels silently into the database and surfaces later as a unique-constraint violation on some table that has no obvious connection to either process. The distance between cause and symptom is what makes this so expensive to diagnose.
How it happens in practice. Almost never by someone typing the same number twice. It happens because a process could not reach the registry at startup and defaulted to worker 0, and three replicas did the same thing during the same brief registry outage. It also happens when a node is partitioned rather than dead: the registry sees no renewal, decides the holder is gone, reissues the number to a new process — and the old process, which is alive and merely unreachable, carries on generating.
Why a lease fixes both. A lease is something a process can lose. The holder renews it periodically, and a process that cannot renew must stop generating before the registry could possibly have reissued its number. That converts the partition case from silent duplication into visible unavailability on one node, which is a strictly better failure. A configuration value has no such property: a partitioned process keeps its number forever because nothing can take it away.
The three ways to issue one. A short-lived registration in a small coordination store, which vanishes if its holder stops renewing. A stable replica index from the deployment system, if yours guarantees each replica a fixed number that survives restarts — that index is a worker number and it is free. Or a lease from a small dedicated service with an expiry.
And the rule that prevents the common case: if the registry is unreachable at startup, fail to start. A process that cannot prove it owns a unique worker number must not generate identifiers, and the temptation to default to something so that the deploy succeeds is exactly the temptation that produces this incident.
StaffDesign the identifier strategy for a multi-tenant product with 5,000 tenants, sharded by tenant, where customers see identifiers in URLs and export data for compliance audits.
There are three distinct needs here, and conflating them is the mistake the question is actually testing.
One: the internal primary key. Snowflake, or UUIDv7 if you would rather not run a registry. Sixty-four bits, time-ordered for index locality, generated with no coordination. The critical decision is that you do not shard by identifier — you shard by tenant_id (10.6), so the time-ordered identifier provides ordering within a tenant's shard while tenant hashing spreads writes evenly. Sharding by identifier range would send every new write in the entire product to the shard holding the newest range.
Two: the public identifier. A random, unguessable string in URLs and API responses. A Snowflake identifier publishes its creation time by a shift, and two of them from the same worker publish how much happened in between — so a customer can estimate another tenant's growth, and a competitor can estimate yours. A 128-bit random value, base62-encoded to about 22 characters, stored beside the internal identifier with a unique index, costs one column and removes enumeration and inference together. State plainly that this is not an authorisation mechanism: every request still checks that the caller's tenant owns the object (9.9.5). Unguessability shrinks the attack surface; it never grants access.
Three: the audit and export identity. Compliance exports are compared across years and across systems, so an export identifier must be namespaced, stable and independent of storage decisions: {tenant}:{entity}:{publicId}. Namespaced so two tenants' record number four never collide in a merged dataset. Stable so a re-shard or a migration never changes a customer's historical record identity — which means never recycling an identifier and never reissuing one after deletion, so anything ever exported is soft-deleted with a tombstone rather than removed.
Four cross-cutting rules to state. Identifiers are immutable — renaming an entity never changes its identifier, and a request to "change my ID" is really a request for an alias. Deletion is soft for anything ever exported, because auditors ask about records you removed. One module owns generation and the internal-to-public mapping, so the translation is in one place and greppable rather than scattered. And never derive one identifier from another — computing the public identifier by encrypting the internal one looks clever and leaks everything the moment the key or the algorithm is compromised, whereas a lookup table has no such property and costs one index.
The sentence for the design document: internal identifiers optimise storage, public identifiers minimise disclosure, and audit identifiers maximise stability. No single identifier satisfies all three, and the cost of pretending otherwise is paid years later in a migration nobody can safely perform.
Flashcards
FlashSnowflake layout and ceiling
1 sign + 41 timestamp (ms, ~69 years from a custom epoch) + 10 worker (1,024) + 12 sequence (4,096/ms) = 4.1M per worker per second. Three shifts on local state, no I/O.
FlashThe UUIDv4 problem, precisely
Random inserts split pages, make the write working set the whole index (so inserts fall off a cliff when the index leaves memory), and scatter clustered row data. Plus 16 bytes everywhere. Fix: UUIDv7/ULID or Snowflake.
FlashBackwards clock
Refuse to generate, throw a retryable error, alarm. Failed writes end; duplicate primary keys do not. Slew, never step. Bound any wait. Keep a unique constraint as the backstop.
FlashWorker number rule
A lease that a stalled node loses, never configuration a stalled node keeps. Registry unreachable at startup ⇒ fail to start, never default to 0 — that default is how duplicates actually happen.
FlashWhat the identifier leaks
Creation time by a shift, and business volume by subtracting two identifiers from one worker. Keep Snowflake internal; mint a random public identifier for URLs. No identifier is ever an authorisation token.
FlashOrdering and sharding tension
Time ordering makes index inserts sequential and makes range sharding useless — every write lands on the newest shard. Shard by owner or hash; use the identifier for ordering inside a partition. Ordering across workers is only as good as clock skew, so it is not a strict cursor.
Scenario Drill
DrillYou inherit a system using database auto-increment identifiers that must now shard across 16 database instances. Plan the migration.
Why the current scheme cannot survive sharding. Each shard's sequence starts at 1, so the same identifier is issued by all sixteen shards within the first minute. The obvious workaround — one database issuing every identifier — reintroduces exactly the single writer that sharding was meant to remove, and puts a network round trip in front of every insert.
The clever fix that is not clever. Offset the sequences: shard k issues k, k+16, k+32, and so on. It works perfectly until someone adds a seventeenth shard, at which point the stride is wrong and every subsequent identifier is a collision risk. It is a design with an expiry date built in, and the migration you are doing today is the evidence that designs like that get inherited by people who did not choose them.
Phase 1 — run the new generator alongside the old. Add a nullable new_id column carrying a Snowflake. New rows get both. Existing rows get one derived from created_at plus a deterministic per-row counter, so the backfill preserves approximate time order and — crucially — is idempotent, so it can be stopped, restarted and re-run without producing different answers. This is expand-migrate-contract (10.11) applied to a primary key, which is the highest-risk version of it, because other tables point at the column you are replacing.
Phase 2 — migrate the references. Every foreign key pointing at the old identifier needs a parallel column, backfilled by join, with the application dual-writing both. This phase dominates the timeline. Enumerate the referencing tables first, and expect the real count to be higher than the schema shows: identifiers hide in JSON columns, in log lines, in exported reports, in cached documents, and in external systems that stored yours. That last category is what turns a database migration into a customer-facing project.
Phase 3 — flip reads, one code path at a time behind a flag, with a reconciliation job continuously verifying that resolving by old identifier and by new identifier returns the same row. Flipping everything at once removes your ability to tell which change broke something.
Phase 4 — flip the primary key and shard. This is the only step that needs a maintenance window on most engines. The new shard key is a hash of tenant or owner — not the identifier, for the reason in section 6.3, because sharding by a time-ordered identifier would send every new write to one instance and you would have done all this work to build a sixteen-node cluster with one busy node.
Phase 5 — contract. Drop the old columns after a deliberate soak period measured in weeks rather than days. Keep the old_id → new_id mapping table permanently if any external party ever saw the old identifiers.
Three honest warnings.
External exposure is the real difficulty. If customers hold your old integer identifiers in their own systems, bookmarks or integrations, you cannot retire them by announcing a date. You keep the mapping and honour lookups on both, quite possibly forever, and you budget for that rather than discovering it.
Ordering semantics change subtly. Auto-increment is totally ordered within a shard. Snowflake is time-ordered with ties across workers, so any code using id > :lastSeen as a strict cursor can now skip rows inserted by a worker whose clock was slightly behind. Every such cursor needs revisiting (9.6.2).
"We'll do it in one deploy" is how this fails. Each phase must be independently deployable and independently reversible. That property is the only thing that makes changing a primary key survivable in a system that cannot stop, and it is worth more than any amount of care taken inside a single large change.