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?