Every table needs a primary key, and the choice usually comes down to two options: an auto-increment integer or a UUID. It looks like a small decision, but it touches performance, how easily you can distribute or merge data, and whether your IDs leak information. Here is how the two actually compare, and how to pick without overthinking it.
The short answer
For a single application talking to a single database, use an auto-increment integer. It is small, fast, and the default for good reasons. Reach for a UUID when one of these is true:
- You generate IDs in more than one place (multiple services, offline clients, or several databases you later merge).
- You do not want IDs to be guessable in a URL, or to reveal how many records you have.
- You need an ID before the row is written, for example to build a reference on the client.
If none of those apply, the integer is the right call and you can stop here.
Auto-increment integers
The database hands out 1, 2, 3, ... as rows arrive.
CREATE TABLE orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
customer_id BIGINT NOT NULL,
total DECIMAL(10,2) NOT NULL
);Strengths. Small (4 or 8 bytes), so indexes and foreign keys stay compact and fast. Values arrive in order, which keeps the InnoDB clustered index tidy and inserts cheap. Easy to read in logs and debugging.
Weaknesses. They only stay unique within one table in one database, so merging two systems means renumbering. They are sequential, so /orders/1042 tells anyone your rough order count and invites them to try /orders/1043. And you do not know the ID until the insert happens.
UUIDs
A UUID is a 128-bit value that is globally unique without any central coordination.
CREATE TABLE orders (
id CHAR(36) PRIMARY KEY, -- or BINARY(16) to save space
customer_id CHAR(36) NOT NULL,
total DECIMAL(10,2) NOT NULL
);Strengths. Unique across tables, services, and databases, so distributed systems and later merges just work. Not guessable or countable, which is safer in public URLs. Can be generated by the application before the row is saved.
Weaknesses. Larger (16 bytes, or 36 as a string), which grows every index and foreign key that references it. And the classic problem: a random UUIDv4 inserts in random order, scattering writes across the clustered index and fragmenting it, which slows inserts on large tables.
The fix that changes the trade-off: time-ordered UUIDs
Most of the UUID performance objection comes from randomness, and that is now solvable. UUIDv7 (and ULID) put a timestamp in the leading bits, so values sort roughly in creation order. You keep global uniqueness and unguessability, but inserts stay nearly sequential, so the index fragmentation that hurt UUIDv4 largely goes away. If you need UUIDs today, a time-ordered form is the sensible default. Store it as BINARY(16) rather than a 36-character string to keep it compact.
How to decide
Ask three questions:
- Will IDs be generated in more than one place, or merged later? If yes, UUID.
- Will IDs appear in public URLs where guessing or counting matters? If yes, UUID.
- Otherwise? Auto-increment integer.
And whichever you choose, be consistent: a primary key type that changes from table to table makes every join and foreign key harder than it needs to be. Choosing keys well is one piece of a sound schema; the database design examples guide shows how they fit into full designs.
Frequently asked questions
Should I use an integer or UUID for my primary key?
For a single-database application, an auto-increment integer is the sensible default: small, fast, and index-friendly. Reach for a UUID when you generate IDs in more than one place, need to merge data from multiple databases, or do not want IDs to expose row counts or be guessable in URLs.
Are UUID primary keys slower than integers?
They can be, mainly because a random UUID is larger (16 bytes vs 4 or 8) and, if inserted in random order, causes index fragmentation in InnoDB. Using a time-ordered form like UUIDv7 or ULID removes most of that penalty by keeping inserts roughly sequential.
Why not just use auto-increment everywhere?
Auto-increment is great until IDs have to be unique across systems, or until you do not want them public. Sequential integers leak how many records you have and let someone guess valid IDs in a URL. In those cases a UUID is the safer choice.
What is UUIDv7 and why does it matter?
UUIDv7 is a newer UUID format whose leading bits are a timestamp, so values sort roughly in creation order. That gives you the global uniqueness of a UUID without the random-insert index fragmentation that hurt older UUIDv4 keys, making it a strong default when you need UUIDs.
Not sure which key strategy fits a system you are designing or scaling? That judgement call is exactly database consulting work. The full database design examples guide has more.