UUID vs Auto-Increment IDs: How to Choose

The first time auto-increment IDs bit me, I was merging two databases from two regional offices into one. Both had a users table, both had a user with id = 100 — and they were two completely different people. There was no clean way to combine them, because each database had been counting from 1 in its own little universe. That merge is where I finally understood what UUIDs are actually for.

Two philosophies of naming things

An auto-increment ID is a sequential integer the database hands out: 1, 2, 3, and so on. One authority (the database) keeps a counter and gives the next number to each new row. A UUID is a 128-bit value that any machine can generate on its own, with effectively no chance of two ever colliding — no counter, no authority, no coordination.

Street addresses vs GPS coordinates

Auto-increment is street numbering. On one street, a single authority assigns 1, 2, 3 in order — tidy, short, and human-friendly. But it only works within that street. Two different towns both have a '100 Main Street,' and if you merge the towns you have a conflict. Someone has to be in charge of the sequence.

A UUID is a GPS coordinate. It's longer and no one hands it out — you can compute your own — but it's globally unique by construction. Nobody coordinates GPS coordinates, and two places never share one. My database merge failed because I'd used street numbers where I needed coordinates.

Where auto-increment wins

• Compact and fast. A 4- or 8-byte integer makes a lean, index-friendly primary key with no fragmentation.
• Naturally ordered. Rows sort by creation order for free; 'latest 10' is trivial.
• Human-friendly. 'Order #4471' is easy to say, type, and support.

The costs: it needs a single coordinator (awkward across distributed systems and merges), and it's guessable and countable — /users/100 tells an outsider you probably have around 100 users, and lets them walk the range.

Where UUIDs win

• No coordination. Any service — even the browser — can mint an ID before the row is saved, which is why they survive distributed systems and merges.
• No information leak. A random UUID reveals neither count nor order, so it's safer as a public identifier.
• Merge-friendly. Independent datasets combine without key collisions.

The costs: 128 bits is bigger than an integer, and random (v4) UUIDs can fragment an index — the problem that UUID v4 vs v7 exists to solve. If a full UUID is heavier than you need for a public ID, a Nano ID is a shorter URL-safe alternative.

The pattern most teams actually use

You don't have to choose globally. A very common setup keeps both:

• An auto-increment integer as the internal primary key — compact, ordered, fast to join on.
• A UUID as the external, public-facing identifier used in URLs and APIs — unguessable and safe to expose.

You get the index performance internally and the privacy externally. Generate the public IDs with the UUID Generator, or an API Key Generator when the identifier is really a secret credential rather than a name.

The lesson

Auto-increment and UUIDs aren't rivals so much as answers to different questions: do I have one authority handing out names, or does everyone need to name things independently? Use auto-increment for compact, ordered, single-database keys where guessability doesn't matter. Use UUIDs when IDs must be generated anywhere, merged freely, or exposed publicly. And when both matter, use both — an integer for the database, a UUID for the world. It would have saved me one very long night of reconciling two tables full of user #100.