UUID vs auto-increment
A BIGINT is 8 bytes and sorts perfectly; a UUID is 16 and can be created anywhere without asking the database. Every other difference follows from those two facts — and v7 changed which one matters.
By the Withuse team · Updated
The trade-off, measured
| Auto-increment | UUID v4 | UUID v7 | |
|---|---|---|---|
| Storage | 8 bytes | 16 bytes binary · 36 as text | |
| Sorts by creation | ✅ | ❌ | ✅ |
| Guess the next value | ❌ trivially (id+1) | ✅ ~1 in 5×1036 | ✅ ~1 in 274 |
| Create before insert | ❌ | ✅ anywhere, offline | |
| Leaks information | Row count, growth rate | Nothing | Creation time |
We verified the sorting row rather than assuming it: five v7 values generated a minute apart come back in creation order, while five v4 values do not. That single property is what made auto-increment attractive for so long, and it is the one v7 recovers.
Where auto-increment genuinely wins
Eight bytes against sixteen sounds trivial until you count where the key is stored. Every secondary index carries a copy of the primary key, so a table with four indexes pays the extra eight bytes five times per row. On a hundred-million-row table that is a few gigabytes of index — which matters mainly because it decides how much of the index fits in memory.
The other advantage is human. /orders/1042 is something you can read out over a phone call, spot in a log, and type from memory. No UUID is.
Where it fails
Enumeration. If /invoices/8821 exists, so does 8822, and it belongs to somebody else. Counting upward is the entire attack, and it appears in breach reports year after year. A UUID does not fix authorisation — you still have to check that the requester may see the record — but it removes the free map of your data.
Coordination. An auto-increment value exists only after the database assigns it. That means a round trip before you can reference the new row, awkward batch inserts when children need the parent's id, and no way to create records on a client that is offline. Merging two databases becomes a renumbering project. A UUID sidesteps all of it because any node can mint one independently and it stays valid.
Disclosure. Sequential keys leak volume. A competitor who signs up twice a month can read your growth rate straight off the identifiers. Whether that matters depends on your business, but it is information you did not intend to publish.
The pragmatic answer: use both
For many schemas the right shape is a BIGINT primary key for internal joins and indexes, plus a UUID column with a unique index as the public identifier that appears in URLs and API responses. Internal storage stays narrow, external references stay unguessable.
The costs are a second index to maintain, one extra lookup when translating between the two, and the discipline of never letting the internal integer appear in a response. If you go this route, generate the public identifier as v7 so it still clusters when you query by it.
Choosing
Use auto-increment when the table is internal, single-node and write-heavy, and the identifiers never appear in a URL. Use UUID v7 when identifiers are exposed publicly or created outside the database — it is the sensible default now that the sorting objection is gone. Use v4 only when the embedded creation timestamp would itself be sensitive; the comparison is laid out in v4 vs v7.
Frequently asked questions
Is an auto-increment primary key faster than a UUID?
For writes, usually yes, though the gap is smaller than the folklore suggests. A BIGINT occupies 8 bytes against a UUID's 16 stored as binary, and every secondary index carries a copy of the primary key, so the difference is paid once per index entry rather than once per row. The larger effect is insert locality: a sequential integer always lands at the right-hand edge of the B-tree, while a random v4 scatters writes across the whole index and causes page splits. UUID v7 removes most of that second cost by putting a timestamp in front, leaving only the eight extra bytes. Whether that matters is an empirical question for your table, not a rule — it shows up as index size and therefore as how much of the index stays in memory.
Why are sequential IDs a security problem?
Because the next one is guessable with certainty. If your order page is /orders/1042, then /orders/1043 exists and belongs to someone else — an attacker enumerates your entire table by counting. This is insecure direct object reference, and it is consistently near the top of the OWASP list. A UUID makes guessing infeasible: a v4 has 122 random bits, so a single blind attempt succeeds with probability around 1 in 5×10^36. Note the important caveat: an unguessable identifier is not authorisation. You still have to check that the requester may see the record. Rate limiting does not substitute either: it slows enumeration down without making the next value any less certain.
Can I generate an auto-increment ID before inserting the row?
No, and that is often the deciding factor rather than performance. The database assigns the value, so the application only learns it after the insert returns. That forces a round trip before you can reference the new row, makes batch inserts awkward when children need the parent's id, and rules out creating records offline or on a client that syncs later. A UUID is generated wherever you like — in the browser, in a queue consumer, on a device with no connection — and stays valid when it eventually reaches the database. Merging two datasets also stops being a renumbering exercise. A client-generated identifier also makes retries idempotent, since the second attempt carries the same key rather than creating a duplicate row.
Does UUID v7 make auto-increment obsolete?
No, it narrows the gap rather than closing it. We confirmed that v7 values sort in creation order while v4 values do not, which restores the insert locality that made sequential integers attractive. What remains is the eight extra bytes per key, multiplied across every secondary index, and the fact that a v7 embeds a creation timestamp that anyone holding the identifier can read. If a table is internal, single-node and high-volume, BIGINT is still the leaner choice. If identifiers are exposed publicly or created outside the database, v7 is the better default. The one case for preferring v4 over v7 is when the embedded creation time is itself sensitive.
Can I use both an integer key and a UUID?
Yes, and for many schemas it is the pragmatic answer. Keep a BIGINT as the internal primary key so joins and indexes stay narrow, and add a UUID column with a unique index as the public identifier used in URLs and APIs. You get compact internal storage together with unguessable external references. The cost is a second index to maintain and one extra lookup when translating between them, plus the discipline of never letting the internal integer leak into a response. For most applications that trade is worth it. Generate the public column as v7 so it still clusters when you query or paginate by it, and add the unique index explicitly rather than relying on the column type.
References
Need keys to try? Generate v7 in bulk with the UUID generator. Related: UUID in PostgreSQL · UUID in MySQL