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.