UUID v4 vs Auto-Increment: Choosing a Primary Key Strategy

Starting a new table and picking a primary key type is a small decision that's genuinely hard to reverse later - changing a primary key's type on a table with real data and real foreign keys pointing at it is a migration, not a quick edit. Here's the actual tradeoff, not just "UUIDs are more secure" or "auto-increment is faster," both of which are true in different, narrower ways than they sound.

Auto-increment: simple, ordered, and tied to one database

An auto-increment integer is small (fits comfortably in an index), sorts by creation order for free, and is easy to read and debug in a log line or a support ticket. Its real constraint is the one that matters here: it needs a single authority - the database - to hand out the next number, which means every writer has to go through that one place to get a valid ID. That's completely fine for a normal single-database application. It stops being fine the moment two services need to create rows independently and merge them later, because now two different "next number" counters can hand out the same value.

UUID v4: no shared authority needed, at the cost of order

A version-4 UUID is generated locally, with no coordination and no round trip to anywhere - any two services, or an offline client that syncs later, can mint IDs independently and the odds of a collision are close enough to zero to treat as zero in practice. That's the entire reason to reach for one: not "it looks more secure" (a sequential ID isn't insecure by itself; something else - authorization - handles that), but "nothing needs to ask a central counter for permission." The cost, covered in more detail in the background piece on ordering, is that the ID itself carries no creation-order information and inserts into an index at a random point instead of the end.

Joins and foreign keys feel the cost too

A UUID primary key doesn't stay contained to its own table - every foreign key that references it stores the same 16-byte (or, stored as text, 36-character) value instead of a compact integer, and every join across that relationship compares the larger value instead of a small one. On a handful of tables this is not something you'll notice. On a schema with many foreign keys and large tables, it's a real, cumulative storage and comparison cost worth weighing against the coordination problem a UUID actually solves - which is exactly why "always use UUIDs, they're more modern" isn't good advice on its own.

A common middle ground: UUID for the public-facing ID, integer for the internal one

A pattern worth knowing about even if you don't end up using it: keep the auto-increment integer as the real, internal primary key that indexes and joins efficiently, and add a separate UUID column - unique, indexed, but not the primary key - as the value you actually expose in a URL or an API response. You get the ordered, compact internal key and the non-guessable external identifier at the same time, at the cost of maintaining two columns instead of one. This is a genuine tradeoff (more schema, one more index), not a free win, which is why it's worth deciding on purpose rather than defaulting into it.

Newer, time-ordered UUID variants exist too

UUID isn't only the random v4 kind. Newer standardized versions are explicitly designed to be time-ordered - sortable like an auto-increment integer while still being generated without a shared counter - trading away some of v4's pure randomness for that property on purpose. This tool generates v4 specifically, the general-purpose random kind that almost every practical use case actually needs; if a table's whole reason for wanting a UUID is "avoid a shared counter" and "keep values roughly sortable," it's worth knowing that dedicated time-ordered variants exist as a separate option, even though they're outside what this particular tool produces.

How worried should you actually be about a collision

With 122 random bits per value, the space a v4 UUID draws from is large enough that treating a collision as effectively impossible for realistic application volumes is the standard, accepted engineering position - it's part of why v4 is trusted as a primary key at all rather than just a convenience ID. An auto-increment integer, by contrast, gives you an absolute guarantee of no collision within one table, for a much smaller total space - it just needs that one shared counter to enforce it. Neither guarantee is "better" in the abstract; they're solving different problems, which is the whole point of this comparison.

The honest default

If you're not sure, and your app is a normal single-database application without multiple independent writers, auto-increment is still the simpler, cheaper default - reach for a UUID when you have an actual reason (multiple writers, a public-facing ID you don't want enumerable, an offline-sync scenario), not because it sounds like the more serious choice.

For the actual mechanics of generating a batch once you've decided you need UUIDs, see the how-to guide. Or try the generator directly.