UUID vs Auto-Increment IDs: Choosing a Primary Key

Every table needs a primary key, and the choice usually comes down to two options: a sequential integer that the database hands out automatically, or a UUID that is generated as a random-looking string. Both work, but they trade off in ways that matter once a system grows. Picking the wrong one early can be painful to undo later.

What each one looks like

An auto-increment integer is just a counter. The first row is 1, the next is 2, and so on:

id | email
 1 | alice@example.com
 2 | bob@example.com
 3 | carol@example.com

A UUID is a 128-bit value, usually shown as 36 characters with dashes:

id                                   | email
f47ac10b-58cc-4372-a567-0e02b2c3d479 | alice@example.com
9b2e4c6a-7d81-4f33-8a19-5c0b1d2e3f44 | bob@example.com

Distributed uniqueness

Auto-increment relies on a single source of truth to hand out the next number. That works beautifully on one database but becomes awkward when you shard, merge datasets, or generate records on multiple servers at once. Two independent nodes will both happily create an id of 1001.

UUIDs are designed so that any machine can generate one at any time with a vanishingly small chance of collision, no coordination required. If your records are created in multiple places and later combined, UUIDs remove a whole class of merge conflicts.

Security and enumeration

Sequential ids leak information. If a user profile lives at /users/42, anyone can guess that /users/41 and /users/43 exist, count your records, and probe for data they should not see. This is called an enumeration attack, and it is a common real-world weakness.

A random UUID in the URL cannot be guessed, so attackers cannot walk the sequence or estimate your total count. This is not a substitute for proper authorization checks, which you still need on every request, but it removes an easy avenue of attack.

Index performance

This is where auto-increment shines. Because new values are always larger than existing ones, inserts append to the end of the index, which keeps the underlying B-tree compact and cache-friendly. Random UUIDs land in arbitrary positions, forcing the index to split pages all over the place. On write-heavy tables this fragmentation shows up as slower inserts and a larger index on disk.

The effect is real but often overstated for small and medium applications. It becomes a genuine concern at high insert volumes.

Storage size

A standard integer is 4 bytes and a bigint is 8 bytes. A UUID is 16 bytes stored in binary, or 36 bytes if you carelessly store it as text. That size difference multiplies across every foreign key and every index that references the key, so a UUID-based schema uses noticeably more space. Always store UUIDs in a native UUID or binary column rather than as a string.

UUID v4 vs sortable UUIDs

The classic UUID v4 is fully random, which is exactly what causes the index fragmentation described above. Newer formats fix this. A sortable identifier, such as UUID v7 or ULID, puts a timestamp in the leading bits so values generated later sort after earlier ones:

v4 (random order):  9b2e4c6a-7d81-...  f47ac10b-58cc-...
v7 (time-ordered):  018f2a... (older)  018f2b... (newer)

These give you the distributed, hard-to-guess qualities of a UUID while behaving like an incrementing value at insert time, which largely neutralizes the performance penalty. For new systems that want UUIDs, a time-ordered format is usually the better default than plain v4.

When to pick each

Reach for auto-increment when you have a single database, write performance is critical, and the ids stay internal where enumeration is not a concern. It is simple, compact, and fast.

Reach for UUIDs when records are created across multiple services, when ids appear in public URLs, or when you need to generate the id before the row is written. Prefer a time-ordered variant to keep inserts healthy.

A common hybrid keeps an internal auto-increment primary key for joins and performance, while exposing a separate UUID in public-facing URLs. You get the index benefits internally and the non-guessable identifier externally.

A practical note

Whichever you choose, be consistent across the schema and decide early, because migrating a primary key on a live table with existing foreign keys is tedious and risky. If you need to produce UUIDs for seeding, testing, or wiring up a new table, the UUID Generator creates valid values on demand.