Unique IDs: UUID, ULID, Snowflake, sequences

Almost every design gives an ID to something: an order, a message, a short link. The interviewer asks: "How do you create the ID?" It sounds small. But the answer shows if you think about many servers making IDs at once, about sorting, and about size.

A weak answer says "use a UUID" or "use auto-increment" without a reason. A strong answer says what the ID must do here: be unique, be sortable by time, be short, or be hard to guess. Then it picks one and names its cost.

At SDE-1 level you should know a counter and a UUID. At SDE-2 level you should choose by job and say the cost. At SDE-3 level you should also explain what an ID generator does when servers fail or clocks are wrong.

Think of a big hospital with many registration desks. Each patient gets a token. There are three ways to hand out tokens:

• One desk counts for everyone. Tokens 1, 2, 3 and so on. No two patients get the same number. But every desk must ask this one desk, and everyone waits if it is busy.
• Each desk invents a long random code. The chance of two equal codes is so small that you can ignore it. No desk asks anyone. But the codes have no order.
• Each desk builds a code from the time, its desk number and its own count. No desk asks anyone, and the codes are in time order.

These are the three families of IDs. First, the job of an ID. A good ID is:

1. Unique. No two items share it. This is a must.
2. Made without waiting. Many servers create IDs at once, and none should ask a central place each time.
3. Sortable by time (often wanted). Newer IDs are larger, so sorting by ID gives the newest first.
4. Small. The ID is stored in the table, and again in every index and every table that points to it.
5. Hard to guess (sometimes wanted). If the ID is in a web address, nobody should be able to try the next number.

One more word: a bit is a single 0 or 1, and 8 bits make 1 byte.

Why order matters for the database. An index keeps its entries sorted in pages, as you saw in the lesson on indexes. If a new ID is bigger than all old IDs, its entry goes to the last page of the index. That page is already in memory, because the last inserts used it. If the new ID is random, its entry goes to a random page. When the index is bigger than memory, that page must first be read from disk. A page that is full must also be split into two pages. On a big table, this makes inserts slower.

1. Database sequence (auto-increment)

A sequence is a counter inside the database. Each new row takes the next number: 1, 2, 3. In Postgres you write id bigint GENERATED ALWAYS AS IDENTITY. It is small (8 bytes for a bigint), simple, and in order. Its costs:

• One place hands out the numbers. Every server must ask the database. No IDs can be made when the database is not reachable.
• Easy to guess. /order/10482 tells everyone that order 10483 probably exists, and shows how many orders you have.
• Gaps. A number that was handed out is not given back if the transaction is cancelled (rolled back), so gaps appear.
• Size of the type. A 32-bit integer stops at 2,147,483,647. Use bigint (up to 9,223,372,036,854,775,807) for big tables.

2. UUID version 4 (random)

A UUID (universally unique identifier) is a 128-bit number (16 bytes), written as 36 characters, such as 3f2b8c1e-.... Version 4 fills 122 of the 128 bits with random values; the other 6 bits mark the version. Any server can make one with no help. The chance of a repeat is tiny. With n IDs, the chance that any two are equal is about n x n / (2 x 2 to the power of 122). For one billion IDs (n = 1,000,000,000):

• n x n = 10 to the power of 18.
• 2 x 2 to the power of 122 = 2 to the power of 123, about 10.6 x 10 to the power of 36.
• Chance = about 9.4 in 10 to the power of 20, or about 1 in 10 to the power of 19. This formula is an estimate that holds when the chance is small.

The costs: it is twice the size of a bigint, it is long as text, and it has no order. So the inserts into a big index land on random pages.

3. Time-ordered IDs: UUID version 7 and ULID

These keep the 128 bits and the "no help needed" feature. They add the time at the front.

• UUID version 7 starts with a 48-bit time in milliseconds (ms), and fills most of the rest with random bits. It is an official standard (RFC 9562, a public standard document, May 2024). Postgres 18 (2025) has a built-in function uuidv7(). Older versions do not.
• ULID (a name for "unique, sortable ID") has a 48-bit time in milliseconds and 80 random bits. It is not an official standard, but the format is public. It is written in 26 characters that sort in time order.

New IDs are larger than old ones, so the index entries go to the end. Both reveal the time of creation, which can matter if that is private.

4. Snowflake-style IDs

A Snowflake ID fits in 64 bits (8 bytes). It was designed at Twitter, and its public design has these parts:

One machine can make 4,096 x 1,000 = 4,096,000 IDs per second. A "machine" here is one server that makes IDs. Each machine needs its own number, and you must make sure that two machines never get the same number. The generator also depends on the clock. If a clock jumps back, the machine could repeat an ID it already made. So a careful generator waits, or refuses, until the clock passes the last time it used.

5. Counter ranges

Sometimes you want a plain counter, but you cannot ask one place for every number. The fix is a counter range: each server asks the central counter for a block of numbers, such as 1 to 1,000. It hands those out from its own memory. When the block is used up, it asks for the next block. The central counter is called once per 1,000 IDs. The costs: if a server crashes, the unused part of its block is lost (a gap), and IDs are in order only inside a block.

To make a short code from a number, write the number in Base62. Base62 uses 62 symbols: 10 digits, 26 small letters and 26 capital letters. Seven symbols give 62 to the power of 7 = 3,521,614,606,208 codes (about 3.5 trillion).

What "ordered" really means

IDs from one counter are in exact order. Time-based IDs follow the clock of the machine that made them, to the millisecond. Two machines have clocks that differ a little. So two IDs made at almost the same moment may sort the wrong way round. Treat time-based IDs as "roughly in order".

A hard-to-guess ID is not a lock. Always check that the user may see the record.

SwadKart, the food-delivery app, needs four kinds of ID. Assumptions (made up to teach with): 1,000,000 orders per day for 5 years; orders peak at 120 per second; 100 new tracking links per second at peak; 20 app servers.

Step 1: the order table's own row ID (one database).

• Rows = 1,000,000 x 365 x 5 = 1,825,000,000.
• A 32-bit integer lasts 2,147,483,647 / 1,000,000 = 2,147 days = 5.9 years. That is too close to the 5-year plan, so use bigint.
• Choice: a bigint sequence. It is 8 bytes. All IDs are made in one database, and at 120 orders per second the database is not a limit.

Step 2: the order number the customer sees in the web address.

• This answers the predict box. A counter has two problems. First, it is guessable: anyone can try /order/10483 and then /order/10484. Second, it shows how many orders you make: a rival who reads the number on two days can subtract.
• Choice: a UUID version 7, or a ULID. It is hard to guess, and it is in time order.
• Size cost: 16 bytes instead of 8, so 8 bytes more per row. 1,825,000,000 x 8 bytes = 14,600,000,000 bytes = 14.6 GB more. This extra size repeats in every place the ID is stored: the table, its indexes and every table that points to it.

Step 3: IDs made by many servers, which must sort by time and stay small.

• Choice: Snowflake-style. It is 8 bytes, so it avoids the extra 14.6 GB of step 2. The 20 servers need 20 machine numbers, which is less than 1,024. Each can make 4,096,000 IDs per second, far above the load.

Step 4: the short tracking code.

• Assumption: 1,000,000 new links per day for 10 years. That is 1,000,000 x 365 x 10 = 3,650,000,000 links.
• 7 Base62 symbols give 3,521,614,606,208 codes. The share used is 3,650,000,000 / 3,521,614,606,208 = 0.10%.
• Counter ranges: each server takes blocks of 1,000. At the peak of 100 links per second, the central counter is called 100 / 1,000 = 0.1 times per second. That is once per 10 seconds, not 100 times per second. A crash loses at most 1,000 numbers: 1,000 / 3,521,614,606,208 = about 3 in 10,000,000,000, a tiny share.

• Sequence. It solves small, ordered IDs. It costs one place that hands out numbers, easy guessing and gaps. I would change it when many servers must create IDs, or when users see the IDs.
• Random UUID. It solves IDs that need no help from a central place. It costs 16 bytes and random inserts into the index. I would change it to a time-ordered ID when the table is big.
• UUID version 7 or ULID. It solves IDs with no central place, in time order. It costs 16 bytes and a visible creation time.
• Snowflake. It solves a small, ordered ID from many servers. It costs machine numbers to manage and a need for a good clock.
• Counter range. It solves too many calls to the central counter. It costs gaps after a crash and no exact order.

"UUIDs can repeat, so I must check the database each time." The chance is tiny: about 1 in 10 to the power of 19 for one billion IDs. A unique key in the table still protects you. Do not build extra checks.

"An ID with a timestamp gives the exact order of events." It does not. The time has a precision of one millisecond, and machine clocks differ. Two IDs made at nearly the same time can sort in either order.

"A random UUID is the best primary key because it never clashes." It never clashes. But it costs twice the size of a bigint, and random IDs scatter the inserts across the index. On a big table, a time-ordered ID is usually the better key.

1. Give the four parts of a Snowflake ID, with the number of bits for each.
2. A table gets 2,000,000 new rows per day. (a) After how many days does a 32-bit integer ID (maximum 2,147,483,647) run out? (b) How many extra bytes do 16-byte IDs need compared with 8-byte IDs, for 1,000,000,000 rows? (c) How many 6-symbol Base62 codes exist?
3. A table of 2 billion rows uses random UUID version 4 as its primary key. Inserts get slower as the table grows. Why, and what do you change?