UUID vs Auto-Increment IDs for Database Primary Keys

Auto-increment integers (1, 2, 3…) and UUIDs (550e8400-e29b-41d4-a716-446655440000) are the two most common choices for database primary keys. Each comes with real trade-offs.

Auto-increment integers

Pros:

• Compact — 4 bytes (INT) or 8 bytes (BIGINT) vs 16 bytes for a UUID
• Naturally ordered — comparing two IDs tells you which row is newer
• Readable in URLs, logs, and support tickets
• Sequential inserts keep B-tree indexes compact — no page splits

Cons:

• Leak information — GET /orders/1847 tells anyone you've processed 1,847 orders
• Require a central sequence, which complicates multi-primary databases and sharding
• IDs from different tables can collide when you merge data or pass IDs around in application code

UUIDs

Pros:

• Generated anywhere — client, offline worker, microservice — without touching the database
• Don't expose row counts or creation timestamps
• Globally unique across tables, services, and databases — safe to merge datasets

Cons:

• 16 bytes per key, multiplied across every index and foreign key
• Random UUIDs (v4) cause index fragmentation: each insert lands at a random page, which is expensive at scale
• Ugly and error-prone in URLs and logs

UUID v7 changes the calculus

UUID v7 is time-ordered: the first 48 bits are a millisecond timestamp, so rows are inserted in roughly sequential order. That eliminates most of the index fragmentation problem while keeping the distributed generation benefit. If your database or ORM supports v7, it's now the recommended default when you want UUID-like IDs.

See UUID versions explained for a detailed breakdown of v1 through v7.

When to use which

Situation	Recommendation
Internal IDs, single-node app	Auto-increment integer
Public-facing resource IDs	UUID — don't leak your row count
Distributed / multi-service architecture	UUID v7
High-write tables, storage is tight	Auto-increment integer
IDs generated on the client before the DB write	UUID
Cross-service data merges	UUID

A middle path

Some teams use auto-increment as the internal primary key and a UUID as a public-facing external_id column. You get fast integer joins internally and safe opaque IDs in your API. The extra column costs a small amount of storage.

Generate UUIDs right now

Use the UUID generator to produce v4 or v7 UUIDs in bulk — no library required.

For a primer on what's inside a UUID, read what is a UUID.