UUIDs in Databases — Performance, Storage, and Best Practices

UUIDs as primary keys eliminate sequential ID guessing and simplify distributed systems — but random UUIDs (v4) cause index fragmentation. Here’s what you need to know before using UUIDs in production.

Use the UUID Generator to generate UUID v4 and v7 values.

UUID storage size

-- Storing as text (worst performance):
id VARCHAR(36)  -- '550e8400-e29b-41d4-a716-446655440000' = 36 bytes

-- Storing as binary (recommended):
id BINARY(16)   -- 16 bytes — less storage, faster comparison

-- PostgreSQL native UUID type:
id UUID         -- 16 bytes, optimized internally

-- MySQL with UUID function:
id BINARY(16) DEFAULT (UUID_TO_BIN(UUID()))

-- Convert when reading:
SELECT BIN_TO_UUID(id) as id FROM users;

The index fragmentation problem (UUID v4)

UUID v4 is completely random. Every INSERT to an indexed table causes:

Page 1: [uuid-c3f, uuid-d2a, uuid-f1b]
INSERT uuid-0aa → must go at the beginning
Page 2: [uuid-4e2, uuid-5f8, uuid-7c1]
INSERT uuid-6bd → must go in the middle of page 2

→ Pages split to make room
→ Index becomes fragmented
→ Each new row goes to a random page (cache misses)
→ Performance degrades at scale

With auto-increment IDs, new rows always append to the end — minimal page splits.

UUID v7 — time-ordered and database-friendly

UUID v7: 018f1e9c-b4a2-7xxx-xxxx-xxxxxxxxxxxx
└────────────────┘ └─────────────────────────┘
  Unix timestamp (ms)    Random bits

Example: 018f1e9c-b4a2-7abc-def1-234567890abc

UUID v7 embeds a millisecond timestamp in the first 48 bits. This makes new UUIDs sort after all existing ones — same behavior as auto-increment for index performance.

-- PostgreSQL: generate UUID v7 (pgcrypto extension):
SELECT gen_random_uuid();  -- v4 (random)

-- UUID v7 in PostgreSQL (pg_uuidv7 extension):
SELECT uuid_generate_v7();

-- UUID v7 in JavaScript:
import { v7 as uuidv7 } from 'uuid';  // npm install uuid
const id = uuidv7();  // '018f1e9c-b4a2-7abc-def1-234567890abc'

UUID vs auto-increment vs ULID

Feature	Auto-increment	UUID v4	UUID v7	ULID
Storage	4-8 bytes	16 bytes	16 bytes	16 bytes
Sortable	Yes	No	Yes	Yes
Index performance	Excellent	Poor (fragmentation)	Good	Good
Globally unique	No	Yes	Yes	Yes
Guessable	Yes (sequential)	No	Partially (timestamp visible)	Partially
Standard format	N/A	RFC 4122	Draft RFC	community

PostgreSQL UUID setup

-- Enable uuid-ossp extension:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- UUID v4 primary key:
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- UUID v7 with pg_uuidv7:
CREATE EXTENSION IF NOT EXISTS "pg_uuidv7";
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  payload JSONB
);

-- Or use gen_random_uuid() (built-in since PG 13):
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL
);

MySQL UUID setup

-- Convert UUID to binary for storage (MySQL 8.0+):
CREATE TABLE users (
  id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
  -- The '1' flag enables time reordering (makes v1 UUIDs sortable)
  email VARCHAR(255) NOT NULL UNIQUE
);

-- Query:
SELECT BIN_TO_UUID(id) as id, email FROM users;

-- Insert:
INSERT INTO users (id, email) VALUES (UUID_TO_BIN(UUID(), 1), 'alice@example.com');

When to use UUID vs auto-increment

Use UUID when:

• Distributing across multiple databases (merging records without ID conflicts)
• Exposing IDs in URLs (auto-increment exposes record count)
• Generating IDs client-side before saving to DB
• Microservices that create records independently

Use auto-increment when:

• Single database, no distribution needs
• Maximum insert performance
• Simple foreign key relationships
• Storage efficiency matters most

Related tools

• UUID Generator — generate UUID v4 and v7
• UUID v4 vs v7 for Databases — performance comparison
• UUID Format — UUID structure explained