UUID as Database Primary Key: Best Practices

Advantages of UUID Primary Keys

Core advantages of using UUID as database primary keys: in distributed systems, multiple nodes can generate IDs independently without conflicts, requiring no global lock or central ID generator; no sequence exposure — users cannot enumerate resources by guessing IDs (improved security); simplifies data migration and merging, records from different databases can be merged without ID conflicts; IDs can be pre-generated on the client side, reducing database round trips; in microservice architectures, each service can independently manage its own ID space.

Performance Challenges of UUID Primary Keys

The main performance issue of UUID v4 as primary keys lies in the B-Tree index: since UUID v4 is completely random, each new record insertion may place the new UUID anywhere in the index tree rather than at the end. This causes frequent "page splits" (B-Tree nodes must split to accommodate new data), and as data volume grows, write performance degrades significantly. In high-concurrency write scenarios (thousands of inserts per second), random UUID primary keys may cause 2-5x write performance loss. For read performance, random UUIDs cause more page cache misses (requiring jumps to random disk locations), increasing I/O pressure.

Solution: Ordered UUID

The best solution for UUID primary key performance issues is to use ordered (time-ordered) UUIDs: UUID v7 is the latest standard solution (RFC 9562, 2024), with the high 48 bits being a Unix millisecond timestamp ensuring increasing order; MySQL's UUID_TO_BIN(UUID(), 1) function rearranges UUID v1's time fields to make them ordered; SQL Server's NEWSEQUENTIALID() generates sequential GUIDs; third-party solutions like ULID (Universally Unique Lexicographically Sortable Identifier) are also good choices. Ordered UUIDs have write performance close to auto-increment integer IDs while retaining UUID's distributed-friendly nature.

UUID Storage Recommendations by Database

• PostgreSQL: Use native UUID type (16 bytes) for optimal performance; supports gen_random_uuid() (v4) and uuid_generate_v4() functions
• MySQL 8.0+: Use UUID_TO_BIN() to store as BINARY(16), or varchar(36) for best compatibility; built-in UUID() generates v1, recommend UUID_TO_BIN(UUID(), 1) to make it ordered
• SQLite: No native UUID type, store as TEXT(36) or BLOB(16); UUIDs must be generated at the application layer
• SQL Server: Use uniqueidentifier type (16 bytes); prefer NEWSEQUENTIALID() over NEWID() for better index performance
• MongoDB: ObjectId is the built-in ordered ID (12 bytes), typically better than UUID; if UUID is needed, use the UUID BSON subtype

Best Practice Code for MySQL

-- MySQL 8.0 中最优的 UUID 主键方案
CREATE TABLE orders (
    id BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)),
    customer_id INT NOT NULL,
    total DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- 插入记录
INSERT INTO orders (customer_id, total) VALUES (1, 99.99);

-- 查询时转回字符串
SELECT BIN_TO_UUID(id, 1) as id, customer_id, total
FROM orders;

-- 按 UUID 字符串查询
SELECT * FROM orders
WHERE id = UUID_TO_BIN('550e8400-e29b-11d4-a716-446655440000', 1);

-- PostgreSQL 中的简洁方案
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Should You Also Keep an Integer ID

In certain scenarios, keeping both a UUID primary key and an auto-increment integer ID (as internal ID) is reasonable: UUID as the externally exposed resource identifier (in URLs and API responses), integer ID for internal foreign key references (improving join performance); however, this increases storage and maintenance cost. Another approach is using an integer primary key with UUID as an extra public_id column (with unique index), using UUID only in external APIs. This approach balances performance and security but requires additional database columns and indexes. The specific choice depends on system scale, performance requirements, and security needs.