Using UUIDs as Database Primary Keys: Performance, Storage, and Best Practices

Why Use UUIDs Instead of Auto-Increment?

• Distributed systems: Multiple databases can generate IDs independently without coordination
• Merge-friendly: Combining data from multiple sources never causes ID collisions
• Security: Auto-increment IDs reveal how many records you have and enable enumeration attacks
• Offline-first apps: Mobile apps can generate IDs before syncing to server

The Index Fragmentation Problem

Database B-tree indexes store keys in sorted order. With auto-increment, each new ID is always larger than all existing ones — inserts go to the end of the index. Fast, sequential, cache-friendly.

With UUID v4 (random), each new ID is random — it inserts into a random position in the index. The database must: find the right position, potentially split an index page, update adjacent pages. At 10 million rows, this causes measurable performance degradation.

Best Practice: Store as BINARY(16)

-- MySQL: Store efficiently, display as string
CREATE TABLE users (
  id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
  name VARCHAR(255)
);

INSERT INTO users (id, name) VALUES (UUID_TO_BIN(UUID(), 1), 'Rahul');
SELECT BIN_TO_UUID(id), name FROM users;

-- PostgreSQL: Native UUID type
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT
);

UUID v7 for New Applications

For new applications starting in 2024+, use UUID v7. It combines the distributed generation benefits of UUID with near-sequential insertion performance. PostgreSQL 17 (2024) has native UUID v7 support. Most ORMs (Prisma, TypeORM, Sequelize) have v7 packages available.