When you create a table, you pick a primary key strategy — and it’s surprisingly consequential. The two most common choices are an auto-increment integer and a UUID. Here’s how they compare and when to use each.
Auto-increment integers
A column like BIGINT AUTO_INCREMENT (or SERIAL in Postgres) hands out 1, 2, 3, … as rows are inserted.
Pros: compact (4–8 bytes), human-readable, naturally ordered, and excellent for index locality — new rows append to the end of the B-tree, keeping inserts fast.
Cons: they leak information (a competitor sees /users/5000 and knows your scale), they’re guessable (enabling enumeration attacks if authorization is weak), and they’re hard in distributed or sharded systems because two nodes can’t independently mint the next number without coordination.
UUIDs
A UUID is a 128-bit identifier like 550e8400-e29b-41d4-a716-446655440000. Version 4 is random, so any node can generate one with no coordination and effectively zero collision risk. You can create them client-side, before the database round trip. Generate a batch with a UUID generator to see the format.
Pros: globally unique without coordination, no count leakage, not guessable, and mergeable across databases.
Cons: larger (16 bytes, or 36 as text), not human-friendly, and — critically — random UUIDv4 hurts index locality. Because new keys land randomly across the B-tree, inserts cause page splits and the index fragments, slowing writes on large tables.
The middle ground: UUIDv7 / ULID
You don’t have to choose between “unique everywhere” and “index-friendly.” Time-ordered IDs like UUIDv7 and ULID embed a timestamp prefix, so they’re globally unique and roughly sequential — restoring index locality while keeping the distributed-friendliness of UUIDs. For new systems that want UUID benefits without the write penalty, these are increasingly the default.
How to choose
- Small, single-database app, internal IDs? Auto-increment is simple and fast.
- Public-facing IDs, or you want to hide scale? UUID (or expose UUIDs while keeping an internal integer key).
- Distributed, sharded, or client-generated IDs? UUID — ideally UUIDv7/ULID for index performance.
- Never rely on a sequential ID for security; authorization must be enforced regardless of how guessable the key is.
Quick takeaway
Auto-increment wins on simplicity and raw index performance; UUID wins on uniqueness, privacy and distribution. If you want the best of both, reach for a time-ordered UUIDv7/ULID.
Need identifiers right now? The free UUID generator produces RFC 4122 v4 IDs in your browser — batch-generate and copy in one click.