Using UUIDs in Databases: Performance & Best Practices

UUIDs in Databases

UUIDs are great for distributed systems, but require careful handling in databases.

Storage Formats

-- String (36 bytes) - Simple but wasteful
CREATE TABLE users (
    id VARCHAR(36) PRIMARY KEY
);
-- Binary (16 bytes) - Efficient but less readable
CREATE TABLE users (
    id BINARY(16) PRIMARY KEY
);
-- Native UUID type (PostgreSQL, MySQL 8+)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

PostgreSQL

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Or use built-in gen_random_uuid() (v13+)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Insert
INSERT INTO users (name) VALUES ('John');
-- Query
SELECT * FROM users WHERE id = '550e8400-e29b-41d4-a716-446655440000';

MySQL

-- Using BINARY(16) for efficiency
CREATE TABLE users (
    id BINARY(16) PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);
-- Insert with UUID_TO_BIN (MySQL 8+)
INSERT INTO users (id, name) 
VALUES (UUID_TO_BIN(UUID()), 'John');
-- Query
SELECT BIN_TO_UUID(id) AS id, name 
FROM users 
WHERE id = UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000');

Indexing Considerations

-- UUIDv4: Random distribution = B-tree fragmentation
-- UUIDv7: Sequential = Better B-tree performance
-- Consider partial indexes
CREATE INDEX idx_users_recent ON users (id)
WHERE created_at > NOW() - INTERVAL '30 days';
-- Or use composite indexes
CREATE INDEX idx_users_created_id ON users (created_at, id);

UUID vs Auto-Increment