Most of us learned databases with auto-increment IDs. id INT AUTO_INCREMENT is in every tutorial. It works — until it doesn't.
The Case for Auto-Increment
Auto-increment integers are fast, small (4 bytes), and naturally ordered. For a single-database app with one writer, they're perfect. But real systems rarely stay that simple.
Where Auto-Increment Fails
Distributed writes. When you have multiple API servers writing to the same database, auto-increment creates a bottleneck. Someone has to own the counter.
Multi-tenant databases. Merging data from different shards or regions? Integer ID collisions are guaranteed. You'll spend days resolving conflicts.
Data exposure. /api/users/1, /api/users/2... users and competitors can estimate your growth, user count, and order volume in minutes.
Offline-first apps. Can't generate IDs while disconnected if you need a central sequence. UUIDs work anywhere, anytime.
UUID v4: The Distributed-Friendly Default
UUID v4 generates 122 random bits via a cryptographically secure PRNG — no coordination needed. The collision probability is astronomically low (1 in 2.7×10^18). PostgreSQL handles them natively:
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL
);
The 16-byte storage overhead is negligible for modern systems.
The Trade-off
UUIDs aren't sortable by insertion order and can fragment B-tree indexes. For write-heavy workloads, consider UUID v7 (time-ordered) or ULID. But for 95% of projects, v4 works fine.
Quick Tip
When setting up a new project, I generate a batch of UUIDs to copy into my seed files or test fixtures. There's a dead-simple generator at codetoolbox.pro/tools/uuid-generator — no signup, no uploads, all browser-side. Just set the count and go.
What do you use for primary keys in your projects?
Top comments (0)