UUID vs ULID vs Nano ID vs Auto-Increment IDs

Picking an ID format comes down to three questions. Do IDs need to sort by creation time? Will they appear in URLs or public APIs? And will the database store them in a native 128-bit column or as text? UUID v7, standardised in RFC 9562 in May 2024, changed the answer for many projects: it gives the time ordering that made ULID popular, in a format every UUID column and library already accepts.

At a glance

How each one is built

• Auto-increment is a counter kept by the database. A signed 32-bit INT ends at 2,147,483,647 and a signed BIGINT at 9,223,372,036,854,775,807. The application only learns the ID after the insert, unless it reserves values from a sequence first.
• UUID v4 is 122 random bits plus 6 fixed bits that mark the version and variant.
• UUID v7 starts with a 48-bit Unix timestamp in milliseconds, followed by the version, 12 bits called rand_a, the variant and 62 bits called rand_b. RFC 9562 lets a generator use some of those bits as a counter, so that IDs created in the same millisecond still come out in order. The generator on this site does that with the 12 bits of rand_a.
• ULID is a 48-bit millisecond timestamp plus 80 random bits, written as 26 characters of Crockford's Base32: 10 for the time and 16 for the randomness. The alphabet leaves out I, L, O and U and is case-insensitive. In monotonic mode, a ULID created in the same millisecond as the previous one takes that ULID's random part and adds one to it.
• Nano ID is a string of random characters, by default 21 of them from the 64-character alphabet A-Z a-z 0-9 _ -. Each character carries 6 bits, which gives 126 bits. Both the length and the alphabet can be changed, and the number of random bits changes with them.

A ULID and a UUID are both 128-bit numbers written differently. 01J9QKMZ1BFN79M7SVBJ6JWVRG and 01926f3a-7c2b-7d4e-9a1f-3b5c8d2e6f10 are the same value, in Base32 and in hex. That is why a ULID can be stored in a native UUID column, such as PostgreSQL's uuid, and keeps its sort order there. A ULID that did not start life as a v7 UUID has arbitrary bits where the version and variant belong, though, so a strict UUID validator may reject it.

Collision odds, computed

For purely random IDs the birthday approximation applies: with n IDs drawn from N = 2 to the power of the random bits, the chance of at least one duplicate is about 1 − e^(−n²/2N), and it reaches 50 percent near n = 1.18 × √N.

The last row is the one to remember: shortening a Nano ID for prettier URLs costs collision resistance quickly, so do this arithmetic before you choose a length.

For UUID v7 and ULID, two IDs can only collide if they carry the same millisecond, so n is the number of IDs created in one millisecond by generators that do not share a counter. With 1,000 such IDs in the same millisecond, the chance of a duplicate is about 2.6 × 10^-17 with 74 random bits and 4.1 × 10^-19 with ULID's 80. In practice the risk for every scheme here is a weak random source, such as Math.random, not the arithmetic.

Database index behaviour

Primary keys usually live in a B-tree index. When each new key is larger than the last, as with auto-increment, UUID v7 and ULID, inserts land on the right-hand edge of the index, pages fill up completely and recently written rows stay close together. Random keys, UUID v4 and Nano ID, land anywhere: pages split, end up partly empty, and once the index no longer fits in memory each insert can mean a random read.

The effect is stronger in MySQL and MariaDB with InnoDB, which store the rows themselves in primary key order, so random keys scatter the table and not only the index. PostgreSQL keeps rows in a separate heap, so the difference is limited to the index.

Storage format matters as much as the scheme:

• Store UUIDs and ULIDs in a native 16-byte type where one exists: uuid in PostgreSQL, UUID in MariaDB 10.7 and later, BINARY(16) in MySQL. CHAR(36) takes more than twice the space and makes every index larger.
• In MySQL, do not pass the swap flag to UUID_TO_BIN(uuid, 1) for v7 values. It moves the time fields around for the benefit of v1 UUIDs and would break v7 ordering.
• SQL Server's uniqueidentifier compares the last six bytes first, so v7 values stored there do not sort by time. See UUID vs GUID for the details.
• Nano IDs are strings and are stored as CHAR or VARCHAR, with a binary collation if case must be significant.

URLs and what an ID gives away

All five formats are safe in a URL without percent-encoding: hex digits, hyphens, Crockford Base32 and the Nano ID alphabet are all unreserved characters in RFC 3986. The difference is length (36, 26 or 21 characters) and case. UUIDs and ULIDs are case-insensitive, so they survive systems that change case; Nano IDs are not.

What an ID reveals is often the deciding factor:

• Auto-increment reveals volume. If your invoice is 1042 today and 1342 a week later, the company issued about 300 invoices that week. Sequential IDs also make it trivial to try neighbouring records, which turns any missing access check into a data leak.
• UUID v7 and ULID reveal when the record was created, to the millisecond. For orders that is rarely a problem; for user accounts or medical records it may be. Paste a v7 UUID into the inspector of the UUID v7 Generator and it shows the timestamp.
• UUID v4 and Nano ID reveal nothing about when or where they were made.

None of these IDs is a secret. An unguessable ID makes probing harder, but it does not replace checking whether the user may see the record.

Which one to choose

• Internal tables in a single database, never shown to users: auto-increment BIGINT. It is the smallest and simplest key.
• Primary keys created by the application or across services, where a native UUID column is available: UUID v7.
• Public identifiers where creation time must stay private, correlation IDs and file names: UUID v4, from the UUID v4 Generator or crypto.randomUUID().
• Short IDs in URLs where order does not matter: Nano ID at its default length or close to it.
• ULID when your stack already uses it or you want the shorter case-insensitive text form. For new projects, UUID v7 covers the same ground in a standard format.

A common combination gets the best of both: a BIGINT or UUID v7 primary key for joins and indexes, plus a separate random public ID with a unique index for URLs and APIs.