Why You Should Stop Using UUIDv4 for Database Primary Keys

Introduction

When you are creating a new database table, you need a primary key to uniquely identify each row. For years, the default advice for modern web apps has been to avoid auto-incrementing integers because they leak your business volume to the public and make distributed systems tricky.

Instead, everyone reaches for UUIDv4.

UUIDv4 gives you a completely random string of 36 characters. It's globally unique, which means you can generate it anywhere without checking the database.

It sounds perfect, but underneath the surface, UUIDv4 is quietly destroying your database performance as your data grows.

Understanding the Problem

UUIDv4: Completely Random

[f81d4fae-c29c-41f3-b359-d0ea0f91a8f1]
[02c0a94b-31a8-4ff5-bf7c-a5d8c8e7f9b2]
[b67c10ea-6f39-4e8c-9d27-c1a8b9e0d5f3]

Result: Inserted scattered randomly across the B-Tree index.
        Causes heavy disk I/O and index fragmentation.

UUIDv7/ULID: Time-Ordered

[018c3a2f-1234-5678-9abc-def012345678]
[018c3a30-1234-5678-9abc-def012345678]
[018c3a31-1234-5678-9abc-def012345678]

Result: Always appended sequentially to the end of the index tree.
        Inserts stay lightning fast.

The B-Tree Nightmare

Most relational databases (PostgreSQL, MySQL) use a data structure called a B-Tree to index your primary keys. B-Trees love sequential data.

When you insert keys that are sequentially ordered, the database simply appends them to the end of the tree index efficiently.

Because UUIDv4 is completely random, every new row you insert gets thrown into a completely random spot inside that index tree. As your table grows to millions of rows, the database has to constantly reshuffle the index in memory and swap data pages in and out of your disk storage (a problem called page splitting).

Your database write performance plummets.

The Performance Impact

With UUIDv4:

• Random inserts require index reorganization
• Page splits happen constantly on large tables
• Disk I/O multiplies as the table grows
• Index fragmentation gets worse over time
• Query performance degrades non-linearly

The effect becomes catastrophic around 10M+ rows, but problems start earlier.

The Fix: UUIDv7 or ULIDs

You don't have to go back to boring integer IDs. Instead, switch to a time-ordered unique ID like UUIDv7 or ULID.

These variants combine a timestamp component at the front with random characters at the back. They are still completely unique and secure, but because they start with a timestamp, they are naturally sorted in chronological order.

They append beautifully to your database index, keeping your inserts fast while maintaining global uniqueness.

UUIDv7 (RFC Draft)

Structure: timestamp (32-bit) + random (96-bit)
Format: xxxxxxxx-xxxx-7xxx-yxxx-xxxxxxxxxxxx

Advantages:
+ Sortable by creation time
+ Still globally unique
+ 36-character format (same as UUIDv4)
+ Growing adoption in frameworks

ULID (Universally Unique Lexicographically Sortable Identifier)

Structure: timestamp (48-bit) + randomness (80-bit)
Format: 01ARZ3NDEKTSV4RRFFQ69G5FAV

Advantages:
+ Sortable and sequential
+ 26 characters (more compact)
+ Better readability
+ Excellent performance characteristics
+ No version number overhead

Implementation Examples

PostgreSQL with UUIDv7

CREATE TABLE users (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Better: Use UUIDv7 extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
  id UUID DEFAULT uuid_generate_v7() PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

Node.js/JavaScript with ULID

import { ulid } from 'ulidx';

const userId = ulid();
// Result: 01ARZ3NDEKTSV4RRFFQ69G5FAV

// In your Supabase schema:
CREATE TABLE users (
  id TEXT PRIMARY KEY DEFAULT gen_ulid(),
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

Python with UUIDv7

from uuid6 import uuid7

user_id = uuid7()
# Result: 018c3a2f-1234-5678-9abc-def012345678

# Insert efficiently into database
db.users.insert({
  'id': str(user_id),
  'email': 'user@example.com',
  'created_at': datetime.now()
})

Real-World Impact

Small Table (< 100K rows)

• UUIDv4: Insert time ~0.5ms
• UUIDv7: Insert time ~0.4ms
• Difference: Negligible

Medium Table (1M-10M rows)

• UUIDv4: Insert time ~5-15ms (and growing)
• UUIDv7: Insert time ~0.4-0.5ms (stable)
• Difference: 10-30x slower

Large Table (100M+ rows)

• UUIDv4: Insert time 50ms+ (and degrades further)
• UUIDv7: Insert time ~0.5ms (still stable)
• Difference: 100x+ slower

Why You Might Still Use UUIDv4

There are legitimate cases where UUIDv4 makes sense:

1. NoSQL databases - They don't use B-Tree indexes, so randomness doesn't matter
2. Distributed systems - If you absolutely need maximum collision resistance and don't control ID generation order
3. Small tables - Under 100K rows, the performance difference is negligible
4. Legacy systems - Migration might be more expensive than the performance cost
5. Security through obscurity - If you specifically need IDs to be unpredictable and unsortable

But for most modern web applications with relational databases, this is a clear win.

Related Guides

For more on database optimization, see Choosing the Right Database. For deployment safety, see Pre-Deployment Checklist.

Also see System Evolution: How to Scale Your Infrastructure to understand when database performance becomes critical.

Migration Guide

If you have an existing table with UUIDv4:

-- Step 1: Add new ULID column
ALTER TABLE users ADD COLUMN id_new TEXT UNIQUE;

-- Step 2: Backfill with ULIDs
UPDATE users SET id_new = gen_ulid() WHERE id_new IS NULL;

-- Step 3: Drop old primary key
ALTER TABLE users DROP CONSTRAINT users_pkey CASCADE;

-- Step 4: Rename and set as primary key
ALTER TABLE users DROP COLUMN id;
ALTER TABLE users RENAME COLUMN id_new TO id;
ALTER TABLE users ADD PRIMARY KEY (id);

-- Step 5: Recreate foreign key constraints
-- (depends on your schema)

Key Takeaways

1. UUIDv4 destroys database performance - Random inserts cause B-Tree fragmentation
2. UUIDv7 and ULIDs are the modern solution - Time-ordered IDs maintain performance
3. Sequential > Random for databases - B-Trees are optimized for ordered data
4. Performance gap widens with table size - Negligible at 100K rows, catastrophic at 100M+
5. Still globally unique - You don't lose uniqueness benefits
6. Sortable by creation time - Nice side benefit for debugging and auditing

Further Reading

Learn more from PostgreSQL UUID documentation and The case against UUID keys.

For production-grade ID generation, see the ULID specification and UUIDv7 RFC draft.

---

Remember: Your database index is the heart of query performance. Feed it sequential data, not random chaos. Your future self operating a 100M-row table will thank you.