Picking a primary key type feels like a small decision until a table hits tens of millions of rows and the index stops fitting in memory. The choice of integer ID vs UUID for a primary key affects storage size, index performance, and how easily IDs can be generated outside the database — and the right answer depends on which of those actually matters for the table in question.
Storage: 4–8 Bytes vs 16
An integer primary key costs 4 bytes; a bigint costs 8. A UUID is 128 bits — 16 bytes stored natively, or up to 36 bytes if a dialect without a native UUID type stores it as a formatted string. That difference sounds small per row, but it compounds: every foreign key referencing that primary key carries the same cost, and on a heavily-joined table with several child tables, a wider key means a wider index on every one of them, not just the parent.
Index Locality: Why Sequential Beats Random
This is the part that actually shows up in production. A B-tree index on a sequential integer always inserts at the rightmost edge of the tree — new rows append cleanly, pages fill in order, and the hot part of the index stays small and cache-friendly. A random UUID (the common v4 format) inserts at a random point in the key space every time, because the bytes are random by design. That scatters writes across the whole index instead of one hot page, causing more page splits, more fragmentation, and a working set that no longer fits comfortably in the buffer cache once the table gets large. The same query pattern can get measurably slower purely because of key randomness, with no other change to the schema.
UUID Versions: v4 vs v7
This is exactly the problem UUIDv7 was designed to fix. Instead of being fully random, a UUIDv7 value embeds a millisecond timestamp in its leading bits, so values generated close together in time sort close together in value — the index gets the same append-mostly, cache-friendly insert pattern as a sequential integer, while keeping the property that made UUIDs attractive in the first place: any client can generate one without asking the database for the next value. Older UUIDv4 doesn't have this property; if index locality matters and you're choosing UUIDs, v7 (or a similar time-ordered scheme like ULID) is usually the better default over v4 today.
The Real Advantage of UUIDs: No Coordination Required
An auto-increment integer needs the database to hand out the next value, which means a round trip before an ID exists. A UUID can be generated anywhere — in the application, on a mobile client before it's ever online, across multiple database shards — with a collision probability low enough to ignore in practice. That property matters far more for distributed systems, offline-first apps, and merge-heavy replication setups than raw index performance does, and no amount of integer efficiency replaces it when it's genuinely needed.
A Common Middle Ground: Integer Primary Key, UUID as Public Alias
A pattern worth knowing even if you don't reach for it immediately: keep a compact auto-increment integer (or identity column — see our guide to sequences and identity columns across dialects) as the actual primary key for internal joins and indexes, and add a separate UNIQUE UUID column as the identifier exposed in URLs and API responses. Internal joins stay fast and compact; nothing external ever sees a guessable sequential ID that leaks row counts or invites enumeration; and if the system later needs distributed ID generation, the alias column is already in place without touching the primary key or any foreign key that references it.
Which to Choose
For a single-database, single-writer table with no external exposure of the ID, a sequential integer or identity column is usually simpler and faster — there's no problem it's solving for you to pay index-locality costs for. Reach for a UUID (v7, not v4, if locality matters) when IDs need to be generated outside the database, merged across systems, or kept unguessable in a public-facing URL. The alias pattern gets you both without picking one universally.
Comparing primary key and index changes across a schema, safely, is what FoxSchema's diff is built for — see how it fits into indexing strategy work more broadly. Self-host it in Docker or check the docs to try it against your own schema.
