Natural primary key or surrogate key

The migration looked trivial: the customers table used the tax ID as the primary key, because "tax ID is unique and never changes." Then a customer came in whose tax ID had been typed wrong in the 2019 signup, and fixing a single digit meant updating the primary key across seven tables with foreign keys pointing at it. What was supposed to be an UPDATE of one field became a whole-weekend database surgery. The tax ID didn't change — the typo did, and that was enough.

This post is about the decision between a natural primary key and a surrogate key, and why it almost always has one answer. First, if you're still structuring the schema from scratch, it's worth reading how to model tables, because this choice is one of the first that decides whether the rest will hurt or not.

A natural key looks immutable until it isn't

A natural key means using an existing business value as the primary key: tax ID, email, product code, ISBN. The seduction is obvious — that value is already unique, so why create an extra column? The problem is that "immutable and unique" is a promise the real world loves to break.

• Tax ID typed wrong: the value exists, it's syntactically valid, but it's wrong. Fixing it becomes a cascade across every foreign key.
• Email changes: the person switches provider, gets married and changes their last name, or simply wants a different address. If email is the PK, every change is a migration.
• A product code that "never changes": until the company is acquired and standardizes its naming with the parent's.

The pattern is always the same: the business attribute was stable enough until the day it wasn't, and on that day the cost of having anchored the row's identity to it shows up all at once. Record identity and business value are different things, and mixing the two is the root of the problem.

Surrogate key: IDENTITY or UUID

A surrogate key is a column with no business meaning, created only to identify the row. It never changes because it represents nothing that could change. There are two families:

-- IDENTITY (auto-increment), Postgres 10+
CREATE TABLE customer (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tax_id CHAR(11) NOT NULL,
  email TEXT NOT NULL
);

-- UUID
CREATE TABLE customer (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tax_id CHAR(11) NOT NULL,
  email TEXT NOT NULL
);

IDENTITY (Postgres' modern successor to SERIAL) generates sequential integers. They're compact, great for indexing and easy to read while debugging. The downside: they're predictable (you can guess /order/124 from /order/123) and require a round-trip to the database to learn the value before inserting related records.

UUID solves both of those — it's generable on the client before the insert and isn't enumerable — at the cost of 16 bytes instead of 8 and one important performance characteristic that deserves its own section.

UUID v4 versus v7 in write-heavy tables

Here lives a detail that wrecks the performance of a large table and almost nobody mentions. UUID v4 is fully random. When it's the primary key and the index is a B-tree (the default), each insert lands in a random position in the index, fragmenting pages and destroying locality. In a write-heavy table, that turns into constant page splits and cache misses.

UUID v7 fixes this by embedding a timestamp in the most significant bits, so values generated in sequence sit close together in the index — the best of both worlds: non-enumerable enough for a public URL, but time-ordered so it doesn't blow up the index.

-- Postgres 18 shipped native uuidv7(); before that, use an extension or generate in the app
CREATE TABLE event (
  id UUID PRIMARY KEY DEFAULT uuidv7(),
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

If you need to generate UUIDs to test a schema or seed data, a UUID generator produces v4 and v7 values instantly, without opening a SQL console. The rule of thumb for choosing: write-heavy table with a B-tree index, prefer v7; v4 only when pure randomness is a security requirement and write volume is low.

The natural key still matters — as a UNIQUE constraint

Swapping the primary key for a surrogate doesn't mean throwing the natural key in the trash. On the contrary: the tax ID still needs to be unique, the email still can't duplicate. The difference is that this guarantee becomes a UNIQUE constraint, not the row's identity.

CREATE TABLE customer (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tax_id CHAR(11) NOT NULL,
  email TEXT NOT NULL,
  CONSTRAINT uq_customer_tax_id UNIQUE (tax_id),
  CONSTRAINT uq_customer_email UNIQUE (email)
);

This gives you the best of both sides: the integrity of the business data is enforced by the database, but the record's identity is anchored to a column that never changes. Fixing a tax ID typed wrong becomes an UPDATE customer SET tax_id = ... WHERE id = ... that touches no foreign key. The problem from the start of this post simply doesn't exist.

My stance: surrogate PK, natural UNIQUE

After already paying the price of the weekend migration, my rule is direct and almost without exception: primary key always surrogate, natural key always as a UNIQUE constraint. The surrogate gives identity stability and cheap foreign keys; the UNIQUE gives business integrity. You give up nothing.

Between IDENTITY and UUID, I decide by context: closed system, internal IDs that don't leak into public URLs, moderate writes, IDENTITY for compactness and readability. Distributed system, IDs in public URLs, client-side generation, or multiple sources inserting, UUID v7. UUID v4 stays reserved for the rare cases where total unpredictability is a security requirement and writes are low. The mistake to avoid is the opposite of the choice: anchoring identity to a business value because "it'll never change." It will.

Frequently asked questions

When does it make sense to use a natural key as the primary key?

Almost never in transactional systems. The reasonable exception is small, stable lookup tables, like a country-code table (ISO), where the value is controlled by an external standard and rarely changes. Even there, surrogate + UNIQUE is usually safer.

IDENTITY or UUID, which to choose?

IDENTITY for internal IDs, moderate writes, and when readability and compactness matter. UUID for distributed systems, IDs exposed in public URLs, or client-side generation. If it's UUID in a write-heavy table, use v7 and not v4.

Why is UUID v4 bad as a primary key for a large table?

Because it's random and fragments the B-tree index on every insert, causing page splits and cache misses at high write volume. UUID v7 embeds a timestamp and keeps values close together in the index, eliminating that problem.

Can I have a natural key and a surrogate key at the same time?

Yes, and it's the recommendation. The surrogate is the primary key and anchors identity; the natural key becomes a UNIQUE constraint that guarantees business-data integrity. You get stability and integrity without choosing between the two.

What to take from here

The choice between a natural key and a surrogate key has an answer that holds for the overwhelming majority of cases: surrogate as the primary key, natural as a UNIQUE constraint. Business data that looks immutable ends up changing, and anchoring the row's identity to it is buying a future migration. Use IDENTITY for internal IDs and UUID v7 for write-heavy tables or public IDs, keep the UNIQUE over the tax ID and the email, and sleep easy knowing that fixing a wrong digit is just an UPDATE.