AUTO_INCREMENT works for one reason: a single database owns the counter and hands out values one at a time. Uniqueness is guaranteed by the fact that nobody else is allowed to increment it.
Shard that database across three machines and the guarantee evaporates. Each shard has its own counter, each cheerfully issues id 100, and you have three different users with the same primary key.
The obvious fix — ask one central service for every id — reintroduces the bottleneck you sharded to escape. So the real question is: how do independent machines produce ids that never collide, without talking to each other?
What a good id has to do
Four properties, and no scheme gives you all four.
Unique, permanently, across every machine.
Roughly time-ordered. This is worth far more than it sounds: it makes "most recent first" a primary-key scan, and it keeps inserts at the end of the index rather than scattered through it.
Compact. 64 bits fits a BIGINT, an integer register and a JSON number. 128 bits does not, and the difference multiplies across every index, foreign key and cache entry.
Generated without coordination, so neither a network hop nor a single point of failure sits in the write path.
UUID: no coordination, at a price
A UUIDv4 is 122 random bits. Any machine can generate one locally, instantly, with no state and no network call, and the probability of collision is negligible.
That makes it the right default for anything that lives outside a database index: request ids, correlation ids, idempotency keys, file names.
Inside a primary key, the randomness is the problem. They are 128 bits, doubling the storage in the key and in every index and foreign key referencing it, and they do not sort by time, so "the 50 newest rows" needs its own timestamp column and index.
Worse, they destroy insert locality. A B-tree fills pages in key order: sequential keys always append to the same rightmost page, which stays hot in memory, while random keys land on a random page every time, so the database fetches and dirties a different page per insert, pages split half-empty, and the index fragments. This is the one that surprises people, because nothing is wrong until the table outgrows memory — and then write throughput falls off a cliff.
Ticket server: keep the counter, isolate it
Flickr's approach keeps AUTO_INCREMENT and gives it a dedicated database whose only job is issuing numbers.
REPLACE INTO tickets64 (stub) VALUES ('a');
SELECT LAST_INSERT_ID();REPLACE keeps the table at exactly one row while still advancing the counter, so the "database" never grows.
Ids are short, strictly sequential and perfectly ordered. But every insert in your system now depends on a network round trip to one machine, which is both a throughput ceiling and a single point of failure.
The standard mitigation is two ticket servers with offset counters — one issuing odd numbers, one even, via auto_increment_increment=2 with different offsets. That removes the single point of failure and doubles the ceiling, but it does not scale further. A ticket server is a reasonable answer at moderate scale and a poor one once id generation sits on the hot path of everything you do.
Snowflake: put the coordination in the bits
Diagram, as text
- 1 bit: sign leads to 41 bits: timestamp ms
- 41 bits: timestamp ms leads to 10 bits: machine id
- 10 bits: machine id leads to 12 bits: sequence
Twitter's Snowflake asks a better question. Rather than coordinating at runtime, coordinate once, at configuration time, by giving each machine a slice of the id space that nobody else can produce.
A Snowflake id is a 64-bit integer cut into four fields:
0 | timestamp (41 bits) | machine (10) | sequence (12)
^ milliseconds since a custom which node counter within
| epoch generated it this millisecond
sign bit, always 0 so ids stay positive| Field | Bits | Gives you |
|---|---|---|
| Sign | 1 | Always 0, so the id is a positive signed BIGINT |
| Timestamp | 41 | 69 years from your chosen epoch |
| Machine id | 10 | 1,024 generators |
| Sequence | 12 | 4,096 ids per millisecond per generator |
Each field does one job. The timestamp leads, so ids sort chronologically as plain integers — you get time ordering for free, and you can extract the creation time from the id itself. The machine id makes two generators structurally incapable of colliding, because their ids differ in those bits no matter what. The sequence disambiguates ids created in the same millisecond on the same machine; if it overflows, the generator simply waits for the next millisecond.
Note the custom epoch. Counting milliseconds from your product's launch rather than 1970 reclaims decades of unused range, which is why 41 bits is enough for 69 years.
Where Snowflake bites: the clock
Snowflake's correctness rests on the timestamp never going backwards. Two things attack that.
The clock steps backwards. NTP corrects a drifted clock, a VM resumes from a snapshot, or an operator adjusts the time. Now the generator can produce a timestamp it has already used, with sequence numbers restarting from zero — and it will hand out ids it issued before.
There is no clever recovery. The generator must remember the last timestamp it used and, if the clock is behind it, refuse to generate until the clock catches up. A brief outage is correct behaviour; silently issuing duplicate primary keys is not. Run NTP in slew mode so corrections are gradual rather than stepped.
Machine ids collide. Two nodes configured with the same machine id will produce identical ids within the same millisecond, and nothing in the scheme detects it. Never hard-code the value. Derive it from something that is unique by construction: a lease from ZooKeeper or etcd, or the ordinal index of a Kubernetes StatefulSet pod.
Reallocating the bits
The layout is a budget of 63 bits, not a law. Spend them on whatever your system is short of.
Consider a photo service that expects modest write rates, wants a lifespan beyond 69 years, and will never run more than a hundred generators. Take one bit from the machine field for the timestamp and give the rest to the sequence:
| Timestamp | Machine | Sequence | |
|---|---|---|---|
| Twitter's original | 41 — 69 years | 10 — 1,024 nodes | 12 — 4,096/ms |
| Low traffic, long life | 42 — 139 years | 7 — 128 nodes | 14 — 16,384/ms |
| High burst | 40 — 35 years | 8 — 256 nodes | 15 — 32,768/ms |
| Many devices | 38 — 8.7 years | 16 — 65,536 devices | 9 — 512/ms |
Each row totals 63. That is the exercise: timestamp bits buy lifespan, machine bits buy generators, sequence bits buy throughput, and there are only 63 to go round.
| UUIDv4 | Ticket server | Snowflake | |
|---|---|---|---|
| Size | 128 bits | 64 bits | 64 bits |
| Time-sortable | No | Yes | Yes |
| Coordination at write time | None | One round trip | None |
| Single point of failure | No | Yes, unless paired | No |
| Depends on | Nothing | One database | Clocks and unique machine ids |
| Insert locality | Poor | Excellent | Excellent |
Check your understanding
Common mistakes
Using UUIDv4 as a clustered primary key on a large table. Random keys scatter inserts across the B-tree, splitting and fragmenting pages. Use a time-ordered id.
Hard-coding machine ids in configuration. One copy-pasted config file and two nodes issue the same ids forever. Lease them or derive them from a unique ordinal.
Letting the generator run when the clock goes backwards. Duplicates appear with no error. Track the last timestamp and refuse.
Using the Unix epoch. You throw away decades of the timestamp field for nothing. Pick an epoch at your product's launch.
Exposing Snowflake ids publicly. They disclose creation times and, by comparison, your growth rate.
Ignoring sequence exhaustion. More than 4,096 ids in one millisecond on one node makes the generator block. If you expect that, spend more bits on the sequence.
Practice
Design an ID scheme for a system expecting 10,000 new records per second across 50 servers, where IDs must be sortable by creation time and must not reveal the total record count. Allocate the bits.
Write your own answer first, on paper or in a note. Then compare.
Show a hint
Work out how many IDs one machine needs per millisecond before choosing the sequence width.
Show a strong answer
Requirements translate directly into bits.
- 10,000/s across 50 servers is 200/s per server, or fewer than 1 per millisecond on average. Bursts matter more than the average, so allow generous headroom.
- Sortable by time means the timestamp must be the most significant component.
- Not revealing the count rules out a plain incrementing counter, which is exactly what an attacker enumerates.
A 64-bit layout:
- 41 bits — timestamp in milliseconds since a custom epoch. That gives about 69 years, and choosing a recent epoch rather than 1970 spends none of the range on the past.
- 10 bits — machine ID. 1,024 machines, comfortably above 50 with room to grow.
- 12 bits — sequence within the millisecond. 4,096 IDs per machine per millisecond, or 4 million per second per machine — roughly 20,000× the required rate, which absorbs any realistic burst.
- 1 bit — sign, left zero so the value is a positive signed 64-bit integer in every language.
Why this meets the requirements: IDs sort by time because the timestamp is highest-order. They reveal nothing about volume, because the sequence resets every millisecond and the machine ID is opaque. And no coordination is needed at generation time — the only coordination is assigning machine IDs once, at startup.
The operational requirement this creates: machine IDs must be unique and stable. Two servers with the same ID generating in the same millisecond produce duplicates. Assign them from a coordination service or a registry at boot, and fail startup loudly if one cannot be obtained — never fall back to a random or default value.
Your team uses a single database sequence to allocate IDs. Name three problems this creates at scale, and give a change that fixes each.
Write your own answer first, on paper or in a note. Then compare.
Show a hint
One database allocating every ID is a bottleneck, a dependency and a disclosure.
Show a strong answer
- It is a throughput bottleneck and a single point of failure. Every insert across every service waits on one database. If it is slow, all writes are slow; if it is down, nothing can be created at all — even in services that would otherwise be unaffected. Fix: allocate in blocks. A service requests 1,000 IDs at once and hands them out locally, so the sequence is consulted a thousand times less often and a brief outage is invisible because each service still holds unused range.
- The IDs are sequential and public, so they disclose business information. Anyone who creates two orders a week apart can subtract the IDs and read your weekly volume, and can enumerate other users' records by counting down. Fix: keep the sequence internally if you like it, but expose a different, non-sequential public identifier — the internal key stays small and ordered for the database, and nothing about it leaks.
- It does not survive multi-region. Two regions cannot share one sequence without a synchronous cross-region call on every insert, which adds a hundred milliseconds and couples both regions' availability. Fix: put the region into the ID — a decentralised scheme where each region generates independently — so no write ever crosses the link.
The pattern across all three: the single sequence is doing one job (uniqueness) and accidentally providing two properties you did not ask for (global ordering and density). Separating "unique" from "ordered" and from "public" lets each be satisfied by the cheapest mechanism.
Key takeaways
AUTO_INCREMENTworks because one machine owns the counter; sharding removes that owner, and asking one service for every id reinstates the bottleneck.- A good id is unique, roughly time-ordered, 64 bits, generated without coordination, and fast.
- UUIDv4 needs no coordination but is 128 bits, unsortable, and destroys B-tree insert locality; UUIDv7 fixes the ordering while keeping the format.
- A ticket server keeps sequential ids at the cost of a round trip and a single point of failure; paired odd/even counters mitigate but do not scale.
- Snowflake coordinates once at configuration time by splitting 64 bits into timestamp, machine id and sequence, so generators are structurally incapable of colliding.
- Its two failure modes are clocks moving backwards (refuse to generate) and duplicate machine ids (lease them, never hard-code them).
- The bit layout is a 63-bit budget: timestamp bits buy lifespan, machine bits buy generators, sequence bits buy throughput.
- Snowflake ids leak creation time and growth rate, so keep them internal if that matters.
Further reading
- RFC 9562, Universally Unique IDentifiers (UUIDs) — including the time-ordered versions 6, 7 and 8.
- Twitter Engineering, "Announcing Snowflake" (2010) — the bit layout this lesson follows.
- Rob Pike and Ken Thompson, "Plan 9 from Bell Labs" (1995) — early decentralised naming, for context on coordination-free identifiers.
- Leslie Lamport, "Time, Clocks, and the Ordering of Events in a Distributed System" (CACM, 1978) — why wall-clock ordering is not causal ordering.
- Peter Bourgon, "ULID specification" (2016) — lexicographically sortable identifiers without a machine ID.