DEV Community

Pablo Infante
Pablo Infante

Posted on

UUID v4 or auto-increment? A practical guide to picking primary keys

Every project hits the same fork the moment you design your first table: do primary keys count up — 1, 2, 3 — or do they look like 550e8400-e29b-41d4-a716-446655440000? The answer isn't "UUIDs are modern, use UUIDs." It's a trade-off, and picking the wrong side costs you either index performance or a painful migration later. Here's the decision the way I actually make it.

The one question that decides it

Ask: who is allowed to mint an ID?

If the answer is "only the database, one row at a time," an auto-increment integer is hard to beat. It's small (4–8 bytes), naturally ordered, and the database hands them out for free.

If the answer is "anything — a mobile client offline, three services writing to sharded tables, a queue consumer" — then you can't have a single counter being the bottleneck. That's the moment a UUID v4 earns its place: 122 random bits mean any process can generate one independently and they won't collide in practice. You'd have to mint billions per second for years before a duplicate became even likely.

What UUIDs quietly buy you

Two benefits get overlooked:

  1. No round-trip to get an ID. With auto-increment you insert, then read back the generated ID. With a UUID the client already knows the ID before the row exists — handy for optimistic UIs and for wiring up related records in one batch.
  2. Your IDs stop leaking business data. /orders/438 tells a competitor you've had roughly 438 orders. /orders/9b2c… tells them nothing. If an ID is ever exposed in a URL, that opacity matters.

The trap nobody warns you about

Here's where teams get burned. Random v4 UUIDs are not ordered, and on a clustered index (think InnoDB's primary key, or SQL Server) that's a real cost. New rows land at random positions in the B-tree instead of appending at the end, which fragments pages and tanks insert throughput on big, hot tables.

If you want UUIDs and insert locality, look at UUID v7 — it's time-ordered, so new values sort to the end like an auto-increment while keeping global uniqueness. For many modern systems v7 is the sweet spot the old "v4 everywhere" advice missed.

And one more: a UUID is unguessable, not secret. It's fine in a URL, but it is not an access token. Authorization is a separate layer — never let "they'd have to guess the UUID" stand in for a permission check.

A quick decision table

Situation Reach for
Single DB owns the sequence, internal IDs Auto-increment
IDs created on clients / multiple services UUID v4
Public-facing IDs that shouldn't reveal volume UUID (v4 or v7)
UUID needed but insert order on a huge table matters UUID v7
You need a secret/token Neither — use a real token + auth

A hybrid that often wins

You don't have to choose globally. A common pattern: keep a compact auto-increment integer as the internal clustered key for join performance, and add a UUID as the public-facing identifier exposed in APIs and URLs. You get index locality internally and opacity externally. Two columns, both problems solved.

Try it before you commit

If you just want to eyeball some v4 values, toggle hyphens or uppercase, or grab a batch for seed data, I use a client-side generator that runs entirely in the browser with crypto.getRandomValues — nothing is uploaded, so it's safe for throwaway or real keys alike: UUID generator.

And if you want the deeper background on the format — the anatomy of the 128 bits, why collisions are negligible, and the full UUID-vs-auto-increment breakdown — I wrote the long version here: What is a UUID v4 and when to use it.

Bottom line

UUID v4 isn't automatically better; it's the right call when IDs must be born without a central counter or must not leak counts. Auto-increment is still excellent when one database owns the sequence and you want small, ordered keys. And if you need UUIDs on a write-heavy table, reach for v7 before you blame the database. Pick for your write pattern, not the trend.

Top comments (0)