ID generation
Sequences, UUIDv4 and v7, Snowflake-style IDs and clock skew, range allocation, short hash IDs with collisions, and enumeration risks.
IDs look small, but they decide whether writes coordinate, indexes stay calm, and public URLs give attackers a counter. This page gets you ready to pick an identifier shape and defend it under scale, secrecy, and ordering pressure.
Read this if your last attempt…
- Your design says “just use auto-increment” across many writers.
- You picked UUIDs but could not explain v4 versus v7.
- Your public URLs leak row counts or let someone scrape adjacent records.
- You designed a URL shortener without collision maths or a retry path.
The concept
An identifier has three jobs: make a row unique, help the system find it, and decide what outsiders can infer from it. Treat those as separate choices. The internal primary key can be compact and efficient while the public ID is opaque. Data model design covers entities and relationships; indexing strategies covers B-tree mechanics; sharding and partitioning covers placement. This page only covers how IDs are minted and exposed.
Auto-increment and sequences
A database sequence is the simplest answer when one primary database owns the write. It gives small integers, fast joins, and friendly operational debugging. It is also a coordination point. If many independent databases must mint IDs, a plain local auto-increment can collide unless each node gets a disjoint range, an offset, or a central allocator.
Start from the caller and storage needs: internal key, public reference, ordering, and coordination are separate decisions.
Pick the ID by coordination, ordering, size, and public leakage.
| Approach | Best fit | Coordination | Ordering and locality | Public exposure risk |
|---|---|---|---|---|
| Database sequence / identity | Single-writer relational data and internal foreign keys. | One primary database or a counter owner. | Compact and naturally increasing, but gaps are normal. | High if exposed: row count and neighbours are guessable. |
| UUIDv4 | Independent writers and opaque public IDs. | None beyond good randomness. | Random insert positions in B-tree indexes. | Low guessing risk, but large for indexes and URLs. |
| UUIDv7 | Decentralised writes that still benefit from time order. | None beyond good randomness and clock handling. | Time-ordered prefix helps append-like locality. | Leaks approximate creation time. |
| Snowflake-style integer | High-throughput services needing compact sortable IDs. | Worker-id assignment plus clock policy. | Roughly time sorted and small enough for integer indexes. | Can leak creation time and worker hints if exposed. |
| Ticket server or block allocator | Many writers that still want integer IDs. | Central counter, often with local blocks. | Increasing by allocator, with gaps after crashes. | Predictable unless paired with an opaque public ID. |
| Short random/base62 token | Human-friendly URLs, invites, and share codes. | Unique index plus retry. | No useful ordering unless backed by a counter. | Depends on length and entropy; short tokens need abuse controls. |
- RFC 9562 defines UUIDs as 128-bit values; Twitter Snowflake-style IDs are commonly discussed as compact 64-bit integers using the archived implementation above.
- If the ID is public, decide what an attacker can infer before you optimise storage size.
How interviewers grade this
- You separate internal primary keys from public references instead of exposing the storage key by default.
- You explain whether the ID needs global coordination, approximate time ordering, or neither.
- You name the index effect: random UUIDv4 spreads writes, while time-ordered IDs improve locality but expose timing.
- You handle collisions for short random tokens with a unique constraint and retry.
- You state a clock rollback and worker-id assignment policy for Snowflake-style IDs.
- You treat sequence gaps as normal for identity, and not acceptable for gapless business numbers.
Variants
Sequence-backed internal key
Use the database to mint a compact primary key, then keep it mostly inside your system.
This is the default for one relational write owner. It keeps foreign keys small and clustered indexes friendly. The weak spot is distribution: if two primaries mint values independently, they need disjoint ranges, offsets, or a shared allocator. PostgreSQL sequence gaps and cache effects are documented behaviour, so do not attach business meaning to missing numbers CREATE SEQUENCE.
Pros
- +Small indexes and simple joins.
- +Easy debugging and ordering inside one database.
- +Works well with relational constraints.
Cons
- −Needs coordination across independent writers.
- −Predictable when exposed.
- −Gaps are normal after rollbacks, cache loss, or failed transactions.
Choose this variant when
- One database primary owns the write path.
- The ID is mostly internal and not a customer-facing reference.
UUIDv4 or UUIDv7
Use v4 for opaque randomness; use v7 when sort order and index locality matter.
UUIDv4 avoids coordination and is hard to guess when generated from a good random source. UUIDv7 keeps the no-coordination property but puts a Unix millisecond timestamp at the front, which gives better locality for B-tree inserts and natural time order RFC 9562. The cost is size and, for v7, visible timing.
Pros
- +Can be minted by clients or services without a central counter.
- +Good fit for public opaque IDs when v4 is used.
- +UUIDv7 improves ordering for logs, events, and database inserts.
Cons
- −Larger than integer keys.
- −UUIDv4 random inserts can fragment ordered indexes.
- −UUIDv7 exposes approximate creation time.
Choose this variant when
- Multiple services or regions need to create IDs independently.
- You need public values that are difficult to enumerate.
Snowflake-style 64-bit ID
Pack timestamp, worker identity, and sequence into one sortable integer.
A compact integer can be roughly sortable when the high bits are time. It still needs safe worker-id assignment and a clock rollback policy.
This is a common compromise when you want integer storage and high generation rate without a database call per row. The original Twitter code shows the shape: timestamp shifted above datacentre, worker, and sequence fields IdWorker.scala. Treat the worker-id registry and clock rollback policy as first-class parts of the design.
Pros
- +Compact integer indexes.
- +Rough creation-time order.
- +No central counter on the hot path after worker IDs are assigned.
Cons
- −Clock rollback can stop generation or break order.
- −Worker-id collisions create duplicate IDs.
- −Public IDs may leak timing and topology hints.
Choose this variant when
- You control the fleet and can assign worker IDs safely.
- You need sortable internal IDs at high write rates.
Ticket server or block allocation
Reserve integer ranges from a central counter and spend them locally.
Each app instance reserves a block from a central counter, then spends IDs locally. Crashes waste the unused tail, so gaps are expected.
Flickr used ticket servers to mint globally unique integers for a sharded MySQL setup, including a 64-bit ticket table and two servers split by odd and even values Flickr engineering. Range allocation reduces counter traffic further: ask for a block, then assign inside the process. The price is a central service and skipped values after crashes.
Pros
- +Integer IDs without per-write database sequence calls.
- +Simple to explain and debug.
- +Can allocate different ranges per entity type or shard.
Cons
- −Central counter availability matters.
- −Unused ranges become gaps.
- −Predictable values need a separate public ID when exposed.
Choose this variant when
- You want globally unique integer keys but can operate a small allocator service.
- Gaps are acceptable and public opacity is handled separately.
Short random or base62 token
Generate a compact URL-safe code, check a unique index, and retry on collision.
For URL shorteners and invite codes, the user experience often asks for a short string. Encoding a counter in base62 is compact and collision-free, but guessable. Random base62 is opaque, but collision probability depends on alphabet size, length, and active tokens. Store the code under a unique constraint and retry with a new token on collision.
Pros
- +Friendly in URLs, speech, and support tickets.
- +Can hide row counts when random.
- +Retry-on-conflict is easy with a unique index.
Cons
- −Short lengths collide sooner than intuition suggests.
- −Counters are enumerable.
- −Random codes have no natural ordering.
Choose this variant when
- Humans type, read, or share the identifier.
- You can choose length from collision maths and enforce uniqueness at write time.
Worked example
Numbers in this section are illustrative.
Scenario: design IDs for a stadium ticket-booking API. Numbers in this section are illustrative.
Use a compact internal key for joins: booking.id BIGINT from the primary database sequence. Do not put it in URLs. The public API returns booking_ref, a random base62 token stored under a unique index. Seat holds get hold_ref, also opaque, because customers and support emails see it. If you need approximate creation ordering in an internal event log, use UUIDv7 or a Snowflake-style event id there, not as the public booking reference.
The write path is: generate a candidate token, try INSERT booking(public_ref, ...), and retry with a new token if the unique index reports a collision. Do not “check then insert” without a unique constraint, because two workers can both see the code as free.
Now size the short code. A base62 alphabet with length 8 has 62^8 ≈ 2.18e14 possible strings. The birthday approximation is p ≈ n(n-1)/(2N), where n is active issued codes and N is the code space. With n = 10,000,000 active codes and N = 62^8, p ≈ 0.23, so at least one collision is plausible over that active set. Length 10 gives 62^10 ≈ 8.39e17, so the same active set gives p ≈ 0.00006, around 0.006%. That is low enough for many systems when paired with retry, but the exact target is a product and abuse decision.
If the booking service later becomes multi-region, keep the public token strategy and change only the internal generator. One option is region-aware Snowflake-style IDs with leased worker IDs and a rollback policy. Another is block allocation from a regional ticket service. In both cases, the public contract stays stable because callers did not depend on the internal integer.
This is the interview answer to say: “Internal IDs optimise storage and joins; public IDs optimise opacity and user experience. I enforce uniqueness in storage, retry collisions, and choose token length from the birthday bound rather than from taste.”
Good vs bad answer
Numbers in this section are illustrative.
Interviewer probe
“How would you choose IDs for bookings and public booking URLs?”
Weak answer
“I would use auto-increment for the booking ID. It is simple, sorted, and I can put it in the URL as /bookings/12345.”
Strong answer
“I would split the IDs. The internal booking row can use a compact sequence-backed integer because it is efficient for joins and one primary database owns the write. The public URL gets an opaque booking_ref, for example a random base62 token or UUIDv4, with a unique constraint and retry on collision. That prevents customers from guessing adjacent bookings or inferring volume. If we later need multi-region internal IDs, I can switch the internal generator to Snowflake-style or block allocation without changing the public API.”
Why it wins: It separates storage needs from public safety, names the collision control, and leaves room to change the internal generator without breaking callers.
When it comes up
- During data modelling, right after you name the main entities.
- When IDs appear in URLs, emails, webhooks, or partner APIs.
- When many services, shards, or regions need to create records independently.
- When the interviewer asks about index locality, scraping, or row-count leakage.
Order of reveal
- 1Separate internal and public IDs. “I first decide whether the storage key and the public reference should be different. For user-visible resources, I usually keep the compact internal key private.”
- 2Choose the coordination model. “One primary can use a sequence. Independent writers need UUIDs, Snowflake-style IDs, or block allocation.”
- 3Name ordering and index locality. “If insert locality matters, UUIDv7 or Snowflake-style IDs beat UUIDv4. If opacity matters more, random wins.”
- 4Handle collisions and clocks. “Short random codes use a unique index and retry. Snowflake-style IDs need unique worker IDs and a clock rollback policy.”
- 5Close with leakage. “Before exposing an ID, I ask what an attacker can infer by guessing the next value.”
Signature phrases
- ““The storage key and the public reference are separate design choices.”” — Prevents the common sequential-URL mistake.
- ““Sequences are unique, not gapless.”” — Shows you know real sequence behaviour.
- ““UUIDv7 buys locality by leaking rough time.”” — Balances the benefit and the cost in one sentence.
- ““A short random code is a retry loop around a unique index.”” — Names the real collision-handling mechanism.
Likely follow-ups
?“Why not use UUIDv4 for every primary key?”Reveal
You can, and many systems do. The costs are larger indexes, less friendly debugging, and random B-tree insert positions. If the row is internal and one database owns writes, a sequence is simpler. If many services mint IDs independently or the ID is public, UUIDv4 is a stronger candidate.
?“When would you choose UUIDv7 over Snowflake?”Reveal
UUIDv7 when you want decentralised generation without operating a worker-id registry. Snowflake-style IDs when compact integer storage matters and you can safely assign worker IDs and handle clock rollback. Both reveal approximate time.
?“What if marketing wants six-character invite codes?”Reveal
I would size the active code space before agreeing. Six base62 characters may be enough for a small active campaign with expiry, but not for a large permanent namespace. If the product requires short codes, add expiry, rate limits, collision retry, and abuse monitoring.
?“How do you make public IDs migration-safe?”Reveal
Keep a stable public_id column with a unique index and route APIs through it. The internal primary key can change shape during a migration, but callers keep using the public reference.
Code examples
const alphabet = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
function randomBase62(length: number): string {
const limit = 256 - (256 % alphabet.length);
const bytes = new Uint8Array(length);
let out = "";
while (out.length < length) {
crypto.getRandomValues(bytes);
for (const byte of bytes) {
if (byte < limit) out += alphabet[byte % alphabet.length];
if (out.length === length) break;
}
}
return out;
}
async function createBookingRef(insert: (ref: string) => Promise<boolean>) {
for (let attempt = 0; attempt < 5; attempt += 1) {
const ref = randomBase62(10);
if (await insert(ref)) return ref; // insert returns false on unique conflict
}
throw new Error("could not allocate booking reference");
}Common mistakes
Sequential URLs leak business volume and make scraping cheap. Keep the integer primary key internal when the resource is visible to users, partners, emails, or webhooks. Add a unique opaque public ID beside it.
Sequence gaps happen after rollbacks, cached values, failed inserts, and crashed workers. Use sequences for identity. If a regulation or finance process needs gapless numbering, design a separate serialisation process with audit controls and accept the throughput cost.
Random tokens can collide. The database unique index is the authority, and the generator retries on conflict. A pre-insert lookup alone is race-prone because another worker can claim the token before your insert.
UUIDv7 is much harder to enumerate than an integer, but it carries time in the high bits. That can be fine for event IDs and internal records. For public secrets, invites, or reset links, use random tokens with enough entropy and expiry.
Two live workers with the same worker id can mint the same sequence values in the same timestamp bucket. Assign worker IDs through a registry, lease, or deployment system that fences old owners before reusing an id.
Time-ordered generators need a rollback plan. If the system clock moves backwards, you can wait, fail generation, use a logical timestamp, or remove the node. The wrong answer is to keep minting as if order and uniqueness were unaffected.
Practice drills
Numbers in this section are illustrative.
Your order table uses `BIGSERIAL`. Should `/orders/123456` be public?Reveal
Usually no. It exposes order volume and lets attackers try neighbours. Keep the internal integer for joins and add an opaque public_id with a unique index for URLs and webhooks.
When is a gap in a generated ID a bug?Reveal
It is not a bug for identity keys. It can be a bug for legal invoice numbers or user-facing serials that promise continuity. Those should use a separate, slower, audited allocation process.
A Snowflake-style generator sees the clock move backwards. What do you say?Reveal
Name the policy instead of hand-waving. The generator can wait, fail requests until time catches up, advance a logical timestamp, or remove the node from service. It should not mint IDs that break its uniqueness or ordering assumptions.
Why does a short random URL code still need a database constraint?Reveal
Probability is not proof. Two random attempts can collide, especially at short lengths or high volume. The unique constraint is the correctness boundary; retry is the recovery path.
Deep dives
Birthday bound for short codes
For a random code space, collisions become likely faster than most people expect because every issued code can collide with every other issued code. The rough approximation is p ≈ n(n-1)/(2N) when p is small, where n is active codes and N is possible codes.
The active set matters more than lifetime count if expired codes can be reused safely. A one-week invite code namespace can be shorter than a permanent URL slug namespace. If codes do not expire, lifetime count becomes active count.
Retry does not remove the need to size the space. Retry makes individual inserts succeed after a collision. A space that is too small will spend more and more time colliding, and it also becomes easier to brute force. Pair length maths with rate limits and monitoring.
Clock and worker safety for sortable IDs
A compact integer can be roughly sortable when the high bits are time. It still needs safe worker-id assignment and a clock rollback policy.
Time-based IDs rely on a promise: a worker will not produce two identical (time, worker, sequence) tuples. Worker identity and clock behaviour are therefore part of correctness, not operations trivia.
Worker IDs can come from static deployment config, a central lease, Kubernetes ordinal names, or a coordination service. The important property is fencing: when a worker dies, a replacement should not reuse the same worker id until the old owner cannot still emit IDs.
Clock rollback policy should be explicit. Waiting preserves order but can stall writes. Failing fast protects correctness and lets the load balancer try another node. Logical timestamps keep service moving but need careful persistence. Pick one and say why.
Public IDs make migrations easier
The internal key stays compact and sortable for joins. The public id is hard to guess and safe to expose in URLs.
A public ID column is not only about security. It is also an API compatibility layer. If the primary key changes from a sequence to Snowflake-style integers, or a table is split across shards, clients do not need to know.
The usual shape is id as the primary key, public_id as a unique indexed column, and all external routes resolving through public_id. Internally, foreign keys and joins continue using id where that is simpler.
This does add a lookup. In most interview systems that cost is tiny compared with the product and security benefits. If it becomes hot, cache the mapping or make the public ID the partition key deliberately.
In production
Flickr
Ticket servers for globally unique integer IDs
Flickr wrote that sharding and master-master pairs meant plain MySQL auto-increment could not guarantee uniqueness across its databases. Their ticket server used a single-row table with REPLACE INTO and LAST_INSERT_ID() to mint 64-bit IDs, then used two servers split by odd and even values for availability.
Cheat sheet
- •Separate the internal key from the public reference when IDs leave your system.
- •Sequences are compact and sorted, but gaps are normal and distribution needs coordination.
- •UUIDv4 is random and opaque; UUIDv7 is time ordered and more index-friendly.
- •Snowflake-style IDs are compact and sortable, but need worker-id leases and clock rollback handling.
- •Ticket servers and block allocators trade central coordination for integer IDs and expected gaps.
- •Short base62 tokens need length maths, a unique index, retry, and rate limits.
- •Sequential public IDs leak volume and invite enumeration.
- •Choose on sortable versus random, coordination versus none, and compact integer versus larger UUID.
Practice this skill
These problems exercise ID generation. Try one now to apply what you just learned.
Read this if