Distributed ID Generation

Federation left us with a cliffhanger: once your data lives on many independent databases, the humble AUTO_INCREMENT column can't hand out unique ids anymore. This is that problem, and its surprisingly elegant answer.

Start by noticing what an auto-increment id actually is: a number you ask the database for. You INSERT a row, and the one machine that owns the counter hands you the next value in line — 1, 2, 3, 4. It's perfect, and it's perfect for exactly one reason: there is a single counter with a single owner. Everybody asks the same authority, so nobody collides.

Now shard the table across eight boxes (Choosing a Shard Key), or federate your domains onto separate databases (Federation). The single owner is gone, and the two obvious fixes both fail:

• Let each box keep its own counter. Now box A and box B both hand out id 1, then 2, then 3. Every shard mints the same ids — instant collisions. Your "unique" key isn't.
• Keep one global counter that everyone calls. Congratulations, you've rebuilt a single owner: one service every single INSERT in the company must wait on. That's a Single Point of Failure (Container 1) and the write bottleneck that sharding just spent four lessons removing. You can't shard your data to spread writes and then funnel every write through one id counter.

So we need ids that are minted without anyone asking anyone. Write the spec down, because every candidate we look at either meets it or dies on one of these lines:

1. Unique across every box — with no two machines ever coordinating to agree on a number.
2. Roughly time-sortable — newer ids sort after older ones, so ORDER BY id gives you "most recent," range scans by time work, and rows created together land near each other on disk.
3. No coordinator on the hot path — minting an id must not require a network round trip, a lock, or a shared counter. Every machine does it locally, instantly.
4. Compact and index-friendly — it becomes the primary key on billions of rows and the target of every foreign key; its size and its insert behaviour are not free.

Four requirements, and they pull against each other. Watch how the industry climbed toward an answer.

You don't have to imagine the candidates — Twitter walked this exact ladder in 2010, and wrote down why each rung failed. They were moving off MySQL onto Cassandra, which has no sequential id generator of its own, so they had to mint their own ids and they set two hard requirements: ids must be roughly sortable by time ("if tweets A and B are posted around the same time, they should have ids close to each other") and must fit in 64 bits. Here's who they rejected, and why.

Rung 1 — a database ticket server (Flickr's trick). Flickr had solved unique-ids-on-the-cheap in 2010 with a tiny dedicated MySQL box whose only job was to hand out numbers: REPLACE INTO Tickets64 …; SELECT LAST_INSERT_ID(); on a single stub row. To avoid that box being a single point of failure they ran two of them, one serving odd numbers and one serving even (auto_increment_increment = 2, offsets 1 and 2). Clever and cheap — but the two servers drift (Flickr measured "a few hundred thousand more odd objects than even"), so the ids are unique but only loosely ordered, and there's still a coordinator sitting in the path of every insert. Twitter's verdict: it "didn't give the ordering guarantees needed without building some re-syncing routine."

Rung 2 — a UUID. A version-4 UUID is 122 random bits; any machine can generate one alone, no coordination, no collisions in practice. It nails requirements 1 and 3 — and fails 2 and 4 hard. It's 128 bits (twice a bigint), it's not sortable at all (pure random), and — the part that quietly wrecks your database — random keys destroy index locality. A B-tree index (B-Trees & B+ Trees) loves keys that arrive in order: each insert lands on the same right-most page, which fills up neatly. Feed it random UUIDs and every insert lands on a different, random page — pages split, parents get rewritten, and your cache thrashes. This isn't a modern discovery: Flickr said it in 2010 — GUIDs "are big and index badly in MySQL."

We measured the two halves of that cost. On a million rows, ORDER BY id matched creation-time order for a time-prefixed id perfectly (0 out of 1249 sampled pairs out of order) but was essentially random for UUIDv4 (613 of 1249 — about half — out of order). And a 64-bit id is literally 8 bytes to a UUID's 16 — in Postgres the compact id's primary-key index came out 28% smaller. The insert-speed penalty is real too but engine-dependent (it's brutal in MySQL/InnoDB, where the primary key is the table, and softer in Postgres's heap); the RFC that standardises UUIDs (RFC 9562) simply notes that non-time-ordered UUIDs "have poor database-index locality." The takeaway isn't "never use a UUID" — it's "never use a random one as a primary key." (Hold that thought; there's a time-ordered UUID coming.)

Rung 3 — ZooKeeper sequential nodes. A strongly-consistent coordinator that can hand out ordered numbers. Twitter tried it and couldn't get the performance, and — this is the sentence that defines the whole lesson — they "feared that the coordinated approach would lower their availability for no real payoff." Every id would depend on a quorum being reachable. Why buy that fragility for a number?

Every rung meets some requirements and dies on another. The answer isn't higher up the ladder — it's a different move entirely.

Here's the leap. Every candidate so far tried to ask something for a number — a counter, a ticket server, a coordinator. Stop asking. Stamp your own.

A machine that wants a unique, sortable id doesn't need to talk to anyone, because it already holds everything required, right now, locally:

• the clock — the current millisecond gives you uniqueness over time and, because time only moves forward, sortability;
• its own id — a number assigned to this machine gives you uniqueness across machines, so two boxes in the same millisecond still differ;
• a local counter — a tiny tally gives you uniqueness within a single millisecond on one machine.

Snap those three together and you have an id that no other machine could have produced — with zero coordination. You've met this before without noticing: a cash-register receipt. A receipt numbered Store 42 · Register 07 · Txn 000531 · 14:03:22 is globally unique across every store with no central number-issuing office (Store 42 Register 07 can't collide with Store 09 Register 03), it sorts by time, and each register just keeps its own running count. No register ever phones head office for a number. That's the whole idea.

Twitter's version is called Snowflake, and it packs those three parts into 64 bits:

• 1 sign bit, always 0 (keeps the number positive so it sorts cleanly as a signed bigint);
• 41 timestamp bits — milliseconds since a custom epoch. 2⁴¹ milliseconds is about 69 years of runway. (The custom epoch matters: start counting from 2010 instead of 1970 and you get the full 69 years ahead of you instead of having already burned 40. Discord uses the same design with a 2015 epoch.)
• 10 worker bits — up to 1024 machines;
• 12 sequence bits — up to 4096 ids per millisecond per machine.

Do the arithmetic on that last pair: 4096 ids/ms is ~4.1 million ids per second, per machine, and 1024 machines is over 4 billion ids per second across the fleet — none of them coordinating. One worker stamps (this millisecond, machine 7, count 531) while another stamps (this millisecond, machine 12, count 4) and they cannot collide, because their worker bits differ.

Now the property that makes it all worthwhile, and it falls out of pure bit arithmetic. The timestamp sits in the highest bits, the sequence in the lowest. So when you compare two ids as plain integers, you compare their timestamps first, and only reach for worker and sequence to break a tie. That means ORDER BY id is ORDER BY created_at — for free, with no separate timestamp column, no index on it. We measured it: sorted by id, a batch came out in exact creation-time order (0 of 1249 pairs inverted), the mirror image of UUIDv4's coin-flip.

Be precise about how sorted, though, because "perfectly sorted" is a myth worth killing. Within a single millisecond, ids from different machines interleave (they order by worker, then sequence, not by the exact instant), and a clock hiccup can locally reorder things. The honest word is k-sorted: roughly time-ordered, close enough that range scans and "newest first" work, but not a global total order down to the individual id. The guarantee that always holds is the one that matters: an id from a later millisecond is always larger than any id from an earlier one — the +1-millisecond id outranks every worker and every sequence value from the millisecond before.

Those bit widths — 41, 10, 12 — aren't handed down from heaven. They're a budget, and it's zero-sum: you have 63 usable bits, and every bit you give to one field is a bit you take from another. Want more ids per millisecond? Add sequence bits — and steal them from the timestamp (shorter lifespan) or the worker field (fewer machines). There is no free bit. Reading the budget as a set of deliberate trade-offs is the difference between memorizing Snowflake and understanding it — and you can see it directly in how different companies spent the same 63 bits:

Read that table as three different bets. Instagram spent more bits on the machine field (13, for 8192 shards) and — the clever part — treats those bits as the shard id itself, so the id tells you which database the row lives on. They even generate it inside Postgres with a little stored function (nextval(...) % 1024 for the sequence, the millisecond shifted into the high bits), so there's no separate id service at all. Sonyflake made the opposite bet: it ticks in 10-millisecond units instead of 1 ms, trading time resolution to stretch 39 timestamp bits across 174 years, and spent big on the worker field (16 bits, 65536 machines) — at the cost of only 256 ids per tick. MongoDB's ObjectId only bothers with second granularity and leans on 40 random bits for its uniqueness. Same envelope, four philosophies.

This is exactly why the interactive below hands you the sliders instead of a finished answer: the "right" split is the one that fits your fleet size, your write rate, and how long you need the scheme to last. Give the timestamp too few bits and your ids roll over in a decade; give the sequence too few and you hit a throughput wall we're about to meet.

The whole scheme leans on the wall clock — and the wall clock is the one ingredient you don't own. It's set by NTP, and NTP moves it backwards: to correct drift, after a VM pause or migration, around a leap second. "Time only moves forward" is the single most dangerous assumption in distributed ids, and it's false.

Failure one — the clock rewinds. Suppose a worker has been stamping ids at millisecond 1,000, and NTP nudges its clock back to 999. A naive generator now re-enters millisecond 999 with its sequence reset to 0 — and re-mints an id it already handed out. We reproduced exactly that: with no guard, the rewound worker produced a duplicate of a previously issued id. The fix is a rule every real implementation follows: keep the last timestamp you issued, per worker, and never go backwards. If the clock reads earlier than your last id, you don't stamp — you either block (spin until the clock catches back up; Twitter's choice for small rewinds) or, for a jump too big to wait out, fail closed and alert. In our capture the guarded generator refused: "clock is 1 ms behind the last id — block until it catches up." Slow beats wrong. And note the corollary: syncing clocks with NTP or PTP is necessary but not sufficient — you still need per-worker monotonicity, because sync reduces drift, it doesn't forbid a backward step.

Failure two — you run out of sequence. Twelve sequence bits is 4096 ids in a single millisecond, on one worker. Ask for the 4097th and there's no number left to stamp in that millisecond. The generator's answer is to wait for the next millisecond and start the sequence over. We asked one worker for 4098 ids in one millisecond: 4096 fit, and the last 2 spilled into the next millisecond — all still unique, just delayed by a fraction of a millisecond. It's a hard ceiling (~4.1M ids/s per worker), and it's a capacity-planning number: if one worker needs more, you add workers or spend more bits on the sequence field.

Failure three — the worker-id irony. Here's the twist that trips people up. This whole design brags about needing no coordinator — and yet every worker must have a unique machine id, because two workers sharing id 7 will happily stamp identical ids and collide silently. So a system built to avoid coordination needs a little coordination, exactly once, to bootstrap itself. Three ways teams do it: static config (simple, brittle at scale); a lease from ZooKeeper or etcd — each worker claims an unused id from a pool on startup and renews it (a typical 30-second lease with a 10-second renewal, so a crashed worker's id is reclaimed automatically — the answer to "what happens if a node dies before releasing its id?"); or derive it from the environment, like taking the low 16 bits of the machine's IP address, which is unique within a subnet with zero negotiation — handy for ephemeral containers where assigning ids by hand makes no sense. "No coordinator on the hot path" was always the honest claim; "no coordination ever" was never true.

Time to hold the whole thing in your hands. Below you can spend the 63-bit budget yourself: drag bits between the worker and sequence fields and watch the timestamp — and its lifespan — pay for your choices in real time. Load the Snowflake, Instagram, and Sonyflake presets to feel three real companies making three different bets with the same envelope.

Then forge a batch: several workers stamp ids at once, with no coordination, and every id is unique. Hit Sort by id and watch them fall into creation-time order — the sortability payoff, live. Finally, break it, because that's where the understanding is: rewind the clock and, with the guard off, watch a duplicate id appear (turn the guard on and the generator refuses instead); and overflow the sequence to slam into the per-millisecond ceiling and see it stall into the next millisecond. Everything you just read, in one panel.

Now the turn that separates a good answer from a senior one. The very property we worked so hard for — time-sortability — is a gift inside your database and a liability the moment the id leaks into a URL.

A public, guessable, time-ordered id is a barcode an attacker can read. It leaks the row's creation time (just decode the timestamp bits). It leaks your volume: capture two ids an hour apart, subtract their sequence/counter values, and you've estimated how many you created in between — the classic German tank problem, inferring total production from a couple of serial numbers. (Our receipt from earlier gives the whole game away: a competitor who photographs two of your receipts an hour apart can estimate your sales per hour.) And because adjacent ids differ by one, it's trivially enumerable — increment and walk to the next record. This isn't theoretical: in January 2021, Parler's sequential post and user ids let researchers scrape the entire site with a simple counting loop.

The reflex fix is "switch to random UUIDs so nobody can guess the next one" — and it's a trap if you stop there. A random id reduces discoverability, but it does not authorize anything. If your endpoint hands over any record whose id is in the URL, an attacker who obtains a valid id (from a referrer header, a shared link, a leak) still gets the data. The real fix is a server-side permission check on every request — a random id is defence-in-depth, never a substitute for authorization.

So the mature pattern is often two ids per row, because the internal id and the external id have different jobs: a compact, time-ordered internal id (Snowflake or the time-ordered UUID we're about to meet) that your index and your joins love, and a separate opaque public id — plus real authorization — at the edge, where sortability is a leak rather than a feature.

Everything above is the why. Here is the what would you reach for, because in 2026 the honest answer is usually not "hand-roll Snowflake."

• One database, not sharded? Use BIGSERIAL / AUTO_INCREMENT. Full stop. It's ordered, tiny, and free, and there is exactly one owner because there is exactly one box. Building a distributed id generator here is the same premature-optimization trap as sharding a table that fits on a laptop (Partitioning vs Sharding's "shard last"). Don't solve a problem you don't have.
• Sharded, and you want a compact id or the id to route to its shard? Use Snowflake (or Instagram's variant). Sixty-four bits is half a UUID on every index and foreign key, and you can bake the shard number into the id so it tells you where the row lives. The price is the operational tax you just studied: assign worker ids, and babysit the clock.
• Want the modern default with the least operational burden? Use UUIDv7 (or ULID). This is the time-ordered UUID we kept promising. Standardised in RFC 9562 (2024), it puts a 48-bit millisecond timestamp in the high bits and fills the rest with randomness — so it's sortable and tail-inserting like Snowflake (it fixes UUIDv4's index problem completely), keeps 128-bit uniqueness, and needs no worker-id assignment and no clock daemon, because the randomness — not a machine number — prevents collisions. Postgres 18 ships a built-in uuidv7(). The trade against Snowflake is pure size: 128 bits vs 64. If ids-per-byte and shard-routing matter, Snowflake; if operational simplicity matters more, UUIDv7.
• Exposing ids to the outside world? Add a separate opaque id and real authorization — regardless of which of the above you chose internally.

Notice these aren't ranked from worst to best; they're fit-for-purpose. The whole lesson is that an id is a bundle of trade-offs — size, sortability, coordination, secrecy — and you pick the bundle your system actually needs.

• An auto-increment id is a number you ask for; it works only because one machine owns the counter. Shard or federate (#37–#41) and that owner is gone — per-box counters collide, and a single global counter is a Single Point of Failure plus the write bottleneck sharding removed.
• The move is to stop asking and start stamping: compose an id from parts every machine already holds — the clock (time + sortability), its own id (across machines), a local counter (within a millisecond). No coordination. A cash-register receipt already works this way.
• Snowflake packs that into 64 bits — 1 sign | 41 timestamp | 10 worker | 12 sequence → ~69 years, 1024 machines, 4096 ids/ms/worker (~4.1M/s). Because the timestamp is in the high bits, ORDER BY id == ORDER BY created_at (measured: 0 inverted pairs vs UUIDv4's ~half). It's k-sorted — roughly time-ordered, not exact.
• The bits are a zero-sum budget — Snowflake, Instagram (shard-in-the-id), and Sonyflake (174 years, 65k machines) split the same envelope three ways. And a random UUIDv4 is the wrong primary key: 16 bytes vs 8, and random keys scatter across the B-tree (page splits, cache misses — Flickr flagged it in 2010). If you want a UUID, use the time-ordered UUIDv7.
• The clock is the one part you don't own — it runs backwards (NTP, VM pauses, leap seconds), so guard against rewind (block or fail closed) or you'll mint a duplicate; the 4096/ms sequence is a hard ceiling; and, ironically, a no-coordinator system still needs a little coordination to hand out unique worker ids. Sortability is a feature in your index and a leak in your URL (creation time + volume + enumeration — Parler, 2021) — so a random external id is fine, but authorization is the real fix. Reach for BIGSERIAL unsharded, Snowflake for compact/shard-routed, UUIDv7 for the least operational burden.
• Next — Write Batching & Buffering: you can now mint millions of ids a second without a coordinator, but each id is a row waiting to be written — and writing is the expensive thing. How do you amortize that cost?