UUID vs Sequential IDs: v4, v7 and Auto-Increment Compared
UUID v4 has 122 random bits, so collisions are not a practical risk. v7 adds a timestamp for sortable keys. When auto-increment is still the better choice.
Use a UUID when IDs must be created in many places without coordination or must not reveal how many records exist, and use an auto-increment integer when one database owns the ID and compact, ordered keys matter most. If you pick UUIDs for a database primary key, prefer version 7 (time-ordered) over version 4 (fully random) for index performance. Collision risk is not the deciding factor: a version 4 UUID has 122 random bits, and the numbers below show why that is enough.
What the versions are
UUIDs are 128-bit identifiers standardized in RFC 9562, usually written as 36 characters in the form 8-4-4-4-12 hex digits.
| Property | Auto-increment integer | UUID v4 | UUID v7 |
|---|---|---|---|
| Size in binary | 8 bytes (bigint) | 16 bytes | 16 bytes |
| Generated by | The database | Any client | Any client |
| Random bits | 0 | 122 | 74 in the standard layout |
| Time-ordered | Yes | No | Yes, to the millisecond |
| Reveals record count | Yes | No | No (reveals creation time) |
Version 4 fills everything except 6 fixed bits (the version and variant) with random data: 128 − 6 = 122 random bits. Version 7 starts with a 48-bit Unix timestamp in milliseconds and fills the remaining bits with random data. In the layout RFC 9562 defines, that leaves 12 + 62 = 74 random bits.
Collision probability: the birthday bound
Collisions follow the birthday problem: the chance that some pair among n random values matches is about 1 − e^(−n(n−1) / 2N), where N is the number of possible values. For v4, N = 2^122, roughly 5.3 × 10^36. Python computes it directly:
import math
N = 2**122
def p(n):
return -math.expm1(-n * (n - 1) / (2 * N))
for n in (10**9, 10**12, 10**15):
print(n, p(n))
print(math.sqrt(2 * N * math.log(2))) # IDs for a 50% chance
| UUIDs generated | Probability of any collision |
|---|---|
| 1 billion (10^9) | about 9.4 × 10^-20 |
| 1 trillion (10^12) | about 9.4 × 10^-14 |
| 1 quadrillion (10^15) | about 9.4 × 10^-8 |
A 50% chance of at least one collision takes about 2.7 × 10^18 UUIDs. Generating a billion per second, that would take roughly 86 years of nonstop output. The calculation assumes a sound random source: a cryptographically secure generator, not a weak or reused seed. In practice, collisions from bugs (copying a UUID, reseeding a generator identically) are far more likely than collisions from chance.
For v7, two IDs can only collide if they are created in the same millisecond with the same random bits, so the relevant random space is the 74 bits within each millisecond. That is still negligible at normal volumes, but it is a reason not to assume v7 has the same 122 bits of protection as v4.
Sortability and index locality
Most relational databases store a primary key in a B-tree index. Sequential keys always insert at the right-hand edge of the tree, so new rows land in pages that are already in memory and pages fill up completely. Random v4 keys insert at arbitrary positions: any page may need to be loaded, and inserts can split pages that are already full. That is the usual reason teams see slower writes and larger indexes with v4 primary keys on big tables. Measure on your own database and workload, because the size of the effect varies.
A v7 UUID gets most of the locality back. Because the first 48 bits are a millisecond timestamp, IDs created later sort after IDs created earlier. A v7 UUID created at 2026-11-02 00:00:00 UTC begins 01a1998e-4400-7 (the timestamp is 1793577600000 ms, which is 01a1998e4400 in hex). IDs generated within the same millisecond are ordered by their random bits unless the generator adds a counter, which RFC 9562 allows but does not require. If strict ordering within one process matters, check whether your library guarantees monotonic output.
The trade-off: a v7 UUID exposes when it was created. If the creation time of a record is sensitive, use v4 or keep the UUID as a public ID separate from an internal key.
When auto-increment is fine
Auto-increment integers are a good default when:
- One database (or one primary) hands out the IDs, so no coordination problem exists.
- Keys are joined and indexed heavily; 8 bytes beat 16 bytes in every index and foreign key.
- IDs are never exposed to users, or access is checked on every request anyway.
- You want the simplest thing that works and a human-readable ID in logs.
Their weaknesses are real but narrow. Sequential IDs in URLs let anyone guess neighboring records and estimate your volume, so authorization must never rely on an ID being hard to guess. Merging data from several databases, offline clients that create records, or sharded systems all push you toward UUIDs. A common compromise is an internal integer key plus a UUID for public use.
A quick decision guide
- IDs created on clients or by several services: UUID.
- UUID as a primary key on a large table: v7 over v4.
- Public identifier that must not leak ordering or time: v4, or a v7 kept apart from the key.
- Single database, internal IDs, join-heavy: auto-increment.
Try it
Generate and inspect identifiers in the UUID generator, which also decodes the version and, for v7, the embedded timestamp. The probabilities above were computed with Python’s math module using the snippet shown; the layout details come from RFC 9562.
Updated 2026-11-02