Why Databases Use UUIDs as Primary Keys

Choosing a primary key type is one of the most important data modeling decisions. For decades, auto-incrementing integers were the default. Today, more teams are choosing UUIDs — especially in distributed and cloud-native systems. Let us explore why.

Auto-Increment Integers: The Traditional Approach

Sequential integer IDs (1, 2, 3, ...) are simple, compact, and index-friendly. They work well for single-server databases where a central authority can assign the next available number.

Advantages:

Compact storage (4 or 8 bytes)
Excellent B-tree insert performance (appends to end)
Easy to read and reference
Naturally sortable by creation order

Disadvantages:

Require coordination in distributed systems (who gets ID = 4?)
Expose business information (total user count, creation order)
URL predictability (easily guessable: /users/42)
Merging data across databases requires ID remapping

UUIDs: The Modern Approach

UUIDs solve the fundamental problem of coordination. Since each UUID is independently generated without requiring a central authority, they work naturally in distributed environments.

Advantages:

No coordination needed — any node can generate IDs independently
Safe to merge datasets from different databases
No information leakage about record count or creation order (v4)
Can be generated client-side before inserting into the database
Universally unique across tables, databases, and even organizations

Disadvantages:

Larger storage (16 bytes vs 4 bytes for an integer)
Random v4 UUIDs can cause B-tree index fragmentation
Harder to read and remember
Slightly slower insert performance in some databases

PostgreSQL UUID Support

PostgreSQL has native UUID support with the uuid data type (requires the uuid-ossp or pgcrypto extension for generation):

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

MySQL UUID Support

MySQL can store UUIDs as CHAR(36) or as a binary BINARY(16) for more efficient storage. MySQL 8.0+ includes built-in UUID functions:

CREATE TABLE users (
  id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

UUID v7: The Best of Both Worlds

If you are choosing between integers and UUIDs today, consider UUID v7. It starts with a Unix timestamp, making it naturally sortable (like auto-increment) while retaining the distributed-friendly properties of UUIDs. This means:

B-tree friendly — new rows append to the end of the index
Time-sortable — you can order records by creation time from the ID alone
Distributed-safe — no coordination required

Generate v7 UUIDs with our UUID Generator.

When to Use Which

Scenario	Recommended Key
Single-server, simple app	Auto-increment integer
Distributed or multi-master	UUID v4 or v7
High-write database	UUID v7 (time-ordered)
Public-facing API	UUID v4 (no info leakage)
Content-addressable storage	UUID v5 (deterministic)

UUID Beautifier