Identity, time and unique IDs
Auto-increment dies with the single database; whatever replaces it must be unique, fast, and ideally sortable.
AUTO_INCREMENT dies the day the second database arrives — two counters both issue 1001, and everything downstream corrupts politely. Every distributed system therefore needs an answer to a deceptively small question: who hands out identifiers, for what properties, at what coordination price?
The requirements matrix
Five properties, and you cannot maximise them all: uniqueness (non-negotiable), coordination cost (may issuers mint without talking?), sortability (do IDs order by creation time? — your B-tree cares deeply), opacity (do IDs leak volume or invite guessing?), and size (128 bits in every index and URL, forever). Every scheme is a stance on these five.
| Scheme | Coordination | Sortable | The catch |
|---|---|---|---|
| UUIDv4 (random) | none | no | random inserts shred B-tree locality — pages split everywhere (storage chapter’s revenge) |
| Snowflake-style | worker-ID assignment once | yes (ms timestamp + worker + sequence) | clocks: skew reorders, backwards steps threaten uniqueness |
| ULID / UUIDv7 | none | yes (time prefix + randomness) | creation time is readable — opacity traded away |
| Ticket server / ranges | rare batch grants | roughly | a service to run; blocks if unreachable when ranges exhaust |
Time-based IDs meet the clocks chapter
Snowflake’s layout — 41 bits of milliseconds, 10 of worker, 12 of per-ms sequence — is elegant until the Distributed Systems time chapter knocks: NTP steps a clock backwards and a worker can re-mint a timestamp it already used. Real implementations must choose — refuse to issue until the clock catches up (brief stall), or track last-issued time and always advance (drift from wall time). Neither is wrong; not choosing is. Same story for sequence overflow: more than 4,096 IDs in one millisecond on one worker → wait for the next tick. These edge cases are the entire implementation; the happy path is bit-packing.
The pragmatic classics, and the API surface
Flickr’s ticket servers (this chapter’s reading) remain the honest budget option: two MySQL boxes, offset auto-increments (one issues odds, the other evens), each app server grabbing IDs in batches — coordination amortised to near-zero, and the whole thing debuggable by anyone. It quietly generalises to pre-allocated ranges: grant each node a block of a million, refill early, never block on the hot path. Finally, the property teams forget until the incident: IDs are API surface. Sequential public IDs leak volume (“order 10,412” tells competitors your daily rate) and invite enumeration (/invoices/10413). The standard resolution: sortable IDs internally where the B-tree lives, opaque tokens externally where strangers browse — and 64-bit integers travel JSON as strings, because JavaScript’s 2⁵³ ceiling (Foundations, data representation) is waiting for exactly this mistake.
The modern standard for time-ordered IDs — short, readable, and the reasoning is in the appendices.
A classic of pragmatic design: two MySQL boxes issuing IDs for one of the largest sites of its era.