Why can't a large system just use an auto-increment column for every ID, and what would you use instead?

One auto-increment column gives unique, ordered IDs, but all writes must go through the one database that owns the counter. That is a bottleneck for throughput and a single point of failure. Once data is sharded across databases, each would start at 1 and collide. Alternatives are several ticket servers that step by the number of servers (odd and even numbers), random UUIDs that need no coordination, or Snowflake-style IDs that each machine builds from time, its own machine number and a sequence. The trade-off is coordination against ordering and size.

Explain how a Snowflake-style ID is built and why two machines never produce the same one.

A Snowflake ID is a 64-bit number made of a timestamp in milliseconds, a machine number and a sequence number. IDs from different machines differ in the machine bits. IDs from the same machine in different milliseconds differ in the timestamp bits. Several IDs in the same millisecond on one machine differ in the sequence bits, which reset each millisecond. In the original design that is 41 bits of time (about 69 years), 10 bits for the machine and 12 bits of sequence. The result is unique, roughly time-ordered, and needs no coordination after each machine has its number.

A server that generates Snowflake IDs has its clock set back by a few milliseconds. What can go wrong and how do you protect against it?

After a backwards step the timestamp can repeat one already used, and the sequence restarts, so the generator can produce an ID it has already issued. The protection is to remember the last timestamp used and, if the clock is behind it, wait until the clock passes it, or refuse requests and raise an alert. Keeping clocks in sync with a service that slews time gradually rather than stepping it helps too. The cost of waiting is a short delay for requests on that machine, which is better than a duplicate primary key.

When would you choose a random UUID over a Snowflake ID, and what does it cost?

A random UUID needs no machine numbers and no coordination, so any service, or even a client, can create one, and collisions are practically impossible. It costs space (128 bits against 64) and ordering: random IDs insert at random places in a database index, which hurts write performance, and they cannot be sorted by creation time. A Snowflake ID is smaller and time-ordered but needs each machine to have a unique number and a trustworthy clock. Time-ordered UUIDs, such as UUID version 7 and ULID, keep the no-coordination benefit and add ordering.

Design the ID generation for a URL shortener that creates 1,000 short links per second across several servers.

Use a unique number per link, then encode it in base 62 (letters and digits) so it is short: 7 characters cover about 3.5 trillion numbers. To make the numbers, either give each server a block of IDs from a central counter in advance (so the counter is called rarely), or use Snowflake-style IDs. If links must not be guessable, avoid plain counters or scramble the number. The trade-off is simplicity (a counter) against the extra moving part and single point of failure, which blocks of IDs and a backup counter reduce.

Unique ID Generation | TheLearningLoop

Why you need a plan for unique IDs, and when to use each scheme

Every record needs an ID: an order, a message, a user, a short link. On one database the answer is easy: an auto-increment column that counts 1, 2, 3. But once many servers create records at the same time, or the data is split across several databases, that one counter becomes a problem. Two servers can pick the same number, or every write has to wait for the one machine that owns the counter.

A good ID scheme gives you two things interviewers always ask about:

Uniqueness at scale: no two records ever get the same ID, even when many machines create IDs at once.
Availability: creating an ID keeps working when one machine, one datacentre or one clock misbehaves.

Many designs also want a third property: IDs that sort by creation time, which is kind to database indexes and makes "newest first" cheap.

Use a dedicated scheme when:

Several servers or services create records at once and you cannot ask one database for every number.
The data is sharded (split across databases), so each shard cannot count on its own.
You need IDs that are short (for a link), sortable by time, or hard to guess.
Clients, such as mobile apps working offline, must create IDs themselves.

Skip it, or use something simpler, when:

One database holds all the data and one writer is enough: a plain auto-increment or identity column is the simplest choice.
The IDs never leave one table and nobody cares about their order or size.
You are early on: start simple, and move the day the counter becomes the bottleneck.

Try it

Three machines, A, B and C, ask for IDs every millisecond. Choose how the IDs are made, then inject a failure at tick 6 and watch what it does to uniqueness. Rows that would share an ID are marked as duplicates.

Generating unique IDs on three machines

Step 1 of 15 · Press Space to play, arrow keys to step

Failure at tick 6:

No IDs yet. Press Play.

Each machine builds IDs itself from three parts: the time in milliseconds, its own machine number, and a sequence number for IDs made in the same millisecond. No machine needs to talk to another. One tick is one millisecond. Press Play to see three machines (A, B and C) ask for IDs.

IDs made:: 0
duplicates:: 0
requests delayed:: 0
in time order:: -

1 tick = 1 millisecond. A duplicate means two records would share an ID. In time order counts how often an ID is bigger than the one made just before it.

Speed

Things to try:

Snowflake, no failure: every ID is unique and each one is bigger than the one before. Open the details of a row: it is built from the time, the machine number and a sequence.
Random UUID: no duplicates and no coordination, but the "in time order" counter shows the IDs have no order at all.
Central counter, then "Counter goes down": IDs are perfectly ordered until tick 6, then every request is refused. One counter is a single point of failure.
Snowflake, "A clock jumps back 3 ms", then "Carry on regardless": machine B reuses times it has already used, and duplicate IDs appear. Switch to "Wait until it catches up": no duplicates, at the cost of some delayed requests.
Snowflake, "Two machines share an ID": a mistake in configuration breaks uniqueness, with no clock involved.

ID generation strategies

Strategy	How it makes an ID	Good when	Weakness
Database auto-increment	The database counts 1, 2, 3	One database, one writer	Bottleneck and single point of failure. Collides across shards.
Ticket servers	A few counters step by the number of servers (odd and even)	You need small, ordered-ish numbers and can run two or three counters	An extra service to run. Adding a server changes the step.
Random UUID (version 4)	122 random bits in a 128-bit value	Any machine, even a client, must create IDs with no coordination	128 bits. No order, so inserts land at random places in an index.
Time-ordered UUID (version 7) or ULID	A timestamp first, then random bits	You want no coordination and time order	128 bits. Exact order within one millisecond is not guaranteed.
Snowflake-style	Timestamp, machine number and sequence in 64 bits	High volume, time order, small IDs	Needs a unique machine number per generator and trustworthy clocks.
Block allocation	A central counter hands each server a block, such as 1,000 numbers	The counter must be called rarely	Unused numbers in a block are lost if a server crashes.

Coordinated vs uncoordinated generation

This is the main fork in the road.

Coordinated schemes (a counter, ticket servers, block allocation) get numbers from a shared service. IDs are small and ordered, but the service is a bottleneck and a single point of failure that you must keep available.
Uncoordinated schemes (random UUIDs, Snowflake) let every machine make IDs on its own. Nothing blocks, and nothing is shared at request time. The price is larger IDs (UUIDs) or a setup step and a reliance on clocks (Snowflake).

Snowflake sits in the middle: the only coordination is once, when each machine is given its machine number.

How Snowflake-style IDs work

A Snowflake-style ID is one 64-bit number with three parts, in this order:

Timestamp (41 bits): milliseconds since a chosen start date. 41 bits last about 69 years.
Machine number (10 bits): which generator made it. 10 bits allow 1,024 generators.
Sequence (12 bits): a counter for IDs made in the same millisecond by the same machine. 12 bits allow 4,096 IDs per millisecond. It resets to 0 when the millisecond changes.

(The layout also keeps one unused sign bit at the front, which makes 1 + 41 + 10 + 12 = 64.)

Why it is unique: two IDs from different machines differ in the machine bits. Two IDs from the same machine in different milliseconds differ in the time bits. Two in the same millisecond differ in the sequence. Because the time comes first, IDs are roughly sorted by creation time.

Two things can break it:

A clock that goes backwards. If the clock is corrected to an earlier time, the generator can reuse a timestamp and a sequence number it has already used. A safe generator remembers the last timestamp it used and waits until its clock passes it, or refuses to make IDs and raises an alert.
Two machines with the same machine number. Nothing in the ID itself can stop this, so machine numbers must be handed out by something reliable, such as a configuration service, or leased from a coordination service.

If more than 4,096 IDs are wanted in one millisecond, the generator waits for the next millisecond.

Unique IDs vs the alternatives

Concern	Snowflake-style	Central counter or ticket server	Random UUID	Time-ordered UUID
Needs coordination while running	No	Yes	No	No
Survives a machine or datacentre failure	Yes	Only with several counters	Yes	Yes
Sorts by creation time	Yes	Yes	No	Yes
Size	64 bits	Small number	128 bits	128 bits
Depends on clocks	Yes	No	No	Yes
Hard to guess	Somewhat	No	Yes	Somewhat
Extra moving part	Machine numbers	A counter service	None	None
Best for	High-volume services	Small systems, short links	Client-made IDs, public IDs	Databases that like ordered keys

Snowflake-style IDs win for high-volume services that want small, ordered IDs. A random UUID wins when simplicity matters more than size. A plain counter is enough until it is not.

Avoiding a new single point of failure

A counter is the classic new single point of failure: if it is down, nobody can create a record. Real setups protect it:

Several ticket servers that step by the number of servers: with two, one hands out odd numbers and the other even numbers. If one is down, the other still works, and the IDs stay unique. The trade-off is that adding a third server later means changing the step, so plan the step with room to grow.
Block allocation: each application server asks for a block of, say, 1,000 numbers and uses them locally, so the counter is called once per block, not once per ID. If the counter is down, servers keep going until their block is empty. The trade-off is that a crashed server loses its unused numbers, which is fine because IDs do not need to be gapless.
No central service at all: use a scheme where each machine makes its own IDs, and give each machine a number once.

The same worry applies to machine numbers: if they come from a service, that service must not be needed on every request. Fetch the number once at start-up and cache it.

Common mistakes

Using an auto-increment for IDs that appear in URLs. Sequential IDs reveal how many records exist and are easy to guess. Use a random or scrambled ID for public values.
Trusting the clock. NTP (the protocol that corrects clocks) can step a clock backwards. Remember the last timestamp and refuse to go back.
Giving two machines the same machine number, for example by copying a configuration file. Assign numbers from one source and check for clashes at start-up.
Random UUIDs as the primary key of a big table. Inserts land at random places in the index. Use a time-ordered ID for the primary key, and keep random ones for public values.
Using 32-bit integers. A 32-bit counter runs out at about 4 billion. Use 64 bits.
Expecting gapless IDs. Block allocation, crashes and rolled-back transactions all leave gaps. Never build logic that needs numbers to be consecutive.

How to talk about unique IDs in an interview

Say the requirements first: IDs per second, whether they must sort by time, how long they can be, and whether they may be guessed.
Start simple: "one database auto-increment works until it becomes the bottleneck", then explain why it breaks with many writers or shards.
Pick one option and say why: for a high-volume service, "I would use Snowflake-style IDs: 64 bits, roughly time-ordered, no coordination at request time".
Mention failure unprompted: the clock going backwards and duplicate machine numbers, and how you guard against both.
Name the trade-off: coordination and a single point of failure against larger IDs and a reliance on clocks.

Where you meet unique ID generation in real systems

Every system that stores records needs IDs, so this problem hides inside most of them. Three examples are drawn out below, the first with configuration you can run, then more.

Example 1: Two ticket servers handing out IDs

A growing photo site has several application servers that all need new primary keys, and one database counter has become both a bottleneck and a single point of failure. The team sets up two ticket servers. One hands out odd numbers and the other even numbers, so they can never clash, and if one goes down the other keeps issuing IDs. Open the example below to see the picture and the configuration for three tools.

Config: two ticket servers, one odd and one evenSetup 1 config or script per serverCode no code changes

Each application server asks either ticket server for the next number. Server A answers 1, 3, 5 and server B answers 2, 4, 6, so no two numbers ever match. If one ticket server fails, requests simply go to the other.

# --- my.cnf on ticket server A: odd numbers (1, 3, 5 ...) ---
[mysqld]
auto_increment_increment = 2   # step between numbers = how many ticket servers there are
auto_increment_offset    = 1   # first number this server hands out

# --- my.cnf on ticket server B: even numbers (2, 4, 6 ...), the same except for the offset ---
[mysqld]
auto_increment_increment = 2
auto_increment_offset    = 2

# --- SQL, run once on each server, then every time an ID is needed ---
-- The table only ever holds one row.
CREATE DATABASE IF NOT EXISTS tickets;
USE tickets;
CREATE TABLE IF NOT EXISTS ids (
  id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  stub CHAR(1) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  UNIQUE KEY (stub)
) ENGINE = InnoDB;

-- To get the next ID: replace the single row, then read the number that was generated.
REPLACE INTO ids (stub) VALUES ('a');
SELECT LAST_INSERT_ID();

Each configuration was checked with the tool itself in a throwaway container. The two MySQL files were accepted by mysqld --validate-config (MySQL 8.0), and a server started with each handed out 1, 3, 5 and 2, 4, 6. The PostgreSQL script ran on PostgreSQL 16, and the Redis commands ran on Redis 7. The step of 2 is a placeholder for "the number of ticket servers", and this approach with a one-row MySQL table is the one the Flickr engineering team described in their write-up on ticket servers.

Example 2: Sortable IDs in a chat system

A chat service shows messages in the order they were sent, in conversations spread across many database shards. A Snowflake-style ID lets each message server make IDs on its own, and because the time is the first part of the number, sorting by ID is the same as sorting by time, with no extra timestamp column to index.

The server builds the ID from its clock, its own machine number and a sequence, so it never asks another machine. The ID is stored with the message, and sorting by ID gives the order of sending.

Example 3: What a Snowflake ID is made of

The three parts sit side by side in 64 bits. The timestamp is first, which is what makes IDs sort by time. The machine number and the sequence follow, and they are what keep two IDs from the same millisecond apart.

One bit is unused. The next 41 hold the time in milliseconds, then 10 bits hold the machine number (up to 1,024 machines), then 12 bits hold the sequence (up to 4,096 IDs per millisecond per machine).

More real-world scenarios

4. URL shorteners

A short link such as example.com/aZ3kQ9x is a unique number written in base 62 (letters and digits). The service makes the numbers with a counter, usually handed to each server in blocks, so servers rarely contact the counter and the codes stay short.

5. E-commerce order numbers

Shops want order numbers that are unique across many services and do not reveal how many orders they take. They often combine a time-based part for sorting with a random part for unpredictability.

6. Sharded databases

When data is split by user across many database servers, each shard cannot count from 1 on its own, or two shards would issue the same key. A Snowflake-style or UUID key keeps keys unique across every shard, and makes it possible to move rows between shards without renumbering them.

7. Mobile apps that work offline

A notes app lets you create items with no network. It creates a random UUID on the phone, so the item has its final ID before it syncs, and two phones can never clash.

8. Event and log pipelines

Systems that process billions of events give each event an ID so duplicates can be spotted and results can be matched up. Time-ordered IDs also make it cheap to find "everything after this point".

9. Payments and idempotency

A payment request carries a unique key, so if the client retries after a timeout the server can recognise the repeat and not charge twice. The key is made by the client, so it is usually a random UUID.

Tools and managed services

Kind	Name	Notes
Database feature	PostgreSQL sequences and identity columns	Atomic counters. `gen_random_uuid()` makes random UUIDs and is built in from PostgreSQL 13.
Database feature	MySQL `AUTO_INCREMENT`	`auto_increment_increment` and `auto_increment_offset` set up the odd and even ticket-server pattern.
Database feature	Redis `INCR` and `INCRBY`	Atomic counters, often used for ticket servers and for blocks of IDs.
Database feature	MongoDB `ObjectId`	A 12-byte ID built from a timestamp, a random value and a counter, made by the client driver.
Specification	UUID version 7 (RFC 9562)	A UUID with a timestamp first, so it sorts by time.
Specification	ULID	A 128-bit ID with a timestamp first, written in 26 characters.
Open source	Twitter Snowflake	The original design behind Snowflake-style IDs. The project is archived, and many libraries copy the layout.
Open source	Sonyflake	A Snowflake-inspired generator written in Go.
Managed database	Google Cloud Spanner	Its documentation advises against monotonically increasing keys, which create hotspots, and suggests UUIDs or bit-reversed sequences.

The takeaway

Whenever a design has many writers or many shards, the first question after "what is the ID?" is "who makes it?". A counter is the simple answer and a Snowflake-style or time-ordered UUID is the scalable one, and either way you must plan for the clock and the machine number.

Unique ID Generation

Key takeaways