UUID v4 vs v7 vs ULID: Which ID Should Your Database Use

Choosing an identifier format looks like a five-minute decision and turns into a performance investigation eighteen months later. The reason is that the property everyone evaluates — uniqueness — is the one where every option is fine, and the property nobody evaluates — ordering — is the one that decides how your database behaves at scale.

Uniqueness is a solved problem

A UUID v4 is 122 random bits. The chance of a collision is negligible in any sense that matters: you would need to generate billions of them per second for decades before the probability became worth a line of code.

So if you are comparing formats on collision risk, stop. They are all safe. Generate a few and look at them instead — the interesting differences are visible in the strings themselves.

The problem is where new rows land

Most databases store table rows in a B-tree ordered by the primary key. InnoDB does this literally — the table is the index. PostgreSQL keeps the heap separate but still maintains a B-tree for the primary key.

With a sequential key, every insert lands at the right-hand edge of the tree. The page you are writing to is the page you just wrote to, so it is already in memory, it fills up neatly, and the tree grows in one direction.

With a random key, every insert lands in an unpredictable position. Three things follow, and they compound:

Cache misses. The page you need is scattered somewhere across an index that may be far larger than RAM. Every insert becomes a potential read from disk before it can be a write.

Page splits. Inserting into the middle of a full page splits it in two. Splits are expensive and they leave both halves partly empty.

Fragmentation. All those half-empty pages mean the index occupies far more space than its data requires, which means less of it fits in cache, which causes more misses. The problem feeds itself.

None of this shows up in development. It shows up when the index outgrows memory, which is a threshold you cross once, without warning, in production.

UUID v7 fixes it

UUID v7 keeps the 128-bit UUID shape and the familiar hyphenated formatting, and changes what goes in the first 48 bits: a Unix timestamp in milliseconds. The remaining bits are random.

The consequence is that v7 values generated later sort after values generated earlier, as plain strings and as bytes. Inserts return to landing at the edge of the tree. You get sequential-insert behaviour without a central sequence generator, so any number of services can mint IDs independently and the ordering still holds.

Two smaller wins come free. You can read the creation time out of the ID, which is occasionally useful in debugging. And range queries over a time window can use the primary key.

ULID: the same idea, different clothes

ULID predates v7 and solves the same problem: 48 bits of timestamp followed by 80 bits of randomness. The differences are presentational, and they matter more than you would think:

• 26 characters, Crockford base32, versus 36 with hyphens. Shorter in URLs and logs.
• No hyphens, so double-clicking selects the whole thing.
• Case-insensitive, and the alphabet excludes I, L, O and U to avoid transcription mistakes and accidental profanity.
• Lexicographically sortable as text, which is genuinely handy when IDs land in a log file or a Redis key.

The cost is ecosystem. UUID has native column types, native functions and first-class support in every driver, ORM and admin tool. ULID usually rides in a char(26) and needs a library. If you store a ULID as text where a UUID would have been 16 bytes, you have also just doubled the size of every index that touches it.

What about v1?

UUID v1 is also time-ordered, so it sounds like it should work. Two problems.

It embeds the machine's MAC address, which leaks infrastructure detail into every URL. And its timestamp bytes are laid out most-significant-last, so the values do not sort chronologically without byte-shuffling. Some databases offer a rearranging function for exactly this reason. If you are choosing today, v7 is what v1 was trying to be.

A decision that holds up

Use UUID v7 for primary keys in a new system. It is the boring correct answer: index locality, no coordination between services, native database support, and a format nobody has to learn.

Use ULID when the ID is going to be read, typed or clicked by humans, or where 10 fewer characters per identifier matters across millions of log lines. Accept that you will carry a library.

Use UUID v4 when the ID must reveal nothing. A v7 or a ULID tells anyone holding it when the record was created — usually harmless, occasionally not. Password reset tokens, invitation links and share URLs should stay fully random, and honestly should be generated as opaque tokens rather than as UUIDs at all.

Use a plain auto-increment integer when there is one database, one writer, and no reason for IDs to be unguessable. It is smaller and faster than every option above. Distributed identifiers solve a distribution problem; if you do not have one, do not buy the ID format that fixes it.

Two things to get right whichever you pick

Store it as bytes, not as text. A UUID is 16 bytes. Stored as a 36-character string it is more than double that, in the primary key, repeated in every secondary index and every foreign key. On a large table this is the difference between an index that fits in memory and one that does not.

Never expose a sequential integer you did not mean to expose. If /orders/1042 is a valid URL, so is /orders/1041, and the only thing standing between a user and someone else's order is your authorisation check. Random or time-ordered IDs are not an access control mechanism, but they do remove the invitation.

The pattern across all of this: uniqueness is easy and everybody gets it right. Locality is invisible until it is expensive, and it is the thing worth choosing for.