UUID vs Auto-Increment: Choosing the Right Primary Key

A primary key is one of the few decisions in a database schema that is genuinely hard to walk back later, which is why the auto-increment-versus-UUID debate never quite dies. Both work. Both are defensible. And the right call depends less on taste than on how your system generates identifiers and whether more than one place ever needs to generate them at the same time. The real question is whether any single authority gets to hand out IDs.

What auto-increment gets right

An integer or bigint auto-increment primary key is 4 or 8 bytes, monotonically increasing, and append-friendly to the B-tree index that backs it. Inserts are cheap because new rows land at the end, pages rarely split, and the database cache stays efficient. Range queries, cursor pagination, and "give me the most recent N rows" all become trivial — you order by the key and you are done. The keys are short, human-debuggable (row 1042 was created after row 1041), and every relational database supports them natively.

The cost is coordination. Only the database can hand out the next ID, which means an insert has to round-trip through the primary node before your application knows its own identifier. That is fine on a single primary and painful the moment you have multiple writers, multi-master replication, or clients that need to mint an ID before saving — offline-first apps, event-sourced systems, distributed queues. Sequential IDs also leak information: a competitor who creates an account twice can estimate how many accounts you created between the two visits.

What UUIDs get right

A UUID is a 128-bit identifier. Version 4, the one most people mean when they say "UUID," carries 122 bits of randomness — the remaining 6 bits encode the version and variant. The collision math is unforgiving in the direction you want: you would need to generate on the order of a quintillion of them — billions per second for years — before the probability of even a single collision becomes meaningful. For any realistic database, a v4 UUID is unique without a uniqueness check, and that is the whole appeal.

A v4 UUID looks like this:

7c1f3a92-4b88-4d20-9e6a-3a2b8c4d5e17

Because generation needs no coordination, any service, batch job, or client can mint an ID before it ever talks to the database. Merging two datasets from different systems just works, since the key spaces do not overlap. Nothing about the identifier leaks a row count or a creation rate. The trade is size and how the keys behave inside an index, which is where the real argument lives.

The tradeoffs that actually matter

UUIDs are 16 bytes when stored as a native type, against 4 or 8 for an integer, so every primary key, every foreign key, and every secondary index entry grows. That cost is real but usually modest; for most tables it is a rounding error against the rest of the row. Store UUIDs as the native uuid type rather than as a 36-character string — keeping them as text roughly doubles their storage footprint for no benefit.

The bigger issue is index behavior. A random v4 UUID scatters inserts across the entire B-tree rather than appending at the end, which causes page splits, fragmentation, and worse cache locality on write-heavy tables. Random UUIDs also do not sort by creation time, so "newest first" queries cannot lean on the primary key order. These problems are why the newer UUID versions exist: UUID v7 (and the similar ULID) put a millisecond timestamp in the high-order bits, so keys sort lexicographically by creation time while still being globally unique and generated without coordination. You keep the no-coordination property and recover most of the locality that random UUIDs give up.

Timestamp-prefixed identifiers have a bonus: you can read the creation time back out of the key itself, which is handy for debugging and for correlating records across services. Pull the embedded timestamp and decode it with the timestamp converter to confirm when a row was created, without needing a separate created_at column.

There is also a question of what you expose. Auto-increment IDs in public URLs leak business volume and let an attacker enumerate resources simply by walking the integer range, which is why exposing them directly is usually a mistake. A UUID in a URL is effectively unguessable, which is why it has become the default for anything user-facing — share links, public API resources, customer-facing document references. The flip side is that a UUID is long and ugly in a URL, and if you need a human-readable identifier — an order number, a support ticket id — neither a raw integer nor a raw UUID is the right answer. The clean pattern is a separate, formatted business identifier layered on top of the storage key, generated independently of whatever primary key you chose.

When to pick which

A short decision guide:

• Single primary database, modest write volume, you want compact and sortable keys — bigint auto-increment.
• Distributed generation, multi-tenant sharding, offline-first clients, or systems that merge datasets from different sources — UUID.
• High write throughput where you still want UUID benefits — UUID v7 or ULID, which sort by time and fragment the index far less.

None of this is religious. Integer keys are not obsolete and UUIDs are not automatically better — they are tools tuned for different coordination models. Pick the one that matches how identifiers actually get created in your system, and the rest of the schema stops fighting you.