Choosing an ID: UUID v4 vs v7 vs ULID vs nanoid

Our companion explainer on UUIDs and random IDs covers what a UUID is and why Math.random() is the wrong source. This guide assumes that and answers the question that comes next: which ID format should this column use? The four serious candidates are UUIDv4, UUIDv7, ULID and nanoid. They differ on five axes: ordering, index behavior, size, text format and collision margin.

The four formats side by side

All numbers below were produced on Python 3.14.5, which ships both uuid.uuid4() and uuid.uuid7().

| | UUIDv4 | UUIDv7 | ULID | nanoid (default) | |---|---|---|---|---| | Specified in | RFC 9562 | RFC 9562 | ulid/spec | ai/nanoid (a library, not a spec) | | Binary size | 128 bits | 128 bits | 128 bits | none; 21 chars | | Random bits | 122 | 74 | 80 | 126 | | Timestamp | none | 48-bit Unix ms | 48-bit Unix ms | none | | Text form | 36 chars, hex and hyphens | 36 chars | 26 chars, Crockford Base32 | 21 chars, A-Za-z0-9_- | | Sorts by creation time | no | yes | yes | no |

Real examples, in the same order:

f64d32f9-ddc9-4f68-bc7e-9468c7656ac8      v4
01a11723-7791-7675-aeb6-edbd379e37ab      v7
01M4BJ6XWY506TDY39D0NHZ5GY                ULID
CI8HuPiz6Bx7D906UZnrp                     nanoid

The first 12 hex digits of the v7 value are the timestamp. Reading those 48 bits as an integer in the test gave 1791389562769, identical to int(time.time()*1000) at that moment. A v7 UUID is therefore a creation timestamp that anyone holding the ID can read. That is a feature for debugging and a leak if the ID is public and the creation time is sensitive.

Sortability

RFC 9562 puts the timestamp in the most significant bits of v7, so byte-wise or lexicographic comparison orders IDs by creation time (at millisecond granularity). ULID does the same by construction, and because Crockford's Base32 alphabet is ordered, the 26-character string sorts the same way as the 128-bit value. I generated five v4 and five v7 UUIDs, 2 ms apart:

u4 = [uuid.uuid4() for _ in range(5)];  u4 == sorted(u4)   # False (true by chance 1 time in 120)
u7 = []
for _ in range(5): u7.append(uuid.uuid7()); time.sleep(0.002)
u7 == sorted(u7)                                           # True

Two details matter. First, ordering is only guaranteed across milliseconds unless the generator adds a counter; RFC 9562 describes optional monotonic counters for v7 and the ULID spec defines monotonic increments within a millisecond, but a naive library does neither. Second, nanoid and v4 carry no order at all, so ORDER BY id is meaningless and you need a separate created_at column.

Why random primary keys hurt B-trees

A B-tree index keeps keys in sorted order across fixed-size pages. With a time-ordered key, every insert lands on the rightmost page, which stays hot in cache, and finished pages are left full. With a random key, every insert lands on a random leaf somewhere in the tree, so the working set is the entire index, and when a leaf is full it splits in half, leaving two half-empty pages. RFC 9562 states the consequence directly: non-time-ordered UUIDs such as v4 have poor database-index locality.

To see the size of the effect I built a toy leaf-page model: 100 keys per page, split in half on overflow, except that appending to the rightmost page starts a new page (which is what real engines do for monotonic keys). I inserted 50,000 keys each:

v4 (random):  725 pages, 724 splits, average fill 69%
v7 (ordered): 500 pages, 499 splits, average fill 100%

The random index needs 45% more pages for the same rows and sits near ln 2 ≈ 69% full, the classic result for random B-tree inserts. This is a deliberately simplified model with no internal nodes, no fillfactor and no page cache, so treat the ratios as directional. The cache-miss cost, which the model does not show, is usually the larger problem on big tables: each random insert touches a page that probably is not in memory.

The effect is real only when the key is indexed and written heavily. For a table of 50,000 rows, the choice is irrelevant. It starts to matter when the index outgrows RAM.

What the databases store

PostgreSQL has a native uuid type that stores 16 bytes and accepts the 36-character text form. gen_random_uuid() (built in since PostgreSQL 13) produces v4. According to the current UUID functions documentation, PostgreSQL 18 adds uuidv4() as an alias and uuidv7(), so you can default a column to uuidv7() with no extension. A ULID or nanoid has no native type: use text (26 or 21 characters plus a 1-byte length header), or convert a ULID to uuid since both are 128 bits.

MySQL has no UUID type. The storage choices are CHAR(36), which costs 36 bytes in an ASCII character set and up to 144 when reserved under utf8mb4, or BINARY(16) using UUID_TO_BIN() and BIN_TO_UUID() (MySQL 8.0). UUID_TO_BIN has a swap flag that moves the timestamp bits of a version 1 UUID to the front for index locality; it does nothing useful for v4 and is unnecessary for v7, which is already in order. Because InnoDB's primary key is the clustered index and every secondary index entry carries a copy of it, a 36-byte random key costs you more than once.

Rule of thumb: store UUIDs in the 16-byte form, and keep the pretty string for the API boundary.

Length and URL safety

A UUID is 36 characters, hyphens included; strip them for 32. A ULID is 26 and nanoid's default is 21. All three use characters that pass through a URL unescaped: hex digits and hyphens, Crockford Base32 (digits and uppercase letters, with I, L, O and U excluded to avoid misreading), and nanoid's A-Za-z0-9_-, which is the RFC 3986 unreserved set minus . and ~. Two practical differences: ULIDs are case-insensitive by design, so they are safe in case-folding contexts such as hostnames and some filesystems, while nanoid is case-sensitive and a and A are different IDs; and nanoid's _ and - can appear at the start or end, which some copy-and-paste tools handle badly.

Collision math

For n IDs drawn uniformly from 2^b values, the birthday approximation gives a collision probability of about 1 - exp(-n² / 2^(b+1)). I computed it in Python:

format                      bits   n for 50% chance   n for 1-in-a-billion
UUID v4 (122 random bits)   122    2.715e18           1.031e14
nanoid, 21 chars (126)      126    1.086e19           4.125e14
ULID, within one ms (80)     80    1.295e12           4.917e07
UUID v7, within one ms (74)  74    1.618e11           6.146e06
nanoid, 10 chars (60)        60    1.264e9            4.802e04

At one billion IDs per second, reaching a 50% chance with v4 takes 86.0 years, and 1.03e14 IDs for a one-in-a-billion chance matches the commonly quoted figure of about 103 trillion v4 UUIDs for a one-in-a-billion chance of a duplicate. A trillion v4 IDs have a collision probability of about 9.4e-14; a trillion default nanoids, 5.9e-15.

Note the two "within one ms" rows. Time-ordered formats only have their random bits to separate IDs created in the same millisecond, because IDs in different milliseconds differ in the timestamp. So the right comparison is collisions among IDs created in one tick: 1,000 ULIDs in the same millisecond collide with probability 4e-19, and even a million in one millisecond is 4e-13. Total volume does not matter much. That is why the time-ordered formats are not less safe in practice.

The one place the math bites is a truncated random ID. A 10-character nanoid carries 60 bits; a million of them collide with probability 4.3e-7, and 100 million with probability 0.43%. Shortening nanoid is a deliberate trade against that curve, not a free win.

All of this assumes a CSPRNG. The uniformity assumption is exactly what a weak generator violates, whatever the format's bit count is.

How to choose

• New relational table, ID is the primary key, any meaningful write rate: UUIDv7 in a native 16-byte column. You keep UUID tooling, get insert locality and need no extension on PostgreSQL 18.
• Existing tooling expects v4, writes are light, or exposing creation time is a problem: UUIDv4.
• Time-ordered but you want a short, case-insensitive, copy-friendly string, and no tool needs UUID syntax: ULID. Beware that many ULID libraries are not monotonic within a millisecond unless you opt in.
• Shortest practical public identifier (slugs, invite codes, short links), no ordering needed: nanoid, with a stated length and alphabet, ideally behind a unique index that retries on conflict.
• Mixed: an internal v7 primary key plus an opaque public ID (v4 or nanoid) in a separate unique column, so that URLs do not reveal creation order or timestamps.

None of these are secrets. A v4 is unguessable only if generated from a CSPRNG, and an ID in a URL is still not an authorization check; always verify access server side. The hub's UUID generator produces v4; ULID and nanoid generators are being added to the hub.