UUID vs auto-increment IDs
UUID vs auto-increment primary keys: which is safer to expose, which indexes faster, and when to reach for each.
Last updated
The short answer
Auto-increment integers are smaller, faster to index, and simplest for a single database. UUIDs cost more storage and can fragment indexes, but they let you generate IDs offline, merge data from multiple sources without collisions, and avoid leaking your row count to anyone reading the URL.
| Dimension | UUID | Auto-increment |
|---|---|---|
| Storage size | 16 bytes (36-char string) | 4 or 8 bytes |
| Generation | Client or server, no DB round trip | Database assigns it, needs a round trip |
| Index locality | Random v4 fragments B-tree indexes | Sequential — index stays compact |
| Guessability | Not guessable, doesn't leak row count | Sequential — /orders/1042 leaks volume |
| Merging data | Safe across databases/services | Collisions on merge, needs remapping |
Choose UUID when
- →IDs need to be generated before the row is inserted — offline clients, distributed systems, or merging data from multiple databases.
- →The ID will sit in a public URL and you don't want competitors or users inferring your growth rate from sequential numbers.
- →You're sharding across multiple databases and need IDs that can't collide without a central counter.
Choose Auto-increment when
- →You have a single database and want the smallest, fastest-to-index primary key available.
- →You'll rely on ID ordering for cheap 'most recent N rows' queries without a separate timestamp index.
- →Storage and index size matter — at scale, 16 bytes per row per index adds up fast.
The catch nobody mentions
Random (v4) UUIDs as a primary key hurt write performance more than people expect — because they insert in random order, B-tree indexes fragment and page splits increase, which shows up as slower writes and worse cache locality on large tables. If you want UUID-style IDs without that cost, use a time-ordered variant (UUIDv7, or ULID) instead of v4.
Questions
Why do random UUIDs hurt database performance?
A v4 UUID is random, so inserts land all over a clustered index rather than at the end. That causes page splits and fragmentation, and the effect is pronounced in MySQL's InnoDB where the primary key is the clustering key.
Does UUIDv7 fix that?
Largely. v7 puts a timestamp in the high bits, so values sort roughly by creation time and insert at the end like a sequence would. It is the current recommendation when you want UUID properties without the index cost.
What does an auto-increment ID leak?
Your record count and growth rate. Anyone who signs up twice a month can estimate how many users you have, and sequential IDs in URLs also invite enumeration of other people's records if authorisation is weak.
How much bigger are UUIDs?
Sixteen bytes against four or eight, and thirty-six characters if stored as text rather than binary. Stored as a string in every index and foreign key, that difference becomes substantial on a large table.
Can I use both?
Yes, and it is a common compromise: an internal auto-increment primary key for joins and index locality, plus an external UUID exposed in URLs and APIs. You pay for a second unique index and get the benefits of each.
Does a UUID guarantee uniqueness across systems?
In practice yes for v4, where a collision is vanishingly unlikely with a decent random source. That is the property that makes them worthwhile — clients can generate identifiers offline without coordinating with a server.