ULID AND UUIDv7 - SORTABLE IDENTIFIERS

Jaime Marinas -

This is one of those things I'd had written down for a long time. At a previous job it was on our list of improvements, the kind you know are there, that someday will have to be done, and that always lose out to the next urgent feature. I'm not there anymore, but the curiosity stayed, so I sat down to study it properly, to have it clear the next time I have to decide how to identify the records of a system.

The underlying question is very simple: what do I use as the identifier of my records? And it turns out the two usual answers both have drawbacks.

The problem with the classic options

Auto-increment (IDENTITY in SQL Server, SERIAL in Postgres) is convenient and fast, but:

  • The database generates the id, so you don't know it until you've inserted. If you want to create an object, fire a domain event with its id and save everything afterwards, you can't.
  • It leaks information: if your order is number 1043 and tomorrow's is 1087, anyone can tell how many orders you get per day. And guessing other people's ids is as easy as adding one.
  • Merging data from several databases is a pain, because every one of them has its record 1, its record 2...

The random GUID (UUID version 4) solves all of that: it's generated anywhere, it reveals nothing and it never collides. But it has a less visible and fairly serious problem: it's random. If you use it as a primary key with a clustered index, every insert lands somewhere arbitrary in the index instead of at the end. The database has to keep splitting pages to make room, the index gets fragmented and grows more than it should, and inserts get slower as the table grows.

The ideal would be something generated anywhere, like a GUID, but always increasing, like an auto-increment. And that's exactly what ULID and UUIDv7 are.

ULID

ULID (Universally Unique Lexicographically Sortable Identifier) is a 128-bit identifier, just like a GUID, but with a trick: the first 48 bits are the date and time in milliseconds, and the remaining 80 are random. It's represented as a 26-character string:

01ARZ3NDEKTSV4RRFFQ69G5FAV
|--------||--------------|
   time        random

Since the time goes first, ULIDs generated later are always "greater" than earlier ones, and sorting them alphabetically is the same as sorting them by creation date. Also:

  • They use Crockford's Base32 encoding, which avoids letters that get mixed up (there's no I, L, O or U), so they're shorter and easier to read than a GUID, and they carry no hyphens or odd characters, so they work in a URL.
  • If you generate several in the same millisecond, the specification includes a monotonic mode that increments the random part, so they keep coming out sorted.

Using them is trivial. In JavaScript:

import { ulid } from 'ulid';

const id = ulid(); // 01J8Z3K2M4N5P6Q7R8S9T0V1W2

And in .NET, with the Ulid package:

var id = Ulid.NewUlid();
Guid comoGuid = id.ToGuid();

And in Java, with the ulid-creator library. To generate several in the same millisecond and have them come out sorted, use the monotonic variant:

import com.github.f4b6a3.ulid.Ulid;
import com.github.f4b6a3.ulid.UlidCreator;
import java.util.UUID;

Ulid id = UlidCreator.getMonotonicUlid();   // 01M508H53K8JKHQ6450QSQ4JEE
String texto = id.toString();
UUID comoUuid = id.toUuid();                // to store it in a uuid column

UUIDv7: the same thing, but standard

The problem with ULID is that it's a community specification, not a standard. And in 2024 RFC 9562 arrived, which updates the definition of UUIDs and includes a new version, the 7th, that does practically the same thing: 48 bits of millisecond timestamp up front, and the rest random.

0192a3b4-c5d6-7e8f-9a0b-1c2d3e4f5a6b
              ^
          version 7

The big advantage of UUIDv7 is that it is a UUID. It fits any uuid or uniqueidentifier column, any library and any API that already works with GUIDs, with no conversions. And native support is arriving everywhere:

// .NET 9 and later, no packages
var id = Guid.CreateVersion7();

-- PostgreSQL 18 and later
SELECT uuidv7();

// Node, with the uuid package
import { v7 } from 'uuid';
const id = v7();

In Java, JDK 26 already ships a method to build a UUIDv7 from a date, but it doesn't guarantee the order between two ids generated in the same millisecond. If you need that, and on earlier JDK versions, the uuid-creator library takes care of it:

import com.github.f4b6a3.uuid.UuidCreator;
import java.util.UUID;

// JDK 26 and later, no libraries
UUID id = UUID.ofEpochMillis(System.currentTimeMillis());

// With uuid-creator (com.github.f4b6a3:uuid-creator)
UUID id = UuidCreator.getTimeOrderedEpoch();

Both return a version 7 java.util.UUID, so they fit any uuid column with no conversions.

Watch out for SQL Server

Here comes the detail that, had we made that change back then, would have cost us dearly, because we worked with SQL Server there. SQL Server doesn't sort uniqueidentifier values the way you'd expect, byte by byte from left to right: it compares them starting with the last group of the GUID. So even though a UUIDv7 carries the time up front, for SQL Server that time isn't what drives the ordering, and the index still fragments just like with a random GUID.

The options to avoid the trap:

  • Store the identifier as binary(16) instead of uniqueidentifier, which does sort byte by byte.
  • If the id only has to be unique and you don't care about the order, keep a clustered index on another column (a creation date or an internal auto-increment) and use the UUIDv7 as a business key with a nonclustered index.
  • And watch out for .NET too, which reverses the order of the first groups when it converts a Guid to bytes. If you store it as binary, make sure the conversion respects big-endian order.

In Postgres, on the other hand, the uuid type sorts byte by byte, and UUIDv7 works as is, with nothing special to do.

Which one to pick?

Today, for a new system, I'd go with UUIDv7: it's a standard, it fits everything that already works with GUIDs and native support keeps growing. ULID still makes sense if the identifier is seen a lot (in URLs, on screens, in logs somebody will read), because it's shorter and friendlier to the eye. And they're not incompatible: both are 128 bits with the time up front, so you can go from one to the other without losing anything.

One last thing to keep in mind: both carry the creation date inside, and anyone can extract it. Usually that doesn't matter, but if the moment something was created is sensitive information, better not to expose that id to the outside.

To wrap up

That improvement stayed on the list, and it's probably still there. Changing the identifier type in a system that's already in production isn't something you do on a Friday afternoon. But at least now I'm clear on where I'd go the next time I start something from scratch: a UUIDv7 generated in the application, and a close look at how the database in question sorts identifiers. Because, as the SQL Server case shows, an identifier being sortable is worth nothing if whoever stores it doesn't sort it the way you expect.