2026-07-02 · Calc-Tools Editor

UUID Generator: What is UUID and How to Generate Unique Identifiers

Choosing an ID scheme is a five-minute decision you pay for over the next three years. It gets baked into table definitions, API contracts, log fields and cache keys, and once traffic is live the migration cost is far higher than it looked at design time. Auto-increment integers, UUID v4, UUID v7, ULID, Snowflake IDs and database sequences can all "generate a unique string", but they diverge sharply on sortability, storage footprint, index friendliness and guessability, and those differences only become visible past ten million rows.

This page does not repeat how UUID versions are computed or how the 8-4-4-4-12 format breaks down; that belongs to the UUID Generator tool page. The question here is narrower and more practical: which scheme should you pick for your case, and what does it cost once you have picked it. Every probability and size figure below is calculated directly from the birthday bound and raw byte counts, so you can reproduce them.

1. Five Questions That Eliminate Most Options

Line the candidates up against one ruler and ask five questions in order:

• Sortability — does the ID carry a time order? This decides whether it can serve as the clustered key, and whether range scans and cursor pagination need a separate timestamp column.
• Length and storage — 8 bytes for an integer, 16 for binary, 36 for a string. The gap gets multiplied inside every index.
• Distributed generation — can any machine mint an ID independently, with no central allocator? This decides whether active-active regions, edge writes and offline imports are possible.
• Guessability — given one ID, can someone derive its neighbours or infer your volume? That is a privacy problem and an access-control problem at the same time.
• Index friendliness — does a new value append to the end of the index, or land in the middle of an existing page? This is the most expensive dimension at the database layer.

Sortability and guessability pull in opposite directions. An ordered ID is easier to enumerate; an unguessable random ID is inherently unordered. UUID v7 and ULID exist to split the difference: a millisecond timestamp up front for order, random bits behind it so values inside the same millisecond cannot be predicted.

2. Six ID Schemes Side by Side

The table below places all six candidates on the same dimensions. The length column gives both the string form and the binary storage form, because that distinction lands directly in index size:

The last row and the v4 row are the two that get misread most often. A Snowflake ID is only 8 bytes but is not strictly ordered: within a single millisecond it counts up through a 12-bit sequence, so a sequence wrap or a clock step backwards can produce an ID smaller than one already issued. And the v4 row's "fully unordered" is not a minor footnote — it means every insert may land at a random position in the index tree, which is the entire subject of the next section.

One more counter-intuitive point: order buys you more than length does. A 16-byte v7 and an 8-byte Snowflake ID differ by a factor of two in width, but if both insert close to sequentially they will both outperform a 16-byte v4 that inserts randomly.

3. Why Random Primary Keys Slow a Database Down

InnoDB's clustered index stores rows in primary key order, 16 KB to a page. When each new key is larger than every existing key, the insert simply appends to the rightmost page, a fresh page opens when it fills, and average page fill stays around 15/16, roughly 93.75%. When keys are fully random, every insert first has to locate the page the key belongs to; if that page is already full, the engine splits it in two. That is a page split. Afterwards each half is about half full, so average fill drops to roughly 50%.

For the same index and the same number of rows, random inserts take about 93.75% ÷ 50% ≈ 1.875 times the space of sequential inserts, and each split writes additional redo and undo records while random page reads churn the buffer pool. The conclusion is not that UUIDs are unusable — it is that a randomly ordered primary key carries a bill of its own.

The second bill arrives with secondary indexes. Every InnoDB secondary index entry carries the primary key, so a wider primary key makes every secondary index wider. Take a 100-million-row orders table with five secondary indexes:

These figures count only the key bytes themselves, without B-tree page overhead or fill-factor loss; apply the 1.875 factor above and a random key grows again. Three fixes, in order of impact: first, switch to a time-ordered version such as UUID v7 or ULID so inserts become sequential again; second, store BINARY(16) instead of CHAR(36) everywhere, which alone more than halves the affected index size; third, if a random key is unavoidable, demote it to a unique index column and cluster on a separate auto-increment integer.

4. What Each UUID Version Leaks

The versions differ not only in how they are computed but in what the result exposes. For selection purposes the second question matters more than the first.

The v1 problem gets worse in the cloud. It encodes the moment of generation together with a node identifier. On physical hardware that reveals a network card address; on a cloud instance the card is virtual, so what leaks is the host-assigned identity, enough to tell an observer that two records came from the same instance and, on metered billing, enough to infer instance counts and scaling patterns. v1 timestamps also tick in 100-nanosecond units, so a clock stepping backwards can produce an ID smaller than an earlier one and silently break anything that sorts by it. The remaining good fit for v1 is narrow: a private network, a strict time-order requirement, and a willingness to accept hardware identity inside that boundary.

The v4 boundary is whether you need order and whether you can leak time. If the ID never enters a B-tree index, v4 is the least fuss: public API resource identifiers, idempotency keys, log trace ids, lock holder identifiers. If it will be a database or log primary key, v4 is the wrong version. One related misconception is worth clearing up: 122 random bits do not automatically make a v4 string a secure token. Whether the randomness is adequate depends on the source. crypto.randomUUID() or a language's cryptographic random API is fine; a "UUID" assembled from Math.random(), C's rand() or any predictable PRNG has the same shape and none of the strength. For what actually happens inside a random generator, see the random number generator guide; when you need random strings at a specific scale, use the random number generator and confirm it draws from a secure source.

The v5 value and its risk are the same property. It hashes a namespace plus a name, so identical input always yields an identical UUID, which makes it ideal for mapping a stable natural key (a URL, a username, an external order reference) onto an internal ID: synchronisation between systems then deduplicates for free. The flip side is that determinism means predictability, so anyone who knows your namespace and naming rule can compute your IDs. Never use v5 for invitation codes, redemption codes or password reset tokens. A namespace is also permanent in practice: change it and every derived ID changes with it.

v7 was formalised in RFC 9562, published in 2024, which superseded RFC 4122 from 2005. Its layout is a 48-bit millisecond timestamp plus 74 random bits: the first 48 bits keep values increasing over time so inserts stay close to sequential, and the remaining 74 keep IDs generated in the same millisecond both unique and unpredictable. It removes both v4 weaknesses at once, random insertion and the absence of ordering, at the price of a readable creation time. If that timestamp is sensitive, a signup time or an order time being business intelligence in its own right, v7 needs extra handling.

The hard part of Snowflake IDs is operational, not algorithmic. The 64 bits split into a 41-bit millisecond timestamp, a 10-bit node id and a 12-bit sequence: 41 bits of milliseconds last about 69.7 years, and 12 bits of sequence allow 4,096 IDs per node per millisecond. The first hazard is clock rollback. When NTP corrects a node's clock backwards, the new timestamp is smaller than the previous one, and an implementation without a guard can issue duplicates. The second hazard is node id reuse. Instances cloned from a VM snapshot or restored from a container image carry the same configuration, so two nodes running the same node id turn collisions from a probability into a certainty. Both hazards are removed by configuration management and explicit detection, not by luck.

5. How Large Is the Collision Risk, Really?

UUID v4 carries 122 random bits, a space of 2^122, about 5.317×10^36 values. But "the chance that this next ID collides" and "the chance that at least one collision has happened after generating a large batch" are very different numbers, and the second one comes from the birthday bound: p ≈ 1 - e^(-n² / 2N), where n is the number generated so far and N is the size of the space. The table below evaluates it for N = 2^122:

Two things follow. First, below roughly 10^15 records random collisions are negligible — 10^15 rows at even 100 bytes each is already on the order of a hundred million gigabytes, and no system gets there. Second, "UUIDs never collide" has a precondition: the random source really supplies 122 bits of entropy. Degrade the source and the digit count looks unchanged while the safety margin collapses.

The next table is the one worth remembering. Suppose a "UUID" in your system actually provides only 32 bits of randomness, the width of an 8-character hex code and the practical strength of many convenience random APIs:

Truncating a UUID, or using a random 8-character hex string as an invite code, cuts the space from 2^122 to 2^32, about 4.3 billion values. The collision rate climbs fast, and the enumeration cost drops to something a single ordinary machine can work through. Short codes need separate design: a wider alphabet and a server-side uniqueness check, both of them.

6. Three Worked Examples

Example 1: index footprint for 100 million orders under three key schemes. Take 100 million rows with five secondary indexes. Scheme A uses an auto-increment BIGINT: 8 bytes × 100M = 0.8 GB for the key column, plus 8 bytes carried by each of five secondary indexes = 4 GB, for 4.8 GB in total. Scheme B stores UUID v4 as CHAR(36): 3.6 GB for the key column plus 36 bytes × 5 indexes = 18 GB, for 21.6 GB. Scheme C stores UUID v7 as BINARY(16): 1.6 GB plus 8 GB, for 9.6 GB. On that dimension alone Scheme B is 4.5 times Scheme A and 2.25 times Scheme C. Add the split penalty and Scheme B's random key multiplies by roughly 1.875, landing near 40 GB, while Scheme C inserts close to sequentially and pays almost none of it. That is what "v4 and v7 are both UUIDs but the bill differs by a factor of two" looks like in practice.

Example 2: throughput per millisecond. A Snowflake node has only 4,096 sequence values per millisecond, so beyond that it must block until the next millisecond. Peak single-node throughput is therefore capped at about 4.096 million IDs per second, and it cannot be raised by adding CPUs, only by adding node ids. A ULID has 80 random bits inside each millisecond, a space of about 1.209×10^24, and UUID v7 has 74, a space of about 1.889×10^22. By the birthday bound, generating one million v7 IDs inside the same millisecond carries a collision probability of roughly 2.6×10^-11. The time-ordered random formats have no per-millisecond ceiling; the trade is that node-id and sequence management is replaced by a requirement on random quality.

Example 3: a Snowflake ID changes when it passes through JSON. A Snowflake ID is a 64-bit integer, typically in the 10^18 range, while JavaScript's Number is exact only up to 2^53 - 1, or 9007199254740991. When a browser parses an API response, everything above that limit is silently flattened: the three distinct integers 1234567890123456789, 1234567890123456790 and 1234567890123456800 all become the same value, 1234567890123456800. Nothing throws. Two different orders simply collapse into one identifier, and the damage surfaces later as unreproducible mix-ups in cache keys, client-side routes or idempotency checks. There is exactly one fix: 64-bit integer IDs must be serialised as strings in JSON payloads, and the same rule covers any integer above 2^53, such as minor currency units or nanosecond timestamps. When tracking this down, pasting the raw response into a JSON formatter and validator is usually faster than setting a breakpoint, and IDs must always be compared as strings, since anything that converts them to numbers first hits the same trap.

7. Frequently Asked Questions

How many UUID v4 values before collisions matter?

By the birthday bound, generating 3.26×10^15 IDs gives roughly a one-in-a-million chance of a single collision, and it takes 10^18 IDs to reach about 9%. Every real system is far below that, so the risk worth defending against is not random collision but duplicate writes, degraded entropy sources and truncated UUIDs.

What does using UUIDs as MySQL primary keys actually cost?

Two bills. Random inserts split pages and drop the fill factor from about 93.75% to about 50%, inflating the index to roughly 1.875 times a sequential one and writing extra redo; and wider keys inflate everything else, with CHAR(36) coming to about 21.6 GB against 9.6 GB for BINARY(16) at 100 million rows with five secondary indexes. Switching to v7 or ULID for ordering and storing BINARY(16) removes most of it.

Should I choose UUID v4 or v7?

Ask one question: will this ID enter a database index? If yes, use v7, whose leading 48 bits are a millisecond timestamp so inserts stay close to sequential. If no, use v4, which leaks no creation time and needs no input. Remember that v7 writes the creation time into the ID, so when that time is sensitive, v4 is the right answer.

How do ULID and UUID v7 differ?

Both start with a 48-bit millisecond timestamp and both sort correctly; the difference is the written form. ULID uses Crockford Base32 and fits in 26 characters, while v7 keeps the standard 36-character UUID layout. ULID is shorter and easier to read but cannot be stored in a native database UUID type; v7 is an RFC 9562 standard that virtually every UUID library and database type accepts. Choose v7 for native database support and ULID when the value only travels through application code and length matters.

Is truncating a UUID to 8 characters safe for invite codes?

No. Cutting it to 8 hex characters leaves 32 bits of randomness, about 4.3 billion values; a direct calculation gives roughly a 1.2% collision rate at 10,000 codes and about 69% at 100,000. Enumeration is cheap enough for one ordinary machine. Short codes need their own design with a wider alphabet and a server-side uniqueness check, and truncation is not a generation method.

8. Four Misconceptions

Misconception 1: a UUID is absolutely unique. It is probabilistically unique, not mathematically unique. What actually puts duplicates into production is almost never randomness: it is the same ID written twice, a random source that has degraded to a predictable PRNG, or a truncation added for cosmetic reasons.

Misconception 2: using UUIDs makes you distributed-friendly at no cost. The distribution benefit is real; the zero cost is not. The cost sits in the index: random insertions, wider keys, and amplification through every secondary index. Choosing v7 over v4 and BINARY(16) over CHAR(36) keeps the distributed property and pays most of the bill down.

Misconception 3: v5 is the more advanced choice because it is reproducible. Reproducible means predictable. Anyone who knows the namespace and the naming rule can compute your IDs, so v5 must never back anything that has to be unguessable. Its real home is synchronisation, where the same natural key derives the same ID in every system and deduplication comes for free.

Misconception 4: a longer ID only looks different. The difference between 36 characters and 16 bytes is the difference between 21.6 GB and 9.6 GB at 100 million rows with five secondary indexes, and the fill-factor penalty on top of that means more pages read per query. Key width is an architectural parameter, not a formatting preference.

9. Five Practical Recommendations

• Decide whether the ID enters a B-tree first. If it will be indexed, as a database key, a log key or a time-series key, pick an ordered scheme such as UUID v7, ULID or Snowflake. If it will not, as a public resource identifier, an idempotency key, a trace id or a lock holder, v4 is the least fuss.
• Store binary, not text. Keep UUIDs in BINARY(16) rather than CHAR(36) and convert in the application layer. This needs no change of scheme and still more than halves the affected index size.
• Separate internal keys from public identifiers. Cluster on a compact ordered key for join and index efficiency, and expose a second, unguessable random column to the outside world. You then avoid trading index performance for public safety, or the reverse.
• Put two safety nets under ID generation. Keep a unique constraint in the database so a collision surfaces as an error instead of a silent overwrite, and confirm the random source is cryptographic, using crypto.randomUUID() or its equivalents rather than Math.random() or a stock PRNG.
• If Snowflake is unavoidable, manage node ids as configuration. Hand node ids out from a configuration service, never bake them into images, and handle clock rollback explicitly by refusing to mint or by waiting for the clock to catch up. Cloned VMs and restored container snapshots are the most common source of duplicate node ids.

Treating an ID as part of the data contract reduces the choice to three steps: decide whether it is indexed, decide whether it must be unguessable, then pick the implementation by width and operational cost. To mint a batch of IDs for an import or a load test, use the UUID Generator; if those IDs will then be signed or transported as encoded text, read the Base64 encoding explainer first so the encoding step does not add size and escaping problems of its own.