UUID vs Auto-Increment: Picking a Primary Key

The argument is usually framed as “sequential integers versus random UUIDs”, and framed that way it has no good answer — each option has a disqualifying flaw. The useful version of the question appeared once time-ordered ids became standard, because they remove the flaw that made UUIDs expensive.

What is actually wrong with auto-increment

They leak. /orders/1042 tells any customer roughly how many orders you have ever taken, and /orders/1043 is somebody else’s. Sequential ids in public URLs are both a competitive-intelligence leak and an enumeration vector — the class of bug that shows up in breach write-ups as “insecure direct object reference”.

They need the database. The id does not exist until the row is inserted, so you cannot build a graph of related objects in memory and write it in one go, and an offline client cannot create anything.

They collide across databases. Two shards, or a merge of two systems, and both have a row 1042.

None of these matter for an internal table in a single database. All of them matter the moment ids are public or generation is distributed.

What is actually wrong with random UUIDs

One thing, and it is severe: inserts go everywhere.

A B-tree index is sorted. An auto-increment key always inserts at the right-hand edge, so the same page stays hot in memory and fills neatly before the next one is started. A random UUIDv4 inserts at a uniformly random position, which means every insert touches a different page.

While the index fits in memory this is invisible. Once it does not, each insert becomes a random read to pull a page in, a modification, and eventually a write out — and because pages are being filled out of order, they split at around half full, so the index is roughly twice the size it needs to be, which makes it fit in memory even less well.

UUIDv7: the flaw removed

UUIDv7 puts a 48-bit Unix millisecond timestamp in the leading bits, then randomness. Sorting by the id sorts approximately by creation time, so inserts land at the right edge of the index — the same locality auto-increment enjoys.

UUIDv4  f47ac10b-58cc-4372-a567-0e02b2c3d479   random → scattered inserts
UUIDv7  01931f3e-7c00-7c3a-b8e1-2f9a4d5e6f70   time-ordered → appends
        └── ms timestamp ──┘

ULID is the same idea with a shorter base32 text form. Snowflake ids are the same idea again, packed into a 64-bit integer at the cost of needing coordinated machine ids. If you want one recommendation: UUIDv7, because it is now a standard type (RFC 9562) with native support arriving across databases and languages, and it needs no coordination.

The cost that does not go away

16 bytes versus 8. That sounds like a 2x storage cost and it is worse than that, because the primary key is copied into every secondary index and every foreign key column referencing it.

A table with four secondary indexes and two child tables pays the extra 8 bytes many times over, in storage and — more importantly — in how much of the index fits in cache. On a small table this is noise. At a billion rows it is a capacity decision.

There is also a text-handling trap: storing UUIDs in a varchar(36) column costs 36+ bytes instead of 16 and makes comparisons string comparisons. Use the native uuid type.

The pattern that gets you both

For public-facing applications, many teams keep two columns:

CREATE TABLE orders (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- internal joins
  slug    uuid   NOT NULL DEFAULT uuidv7() UNIQUE,          -- public URLs
  ...
);

Joins and foreign keys use the compact integer; URLs and API responses use the UUID. You pay for one extra unique index and get small internal keys with non-enumerable public ids.

This is genuinely more complexity — two identities for one row, and a rule about which one crosses the API boundary that everyone must remember. It earns its keep on high-volume tables with public ids. For most tables, a single UUIDv7 primary key is simpler and fast enough.

What you have actually chosen

Not “random versus sequential”. You have chosen where ids are allowed to be created, and what a stranger can infer from one.

Auto-increment says ids come from the database and encode position in a sequence. UUIDv7 says anyone can mint one, they collide never, and they reveal a timestamp and nothing else. Pick by which of those properties your system actually needs — and if you pick UUIDs, pick a time-ordered one, because the random version’s cost arrives long after the decision that caused it.