Primary Keys Are Forever: Choosing Between Ints, UUIDs, and Regret

A founder once showed me a competitor's pricing page and, with a grin, their own order confirmation URL: /orders/48213. Two weeks later he created a test order — /orders/49907. Simple subtraction told him the competitor's order volume better than any analyst report. This trick is older than software; statisticians used sequential serial numbers on captured equipment to estimate German tank production in World War II. Your auto-incrementing primary key is cheerfully running the same leak, twenty-four hours a day, for anyone who can see a URL.

I open with this not to scare you off integers — I'll defend them shortly — but to make a point about primary keys generally: they're the single longest-lived decision in your schema. You will rewrite the application, swap frameworks, maybe change databases. The keys survive all of it, because by then they're in URLs, foreign keys, logs, partner integrations, and customers' bookmarks. Data outlives code, and keys outlive nearly everything. So it's worth choosing them on purpose.

The case for boring integers

A bigint sequence is the default for good reasons:

• Small and fast. 8 bytes. Every foreign key, every index entry, every join carries the key — and your primary key is embedded in every secondary index too, so key size multiplies across the whole table.
• Insert-friendly. New keys are always the largest value, so B-tree inserts land on the same rightmost pages. Cache-warm, tightly packed, no drama.
• Human-friendly. "Check order 48213" survives being said aloud over a pager call. Nobody reads a UUID to a teammate at 2am without at least one transcription error and some light despair.

Use bigint, not int, always. The teams that picked 4-byte ints "because we'll never have 2 billion rows" have all since attended the same very stressful migration party. An 8-byte key costs you nothing today and spares you an emergency later.

The weaknesses are just as concrete: sequential keys leak volume and ordering (the tank problem), invite enumeration (/users/1, /users/2, ...), and can't be generated without asking the database — which complicates offline clients, batch imports that pre-wire relationships, and multi-region writes.

The case for UUIDs — and their hidden tax

UUIDs solve the generation problem beautifully: any client, any region, any moment, no coordination, no meaningful collision risk. Nothing leaks, nothing enumerates.

But random UUIDs (v4) have a cost that rarely makes it into the pitch: your primary key index stops having locality. Inserts land on random pages across the whole B-tree instead of the rightmost edge. On a big, busy table that means page splits, a cold cache, index bloat, and more write-ahead-log traffic. Add that every key is 16 bytes — and rides along into every foreign key and every index — and "just use UUIDs" turns out to have a monthly bill.

UUIDv7 is the modern compromise and, for new systems, my usual recommendation when UUIDs are warranted: the leading bits are a timestamp, so values are roughly ordered and inserts behave like a sequence, while the trailing randomness keeps them unguessable. You keep decentralized generation, you lose most of the index pain. (Caveat honestly noted: v7 keys reveal creation time — usually fine, occasionally not.)

# Postgres 18+ has uuidv7() built in; earlier versions can use a
# pgcrypto/v4 default or an extension providing v7.
create_table :orders, id: :uuid, default: -> { "uuidv7()" } do |t|
  t.references :customer, type: :uuid, null: false, foreign_key: true
  t.timestamps null: false
end

One review note while we're here: AI assistants tend to mirror whatever convention your prompt implies, and they'll happily generate id: :uuid on one table and default bigint on the next without blinking. Mixed key types across a schema is a subtle mess — every join and every future foreign key has to remember which table uses what. Pick one strategy, write it down, and check generated migrations against it like you'd check the license on a dependency.

The move that dissolves the dilemma

Here's the reframe that has served me for years: the argument is only hard because we're asking one column to do two jobs — internal join key and external identifier. Split the jobs and both get easier:

create_table :orders do |t|                      # bigint PK: fast joins, small indexes
  t.uuid :public_id, null: false,
         default: -> { "uuidv7()" }              # what URLs and APIs see
  t.index :public_id, unique: true
end

Inside the database: compact sequential keys, happy indexes, cheap foreign keys. Outside: an opaque identifier that leaks nothing and enumerates nowhere. Prettier still, some teams use short human-friendly codes (ord_9f3kx2) as the public face. The invariant to enforce in review is simple and absolute: the sequential id never crosses the API boundary. Not in URLs, not in JSON payloads, not in webhook bodies, not in "temporary" admin endpoints. Once an identifier ships to a customer, it's forever — see title.

And to close a common misconception: unguessable IDs are not authorization. /orders/0193e5a2-... still needs an ownership check; obscurity just reduces drive-by enumeration. UUIDs are a privacy improvement, not an access-control system.

How to actually decide

For a new system, my flowchart is short:

• Default: bigint primary keys everywhere, plus a public_id (UUIDv7 or short code) on anything exposed via URL or API.
• Go UUIDv7 primary keys when clients generate records offline, when you're merging data across regions or shards, or when the two-column pattern genuinely feels like ceremony for your team's size.
• Avoid UUIDv4 as a primary key on high-write tables unless you've measured and accepted the index behavior.
• Never let anyone "fix" an existing key strategy by migrating primary keys on a live system without treating it as the major project it is. That's not a migration, that's a saga.

Rules I'd tattoo somewhere discreet

• Primary keys are the longest-lived decision in your schema; choose them like you'll live with them for a decade, because you will.
• bigint, never int. The upgrade you skip today is the outage someone inherits.
• Sequential ids leak volume and invite enumeration the moment they touch a URL. Separate internal keys from public identifiers.
• If you want UUIDs, want UUIDv7. Random v4 keys quietly tax every insert and every index.
• One key strategy per schema. Review generated migrations for accidental mixing — consistency is a feature.

Choose boringly, expose carefully, and your keys will do the one thing keys are supposed to do: never be interesting again.