UUID v4 or v7 as a database key

Choosing a primary key type feels like a five-minute decision and then quietly shapes how a table behaves at ten million rows. The short version: use UUID v7 unless you have a specific reason not to, and the reason is usually privacy rather than performance.

Why not a plain auto-increment integer #

Auto-increment keys are compact, sort naturally, and index beautifully. They also have two properties that get awkward at scale.

They are guessable. /orders/1042 invites someone to try /orders/1041, and now authorisation is the only thing standing between a stranger and someone else's order. That is fine if authorisation is correct, and it very often is not.

They also require a round trip. The database assigns the id, so the client cannot know it until after the insert. That rules out generating an id on the client, or in one service before another has stored anything, which is exactly what you want when writes are batched or offline.

UUIDs fix both. They cost you sixteen bytes instead of four or eight, and they introduce a problem of their own.

The problem with v4 #

A v4 UUID is 122 random bits. Random is the point, and random is also what hurts.

Most databases store table rows in primary key order, or maintain a B-tree index in that order. Insert a row whose key is random and it belongs somewhere in the middle of that structure, not at the end. Do that a few million times and you get:

• Page splits. A full index page receiving a new middle value has to split in two, which is write amplification you did not ask for.
• Cache misses. Inserts touch pages scattered across the whole index rather than the same few hot pages, so the working set is effectively the entire index.
• Fragmentation. Pages end up part-full, so the index occupies more space and fewer entries fit in memory.

None of this shows up on a small table. It shows up as inserts getting slower over months, which is a miserable thing to diagnose after the fact.

Note

This is why MySQL guidance has long said not to use a random UUID as a clustered primary key. In InnoDB the primary key is the row order, so a random key scatters the table itself, not just an index.

What v7 changes #

UUID v7 keeps the same 128-bit shape and the same text format, so nothing downstream needs to change. What differs is the layout: the first 48 bits are a Unix timestamp in milliseconds, big-endian, followed by the version and variant bits, then randomness.

019bd4e2-7a10-7c3e-9f21-4a6b8d3e5c07
└──────┬─────┘ │
       │       └── version 7
       └────────── 48-bit millisecond timestamp

Because the timestamp leads, values generated later sort after values generated earlier, as strings and as bytes. New rows land at the end of the index, which is exactly what an auto-increment key gives you:

const a = uuidv7();
await sleep(5);
const b = uuidv7();

a < b; // true, because the leading bits are time

You keep client-side generation and unguessability, and you lose the index churn.

What v7 gives away #

Here is the part usually left out. A v7 UUID leaks its creation time to anyone who has it, to the millisecond. Decode the first 48 bits and you know when the row was made.

Often that is harmless, and sometimes it is not:

• A public identifier for a user account discloses signup time.
• Two ids generated close together reveal that the underlying records were created close together, which can be enough to correlate things you meant to keep separate.
• Sequential-ish ids make it easier to estimate volume. If someone can create two records an hour apart, the gap between the ids tells them roughly how many others appeared in between.

That last one is the classic "German tank problem" applied to a startup's dashboard.

Watch out

If an id is exposed publicly and creation time is sensitive, use v4. This is the real decision, and it is about disclosure rather than speed.

A common middle path: v7 for internal primary keys, and a separate opaque public identifier for anything appearing in a URL.

ULID and Nano ID #

Two neighbours worth knowing.

ULID encodes the same idea, 48 bits of time plus 80 of randomness, in Crockford base32. It comes out as 26 characters with no hyphens, no case sensitivity, and no vowels that could form accidental words, which makes it friendlier in URLs and easier to read aloud. It is not a UUID, so a uuid column type will reject it.

Nano ID is the compact option: 21 characters from a URL-safe alphabet, no timestamp, comparable collision resistance to v4 in practice. Good where the id shows up in a URL and you do not need it to sort.

Collisions, briefly #

People worry about this more than they need to. With 122 random bits you would have to generate billions of v4 UUIDs per second for a working lifetime before a collision became likely. v7 has fewer random bits, 74, but they only have to be unique within the same millisecond, which is a far smaller problem.

What does matter is the source of randomness. Use the platform's cryptographic generator, not a seeded pseudo-random one:

// Wrong: predictable, and in some engines repeats across processes.
Math.random();

// Right, in a browser or Node.
crypto.getRandomValues(new Uint8Array(16));

Storage #

Store UUIDs as 16 bytes, not as a 36-character string. Postgres has a native uuid type. MySQL does not, so use BINARY(16) and convert at the edges. A CHAR(36) column costs more than twice the space, and every index on it is more than twice as large, which undoes the thing you were optimising for.

Generate some #

The UUID generator here produces v4, v7, ULID and Nano ID, in batches, from the browser's cryptographic generator. Nothing is uploaded, which for identifiers matters less than for a token, but the tool works the same way as the rest: locally, with no signup.