UUID as Database Primary Keys: The Complete Implementation Guide

Sequential integer IDs are simple and fast — until you add a second database node, a microservice that generates its own records, or a client that needs to create IDs before the server confirms them. UUID solves the coordination problem integers cannot.

When to Use UUID Primary Keys

Use UUIDs when: multiple services or database nodes generate records independently without coordination. Client-side ID generation is needed (mobile apps, offline-first systems). Records are merged from multiple databases and integer ID conflicts would occur. You want to avoid exposing sequential record counts to users.

UUID Version Comparison

UUID v4 — Pure random, 122 bits of entropy. Ideal for security-sensitive identifiers. Causes B-tree index fragmentation due to random insertion order. UUID v7 — Time-ordered with millisecond precision prefix. Sorts chronologically, dramatically reducing index fragmentation. The recommended choice for new database primary keys in 2025.

PostgreSQL Implementation

Use the uuid-ossp extension: CREATE EXTENSION IF NOT EXISTS uuid-ossp. Primary key: id UUID DEFAULT uuid_generate_v4() PRIMARY KEY. For UUID v7 in PostgreSQL 17+, use the built-in gen_random_uuid() which now produces time-ordered UUIDs.

The Index Performance Reality

At small scales (under 10 million rows), UUID v4 vs integer performance is negligible. At large scales, UUID v4 fragmentation can increase index size by 30-50% and slow inserts measurably. UUID v7 eliminates most of this fragmentation. If you cannot use v7, consider ULID as an alternative time-ordered unique identifier.

Primary Key Design Decisions and Their Long-Term Consequences

The primary key design chosen at the start of a project shapes the database schema, API design, and distributed architecture for the lifetime of the application. Changing primary key types after a system has data in production is one of the most expensive database migrations imaginable — every table, every foreign key, every API endpoint, every client application, and every external integration that references those IDs must be updated simultaneously. Getting the primary key design right initially is worth significant upfront consideration.

The four main primary key options for relational databases are: auto-increment integers (simplest, sequential, not distributed-safe), UUIDs (distributed-safe, unpredictable, larger storage), ULIDs (distributed-safe, time-ordered, URL-safe encoding), and composite keys (multiple columns together, appropriate for junction tables and time-series data). Most new applications in 2024 should use either auto-increment integers for simple single-server applications where external IDs are not a security concern, or UUID v7/ULID for distributed systems, APIs with external ID exposure, and applications that will scale significantly.

Composite Primary Keys and When to Use Them

Composite primary keys — primary keys consisting of two or more columns — are the correct choice for specific table types. Junction tables (many-to-many relationship tables) naturally use composite keys composed of the two foreign keys they join: a user_roles table with user_id and role_id columns has (user_id, role_id) as its natural primary key. Adding a separate UUID or integer primary key to a junction table adds storage overhead and a second unique constraint without benefit.

Time-series tables that store measurements, events, or logs may use composite keys of (entity_id, timestamp) when queries always filter by entity and time range. A sensor_readings table with composite key (sensor_id, recorded_at) enables highly efficient range scans for time-series queries against specific sensors. The tradeoff is that inserting out-of-order timestamps — common in distributed systems where events arrive late — causes the same B-tree fragmentation problem as UUID v4. Partitioning the table by time range addresses this.

Foreign Keys and UUID Relationships

Foreign key relationships in UUID-primary-keyed schemas require careful storage type consistency. If the primary key is stored as UUID type in PostgreSQL, foreign keys referencing it must also be UUID type. If the primary key is stored as BINARY(16) in MySQL, foreign keys must also be BINARY(16). Mixed storage types for the same UUID value — one table storing as VARCHAR(36), another as BINARY(16) — prevent the database from enforcing foreign key constraints and cause comparison failures.

Index coverage for UUID foreign keys is critical for join performance. Unlike integer foreign keys where equality comparisons are extremely fast, UUID comparisons involve comparing 16 bytes instead of 4 or 8. Ensuring that every column used in a JOIN condition has an appropriate index — and that the index type matches the storage type — is more important for UUID schemas than for integer schemas. Use EXPLAIN ANALYZE in PostgreSQL or EXPLAIN in MySQL to verify that queries joining on UUID foreign keys are using index scans rather than sequential scans.

UUID Security Considerations

UUID v4's unpredictability is a meaningful security property, not just a convenience. Integer auto-increment IDs enable IDOR (Insecure Direct Object Reference) vulnerabilities — an attacker who has access to resource ID 1042 can easily attempt 1041, 1043, and so on, potentially accessing data belonging to other users if authorisation checks are missing or incomplete. UUID v4's 122 bits of randomness makes guessing valid IDs computationally infeasible.

However, UUID alone is not access control. The application must still verify that the authenticated user has permission to access the specific resource identified by the UUID. An endpoint that returns any resource given a valid UUID — without checking that the requesting user owns it — has an IDOR vulnerability regardless of whether the ID is an integer or a UUID. UUIDs reduce the ease of exploitation but do not eliminate the vulnerability class. Authorisation checks are required in all cases.