UUID vs Auto-Increment Database Keys — Trade-offs That Actually Matter

"Should primary keys be UUIDs or auto-increment integers?" is one of the longest-running debates in backend development, and both camps are right — about different situations. Integers are smaller and faster for the database; UUIDs are safer to expose and can be generated anywhere. This guide walks through the trade-offs that actually matter in production — security, distribution, replication, index behaviour, and storage — and ends with concrete guidance per scenario, including the UUIDv7/ULID middle ground that resolves most of the tension.

Security: Sequential IDs Invite Enumeration

Auto-increment IDs are predictable by design. If a user sees their invoice at /invoices/1041, nothing stops a curious (or malicious) visitor from requesting /invoices/1042. If any endpoint is missing an authorization check — an IDOR vulnerability — sequential IDs turn one bug into a full data leak, because every record is one increment away.

Sequential IDs also leak business intelligence. Sign up twice, a week apart, and subtract your two user IDs: that is the competitor's weekly signup count. The same trick works on order numbers and invoice IDs.

A random UUID in the URL makes both attacks infeasible — there are 2^122 possible v4 values, so guessing a valid one is hopeless. To be clear: UUIDs are obscurity, not authorization. You still need permission checks on every endpoint. But unguessable IDs are a genuinely useful second layer.

Distribution: Who Gets to Hand Out Numbers?

Auto-increment works because a single database instance owns the counter. That assumption breaks in modern architectures:

• Multiple writers — sharded databases or multi-primary replication need offset/interleaved sequences or a central ID service, both operationally painful. UUIDs need zero coordination.
• Client-side creation — a mobile app working offline can assign UUIDs to new records locally and sync later; with integers it must wait for the server to know its own ID.
• Merging datasets — combining rows from two environments or acquired systems is trivial when keys are UUIDs and a remapping project when they are integers 1..N in both.
• Replication friendliness — with UUIDs, rows created independently on two nodes can never claim the same key, which removes one whole class of replication conflicts.

Performance: Index Locality and Size

This is where integers fight back, on two fronts.

Index locality. B-tree indexes love sequential inserts: each new auto-increment key appends to the rightmost page, which stays hot in memory. Random v4 UUIDs land anywhere in the tree, so insert-heavy tables suffer page splits, fragmentation, and cache misses. On MySQL/InnoDB the primary key is the clustered index — the table itself is stored in key order — so random keys scatter the actual row data too, and every secondary index carries a copy of the 16-byte key.

Size. The raw numbers per key, per index entry:

Key type              | Storage
----------------------|------------------------------
INT (auto-increment)  |  4 bytes (max ~2.1 billion)
BIGINT                |  8 bytes
UUID (binary)         | 16 bytes
UUID (CHAR(36) text)  | 36+ bytes  <- avoid this

A 100M-row table with 3 secondary indexes pays the key size
4x over: once in the PK, once per secondary index.

Bigger keys mean fewer entries per page, deeper trees, more I/O, and a smaller share of the index fitting in RAM. None of this matters at ten thousand rows; all of it matters at a hundred million.

The Middle Ground: UUIDv7 and ULID

The locality problem is caused by randomness in the high-order bits, not by UUIDs themselves. Two modern formats fix exactly that:

• UUIDv7 — a 48-bit Unix millisecond timestamp followed by 74 random bits. New keys are always roughly the largest yet, so inserts append to the right edge of the index like auto-increment. Standardised in RFC 9562; PostgreSQL 18 has native uuidv7().
• ULID — the same timestamp-then-random idea, encoded as 26 characters of Crockford base32. It predates v7 and remains popular; many ULID libraries can output standard UUID format too.

Benchmarks on insert-heavy workloads consistently show time-ordered UUIDs closing most of the gap with integer keys, while keeping decentralised generation and non-guessability (74 random bits per millisecond is still unguessable). The remaining cost — 16 bytes vs 8 — is real but usually acceptable. This combination is why "UUIDv7 primary keys" has become the default recommendation for new distributed systems.

Guidance Per Scenario

• Single-database internal app, keys never leave the backend → auto-increment BIGINT. Simplest, smallest, fastest.
• Public API or URLs expose the IDs → UUID (v7 if supported, else v4), or the hybrid below.
• Microservices, sharding, offline-first clients, event sourcing → UUIDv7. Decentralised generation is the whole point.
• Huge insert-heavy tables where every byte counts → BIGINT internally; if you need external IDs, add a UUID column alongside.
• The hybrid — internal auto-increment primary key for joins and foreign keys, plus an indexed, unique UUID column as the public identifier. More moving parts, but each key does what it is best at.
• Storing UUIDs — always use the native uuid type (PostgreSQL) or BINARY(16) (MySQL), never CHAR(36) text.

Whichever route you choose, decide before the table has a hundred million rows — migrating primary keys later is one of the most painful schema changes there is.

Frequently Asked Questions