beginnerintermediateadvanced

System Design · Section 91

Unique ID Generation

Level: intermediate
Read: 15 min
Concepts: 2

Four schemes cover almost every identifier you will generate, and they differ in how much coordination producing one costs. An auto-increment id is assigned by the database: compact, creation-ordered, unknown until the row is inserted, guessable, and impossible to generate independently in two databases without collision. A UUIDv4 is 128 random bits produced anywhere with no coordination, which makes it unguessable and unordered. A ULID is a 48-bit millisecond timestamp followed by 80 random bits, so ids sort by creation while remaining independently generatable — the same shape RFC 9562 standardised as UUID version 7. A Snowflake-style id packs a timestamp, a machine identifier and a per-millisecond sequence into 64 bits, giving compact ordered ids that need coordination only once, when machine ids are handed out, and that depend on a clock which never steps backward. Choosing between them means knowing which of five properties you actually need. Randomness stops enumeration and is defence in depth, never a substitute for authorization, since ids leak through browser history, referrers, screenshots and shared links. Sortability makes id order equal creation order, which cursor pagination and recent-record range queries both want. Coordination cost ranges from none through one-off to per id. Index locality is the property that surprises people: a B-tree inserts each key at its sorted position, so sequential keys concentrate on one hot trailing page while random keys scatter across the whole index — costless while the index fits in memory, and a disk read per insert once it does not, which is why the penalty is absent from every test suite and arrives in production as an unexplained write slowdown. And collision behaviour differs in kind rather than degree. Randomness and locality pull against each other, and a timestamp prefix is the standard resolution: order the high bits, randomise the low ones.

This section

System Design - Part 12 | Design a Unique ID Generator | 4 MethodsNikhil Lohia

What is true here

The distinguishing axis is coordination cost at generation time: none (UUID), once (Snowflake machine ids), or per id (auto-increment).
Sequential ids in public URLs leak volume and make enumeration free, which turns any missing authorization check into a full export.
Index locality is invisible while the index fits in memory and dominant afterwards — a random primary key costs roughly a disk read per insert at scale.
A timestamp prefix resolves the randomness-versus-locality tension: ordered high bits, random low bits (ULID, UUIDv7, Snowflake).
An unguessable id is not an access-control mechanism; ids leak through links, history and screenshots, so authorization is still checked per request.

What you will be able to do

Choose an id scheme by asking whether the id is needed before insert, whether enumeration matters, and whether volume makes size significant
Explain the B-tree mechanism behind the random-primary-key write penalty and why it is absent in testing
Justify a timestamp-prefixed id as satisfying unguessability and index locality at once
Recognise when an id scheme conflicts with the intended sharding strategy

The four schemes

Auto-increment, UUIDv4, ULID/UUIDv7 and Snowflake-style, ordered by coordination cost.

Auto-increment, UUID, ULID and Snowflake-style ids

coreintermediate

Four schemes cover almost every identifier you will generate, and they differ in who has to be involved to produce one. An auto-increment id is assigned by the database from a sequence: it is compact, ordered by creation, and requires the database to have seen the row, which means you cannot know the id before inserting and you cannot generate ids independently in two databases without them colliding. It is also guessable and it leaks volume — `/orders/1041` tells a reader roughly how many orders exist. A UUID is 128 bits generated anywhere with no coordination at all; version 4 is random, which makes it unguessable and unordered, and that lack of order is what makes it awkward as a primary key in a B-tree index. A ULID is 128 bits split into a 48-bit timestamp followed by 80 random bits, so ids sort by creation time while staying independently generatable — the same idea standardised as UUID version 7 in RFC 9562. A Snowflake-style id is a 64-bit integer packing a timestamp, a machine or shard identifier and a per-millisecond sequence counter, giving compact, time-ordered ids that need coordination only once, when a machine is assigned its identifier. The choice is not about taste: it is about whether you need to generate ids before writing, whether they must sort by time, whether they must be unguessable, and how much index locality you are willing to give up.

Think of it as

Think of who signs the ticket. An auto-increment id is signed by a single clerk, so the numbers are neat and everything queues at that desk. A UUIDv4 is a number everyone picks at random from a space so vast that collisions are not worth worrying about — nobody queues, and nothing is in order. A ULID or UUIDv7 is the same self-service idea with the date written first, so the pile sorts itself. A Snowflake id is self-service too, but each desk was given a number in advance, so the desk identifier is baked into every ticket and the ids stay short. The question is never "which is best" — it is "how much coordination can this system afford at the moment an id is needed".

text

auto-increment   1041

UUIDv4           f47ac10b-58cc-4372-a567-0e02b2c3d479
                 (all random after the version bits)

ULID             01ARZ3NDEKTSV4RRFFQ69G5FAV
                 |----------||--------------|
                 48-bit time    80-bit random

UUIDv7           0190a5c3-7e10-7c3f-8b2a-1f6d4c9e0a55
                 |-----------| unix ms, then random

Snowflake        1541815603606036480
                 41 bits time | 10 machine | 12 seq

What we're doing: Pick a scheme for four real situations by asking one question each.

choosing-a-scheme.txttext

1. Internal join table, single database,
   never exposed in a URL
   Question: does anything need the id before
   the insert?  No.
   -> AUTO-INCREMENT. Smallest, fastest,
      perfectly ordered. Do not pay for more.

2. Public order id shown in a URL and quoted
   in support emails
   Question: is enumeration or volume leakage
   a problem?  Yes.
   -> ULID or UUIDv7. Unguessable, and still
      sorts by creation for range queries.

3. Mobile client creates records offline and
   syncs them later
   Question: must the client know the id before
   the server sees it?  Yes.
   -> UUIDv7 (or ULID). No coordination at all,
      and the sync order is meaningful.

4. Event ids at 400,000 events/second across
   30 producers, stored for years
   Question: is 8 bytes vs 16 bytes worth
   coordinating machine ids?  At this volume,
   yes -- it is ~100 GB/year of difference.
   -> SNOWFLAKE-STYLE.

5: The most common mistake in this list is not choosing wrongly here but choosing UUIDs by reflex. An internal id nobody outside the database ever sees gains nothing from being 128 bits and unguessable, and pays for it on every index page.
13: Time-ordering is what makes the unguessable option affordable: it keeps inserts landing at the end of the index rather than scattered across it, which is the specific cost the next concept quantifies.
26: Size only becomes the deciding factor at volume. Eight bytes saved per id is invisible at a million rows and is a hundred gigabytes a year at this rate — which is also why the coordination cost of assigning machine ids becomes worth paying.

Why this works: Each situation is decided by one question — is the id needed before the insert, is enumeration a risk, is the volume high enough for size to matter — rather than by a general ranking of schemes. Working the questions in that order gives a defensible answer and avoids the two default failures: sequential ids in public URLs, and random UUIDs as a clustered key on a high-insert table.

Exposing auto-increment ids in public URLs

Wrong

http

GET /api/invoices/1041
# an attacker requests 1040, 1039, 1038...
# and learns both your invoice volume and,
# without object-level authorization, other
# customers' invoices

Better

http

GET /api/invoices/01J8K2WQ6RZ0T5M3N7B9XCVD4E
# a ULID: unguessable, still creation-ordered,
# and no information about how many exist
# (authorization is still required -- this
# removes enumeration, not access control)

What you see: Competitors quote your monthly order volume accurately, having read it off two order numbers a week apart. If object-level authorization is missing anywhere, the same sequential ids turn a single leaked URL into a complete data export.

Why: A sequential id is a public counter, so it discloses volume and growth rate to anyone who can obtain two of them. It also makes enumeration free, which turns any missing authorization check from a bug affecting one record into a bug affecting every record — an unguessable id does not fix the authorization gap, but it removes the cheap way to exploit it.

Coordination cost against time-ordering

Time-orderedUnordered

No coordinationCoordination per id

UUIDv4: generate anywhere, sorts nowhere
ULID / UUIDv7: generate anywhere, sorts by creation time
Snowflake: one-time machine-id assignment, then independent
Auto-increment: the database assigns every id

The four schemes at a glance

The four schemes at a glance
Scheme	Size	Coordination needed	Sorts by time?	Guessable?
Auto-increment	4–8 bytes	Every id — the database assigns it	Yes	Yes, trivially
UUIDv4	16 bytes	None	No	No
ULID / UUIDv7	16 bytes	None	Yes (to the millisecond)	No — the random half is unguessable
Snowflake-style	8 bytes	Once, to assign a machine id	Yes	Partly — the sequence is predictable

Snowflake-style bit layout (a common 64-bit arrangement)

Snowflake-style bit layout (a common 64-bit arrangement)
Field	Bits	Gives you
Sign (unused)	1	Keeps the value positive in signed 64-bit types
Timestamp (ms since a custom epoch)	41	~69 years of range from the chosen epoch
Machine / shard id	10	1,024 independent generators
Sequence within the millisecond	12	4,096 ids per generator per millisecond

Remember: Four schemes, distinguished by how much coordination an id costs to make. Auto-increment is smallest and ordered but needs the database and is guessable, so keep it internal. UUIDv4 needs no coordination and sorts nowhere. ULID and UUIDv7 put a millisecond timestamp in front of random bits, so ids are independently generatable, unguessable and still time-ordered — the usual choice for anything public or client-generated. Snowflake-style packs time, machine id and sequence into 64 bits for compact ordered ids at high volume, at the cost of assigning machine ids and depending on a clock that never steps backward.

The five properties

Randomness, sortability, coordination, index locality and collisions — and the tension a timestamp prefix resolves.

Randomness, sortability, coordination, index locality and collisions

coreintermediate

The four schemes differ along five properties, and knowing which property you actually need is what turns the choice from preference into engineering. Randomness is about guessability: a random id cannot be enumerated, which matters the moment ids appear in URLs, and it is a defence in depth rather than a substitute for authorization. Sortability is whether ordering ids gives you creation order — useful for pagination, for range queries over recent records, and for debugging, and it comes free with a timestamp prefix. Coordination is how much has to be agreed before an id can be produced: none for a UUID, a one-off machine assignment for Snowflake, a round trip to the database for auto-increment. Index locality is the property that surprises people: a B-tree index inserts each new key at its sorted position, so sequential keys all land on the same trailing page — a small, hot, cached region — while random keys land anywhere across the whole index, so every insert touches a different page and, once the index exceeds memory, becomes a disk read. That is the concrete cost of a random primary key on a large, high-insert table, and it is exactly what a timestamp prefix removes. Collision properties are the last: an auto-increment sequence cannot collide within one database and always collides across two, a 122-bit random UUID has a collision probability small enough to ignore, and Snowflake ids are unique only while machine ids are unique and clocks never step backward. Note that randomness and sortability pull against each other and a timestamp prefix is the standard compromise: order the high bits, randomise the low ones.

Think of it as

A filing cabinet where every insert means walking to the right drawer. Sequential ids mean you are always at the same open drawer at the front — one page stays warm and everything is fast. Random ids scatter you across every drawer in the room, and once the cabinet is bigger than what you can hold in your arms, each insert is a separate trip. That is index locality, and it is why the same random id that costs nothing in a hundred-thousand-row table costs a great deal in a hundred-million-row one.

text

B-tree insert positions, 4 consecutive inserts

sequential ids           random ids
[..][..][..][XXXX]       [.X.][...][..X][.X.]
              ^^^^        ^      ^     ^   ^
one page, already in     four pages, three of
memory, stays hot        them read from disk

What we're doing: Watch the random-key penalty appear as a table grows past memory, and remove it with a timestamp prefix.

index-locality.txttext

Same table, same hardware, same insert code.
Only the primary key type differs.

Rows: 500,000  (index fits comfortably in RAM)
  UUIDv4 key    inserts fine
  ULID key      inserts fine
  Difference: not measurable. This is the size
  every benchmark and every test suite uses.

Rows: 200,000,000 (index far exceeds RAM)
  ULID key    : each insert targets the trailing
                page, which is already cached.
                Reads from disk per insert: ~0.
  UUIDv4 key  : each insert targets a random
                page among millions. Reads from
                disk per insert: ~1, plus a
                write of that page.

The UUIDv4 table is also physically larger for
the same data, because pages split part-full
when keys arrive out of order rather than
filling to the end.

The fix does not require giving up
unguessability -- only giving up putting the
random bits FIRST:
  UUIDv4  : random from byte 0   -> scattered
  UUIDv7  : 48 bits of time, then random
            -> clustered, and still unguessable
            for any practical purpose

7: This is why the problem reaches production. At development and staging sizes the index fits in memory and every key type performs identically, so nothing in the test suite can distinguish them.
16: One disk read plus one page write per insert, instead of writing to an already-cached page. That is the concrete mechanism behind "UUID primary keys are slow", and it is a property of the access pattern rather than of the id being 16 bytes.
27: The tension between randomness and locality is resolved by ordering, not by choosing sides. Only the high bits need to be ordered for locality, and only the low bits need to be random for unguessability, so a timestamp prefix gives both.

Why this works: Index locality is the one property in this list that is invisible until scale and then dominates, which is why it deserves to be decided at design time rather than discovered. Framing the choice as "ordered high bits, random low bits" replaces the usual UUID-versus-integer argument with a design that satisfies both requirements.

Treating an unguessable id as an access-control mechanism

Wrong

python

@app.get("/documents/{doc_id}")
def get_document(doc_id):
    return db.get(doc_id)   # "the id is a UUID,
                            # nobody can guess it"

Better

python

@app.get("/documents/{doc_id}")
def get_document(doc_id, user):
    doc = db.get(doc_id)
    require_access(user, doc)   # the id is not
    return doc                  # a credential

What you see: A document reaches someone it should not, through a shared link, a browser history, a referrer header, a support ticket screenshot, or a search-engine crawl of a page that embedded the URL — and the system has no record of an unauthorised access, because as far as it is concerned the request was legitimate.

Why: Randomness makes an id hard to guess, which is a property of the id, not of the request. Ids leak through every channel a URL travels along, so unguessability delays discovery rather than preventing access — authorization has to be checked on every request regardless of how the identifier was constructed.

Where the inserts land

Time-ordered key (auto-increment, ULID, Snowflake)

+Every insert lands on the trailing index page
+That page stays in memory and stays hot
+Index pages fill densely, so the index stays compact
+Range queries over recent records are contiguous reads

Random key (UUIDv4)

−Each insert lands on an arbitrary page
−Once the index exceeds memory, most inserts read from disk
−Pages split part-full, so the index grows larger than its data warrants
−No creation-order range query without a second index

Five properties across the four schemes

Five properties across the four schemes
Property	Auto-increment	UUIDv4	ULID / UUIDv7	Snowflake
Randomness (unguessable)	No	Yes	Yes (low bits)	Partly
Sortability (creation order)	Yes	No	Yes, to the millisecond	Yes, to the millisecond
Coordination	Per id	None	None	Once, per machine
Index locality	Best	Worst	Good	Good
Collision risk	None in one db; certain across two	Negligible	Negligible	None if machine ids and clocks hold

Which property each requirement actually needs

Which property each requirement actually needs
Requirement	Property needed	Cheapest scheme that provides it
Ids appear in public URLs	Randomness	ULID / UUIDv7
Cursor pagination over recent records	Sortability	ULID / UUIDv7 / Snowflake
Client generates ids offline	No coordination	UUIDv7 / ULID
High insert rate on a very large table	Index locality	Auto-increment, or a timestamp-prefixed id
Merging two databases later	Global uniqueness	Anything except auto-increment
Minimum storage at extreme volume	Compactness	Snowflake (8 bytes)

Remember: Five properties: randomness (unguessable, and never a substitute for authorization), sortability (id order equals creation order), coordination cost (none, one-off, or per id), index locality (sequential keys stay on one hot page, random keys scatter across the whole index once it exceeds memory), and collision behaviour. Randomness and locality pull against each other, and a timestamp prefix resolves it — ordered high bits for locality, random low bits for unguessability, which is precisely what ULID, UUIDv7 and Snowflake do.