UUID and ULID Generator

The primary key that shredded the index

You built the table with a UUID primary key and the first few months were uneventful. Past a million rows, inserts started getting slower and no single query could be blamed. The cause was not in the queries but in the key itself: because v4 is fully random, every new row lands on a random leaf of the index tree, forcing the database to fetch and split a different page each time instead of working at the hot end.

What actually separates v4 from v7

A v4 UUID carries 122 random bits, with the remaining 6 spent on the version and variant fields. That randomness makes an excellent identifier and a terrible index key: two v4 values generated a microsecond apart sort nowhere near each other. A v7 instead writes Unix milliseconds big-endian into the first 48 bits and leaves the rest random. The consequence is that lexicographic order matches creation order, new rows append at the end of the index, and the database keeps working the same warm pages.

1# Consecutive v4 values (order is arbitrary)29f8a2c14-...  3b1e77d0-...  c40d5e92-...  1a7f30bb-...3 4# Consecutive v7 values (order is creation order)501994e2a-7c10-7...  01994e2a-7c10-7...  01994e2a-7c11-7...6^^^^^^^^^^^^^ shared time prefix, increasing as the clock advances7 8# ULID (26 characters, Crockford base32)901JQ8ZK4T0XA9V7C2M5N3PQRSD10^^^^^^^^^^ first 10 characters are time, the other 16 are random

The big-endian layout is a requirement rather than a stylistic choice. Only in that byte order does a byte-wise comparison of the encoded form agree with a numeric comparison of the timestamp, which is the entire point. A little-endian v7 would look identical to the eye and shuffle arbitrarily, losing its one advantage. Ordering within a single millisecond is guaranteed too: the 12 bits of rand_a act as a counter, so a hundred ids minted in a tight loop still come out sorted.

ULID and Crockford base32

A ULID packages the same idea in a different shell: 48 bits of time, 80 bits of randomness, rendered as 26 characters of Crockford base32. The alphabet drops I, L, O and U. The first three because they are visually confusable with 1 and 0 when somebody retypes an id from a screen, and U so the encoding cannot accidentally spell something unfortunate. The result is case-insensitive and contains no hyphens, which makes it comfortable in URLs and in text selected by double-click. The time prefix is exactly 10 characters, so prefix range scans work directly.

Collision risk and auto-increment integers

With 122 random bits, v4 collisions are not a practical concern: you would need to mint billions of ids before the probability became worth reasoning about. The real risk is a weak source of randomness rather than the arithmetic, since generators built on Math.random() produce values that look like UUIDs while being predictable from prior draws. Every byte here comes from crypto.getRandomValues, and the version and variant nibbles are written explicitly so strict parsers such as the Postgres uuid type accept the result.

Compared with an auto-increment integer, the tradeoffs are clear. Integers are short and easy to read, but anyone who sees /orders/1042 can try /orders/1043, and the numbers disclose your volume. They can also only be produced by the database, so you cannot know a row identity before writing it. With UUIDs you generate the id on the client and build related records in a single round trip, at a cost of sixteen bytes instead of eight.