Every table needs a primary key, and the choice between an auto-incrementing integer and a UUID primary key looks trivial — a decision you make once without thinking and never revisit. But it quietly shapes index performance, whether you accidentally expose your business metrics, and how painful it is to merge data across systems later. It is worth five minutes of thought, because changing it after a table has millions of rows and foreign keys pointing at it is genuinely hard.
The case for auto-increment integers
The traditional choice is a sequential integer — 1, 2, 3 — and it has real, concrete advantages. It is small: a bigint is 8 bytes, and it appears not just in the table but in every index and every foreign key referencing it, so the size difference multiplies across your whole schema. It is fast to insert, because new rows append to the end of the index in order, keeping the index compact and cache-friendly. And it is human-friendly: “order 4521” is easy to read, compare, and talk about.
The sequential ordering is itself useful. Rows are naturally ordered by creation, so “the most recent records” is just the highest ids, and range queries over id ranges are efficient. For a single database with no plans to distribute, an auto-increment integer is a perfectly good default and often the right one.
The two problems with sequential integers
Sequential integers have two weaknesses, and whether they matter depends on your situation. The first is that they leak information. If your invoice URLs are /invoice/4521, anyone can infer you have issued roughly 4,521 invoices, and by creating one today and one tomorrow, estimate your daily volume. Sequential ids in public-facing URLs expose your counts and growth rate to competitors and the curious — a real business-intelligence leak that costs you nothing to avoid but is invisible until someone points it out. They also let people enumerate your resources by simply incrementing the number, which turns any missing authorization check into a walk through your entire dataset.
The second problem is that sequential ids require coordination. To hand out the next number, something has to be the single source of truth for “what number comes next,” which is easy with one database and hard the moment you have several, or want to generate ids on the client, or need to merge data from multiple systems where the id ranges collide. A central sequence is a coordination point, and coordination points resist distribution.
The case for UUIDs
A UUID is a 128-bit value large enough to be generated randomly anywhere with effectively no chance of collision. That single property solves both of the integer’s problems at once. UUIDs leak nothing — you cannot tell how many records exist or guess a neighbor’s id from your own, so they are safe in public URLs and resist enumeration. And they need no coordination — any service, any client, even an offline device can mint a valid id independently and know it will not clash with one generated elsewhere, which is exactly what distributed systems and offline-first mobile apps need.
This is why UUIDs are the natural fit whenever ids are created outside a single central database: microservices that each generate their own, clients that create records before syncing, or systems that merge data from many sources. The independence is the whole point.
The UUID performance problem, and its fix
Historically, UUIDs came with a serious database cost that gave them a bad reputation, and it is worth understanding because the situation has changed. A standard random UUID (version 4) is, by design, random — so new rows insert at random positions in the primary-key index rather than appending at the end. That scatters writes across the whole index, causes page splits and fragmentation, and hurts insert performance and cache locality badly on large tables. For a high-write table, a random UUID primary key was genuinely slow.
The modern fix is a time-ordered UUID — UUID version 7 — which puts a timestamp in the high bits so the values are sequential-ish, sorted roughly by creation time, while keeping the randomness in the lower bits. This gives you the best of both: the coordination-free, non-leaking, globally-unique properties of a UUID, with the append-friendly, index-compact insert behavior of a sequential id. If you want UUIDs today, a time-ordered variant is almost always the right kind to use, because it removes the historic performance objection that made people avoid them. Reach for a random v4 only when you specifically need the id to reveal nothing about creation time.
How to actually choose
The decision comes down to a few honest questions. Will ids ever be generated outside a single database — by clients, multiple services, or offline devices? If yes, lean UUID (v7). Will ids appear in public-facing URLs where leaking counts or allowing enumeration is a concern? If yes, lean UUID. Is this a single database where neither of those applies, and you value the smallest possible keys and simplest debugging? Then a plain auto-increment integer is a fine, boring, correct choice.
A common and pragmatic middle path is worth knowing: use an auto-increment integer as the internal primary key for its size and speed, and add a separate UUID column as the external, public-facing identifier used in URLs and APIs. You get compact internal keys and fast joins, while never exposing the sequential number to the outside world. It costs one extra column and indexes, and it neatly separates “the id my database uses” from “the id I show the world” — which are genuinely two different concerns that a single key is trying to serve at once.