UUID as Database Primary Key: Best Practices

Meta Description: Should you use UUIDs as primary keys? Learn the trade-offs, performance implications, and best practices for UUIDs in PostgreSQL, MySQL, and more.

The auto-increment integer has been the default primary key in relational databases for decades. It's simple, efficient, and universally supported. So why are more teams switching to UUIDs?

Because modern applications are distributed. Microservices generate IDs independently. Mobile clients create records offline. Data gets merged across shards, replicas, and regions. Auto-increment integers need a single authority to hand out the next number — and in a distributed world, that's a bottleneck.

UUIDs solve this. But they come with trade-offs. Let's break down when to use them, how to avoid performance pitfalls, and the right way to implement UUID primary keys in 2025.

Why Use UUIDs as Primary Keys?

1. Distributed Generation

Any service, client, or device can generate a UUID independently without coordinating with a central database. No need to insert a row to get its ID — you know the ID before the row exists.

This enables:

• Offline-first apps that create records on the device and sync later
• Microservices that generate IDs without calling the database
• Event sourcing where events carry their own IDs before persistence
• Data migration between systems without ID conflicts

2. No Information Leakage

Auto-increment IDs reveal information:

• /api/users/15847 tells an attacker there are roughly 15,847 users
• Sequential IDs are enumerable — scrape /api/orders/1 through /api/orders/99999

UUIDs like 7c9e6679-7425-40de-944b-e07fc1f90ae7 reveal nothing about count, order, or creation time (for v4).

3. Merge-Friendly

When you combine data from multiple databases (sharding, multi-tenancy, acquisitions), auto-increment IDs collide. UUIDs don't. Two databases can independently generate millions of records, and merging them is trivial.

4. API Stability

UUIDs make great external-facing identifiers. They're opaque, non-sequential, and don't change when you restructure your database. Expose UUIDs in APIs; keep auto-increment integers as internal implementation details if you need them.

The Performance Problem

Here's where things get real. Random UUIDs (v4) as primary keys cause measurable performance degradation in B-tree indexed databases. Here's why.

How B-Tree Indexes Work

Most databases (PostgreSQL, MySQL InnoDB) store primary key indexes as B-trees. Data is physically ordered by the primary key. When you insert a new row:

• Sequential keys (auto-increment): New rows always go to the end. The last page of the B-tree stays "hot" in memory. Insertions are fast and predictable.
• Random keys (UUID v4): New rows land at random positions throughout the tree. This causes:
  • Page splits: Full pages get split to accommodate random insertions
  • Cache thrashing: The entire index needs to be in memory, not just the tail
  • Fragmentation: Pages end up partially full, wasting space
  • Write amplification: More disk I/O per insert

Real-World Impact

On a table with 10M+ rows:

• UUID v4 primary keys can be 2–5× slower on inserts compared to auto-increment
• Index size increases by 20–40% due to fragmentation
• Range scans and pagination become slower because physically adjacent rows are logically scattered

The Solution: UUID v7

UUID v7 fixes the performance problem. Its timestamp prefix means UUIDs are generated in chronological order. New UUIDs are always greater than previous ones, so inserts always go to the end of the B-tree — just like auto-increment integers.

Generate both v4 and v7 UUIDs at createuuid.com to see the difference in sortability.

Implementation Guide

PostgreSQL

-- Native UUID type (16 bytes, efficient storage)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT now()
);

-- For UUID v7 (PostgreSQL 17+)
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    user_id UUID REFERENCES users(id),
    total DECIMAL(10,2)
);

-- For older PostgreSQL: use pg_uuidv7 extension
CREATE EXTENSION IF NOT EXISTS pg_uuidv7;

PostgreSQL's UUID type stores UUIDs as 16 bytes internally — more compact than the 36-character string representation.

MySQL (InnoDB)

-- Store as BINARY(16) for performance
CREATE TABLE users (
    id BINARY(16) PRIMARY KEY,
    id_text VARCHAR(36) GENERATED ALWAYS AS (
        INSERT(INSERT(INSERT(INSERT(
            HEX(id), 9, 0, '-'), 14, 0, '-'), 19, 0, '-'), 24, 0, '-')
    ) VIRTUAL,
    email VARCHAR(255) UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Or use CHAR(36) for simplicity (at the cost of storage)
CREATE TABLE simple_users (
    id CHAR(36) PRIMARY KEY,
    email VARCHAR(255)
);

Important: In MySQL InnoDB, the primary key IS the clustered index. Random UUIDs (v4) as primary keys will fragment the table itself, not just an index. UUID v7 or the uuid_to_bin() with swap flag can mitigate this:

-- MySQL 8.0+ swap flag reorders timestamp bits for better locality
INSERT INTO users (id) VALUES (UUID_TO_BIN(UUID(), 1));

SQLite

-- SQLite has no native UUID type — store as TEXT or BLOB
CREATE TABLE items (
    id TEXT PRIMARY KEY,  -- '550e8400-e29b-41d4-...'
    name TEXT
);

Application-Level Generation

Generate UUIDs in your application, not the database, for distributed systems:

// Node.js
import { v7 as uuidv7 } from 'uuid';

const newUser = {
    id: uuidv7(),  // Generated before DB insert
    email: '[email protected]',
    name: 'Alice'
};
await db.insert('users', newUser);

# Python
from uuid6 import uuid7

user = User(
    id=str(uuid7()),
    email="[email protected]"
)
session.add(user)

Storage Optimization

Size Comparison

UUIDs are 2–4× larger than integers. On a table with many foreign key references, this adds up. Mitigation strategies:

1. Always store as binary (BINARY(16) in MySQL, UUID type in PostgreSQL) — not as CHAR(36)
2. Use UUIDs for primary/external keys only — internal foreign keys can use integers if performance is critical
3. Consider a hybrid approach: Auto-increment integer as internal PK, UUID as external-facing identifier in a separate indexed column

Hybrid Pattern

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,              -- Internal, fast, compact
    public_id UUID UNIQUE DEFAULT uuidv7(), -- External, exposed in APIs
    email VARCHAR(255) UNIQUE
);

-- Foreign keys use the integer
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),  -- 8 bytes, not 16
    public_id UUID UNIQUE DEFAULT uuidv7()
);

This gives you the performance of integers internally and the distributed-friendly properties of UUIDs externally.

Indexing Strategies

Primary Index

For UUID primary keys, the storage engine's clustered index is the main concern:

• Use UUID v7 to ensure chronological insert ordering
• Store as binary (not string) for compact indexes
• Avoid secondary indexes on UUID v4 columns if possible — they fragment just like the primary key

Composite Indexes

If you frequently query by both a UUID and another column:

-- This index covers lookups by user_id + created_at
CREATE INDEX idx_orders_user_date 
ON orders(user_id, created_at DESC);

Partial Indexes (PostgreSQL)

-- Index only active orders for faster queries
CREATE INDEX idx_active_orders 
ON orders(id) WHERE status = 'active';

Common Mistakes to Avoid

1. Storing UUIDs as VARCHAR(36)

Every comparison, join, and index lookup processes 36 characters instead of 16 bytes. Use native UUID types or BINARY(16).

2. Using UUID v4 Without Understanding the Performance Cost

Test with realistic data volumes. On a 1,000-row table, the difference is invisible. On 10M rows with high write throughput, it's significant.

3. Generating UUIDs in the Database for Distributed Systems

If the goal is distributed ID generation, generate UUIDs in the application layer. DEFAULT gen_random_uuid() works, but it means you don't know the ID until after the insert.

4. Not Indexing UUID Foreign Key Columns

Just because the UUID is a primary key in one table doesn't mean the foreign key column in another table is automatically indexed. Always create explicit indexes on FK columns.

5. Comparing UUIDs as Strings

-- Slow: string comparison
WHERE id = '550e8400-e29b-41d4-a716-446655440000'

-- Fast: native UUID comparison (PostgreSQL)
WHERE id = '550e8400-e29b-41d4-a716-446655440000'::uuid

Frequently Asked Questions

Should I use UUID or auto-increment for my new project?

If you're building a monolithic application with a single database, auto-increment integers are simpler and faster. If you're building microservices, need offline ID generation, or want opaque external identifiers, use UUID v7. The hybrid approach (integer PK + UUID public ID) works well when you want both.

How much slower are UUID primary keys compared to integers?

On high-write workloads (10K+ inserts/sec) with millions of existing rows, UUID v4 can be 2-5× slower for inserts. UUID v7 narrows this to roughly 1.1-1.3× — negligible for most applications. For read-heavy workloads, the difference is minimal regardless of UUID version.

Can I use UUIDs with ORMs like Prisma, TypeORM, or Django?

Yes. All major ORMs support UUID primary keys. In Django, use models.UUIDField(primary_key=True, default=uuid.uuid4). In Prisma, use id String @id @default(uuid()). In TypeORM, use @PrimaryGeneratedColumn("uuid"). For UUID v7, generate in application code rather than relying on the ORM's default.

Is it safe to expose UUIDs in URLs?

UUID v4 is safe — it reveals nothing. UUID v7 reveals creation time (millisecond precision), which may or may not matter for your use case. Neither reveals record count or allows enumeration. For maximum security, consider encrypting or hashing the UUID before exposing it, but for most applications, raw UUIDs are fine.

How do I migrate from auto-increment to UUID primary keys?

Add a UUID column, populate it for existing rows, update foreign keys to reference the new column, then swap primary keys. Do this in a migration with proper locking and testing. It's a significant operation on large tables — plan for downtime or use online schema migration tools like pt-online-schema-change (MySQL) or pg_repack (PostgreSQL).

Need to generate UUIDs for testing or development? Use our free UUID Generator — instant v4 and v7 UUIDs with bulk export options.