UUID vs Sequential IDs: Which Should You Use in Your Database?

Primary key strategy is one of the most consequential database design decisions you make. It affects query performance, storage requirements, URL security, and distributed system scalability. Understanding the trade-offs clearly helps you make the right choice for each context.

Sequential IDs: Strengths and Weaknesses

Auto-increment integers (1, 2, 3...) are compact (4 or 8 bytes vs 16 for UUID), human-readable, naturally ordered, and fast to index. The weakness is information leakage: /orders/1 exposes your order volume; /users/42 confirms the 41 users that exist. Sequential IDs also require coordination in distributed systems — two nodes generating IDs independently will collide.

UUID v4: Strengths and Weaknesses

A UUID v4 is a 128-bit random identifier: 550e8400-e29b-41d4-a716-446655440000. It carries no sequential information, cannot be guessed or enumerated, and any system can generate one without coordination. The collision probability — approximately 1 in 5.3 × 10³⁶ — is effectively impossible in practice. The cost is size (16 bytes vs 4), random insertion order causing B-tree page splits, and reduced readability in logs.

When Each Is the Right Choice

Use UUID v4 for distributed systems, public-facing IDs in URLs and APIs, and identifiers generated before database insertion (for optimistic UI or event sourcing). Use sequential IDs for single-database internal systems where IDs are never exposed publicly and insertion performance is critical. Generate batches of UUIDs for seed scripts and test fixtures using the UUID Generator — up to 100 at once, copied with one click.

UUID vs Auto-Increment: A Practical Decision Framework

The choice between UUID and auto-increment integer primary keys affects database performance, application architecture, API design, and security properties. Auto-increment integers are the default in most relational database tutorials and many real applications, but they carry significant drawbacks at scale and in distributed architectures. UUIDs address those drawbacks while introducing their own tradeoffs.

Auto-increment integers have three advantages: they are compact (4-8 bytes vs 16 bytes for UUID), they are ordered (new records always have higher IDs than old ones, enabling efficient range scans), and they are simple to generate (the database handles it, no application code required). Their disadvantages at scale are significant: they are predictable (knowing ID 1042 implies the existence of 1041 prior records, enabling enumeration attacks), they require a central authority to assign them (making distributed generation without coordination impossible), and they reveal information about business volume (competitors can deduce order volumes by creating two orders and comparing IDs).

UUID Version Comparison for Database Use

UUID v4 generates a random 128-bit identifier with 6 fixed bits and 122 random bits. The randomness is its security strength — there is no way to predict or enumerate v4 UUIDs. Its weakness for database primary keys is that random values cause B-tree index fragmentation over time, reducing insert performance on large tables. This fragmentation becomes significant at tens of millions of rows and severe at hundreds of millions.

UUID v7, standardised in RFC 9562 (2024), addresses the fragmentation problem by embedding a millisecond-precision timestamp in the first 48 bits followed by 74 bits of randomness. Because the timestamp prefix is monotonically increasing, v7 UUIDs inserted in time order produce sequential-like insertions in B-tree indexes, dramatically reducing fragmentation while retaining the unpredictability needed for external identifiers. UUID v7 is the recommended choice for new database primary keys in 2024 and beyond.

Implementing UUIDs Across the Stack

Database layer: PostgreSQL has a native UUID type that stores 16 bytes efficiently. MySQL lacks a native UUID type; use BINARY(16) with UUID_TO_BIN(uuid, 1) for storage (the second argument swaps byte order for better sequential performance). SQLite stores UUIDs as TEXT(36) by default, or BLOB(16) for efficiency. Indexes on UUID columns should be created for all lookup columns — primary key index is automatic, but foreign key and lookup columns need explicit indexes.

Application layer: generate UUIDs in application code using the uuid npm package (Node.js), the uuid Python package, or the java.util.UUID class. Never rely on the database to generate UUIDs in application code — this requires a round trip to the database before inserting a new record, preventing client-side optimistic updates. Generate the UUID before sending the insert query and include it in the INSERT statement. This pattern allows the application to know the new record's ID immediately without waiting for the database response.

UUIDs in REST API Design

Using UUIDs as API resource identifiers follows RESTful design principles and provides security benefits over integer IDs. A URL like /api/users/550e8400-e29b-41d4-a716-446655440000 is opaque — it reveals nothing about the user base size, creation order, or rate of growth. Integer-ID URLs like /api/users/10042 are information leaks — they reveal that there are at least 10,042 users and enable sequential enumeration of all user records by incrementing the ID.

Practical implementation: return UUIDs as lowercase hyphenated strings in JSON responses (the canonical format defined by RFC 4122). Accept UUIDs in both hyphenated and compact (no hyphens) forms in API inputs, normalising to hyphenated form before storage. Validate UUID format at the API boundary using a regex or a UUID parsing library before attempting database lookups — an invalid UUID format should return a 400 Bad Request, not a 500 database error.

Performance Benchmarks and Real-World Numbers

At what scale does UUID index fragmentation become a real performance problem? Benchmark studies show that UUID v4 primary key insert performance matches auto-increment integer performance up to approximately 10 million rows on modern hardware. Between 10M and 100M rows, insert performance degrades noticeably due to page splits from random insertions. Above 100M rows, the performance difference becomes severe. UUID v7 or ULID reduces fragmentation to levels comparable to integer IDs at all scales, making sequential UUID generation the practical solution for large tables.

The storage overhead of UUIDs versus integers is real but typically modest. A table with 10 million rows and UUID v4 primary key stored as TEXT(36) uses approximately 360MB for the primary key values alone, versus 80MB for 64-bit integers. Using BINARY(16) storage reduces UUID storage to 160MB — approximately double the integer storage. For most applications, this overhead is acceptable given the architectural benefits. For very high-volume tables (billions of rows), the storage difference becomes significant and warrants careful evaluation.