Understanding ULID: what those 26 characters actually encode

You need the ten most recent rows. The table is keyed on UUID v4, so you write ORDER BY id DESC LIMIT 10 and get ten rows in no particular order — because there is no order in the value. You add a created_at column, an index on it, and now every "recent items" query does a second lookup that the primary key could have answered by itself. Multiply that across an event log, an audit trail, and a message table, and you have three indexes that exist only to recover information you threw away when you generated the key.

ULID is the format that refuses to throw it away. It's a 128-bit identifier laid out as a 48-bit Unix millisecond timestamp followed by 80 bits of randomness, encoded as 26 characters of Crockford base32. Sorting the strings sorts the records by creation time, exactly, with no extra column.

What's actually in the bytes

The sibling piece on this site compares UUID v4, v7 and ULID as a choice — which one to reach for. This is the other half: what physically differs between a v4 and a ULID once it's a value in your hand.

The sort property is not a happy accident of base32, it's a property of this base32. Crockford's alphabet is strictly ascending in ASCII — 0 through 9, then A through Z with the four ambiguous letters removed — so a character's symbol value and its byte value rise together. Compare the values character by character and you are comparing the underlying 128-bit integers. Standard base64 does not have this property: its alphabet runs A–Z, a–z, 0–9, so symbol value 52 (0, byte 0x30) sorts before symbol value 0 (A, byte 0x41), and a base64-encoded counter sorts into nonsense.

Decoding one by hand

Take the canonical example from the ULID spec, 01ARZ3NDEKTSV4RRFFQ69G5FAV. The first 10 characters are the timestamp; the remaining 16 are random and carry no meaning. Read the timestamp left to right, accumulating acc = acc * 32 + symbolValue:

1469922850259 milliseconds since the epoch is 2016-07-30T23:54:10.259Z. That's it — no library, no lookup table beyond the 32-symbol alphabet. The takeaway is that the timestamp is not metadata attached to the ID, it is the leading half of the ID, which is why sorting works and also why you should assume anyone holding a ULID knows when the record was created down to the millisecond.

If you want to check the arithmetic against a generator, the ULID generator takes an optional pinned timestamp, so you can feed it 1469922850259 and confirm it emits a value starting 01ARZ3NDEK.

The 130-bit trap

Twenty-six base32 characters hold 26 × 5 = 130 bits. A ULID is 128. The spec resolves this by capping the value: the largest valid ULID is 7ZZZZZZZZZZZZZZZZZZZZZZZZZ, which is why the first character of every real ULID is 0 through 7 and never 8 or Z.

This is the validation bug worth catching in review. A regex like the one below matches the shape of a ULID but accepts two extra bits of garbage, so ZZZZZZZZZZZZZZZZZZZZZZZZZZ sails through and then either wraps around or throws deep inside whatever decodes it. Anchor the first character.

# Accepts overflowed values — too permissive
^[0-9A-HJKMNP-TV-Z]{26}$

# Correct: first symbol is capped at 7
^[0-7][0-9A-HJKMNP-TV-Z]{25}$

The 48-bit timestamp has its own ceiling, at 281474976710655 ms — the year 10889. Not your problem, but worth knowing that the cap is on the timestamp field and the overflow bug above is a separate thing.

Same millisecond, still ordered

Two ULIDs generated in the same millisecond share their first 10 characters, and their relative order then comes down to 80 random bits — which is to say, it's arbitrary. For a log table that's fine. For anything that needs a strict sequence it isn't, so the spec defines monotonic generation: within a single millisecond, don't redraw the randomness, take the previous value's random component and increment it by one in the least significant bit, carrying as needed.

That gives you a genuine total order inside the millisecond, with room for 2^80 values before the random field overflows and the generator has to error out rather than silently wrap. In practice you will hit your database's write throughput several orders of magnitude before that. The thing to know is that monotonicity is opt-in — most libraries expose it as a separate factory (monotonicFactory() in the JS reference implementation, for instance) and the plain generator does not give it to you.

Where it's the wrong call

Two hard limits, both structural rather than matters of taste. First, ULID is not a UUID, so Postgres's native uuid column rejects it and you store 26 bytes of text or a 16-byte bytea you have to encode and decode yourself — losing the operator support and the compactness that made the uuid type worth having. Second, there is no RFC. It's a community spec on GitHub, widely implemented and stable for years, but if your organisation's answer to "where is this standardised" has to be a document number, ULID does not have one and UUID v7 does.

Neither is a reason to avoid ULID in a service that owns its own storage and hands IDs to humans — that's where the short, hyphen-free, double-click-selectable string genuinely pays. Both are reasons not to introduce it into a schema where everything else is already a uuid column, for a sort order you can get from v7 without changing the type.

Cover photo by cottonbro studio on Pexels.