UUIDv4 vs UUIDv7 vs ULID: Why Random Primary Keys Hurt Your Index

A random UUIDv4 is a perfectly good identifier and a surprisingly bad clustered primary key: every insert lands on a random page of the index, so a write-heavy table pays for it in page splits, cache misses and extra write-ahead log. UUIDv7 and ULID fix that by putting a millisecond timestamp in the leading bits, which keeps new keys appending to the right edge of the B-tree while staying globally unique and generatable anywhere.

That's the whole argument. The rest of this post is the mechanism, the bit layouts, and the cases where you should still pick v4.

Why "just use a UUID" became a performance question

For years the advice was simple: auto-increment integers leak row counts and need a central sequence, so use UUIDs and generate them in the application. That advice was right about uniqueness and wrong about cost. The original spec, RFC 4122, offered v1 (timestamp plus MAC address, with the time bits stored in an order that doesn't sort) and v4 (random). Most teams picked v4 because nobody wants hardware addresses in their IDs.

In May 2024, RFC 9562 replaced RFC 4122 and added three versions: v6 (v1 with the time bits reordered so it sorts), v7 (Unix-epoch milliseconds plus randomness) and v8 (a vendor-defined layout). The RFC explicitly recommends v7 over v1 and v6 when you can choose. ULID, a community spec that predates RFC 9562 by years, reached the same design independently.

What actually happens inside the B-tree

A B-tree index keeps keys in sorted order across fixed-size pages (8 KB in PostgreSQL, 16 KB by default in InnoDB). Where a new key goes is decided entirely by its value.

Sequential keys: append to the right edge

With an auto-increment integer or a time-ordered ID, every new key is larger than every existing key. Inserts go to the rightmost leaf page. That page is almost certainly already in memory, it fills up completely, and when it's full the engine starts a fresh page next to it. Both PostgreSQL and InnoDB detect this pattern and split rightmost pages so the old page stays nearly full rather than half full.

Random keys: scatter across every leaf

A v4 UUID is uniformly random, so a new key belongs on an essentially random leaf page. Three costs follow:

• Cache misses. Once the index is larger than the buffer pool, the target page is often not in memory, so an insert becomes a random read before it can become a write.
• Page splits and low fill. Inserts into the middle of full pages force splits that leave two half-full pages. Random-key indexes tend to settle well below the fill of sequential ones, so the same rows take more pages, more memory and more disk.
• Write amplification. PostgreSQL writes a full-page image to the WAL the first time a page is modified after each checkpoint. Touching many distinct pages means many full-page images, so WAL volume (and replication traffic) grows faster than the data does.

InnoDB makes this worse, because its primary key is the clustered index: the table rows themselves are stored in primary-key order. A random primary key in MySQL scatters the rows, not only an index. Every secondary index also stores the primary key, so a 16-byte random key (or a 36-byte string one) is copied into each of them.

None of this shows up on a table with ten thousand rows. It shows up when the hot index no longer fits in memory, which is exactly when you can least afford it.

The bit layouts side by side

All three formats are 128 bits. The difference is what the leading bits mean.

v7 and ULID both start with a 48-bit count of milliseconds since the Unix epoch, which runs out around the year 10889. The difference is in the tail: v7 gives up 6 bits to the version and variant fields so it stays a valid RFC UUID (74 random bits), while ULID uses all 80 remaining bits for randomness and has no version marker at all.

Worked example: reading the time out of an ID

Take this v7 UUID:

01a1c9f1-5a3b-7a3f-9c41-5e8d2b07f6a1

The first 12 hex digits (48 bits) are the timestamp: 01a1c9f15a3b in hex is 1794389400123 in decimal, which is milliseconds since 1970-01-01, or 2026-11-11T09:30:00.123Z. The 7 that starts the third group is the version nibble. The 9 that starts the fourth group has the binary form 1001, and its leading 10 is the RFC variant. Everything else is randomness.

The same instant as a ULID begins with the 10-character timestamp 01M74Z2PHV, the Crockford Base32 encoding of that same millisecond count, followed by 16 characters of randomness. Crockford's alphabet drops I, L, O and U to avoid misreads, a design choice covered in more depth in our comparison of Base64, Base32 and Base58 encodings.

Extracting the timestamp takes a few lines:

function uuidV7Time(id: string): Date {
  const hex = id.replace(/-/g, '')
  if (hex[12] !== '7') throw new Error('not a v7 UUID')
  return new Date(parseInt(hex.slice(0, 12), 16))
}

const CROCKFORD = '0123456789ABCDEFGHJKMNPQRSTVWXYZ'
function ulidTime(id: string): Date {
  let ms = 0
  for (const ch of id.slice(0, 10).toUpperCase()) ms = ms * 32 + CROCKFORD.indexOf(ch)
  return new Date(ms)
}

uuidV7Time('01a1c9f1-5a3b-7a3f-9c41-5e8d2b07f6a1') // 2026-11-11T09:30:00.123Z

Because the timestamp is the prefix, sorting the IDs as bytes or as strings sorts them by creation time. That's also why a v7 primary key works well as the tiebreaker in keyset pagination: WHERE id > $last ORDER BY id gives you a stable, roughly chronological feed without a separate created_at index.

Ordering within the same millisecond

A busy service generates many IDs per millisecond, and pure randomness in the tail means IDs from the same millisecond sort arbitrarily among themselves. For index locality that doesn't matter much, since they all land on the same right-edge page anyway. For anything that needs strict ordering, it does.

RFC 9562 describes three optional ways to guarantee monotonicity: a dedicated counter in the leading random bits, incrementing the random portion by a random amount, or spending the 12 rand_a bits on extra sub-millisecond timestamp precision. ULID's spec defines a monotonic mode that increments the random component by one when the millisecond hasn't changed. PostgreSQL 18's built-in uuidv7() takes the sub-millisecond approach, filling part of the random field with extra timestamp precision.

Two caveats. Monotonicity is only guaranteed within one generator: two app servers producing IDs in the same millisecond still interleave by their random bits. And all of this assumes clocks that roughly agree. A server whose clock is a few seconds behind produces keys that insert slightly left of the edge, which costs a little locality and nothing else. Uniqueness never depended on the clock.

Storing them correctly

Most of the space penalty attributed to UUIDs comes from storing them as text. A uuid column in PostgreSQL is 16 bytes; a char(36) is 36 bytes plus overhead, and every secondary index in InnoDB repeats it. Use the native type, or BINARY(16) in MySQL.

-- PostgreSQL 18+
CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  customer_id uuid NOT NULL,
  total_cents bigint NOT NULL
);

On older PostgreSQL versions, generate v7 in the application with any maintained library and keep the column type uuid. ULIDs can go in the same 16-byte column if you decode them to bytes on the way in and encode on the way out. Store them as text and you've thrown away most of the reason to choose a compact format.

When v4 is still the right call

Putting a timestamp in the ID is a feature until it's a leak. A v7 UUID or ULID tells anyone holding it exactly when the record was created, and a run of them reveals your creation rate. That matters for:

• Public, user-facing identifiers where creation time is sensitive (account IDs, invite links, document share IDs).
• Idempotency keys and request IDs, which are never indexed as a clustered key and gain nothing from ordering. Plain v4 is the right default for idempotency keys.
• Anything resembling a secret. No UUID version is a security token. If guessing an ID grants access, fix the access control rather than the ID format.

A common split is a v7 internal primary key for storage efficiency plus a separate random v4 (or opaque token) for anything exposed in a URL.

Between v7 and ULID, choose v7 by default. It is an IETF standard, it fits every existing uuid column, driver and validator unchanged, and databases are starting to generate it natively. Pick ULID when the 26-character, case-insensitive, URL-friendly text form is the thing you actually need, for example in IDs people read aloud or paste into support tickets. You can generate and inspect both with the UUID generator and ULID generator.

The takeaway

If a table's primary key is a UUID and the table grows without bound, make it a v7. You keep application-side generation and global uniqueness, you get insert behaviour close to an auto-increment integer, and the migration is a change of default rather than a change of type. Keep v4 for identifiers that leave your system or must not reveal when they were made, and store every variant as 16 bytes, never as a 36-character string.