Unique ID generation

The problem it solves

An orders table lives in one Postgres database with a BIGSERIAL key. Every insert takes the next number, and every number is unique because one process hands them all out. Then the table is split across 16 shards. Each shard has its own sequence, so order 1,001 now exists 16 times, and a customer who quotes “order 1001” could mean any of them.

Splitting the database is only the first way auto-increment breaks. A mobile app that works offline must name a draft before it reaches any server. A payment request carries an ID so a retry is recognized as the same request. An event is published to a queue before its row is written. In each case the ID has to exist before, or without, the database insert.

This page is the reference for the schemes that solve this. The unique ID generator problem designs a full ID service in depth: Snowflake layout, clocks that go backwards, worker leases, and multi-region. This page compares the options, and the numbers behind them.

Five requirements decide which scheme fits. Settle them before naming one.

Requirement	The question	Why it changes the answer
Uniqueness scope	Unique in one table, one company, or across systems that merge?	A per-shard counter is fine for one shard; data that merges needs global IDs
Sortability	Should ID order follow creation order, and how closely?	Time-ordered keys keep B-tree inserts at the right edge and give free cursors
Size	Must it fit a 64 bit integer column, or is 128 bits fine?	64 bits fits BIGINT but needs coordination; 128 bits has room for randomness
Coordination	May creating an ID wait on another machine?	Per-ID coordination costs a round trip and a single point of failure
Leakage	Will the ID appear in URLs or to other customers?	Counters reveal volume; time-based IDs reveal creation time

Two more questions follow from those. What’s the peak rate: thousands a second, or millions? And who creates the ID: a server you run, or a client you do not trust?

How it works

Step 1: One counter in the database

The simplest scheme is the database’s own counter: AUTO_INCREMENT in MySQL, an identity column or sequence in Postgres. It produces small, dense, perfectly ordered 64 bit numbers, and for one database it is the right answer.

Its limits are all about being one counter:

• One writer. Every ID comes from one primary. Sharding, multi-primary setups and a second region all need another plan.
• The ID arrives after the insert. The application learns the ID from the insert’s result. So it cannot name the row first, put the ID in a message, or use it as an idempotency key.
• Gaps are normal. A Postgres sequence never hands a value back. So a rolled-back transaction leaves a hole. And sessions that cache 50 values leave holes of 50 when they end. Code that assumes “no gaps”, or “max ID equals row count”, is wrong.
• Sizes run out. A signed 32 bit INT stops at 2,147,483,647. At 100 inserts a second that is eight months. Basecamp 3 was read-only for hours in November 2018 when one table’s 32 bit key reached its limit. Use 64 bit keys from the start.
• It leaks volume. Anyone who sees order 1,042 today and order 1,302 a week later knows you take about 37 orders a day.

Step 2: A shared counter: ticket servers and leased ranges

Keep the counter, but take it out of the data database, so every shard can use it. Flickr did this in 2010 with ticket servers. That’s a MySQL table with one row. Each request runs REPLACE INTO Tickets64 (stub) VALUES ('a'), then SELECT LAST_INSERT_ID(). Two ticket servers avoid a single point of failure by counting in steps of 2. One starts at 1, and one at 2. So one hands out odd IDs, and the other even ones.

That still costs a network round trip per ID. And every ID in the company passes through two databases. The fix is to take numbers in blocks. A leased range (also called hi/lo, or the segment method) lets each app server claim a block of, say, 10,000 IDs in one transaction. It then hands them out from memory.

Leased ranges: one round trip per block, not per ID

Scroll or pinch to zoom, drag to pan

• Color key
• Service

The allocator now sees one request per 10,000 IDs, so a single small database serves the whole company. Meituan’s open-source Leaf service and Hibernate’s hilo and pooled generators work this way.

TradeoffLeased ranges

We gain

64 bit, dense, unique without any clock. No coordination on the hot path, and the allocator can be down for as long as the current block lasts.

We give up

A crashed server wastes the rest of its block. IDs are only roughly ordered across servers. (Server A may still be in block 4.21M, while B is in 4.38M.) And they leak volume, like any counter.

Step 3: No coordination at all: random IDs

If every ID is a large random number, no machine needs to ask anyone. That is UUIDv4: 128 bits, of which 6 are fixed (4 for the version, 2 for the variant) and 122 are random. Any server, phone or browser can make one with no network call.

The cost of skipping coordination is a chance of collision. It follows the birthday bound: with N possible values and n IDs made, the chance that any two match is about

p ≈ n² / (2N)          (accurate while p is small)
n at 50% ≈ 1.18 × √N

The number that matters is √N, not N. A random 64 bit ID has √N ≈ 4.3 billion. So it reaches 50% around 5 billion IDs. Random 64 bit IDs are not safe for big tables. With 122 bits, √N ≈ 2.3 × 10¹⁸:

Random bits	Space N	p after 1 billion	p after 1 trillion	n for a 50% chance
64	1.8 × 10¹⁹	2.7%	certain	5.1 billion
80 (ULID, per ms)	1.2 × 10²⁴	4 × 10⁻⁷	34%	1.3 × 10¹²
122 (UUIDv4)	5.3 × 10³⁶	10⁻¹⁹	10⁻¹³	2.7 × 10¹⁸

ULID and UUIDv7 only need their random bits to differ within one millisecond, so their row is far safer than it looks. 2.7 × 10¹⁸ is a billion IDs a second for 86 years.

In practice, UUIDv4 collisions come from broken random number generators, not from the math. Think of a VM restored from a snapshot, which replays the same random state. Or a process that forks without reseeding. Or a library that uses a non-cryptographic generator, such as Math.random(). Use the operating system’s generator (crypto.randomUUID(), SecureRandom, secrets). And keep the unique constraint on the column anyway.

UUIDv4 has two real costs. It is 16 bytes, twice a BIGINT. And it is random in order, which hurts every B-tree index it goes into (Step 6).

Step 4: Time first, then randomness: UUIDv7 and ULID

Put a timestamp in the high bits and randomness in the low bits. IDs then sort by creation time while still needing no coordination. UUIDv7, standardized in RFC 9562 (May 2024), is the current standard form:

Bits	Field	Content
48	unix_ts_ms	Milliseconds since 1970, big-endian; lasts until about year 10,889
4	ver	0111, version 7
12	rand_a	Random, or a counter inside the millisecond
2	var	10, the RFC variant
62	rand_b	Random

The string form keeps that order. So 01a180f3-4c6b-728e-9957-a73af22b340b starts with the time, in its first 12 hex digits. And two UUIDv7s compare correctly as strings, as bytes, or as a native uuid column.

Inside one millisecond, the timestamp ties, so order falls back to the random bits. RFC 9562 lets a generator fix that. Use rand_a as a 12 bit counter that starts at a random value each millisecond, and counts up (its “method 1”). IDs from one generator then sort in exact creation order. IDs from different servers in the same millisecond still interleave by random bits. That’s the best anything without coordination can do.

JUuidV7.javaPyuuid_v7.pyC#UuidV7.csGouuid_v7.goTSuuid-v7.ts

JavaPythonC#GoTypeScript

JavaPythonC#GoTypeScript

import java.security.SecureRandom;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

// UUIDv7 as RFC 9562 lays it out: 48 bits of Unix milliseconds, 4 version bits (0111),
// 12 bits rand_a, 2 variant bits (10), 62 bits rand_b. Here rand_a is a counter inside the
// millisecond (the RFC's "method 1"), so IDs from this generator sort in creation order.
class UuidV7 {
    private final SecureRandom random = new SecureRandom(); // never java.util.Random: guessable
    private long lastMs = -1;
    private int counter;

    public synchronized UUID next() {
        // Never step backwards: if the clock jumps back (NTP, a VM resume), keep the last
        // millisecond and keep counting, so order and uniqueness survive.
        long ms = Math.max(System.currentTimeMillis(), lastMs);
        if (ms == lastMs) {
            counter++;
            if (counter > 0xFFF) { // 4,096 IDs in one millisecond: borrow the next one
                ms++;
                counter = random.nextInt(0x800);
            }
        } else {
            // A random start in each new millisecond, top bit clear so there is room to count.
            counter = random.nextInt(0x800);
        }
        lastMs = ms;

        long msb = (ms << 16) | (0x7L << 12) | counter;
        long lsb = (random.nextLong() & 0x3FFFFFFFFFFFFFFFL) | 0x8000000000000000L; // variant 10
        return new UUID(msb, lsb);
    }

    // The creation time is readable by anyone: it is the first 48 bits.
    public static Instant timeOf(UUID id) {
        return Instant.ofEpochMilli(id.getMostSignificantBits() >>> 16);
    }
}

class Main {
    public static void main(String[] args) {
        UuidV7 gen = new UuidV7();
        List<String> ids = new ArrayList<>();
        for (int i = 0; i < 5; i++) ids.add(gen.next().toString());
        ids.forEach(System.out::println); // 01a180f3-4c6b-728e-..., the same prefix for the same ms

        List<String> sorted = new ArrayList<>(ids);
        sorted.sort(null);
        System.out.println(sorted.equals(ids)); // true: string order is creation order
        System.out.println(UuidV7.timeOf(UUID.fromString(ids.get(0)))); // e.g. 2026-10-28T09:14:03.123Z
        System.out.println(UUID.fromString(ids.get(0)).version()); // 7
    }
}

ULID (2016) is the same idea, from before the RFC existed. It’s 48 bits of milliseconds and 80 random bits, written as 26 characters of Crockford Base32 (01M60FF8EWQSZEXACJJQXPJK3N). Crockford’s alphabet drops I, L, O and U, so it is case-insensitive and hard to misread. Its monotonic mode adds one to the random part, for IDs in the same millisecond. For new systems, UUIDv7 does the same job, and fits every database’s native uuid type. ULID remains common where a short string matters.

KSUID (Segment, 2017) is 160 bits: 32 bits of seconds since a 2014 epoch, plus 128 random bits, written as 27 Base62 characters. It sorts only to the second, and it is bigger than a UUID. But its 128 random bits are useful where the ID must also be hard to guess.

Step 5: Fit it in 64 bits: Snowflake

UUIDv7 needs 128 bits because, with no coordination, only a wide random field avoids collisions. Coordinate once per process instead of once per ID, and 64 bits are enough. Twitter’s Snowflake (2010) packs three fields into a signed 64 bit integer:

Bits	Field	Content
1	sign	Always 0, so the ID is positive in every language
41	timestamp	Milliseconds since a custom epoch; 2⁴¹ ms is about 69.7 years
10	machine	Worker ID, unique among live generators: 1,024 of them
12	sequence	Counter inside the millisecond: 4,096 IDs per ms per worker

The machine field replaces the randomness. Two workers can never produce the same ID, because their machine bits differ. And one worker never repeats, because its (time, sequence) pair only moves forward. Each worker can make 4.096 million IDs a second. The whole space makes 4 billion a second.

The scheme rests on two promises that distributed systems do not give for free, and both belong to the problem page:

• Every live process has a different worker ID. Assign it from a lease in ZooKeeper, etcd or a database, not from a config file. And stop minting when the lease cannot be renewed.
• A worker’s clock never goes backwards. NTP corrections and VM pauses move clocks backwards by milliseconds or more. A generator remembers the last timestamp and waits, or refuses, until the clock passes it.

Variants spend the bits differently. Instagram uses 41 bits of time, 13 bits of logical shard, and 10 bits of sequence. So the ID also says which shard holds the row. Sony’s Sonyflake uses 39 bits of 10 ms units (174 years), 8 bits of sequence, and 16 bits of machine. Discord uses Twitter’s layout, with a 2015 epoch.

Generate a few IDs of each kind below, and a burst of five in one millisecond. Each character is colored by the field it comes from. Then sort each scheme. UUIDv4 comes out in random order, and the others in creation order. Turn the monotonic option off, and burst again. Same-millisecond UUIDv7s and ULIDs fall back to random order. Snowflake’s sequence keeps them in line.

Decode IDsB-tree inserts

Generate one of eachBurst: 5 in one millisecondClear

Monotonic inside a millisecond (UUIDv7 counter, ULID increment)

• Timestamp
• Counter or sequence
• Machine
• Random
• Version, variant, sign

UUIDv4128 bits, 36 chars

press Generate

UUIDv7128 bits, 36 chars

press Generate

ULID128 bits, 26 chars

press Generate

Snowflake64 bits, up to 19 digits

press Generate

Sort the recent IDs of one scheme by their value:

UUIDv4UUIDv7ULIDSnowflake

Generate a few IDs, then sort them.

Step 6: Why order matters to the index

A primary key is usually a B-tree, and in MySQL’s InnoDB the table itself is stored in primary key order. Where each new key lands decides how much the insert costs. Switch the demo above to B-tree inserts and add 100 keys of each kind.

• Time-ordered keys (UUIDv7, ULID, Snowflake, a sequence) always land on the rightmost leaf page. That page is always in memory. When it fills, the database starts a new page, and leaves the old one full. InnoDB fills pages to about 15/16 for sequential inserts. Postgres fills them to its 90% leaf fillfactor.
• Random keys (UUIDv4) land on any leaf. Every leaf page is equally likely to be needed. So the whole index must be in memory, to avoid disk reads. And full pages split down the middle, leaving pages around 70% full on average. With a 500 GB index and a 64 GB buffer pool, about 87% of inserts touch a page that is not in memory.
• Roughly ordered keys from many servers whose clocks differ by a few milliseconds land near the right edge. A few middle splits happen, but the pages being written still fit in memory.

Random inserts also write more. Postgres logs the whole 8 KB page the first time it changes after each checkpoint. So touching many different pages multiplies the write-ahead log, and every replica must replay it. The effect grows with the table. Once the index is much larger than memory, random keys mean more disk reads per insert, more page splits, and more WAL per row. Time-ordered keys keep all three close to a sequence’s.

The opposite holds in range-partitioned stores such as Spanner, Bigtable, HBase and CockroachDB. There, time-ordered keys all go to the last range, so one server takes every write. Those stores prefer random keys, or a time-ordered ID behind a hash prefix. See partitioning for why.

Which key order suits which store

Scroll or pinch to zoom, drag to pan

• Color key
• Service

Options and tradeoffs

The schemes side by side

Scheme	Bits	Sortable by time	Coordination	Collisions	Leaks
Auto-increment	64	Exactly	Every ID, one database	None	Count and growth rate
Ticket server	64	Exactly per server	Every ID, a round trip	None	Count and growth rate
Leased ranges	64	Roughly, by block	One round trip per block	None	Count, roughly
UUIDv4	128 (122 random)	No	None	50% at 2.7 × 10¹⁸	Nothing
UUIDv7	128 (74 random)	To the ms; exact per generator with a counter	None	Only within one ms: negligible	Creation time
ULID	128 (80 random)	To the ms	None	Only within one ms: negligible	Creation time
KSUID	160 (128 random)	To the second	None	Negligible	Creation time
Snowflake	64	To the ms; exact per worker	Worker ID lease, at startup	None, if worker IDs and clocks hold	Time, worker, rough rate

“Negligible” for UUIDv7 is concrete: two generators collide only if they pick the same millisecond and the same 74 random bits. With 1,000 servers each minting an ID every millisecond, the chance of any collision is about one in a million per year.

Choosing a scheme

Scroll or pinch to zoom, drag to pan

• Color key
• Service
• Database
• Background worker

TradeoffUUIDv7 as the default for new services

We gain

No coordination, no worker IDs, and no clock-backwards duplicates (random bits cover it). Time order for indexes and cursors. And a native uuid type in Postgres and most drivers.

We give up

16 bytes in the primary key, and in every secondary index that carries it. Creation time visible to anyone who sees the ID. And no fit for systems that need 64 bit integers.

64 bits or 128 bits

Choose	When	Watch out for
64 bit	Huge tables with many secondary indexes, IDs in compact encodings or short URLs	Needs coordination (sequence, ranges, worker IDs); above 2⁵³ JavaScript loses precision
128 bit	IDs made by clients or many independent services, data that may merge later	Double the key size; text storage (36 bytes) instead of binary (16) doubles it again

In InnoDB, every secondary index entry stores the primary key. So the key size is paid once per index. A table of 1 billion rows with five secondary indexes stores the key six times. That’s 48 GB as BIGINT, 96 GB as binary UUIDs, and over 200 GB as UUID strings.

TradeoffSnowflake instead of UUIDv7 at large scale

We gain

Half the key size in every index, a plain integer every language and database handles, and exact order per worker.

We give up

A worker ID lease service, clock monitoring, a bits budget that ends in about 70 years, and JSON APIs that must send IDs as strings.

Exposing IDs publicly

An ID that appears in a URL is read by people you do not control. Three separate concerns apply.

Enumeration. If /invoices/1042 works, someone will try /invoices/1043. The defense is an authorization check on every read, never a hard-to-guess ID. An unguessable ID is a second layer, and only random IDs give it. UUIDv4’s 122 bits cannot be walked. UUIDv7’s 74 random bits cannot either. Snowflake’s time and worker fields can be guessed, and its sequence is small.

Leakage. Counters reveal how many rows exist and how fast they grow. Time-based IDs reveal when each row was created: anyone can shift a Snowflake ID right by 22 bits and read the millisecond. If that matters (an order count a competitor should not see, a signup date a user should not reveal), show a different ID in public.

Encoding. Encoding makes an ID shorter, not secret.

Encoding	64 bit ID	128 bit ID	Notes
Decimal	Up to 19 or 20 chars	39 chars	Snowflake’s usual form
Hex (UUID text)	16 chars	32, or 36 with dashes	The standard UUID form
Crockford Base32	13 chars	26 chars	Case-insensitive, no I L O U: safe to read aloud
Base62	11 chars	22 chars	URL-safe, case-sensitive: needs a binary collation

Use an alphabet in ASCII order, and a fixed width. Then the encoded strings sort in the same order as the numbers, when compared byte by byte. So a time-ordered ID stays time-ordered as text.

JBase62.javaPyexample.pyC#Base62.csGomain.goTSexample.ts

JavaPythonC#GoTypeScript

JavaPythonC#GoTypeScript

import java.util.ArrayList;
import java.util.List;

// A 64 bit ID as 11 Base62 characters, for URLs. The alphabet is in ASCII order (digits,
// then upper case, then lower case), and every ID is padded to the same width, so sorting the
// strings byte by byte sorts the IDs. Encoding hides nothing: anyone can decode it.
class Base62 {
    static final String ALPHABET =
        "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
    static final int WIDTH = 11; // 62^11 is about 5.2e19, more than 2^64 (1.8e19)

    static String encode(long id) {
        char[] out = new char[WIDTH];
        // Unsigned arithmetic: a 64 bit ID may use the top bit, which Java calls negative.
        for (int i = WIDTH - 1; i >= 0; i--) {
            out[i] = ALPHABET.charAt((int) Long.remainderUnsigned(id, 62));
            id = Long.divideUnsigned(id, 62);
        }
        return new String(out);
    }

    static long decode(String s) {
        if (s.length() != WIDTH) throw new IllegalArgumentException("bad length");
        long id = 0;
        for (char c : s.toCharArray()) {
            int d = ALPHABET.indexOf(c);
            if (d < 0) throw new IllegalArgumentException("bad character");
            // Reject strings above 2^64 - 1 instead of silently wrapping to another ID.
            if (Long.compareUnsigned(id, Long.divideUnsigned(-1L - d, 62)) > 0)
                throw new IllegalArgumentException("overflow");
            id = id * 62 + d;
        }
        return id;
    }
}

class Main {
    public static void main(String[] args) {
        long[] ids = {1_541_815_603_606_036_480L, 1_541_815_603_606_036_481L, 42L};
        List<String> codes = new ArrayList<>();
        for (long id : ids) {
            String c = Base62.encode(id);
            codes.add(c);
            System.out.println(id + " -> " + c + " -> " + Base62.decode(c));
        }
        // 1ptWyK4WgZU, then 1ptWyK4WgZV: the next ID is one character away, still guessable
        List<String> sorted = new ArrayList<>(codes);
        sorted.sort(null);
        System.out.println(sorted); // [0000000000g, 1ptWyK4WgZU, 1ptWyK4WgZV]: numeric order
        try {
            Base62.decode("zzzzzzzzzzz"); // above 2^64 - 1
        } catch (IllegalArgumentException e) {
            System.out.println(e.getMessage()); // overflow
        }
    }
}

Hashids are not encryption. Hashids, now renamed Sqids, turn 1042 into something like jR8kW, with a shuffled alphabet. It hides the number from people, not from scripts. The mapping is reversible by design, and the project’s own documentation says not to use it for security. When the public ID must hide time and volume, use one of these:

• A keyed permutation. Encrypt the 64 bit ID with a small block cipher, for example a Feistel network keyed with a secret. Decrypt it on the way in. It’s unique, reversible, and opaque. The URL shortener builds one.
• A separate random public ID. Store a UUIDv4 or a 128 bit random token next to the internal key, with a unique index. Costs a column and an index, but nothing about the internal ID leaks.

Secrets such as password reset links and unlisted share links always get their own random token of at least 128 bits. They are capabilities, not IDs.

What the database already offers

Before building anything, check what the database does natively.

Database	Counters	UUIDs and others
PostgreSQL	Identity columns and sequences	uuid type; gen_random_uuid() (v4) since 13; uuidv7() since 18
MySQL	AUTO_INCREMENT	UUID() is version 1; UUID_TO_BIN(u, 1) moves its time bits first for index order
SQL Server	IDENTITY, sequences	NEWID() (random), NEWSEQUENTIALID() (ordered per machine, restarts lower after a Windows reboot)
MongoDB	None	ObjectId: 4 bytes of seconds, 5 random bytes per process, 3 byte counter; sorts to the second
CockroachDB	unique_rowid(): timestamp and node ID	gen_random_uuid(), recommended to spread writes across ranges
Spanner	Bit-reversed sequences, to avoid hot ranges	GENERATE_UUID() (v4)
DynamoDB	None	None: generate in the application

Go deeperSQL Server and Java sort UUIDs their own way1 min

SQL Server’s uniqueidentifier compares the last six bytes first, then the groups before them in an unusual order. A UUIDv7 stored there does not sort by time. Store it as binary(16), or use NEWSEQUENTIALID(), which is generated to match SQL Server’s order.

Java’s UUID.compareTo compares the two 64 bit halves as signed numbers. For UUIDv7, this happens to work. The top half starts with a positive timestamp. Every bottom half starts with the variant bits 10. So all bottom halves are negative, and compare in the right order among themselves. For other layouts, compare the bytes as unsigned.

MySQL’s UUID() is version 1, whose timestamp is stored low bits first, so raw v1 UUIDs are not time-ordered. UUID_TO_BIN(uuid, 1) swaps the time fields into order when storing as BINARY(16).

How it fails

Failure	What happens	How you detect it	Fix
Clock steps backwards	A Snowflake worker reuses milliseconds it already used: duplicate IDs	Generator counts “clock behind last ID” events	Remember the last timestamp; wait out small steps, refuse and alert on big ones
Two processes, one worker ID	Both mint identical IDs in the same millisecond	Unique key violations on insert	Worker IDs from an expiring lease; stop minting when the lease cannot be renewed
Bad randomness	Cloned VMs or forked processes produce the same “random” UUIDs	Unique key violations, often in bursts after a deploy	OS cryptographic generator only; never seed from time; keep the unique constraint
Counter goes back after restore	A database restored from backup hands out IDs that already exist elsewhere	Inserts fail, or worse, references point at the wrong row	Move sequences past the highest ID seen anywhere before taking writes
32 bit key runs out	Inserts fail at 2,147,483,647; the table is effectively read-only	Monitor remaining headroom per key column	64 bit keys from day one; migrating later rewrites the table and every foreign key
Precision loss in JavaScript	A 64 bit ID above 2⁵³ is rounded when parsed as a number, pointing at another row	Clients request IDs that do not exist	Send 64 bit IDs as JSON strings; Twitter’s API added id_str for this reason
Case-insensitive collation	Base62 IDs aB3 and Ab3 compare equal: unique key errors or wrong rows	Duplicate key errors on IDs that look different	Binary or _bin collation on the column, or store the integer and encode only at the edge
Ticket server or allocator down	No new IDs, so no inserts	Allocation errors and latency	Two servers (odd and even), or leased ranges with blocks that last minutes

Before MySQL 8.0, InnoDB kept the AUTO_INCREMENT counter only in memory and reset it to the largest existing ID plus one at restart. Deleting the newest rows and restarting reused their IDs, which broke anything that still referred to them. MySQL 8.0 persists the counter.

Common mistake

Checking for a UUID collision before inserting

A lookup before every insert costs a read and still races with a concurrent insert. With a good random generator, the chance it ever finds anything is far below the chance of a disk corrupting the row. Keep the unique constraint, let the rare violation fail the insert, and retry with a new ID. Spend the effort on making sure the generator is a cryptographic one.

At scale

Number	Value
UUIDv4 or v7 generation in process	About a microsecond or less, no network
Snowflake capacity	4,096 IDs per ms per worker, 4.1 million a second
Snowflake lifetime	2⁴¹ ms ≈ 69.7 years from its epoch
Ticket server, one ID per round trip	Bounded by the network: about 0.5 ms in a data center
Leased range of 10,000 at 1,000 IDs/s	One allocator call every 10 seconds per server
Key size per row per index	8 bytes (BIGINT), 16 (binary UUID), 36 (UUID text)

At 10 times the insert rate, per-ID coordination is the first thing to go. A ticket server’s round trip per ID becomes the allocator’s whole load. So move to ranges, or to IDs made in process. Random primary keys start to hurt once the index outgrows memory. At a few thousand inserts a second, that happens within months.

At 100 times, the bits budget becomes the constraint. Snowflake’s 1,024 worker IDs run out with many small containers. And its 4,096 per millisecond per worker caps a hot worker. Rebalance the fields (fewer sequence bits, more worker bits). Give each region its own block of worker IDs. Or move services that do not need 64 bits to UUIDv7. Range-partitioned stores need a hash prefix before any time-ordered key.

What to monitor:

• Unique key violations on ID columns. Every one is a bug in generation, and the first sign of a clock, lease or randomness problem.
• Clock offset on every generator host (chrony or NTP reports), and generator events where the clock was behind the last timestamp.
• Worker ID lease renewals and failures, and how many worker IDs are free.
• Headroom: time until the timestamp field, a 32 bit column or the allocator’s range runs out.
• For leased ranges, IDs used per block, so wasted blocks after crashes stay visible.

In the interview

Interviewer asksWhy not use UUIDv4 for every primary key? It needs no coordination.

It is a fine ID, and a poor clustered key once the table is bigger than memory. Random keys land on random B-tree pages. So with a 500 GB index and a 64 GB buffer pool, most inserts read a page from disk. Splits leave pages about 70% full. And the write-ahead log grows, because many pages change per checkpoint. It is also 16 bytes in the primary key, and in every secondary index. I would use UUIDv7. It still needs no coordination, but it appends at the right edge like a sequence. If the table is small, or the store is range-partitioned like Spanner, UUIDv4 is fine, or even better.

Interviewer asksWhat is the chance two UUIDv4s collide, and when would you worry?

By the birthday bound, p ≈ n²/2N, with N = 2¹²². After a trillion IDs, that is about 10⁻¹³. A 50% chance needs 2.7 × 10¹⁸ IDs: a billion a second for 86 years. I would worry about the generator, not the math. A VM restored from a snapshot, or a forked process, can replay the same random state. And a non-cryptographic generator can repeat. So I use the OS generator, and keep a unique constraint. I would worry about the math with fewer bits. Random 64 bit IDs hit 50% at about 5 billion.

Interviewer asksYou need 64 bit IDs, sortable by time, from 200 servers. Design it.

Snowflake. 41 bits of milliseconds since a recent custom epoch (about 70 years). 10 bits of worker ID (1,024 workers). 12 bits of sequence (4,096 per millisecond each). Workers take their ID from an expiring lease in etcd or ZooKeeper. They stop minting if they cannot renew, so two processes never share one. The generator remembers its last timestamp. It waits if the clock goes backwards a little, and refuses and alerts if it goes back a lot. Clients get IDs as JSON strings. If 64 bits were not required, I would use UUIDv7, and skip the lease service.

Interviewer asksOur order IDs appear in URLs. Is that a problem?

It depends on the scheme. Sequential or Snowflake IDs leak order volume and timing. And sequential ones can be enumerated. But enumeration is solved by checking authorization on every read, never by obscure IDs. If volume is sensitive, I would show a different ID in public. Encrypt the internal 64 bit ID with a keyed permutation, or store a separate random public ID. Base62 or Hashids only change the alphabet. Anyone can decode them.

Common mistake

"Snowflake IDs are globally ordered"

They are ordered per worker, and only roughly across workers. Two servers whose clocks differ by 5 ms can produce IDs that sort in the opposite order to creation. That is fine for indexes, feeds and cursors. But it is not a total order. So it cannot decide which of two concurrent writes came first. When an exact order is required, as in a chat conversation, one owner per scope assigns a sequence number.

Mid-level covers

Explains why auto-increment stops working across shards. Names UUIDs and Snowflake. Draws the Snowflake bit layout. Knows UUIDs are 128 bits, and Snowflake IDs 64.

Senior adds

Starts from the requirements (scope, order, size, coordination, leakage). Gives the birthday bound and its numbers. Explains what random keys do to a B-tree, and the opposite effect in range-partitioned stores. Defaults to UUIDv7, and justifies Snowflake by size. Handles clocks and worker leases. Separates enumeration (authorization) from leakage (a public ID).

Seen in

• Design a unique ID generator: the full design, from one sequence to leased ranges to Snowflake. It covers clocks that go backwards, worker leases, and the bits budget.
• Design a URL shortener: leased counter ranges shuffled by a keyed permutation, because Snowflake’s 64 bits need 11 Base62 characters.
• Design Pastebin: two kinds of ID, a short one for public pastes and a 16 character random secret for unlisted ones.
• Design Instagram: 64 bit IDs with 13 bits of logical shard inside, so an ID alone names the shard. And a UUID upload key, for idempotent creates.
• Design a news feed: time-sortable Snowflake post IDs, so sorting by ID is sorting by time for cursors and timeline merges.
• Design a chat system: message order comes from a sequence number the server assigns per conversation, not from clocks or global IDs. A client message ID makes resends idempotent.

Recap

The problem. One database counter gives unique IDs for one writer. Shards, clients, retries and messages need IDs that exist before or without the insert.

Five requirements. Uniqueness scope, sortability, size (64 or 128 bits), coordination (per ID, per block, per process, none) and leakage. Then the rate and who creates the ID.

The schemes.

• Auto-increment and ticket servers: a round trip per ID.
• Leased ranges: one round trip per block of 10,000.
• UUIDv4: 122 random bits, no order.
• UUIDv7 and ULID: 48 bits of milliseconds, then random bits, sorted by time.
• Snowflake: 41 bits of time, 10 of worker, 12 of sequence, in 64 bits. It needs a worker lease, and a clock that must not go back.

The numbers. p ≈ n²/2N. Random 64 bits: 50% at 5 billion. UUIDv4: 50% at 2.7 × 10¹⁸. Snowflake: 4,096 per ms per worker, 69.7 years.

Index locality. Time-ordered keys append at the right edge of a B-tree, with full pages. Random keys touch any page, and leave them about 70% full. Range-partitioned stores want the opposite.

Public IDs. Authorization stops enumeration. Encoding is not secrecy, Hashids included. Hide time and volume with a keyed permutation or a separate random ID.

Further reading

• Design a unique ID generator for the full Snowflake service, and databases, indexing and partitioning for the B-tree and hot range effects.
• RFC 9562, Universally Unique IDentifiers: UUIDv7’s layout, the monotonic counter methods, and why UUIDs should not be used as security capabilities.
• Announcing Snowflake, Twitter engineering: the original layout and its “roughly sorted” promise.
• Ticket Servers: Distributed Unique Primary Keys on the Cheap, Flickr engineering: the two-server odd and even design.