UUIDs vs. Sequential IDs: Why Databases Disagree on Which Is Better and When Version 7 Changes the Argument

The Origins of the UUID Standard

UUIDs were not invented for the web. Their earliest known standardized implementation appeared in Apollo Computer's Network Computing System (NCS) around 1987, where they were used to uniquely identify remote procedure calls across a distributed network. The core problem NCS was solving still defines UUIDs today: how do you assign a unique identifier to something without coordinating with any central authority?

The Open Software Foundation incorporated UUID generation into its Distributed Computing Environment (DCE) specification in the early 1990s, and Microsoft adopted the format for the Component Object Model (COM) architecture, where they were called GUIDs (Globally Unique Identifiers). GUIDs are structurally identical to UUIDs; the different name reflects Microsoft's independent marketing of the concept.

RFC 4122, authored by P. Leach of Microsoft, M. Mealling of Refactored Networks, and R. Salz of DataPower Technology, was published by the IETF in July 2005. It formalized the five UUID versions, defined the bit-field structure, and established UUIDs as a URN namespace. In May 2024, RFC 9562 was published as a Proposed Standard, updating RFC 4122 with formal definitions for the newer UUID versions including v6, v7, and v8 that had developed in practice.

The Five UUID Versions and What Each Solves

UUID is defined by RFC 4122 as a 128-bit value structured into five fields, formatted as eight hexadecimal characters, followed by three groups of four, and ending with twelve, all separated by hyphens. The version and variant bits embedded in specific positions identify which generation method was used.

Version 1 generates UUIDs from the MAC address of the generating machine and the current timestamp, measured in 100-nanosecond intervals since October 15, 1582. The MAC address component creates a privacy problem: the UUID leaks information about the machine that generated it. After the Melissa worm in 1999 was partly traced through a v1 UUID embedded in the malicious Word document, the privacy implications of version 1 became widely understood in security communities.

Versions 3 and 5 generate UUIDs deterministically from a namespace and a name using MD5 and SHA-1 hashing respectively. Given the same namespace and name, they always produce the same UUID. This is useful for generating stable identifiers derived from known data, such as creating a consistent UUID for a specific URL or DNS name.

Version 4 generates UUIDs from 122 bits of cryptographically random data, with the remaining 6 bits used for version and variant markers. V4 is the most commonly generated UUID type for general-purpose identifiers and is what most developers mean when they say "generate a UUID."

Version 7, formalized in RFC 9562 in 2024, embeds a Unix millisecond timestamp in the most significant 48 bits, followed by random data. This preserves approximate ordering by creation time while retaining a large random namespace. A v7 UUID is sortable by when it was created, which largely eliminates the B-tree fragmentation problem of v4 in database indexes.

The Collision Probability Question

A frequently asked question about UUID v4 is how likely two generated values are to collide. There are 2 to the power of 122 possible v4 UUIDs, approximately 5.3 times 10 to the 36th power. To reach a 50 percent probability of a single collision among randomly generated v4 UUIDs, you would need to generate approximately 2.7 quintillion UUIDs, the number produced by the birthday paradox applied to this namespace.

If you generated one billion UUIDs per second continuously, you would need to sustain that rate for approximately 85 years to reach the 50 percent collision probability threshold. At realistic generation rates, UUID v4 collision is not a practical concern. The UUID namespace is large enough that for all engineering purposes, every generated v4 UUID is unique.

The one scenario where this changes is using a poor random number generator. UUID v4 security depends entirely on the quality of the underlying entropy source. A UUID generated with a poorly seeded or cryptographically weak PRNG can produce predictable values that an attacker could reproduce. This is not a theoretical concern: in 2012, researcher Argyrios Zymnis demonstrated that certain Java implementations with weak seeding produced UUID v4 values with reduced effective entropy. Using a system-provided cryptographically secure random number generator (CSPRNG) is the correct approach.

Sequential IDs vs. UUIDs in Database Indexes

The performance argument for sequential IDs centers on how B-tree indexes work. A B-tree is a self-balancing tree structure that databases use for ordered index access. When new records are inserted, they must be placed at the correct sorted position in the tree. Sequential integers always go at the rightmost leaf of the tree, which minimizes page splits and keeps index pages dense and cache-friendly.

UUID v4 inserts at random positions in the tree. As the table grows, insertions are statistically distributed across all existing index pages. This causes two problems: page splits, where full pages must be divided to accommodate new entries, and cache inefficiency, where the working set of hot index pages is spread across the full index rather than concentrated at the leading edge.

In PostgreSQL benchmarks published by database engineers, tables using UUID v4 as primary keys typically show 20 to 40 percent higher write overhead than tables using sequential bigint keys at large table sizes, with the gap widening as the table grows. MySQL's InnoDB storage engine is particularly sensitive to this because its primary key is also the physical row ordering in the clustered index.

UUID v7 largely resolves this. Because v7 values increase monotonically in time, new inserts go to the end of the index, similar in behavior to sequential IDs. The write performance difference between v7 and sequential integers in benchmarks is minimal. The practical implication is that for new applications where database write performance is a priority and some time-ordering of IDs is acceptable, v7 is the technically superior choice.

Conclusion

Sequential integers work well for internal systems where records are never exposed in external URLs, where the generating system is the only source of new records, and where maximum write performance is required. A single-server application with a private API is a good fit.

UUID v4 works well for distributed systems where multiple services or databases generate records independently and need to merge them without coordination. It also works for any application that exposes record identifiers in URLs, where guessability is a security concern. Multi-tenant SaaS applications, systems with external APIs, and any application handling sensitive record types benefit from non-guessable identifiers.

UUID v7 works for systems that need both: non-guessable identifiers for external exposure, and efficient database index performance. The tradeoff is that v7 leaks approximate creation timestamps from the identifier itself, which may be a consideration in some privacy contexts.

When you need a globally unique identifier that requires no central coordination, UUID v4 provides that reliably across any scale. ToolHQ's UUID generator produces v4 UUIDs in any quantity you need, using a cryptographically secure random source.

Frequently Asked Questions

Try These Free Tools