“How do we generate IDs?” sounds like a detail, and in a single-database system it is: an auto-increment column hands out 1, 2, 3. It turns into a design question once you have many writers that can’t share a counter, because then you have to decide what the ID is for. It must be unique, obviously. Does it also need to sort by creation time? Be impossible to guess? Fit in 64 bits? Those are separate properties, and different schemes give you different subsets.
What are the requirements?
Start by asking which of these the system actually needs:
- Uniqueness, and in what scope. Per table, per tenant, or globally across services and regions?
- Rough time ordering. Can you sort by ID to get “newest first” without a separate timestamp index? Feeds and logs like this.
- Unpredictability. If IDs appear in URLs (
/invoices/1043), sequential values let anyone enumerate other customers’ invoices by changing the number. Authorisation should stop that, but guessable IDs also leak business volume. - Size. A 64-bit integer fits in a
BIGINTand in every index more compactly than a 128-bit UUID. - Generation without coordination. Can each server create IDs offline, without asking a central service?
A common misconception is that one scheme is “best”. A database sequence is perfect for an internal table on one primary. It’s a bad fit for an offline-first mobile app that creates records before it can reach the server.
The main options
Database sequences or auto-increment. Simple, compact, strictly increasing on one primary. They need a round trip to the database to get an ID, they’re guessable, and they don’t merge across shards without tricks (each shard gets a different offset and step, or a range of IDs).
Random UUIDs (version 4). 122 random bits, generated anywhere with no coordination. Unpredictable, but not ordered, 128 bits wide, and random inserts scatter across a B-tree index: each new row lands on a random page, which hurts cache locality and causes page splits on large tables.
Snowflake-style IDs. Twitter’s original layout packs a 64-bit integer with 41 bits of milliseconds since a custom epoch, 10 bits of worker id and 12 bits of sequence. IDs sort roughly by time, fit in a BIGINT, and need coordination only to assign worker ids.
UUIDv7. Standardised in RFC 9562 (2024): a 48-bit Unix millisecond timestamp, then version and variant bits, then 74 random bits. Time-ordered like Snowflake, generated without any worker id, in the standard UUID format that databases and libraries already understand.
A Snowflake generator in Node
Here’s a minimal Snowflake generator, using BigInt because the values exceed JavaScript’s safe integer range:
const EPOCH = 1735689600000n; // 2025-01-01T00:00:00Z
function snowflake(workerId, clock = () => BigInt(Date.now())) {
let lastMs = -1n, seq = 0n;
return () => {
let now = clock();
if (now < lastMs) throw new Error(`clock moved backwards by ${lastMs - now} ms; refusing to issue ids`);
if (now === lastMs) {
seq = (seq + 1n) & 4095n;
if (seq === 0n) { while ((now = clock()) <= lastMs); } // 4,096 ids used this ms: wait for the next one
} else seq = 0n;
lastMs = now;
return ((now - EPOCH) << 22n) | (BigInt(workerId) << 12n) | seq;
};
}
const decode = (id) => ({ time: new Date(Number((id >> 22n) + EPOCH)).toISOString(), worker: Number((id >> 12n) & 1023n), seq: Number(id & 4095n) });Running it with a fixed clock, plus a few experiments covered below:
two ids in the same ms: 234987123629912064 234987123629912065
decoded: {"time":"2026-10-11T10:34:56.123Z","worker":7,"seq":1}
41-bit timestamp lasts 69.7 years; 4096 ids per ms per worker; 1024 workers
event A (earlier) id 234987123546001408, event B (later) id 234987123537616896; sorted by id, B comes FIRST
after an NTP step back: clock moved backwards by 5 ms; refusing to issue idsThe capacity numbers are worth having ready. 12 bits of sequence is 4,096 IDs per millisecond per worker, about 4 million per second; the generator above spins until the next millisecond if a worker uses them up. 10 bits allow 1,024 workers. 41 bits of milliseconds last 69.7 years from the chosen epoch, which is why you pick a recent custom epoch instead of 1970.
Worker ids need managing. If two processes run with the same worker id, they can produce identical IDs in the same millisecond. Assign them from configuration, a ZooKeeper or etcd lease, or the pod’s ordinal in a Kubernetes StatefulSet, and make a duplicate assignment fail loudly.
One practical trap: a 64-bit ID sent to a browser as a JSON number gets rounded, because JavaScript numbers lose integer precision above 2^53. 234987123629912065 becomes 234987123629912060. APIs that use Snowflake IDs send them as strings for this reason.
Ordered IDs are not event order
The fourth output line is the one interviewers care about. Worker 1’s clock runs 3 ms fast. Event A happens on worker 1; one millisecond later, event B happens on worker 2, whose clock is correct. A’s ID embeds a timestamp 3 ms in the future, so B gets the smaller ID, and sorting by ID puts B first.
Time-ordered IDs sort by the generating machine’s clock. Across machines, clocks disagree by milliseconds even with NTP, and more during incidents. That’s fine for “show newest posts first”, where a few milliseconds of disorder is invisible. It’s wrong for anything that must know which of two events really happened first, such as which of two bids on an auction arrived first or which update to an account balance applies last. For those, order has to come from a single sequencer: one database primary, one Kafka partition (offsets are a true order within it), or a per-entity version number checked on write.
The fifth line shows the related failure. If NTP steps the clock backwards, a naive generator could reissue a timestamp it already used and produce duplicates. This one refuses to issue IDs until the clock catches up; others wait, or keep using the last timestamp and borrow from the sequence. Every option is better than silently issuing duplicates.
Collision odds for random IDs
Random IDs avoid coordination entirely, and the question becomes how many bits you need. The birthday bound gives the probability of at least one collision among n random b-bit values, roughly 1 − e^(−n²/2^(b+1)):
const p = (n, bits) => -Math.expm1(-(n * n) / 2 ** (bits + 1));1 billion random 64-bit ids P(collision) = 2.67e-2
10 billion random 64-bit ids P(collision) = 9.33e-1
1 trillion UUIDv4 (122 random bits) P(collision) = 9.40e-14
1 million UUIDv7 in the SAME millisecond (74 random bits) P(collision) = 2.65e-11A random 64-bit ID has a 2.7% chance of a collision somewhere by one billion IDs, and a collision is close to certain by ten billion. That surprises people who think 2^64 is “basically infinite”. With 122 random bits (UUIDv4), even a trillion IDs gives odds around one in ten trillion. UUIDv7’s 74 random bits only need to be unique among IDs created in the same millisecond, and a million in one millisecond still gives about one in 38 billion.
For reference, two UUIDv7 values generated one millisecond apart look like this (the random part differs on every run):
uuidv7: 01a12a87-a8fb-7b0e-8ace-dc6a5211e86e 01a12a87-a8fc-702b-ad96-851cb8eeff4aThe first 12 hex digits are the timestamp, so they sort in creation order, and inserts land at the right-hand end of a B-tree index instead of on random pages.
Interview practice: order IDs for a multi-region shop
“An e-commerce platform runs in three regions, each writing orders to its own database. Order IDs appear in confirmation emails and URLs, support staff sort orders by ID, and analytics wants IDs to be roughly chronological. What ID scheme would you choose?”
Answer
I’d list the requirements first: globally unique across three independent databases, generated without cross-region calls, roughly time-ordered for sorting, not guessable since they appear in URLs, and short enough to read over the phone for support.
A database sequence fails the multi-region and guessability requirements. UUIDv4 fails the ordering requirement and hurts index locality. That leaves Snowflake or UUIDv7. Snowflake is 64 bits and compact, but needs worker id management across regions (I could reserve bits for the region) and is fairly guessable: anyone who sees two IDs can estimate order volume. UUIDv7 needs no worker ids at all, is time-ordered and has 74 random bits, so consecutive IDs can’t be guessed. I’d use UUIDv7 as the primary key.
For the human side, I’d give each order a separate short display reference (eight characters from an alphabet without lookalikes like 0 and O), unique per customer, used in emails and on the phone. I’d also tell analytics that ID order is clock order: within a few milliseconds across regions it can disagree with true order, so anything that depends on exact sequence should use the database commit timestamp in one region, not the ID.