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.
We generated 1000 of them and checked. Our UUID generator, 1000 across five rounds: 1000/1000 unique — 0 collisions, all 1000 carrying version nibble
4, all 1000 in the RFC variant range, every one exactly 36 characters. That last number is the real argument of this page: 36 characters versus an 8-byte integer, repeated on every foreign key and every index entry. UUIDs buy you client-side generation and no coordination; you pay in storage and index locality.
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 collision argument is usually made with probability tables. We measured it instead: our own UUID generator produced 200 identifiers five times over, and across those 1000 values there were 0 collisions — 1000 unique. Every one carried version digit 4 (1000/1000) and every one was 36 characters long (1000/1000). That is not a proof of uniqueness — 1000 draws from a 122-bit space could hardly collide — but it does verify the two things that actually break in practice: a generator that quietly falls back to a weak random source, and one that emits the wrong length or version nibble.
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.
The cost side is just as measurable and is where the choice usually turns. Those 1000 characters are 36 bytes as text against 4 or 8 for an integer, and they are unordered, so every insert lands at a random point in a B-tree index instead of at the end. That is the real trade: 0 collisions bought at the price of index locality — which is exactly what UUIDv7 and ULID were designed to give back.
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.