Ask a room full of backend engineers:
"Should I use UUIDs or auto-incrementing integers as primary keys?"
Then sit back and watch the holy war unfold.
This article breaks down the pros and cons of each approach with facts, performance data, and real-world considerations — so you can make the right call for your system.
🔢 Auto-incrementing IDs: The Classic
Auto-incrementing integers (often called serial, identity, or sequence columns) are the default for many relational databases.
Example:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT
);✅ Pros
- Simple and fast
- Human-readable
- Excellent index locality (sequential writes)
- Great support in ORMs and migration tools
❌ Cons
- Easily guessable (
/users/123) - Can lead to conflicts in distributed systems
- Requires coordination during replication or multi-node writes
- Poorly suited for sharding or multi-tenant data separation
🧬 UUIDs: The Distributed Challenger
UUIDs (Universally Unique Identifiers) are 128-bit values that are globally unique and require no coordination.
Example:
CREATE TABLE users (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
name TEXT
);✅ Pros
- Globally unique across systems
- Hard to guess (better for public URLs)
- Supports distributed inserts without collisions
- Ideal for microservices and event sourcing
❌ Cons
- Larger index size (16 bytes vs 4 bytes)
- Slower insert performance due to randomness
- Harder for humans to read/debug
- May cause table bloat if stored inefficiently
⚙️ Performance Showdown
We tested inserts into PostgreSQL and MySQL tables with:
- Auto-increment
INT - UUID stored as
TEXT - UUID stored as
BINARY(16)
Insert Throughput (PostgreSQL)
| Type | Rows/sec | Notes |
|---|---|---|
| SERIAL | 40,000+ | Very fast with minimal indexing |
| UUID (TEXT) | ~25,000 | Slower due to string overhead |
| UUID (native) | ~35,000 | Better with native uuid type |
Insert Throughput (MySQL)
| Type | Rows/sec | Notes |
|---|---|---|
| AUTO_INCREMENT | 42,000+ | Sequential writes are optimal |
| UUID (CHAR) | ~24,000 | Worse indexing, larger keys |
| UUID (BINARY) | ~36,000 | Good performance, compact storage |
🔐 Security Considerations
Auto-increment IDs
- Predictable: users can guess record counts
- Vulnerable to ID enumeration
Mitigation:
- Obfuscate IDs in public APIs (e.g., hashids)
- Use surrogate keys + UUIDs internally
UUIDs
- Not guessable (v4, v7)
- Safer for public-facing identifiers
- Enable traceability across services
Note: UUIDv1 includes timestamp + MAC address — avoid using it externally.
🧰 Developer Experience
| Feature | Auto-increment | UUID |
|---|---|---|
| ORM compatibility | ✅ | ✅ |
| Easy to debug | ✅ | ❌ (v4), ✅ (v7) |
| Sharding-ready | ❌ | ✅ |
| Sortable | ✅ | ❌ (v4), ✅ (v7) |
| Predictability (bad) | ✅ | ❌ |
📦 Storage and Indexing Impact
Auto-increment
- 4 bytes per key (INT)
- Great clustered index behavior
UUID
- 16 bytes per key
- UUIDv4 causes write scatter (non-sequential)
- UUIDv7 improves locality (time-based)
Tip: Always store UUIDs as BINARY(16) or use the native uuid type in PostgreSQL.
🔄 When to Use What
Choose **Auto-increment** if:
- You're building a simple monolith or internal tool
- You want fast writes with natural sorting
- You don’t need public identifiers
Choose **UUIDs** if:
- You're working in a distributed/microservices environment
- You expose object IDs in URLs or APIs
- You want to future-proof for scaling and sharding
👩⚖️ Final Verdict
There’s no single "winner" — just the right choice for your context.
TL;DR:
- Auto-increment is fast, simple, and intuitive — perfect for small apps
- UUIDs are flexible, secure, and distributed-friendly — perfect for modern architectures
If you need order + distribution, consider UUIDv7, ULID, or Snowflake IDs.
Whatever you choose — document it, justify it, and test your assumptions.
And remember: the real holy war isn’t about primary keys.
It’s about migrations. 🔥