Unique ID Generation at Scale
Auto-increment works great on one database. The moment data is sharded, something else has to guarantee uniqueness across all of them.
Advanced
TopNotchNote
Notes that matter
why a database auto-increment column doesn't scale
A single relational database can hand out unique IDs trivially with an auto-incrementing column. The moment order data is sharded across multiple databases, that trick breaks: two shards each running their own auto-increment would both produce order ID 1, 2, 3... Something has to generate IDs that are unique across the whole system, not just within one machine.
requirements for a distributed ID generator
| Requirement | Why it matters |
|---|---|
| Uniqueness | Two different events must never receive the same ID, even generated by different servers at the same instant. |
| High throughput | A large system can need to generate many thousands of IDs per second. |
| Availability | ID generation can't have a single point of failure — if it's down, nothing that depends on an ID (placing an order, for instance) can proceed. |
| Reasonably compact | A 64-bit numeric ID is far cheaper to index and compare than, say, a long string. |
option 1: UUIDs
A UUID (v4) is a 128-bit pseudorandom number, generated independently on any machine with no coordination needed. Trivially scalable and highly available, since generation never touches a shared resource.
The cost: 128 bits is much larger than a typical 64-bit integer ID, which makes database indexes slower to update and query; and because UUIDs are random, they arrive at the database in no particular order, which is worse for index locality than IDs that increase roughly monotonically over time.
option 2: a centralized counter (and why plain auto-increment fails at scale)
A single database that hands out the "next" ID solves uniqueness trivially but reintroduces a single point of failure and a write bottleneck — every single ID request funnels through one machine. A common fix is a multi-master counter: instead of incrementing by 1, each of
m ID-generating servers increments by m, starting from a different offset, so no two servers ever produce the same number without needing to coordinate on every request.
Server 1 generates: 1, 4, 7, 10, ...
Server 2 generates: 2, 5, 8, 11, ...
Server 3 generates: 3, 6, 9, 12, ... (m = 3 servers)
This scales writes across multiple servers, but adding or removing a server mid-flight (changing
m) risks duplicate IDs unless handled carefully.option 3: range handlers
A central range-handler service hands out whole blocks of IDs to each application server on request — server 1 claims IDs 1–100,000, server 2 claims 100,001–200,000, and so on. Each server then hands out its own block locally without contacting the range handler again until it runs out. This keeps most ID generation entirely local (fast, no per-request coordination) while still guaranteeing global uniqueness, at the cost of a bounded range of "wasted" IDs if a server dies before exhausting its block.
option 4: Twitter Snowflake — IDs that encode time
Sometimes an ID needs to do double duty: be unique and roughly sortable by creation time, which is genuinely useful for range queries like "give me the last 24 hours of orders." Snowflake-style IDs pack a timestamp, a machine/worker identifier, and a per-millisecond sequence number into a single 64-bit integer:
64-bit layout (illustrative):
[ 41 bits: milliseconds since a custom epoch ][ 10 bits: worker ID ][ 12 bits: sequence within that millisecond ]
This gives roughly time-ordered, compact, collision-free IDs generated independently by each worker with no coordination — the dominant trade-off is dependence on reasonably synchronized clocks; if a server's clock drifts backward and then corrects, IDs generated during the drift can violate the intended ordering.
option 5: Google's TrueTime — the expensive, precise answer
Google's Spanner database uses a purpose-built API, TrueTime, backed by GPS and atomic clocks in every datacenter, that returns not a single timestamp but a bounded interval — "the true time is somewhere between T-epsilon and T+epsilon." By waiting out that uncertainty window before committing a transaction, Spanner can offer genuinely globally-ordered, externally-consistent transactions, something no ordinary clock-based approach can promise. This level of precision comes at real infrastructure and latency cost, and is reserved for systems (like Spanner itself) where global strict ordering is worth paying for.
choosing in practice
| Need | Reasonable choice |
|---|---|
| Simple, fast, no ordering requirement | UUID |
| Compact IDs, moderate throughput, simple to reason about | Range handler |
| Roughly time-sortable IDs at high throughput | Snowflake-style ID |
| Strict global ordering, willing to pay for specialized infrastructure | TrueTime-style approach |