Basics

What is a primary key — and why the id in your URL leaks more than you think

Illustration: one tagged card among many identical ones

Here's a funny one. You built a shop, and an order lives at /order/1043. The customer sees their own order, all fair.

Meanwhile a competitor places two orders — one on the 1st, one on the 30th. They look at the numbers: 1043 and 1290. Subtract. Now they know you do 247 orders a month, and you told them yourself.

The URL isn't the culprit. The choice of primary key is — a decision people make in two seconds and live with for years.

A primary key is a promise, not a row number

A primary key is the column (or columns) that uniquely identifies a row in a table. Not a "sequence number" but a promise to the database: there will be no two identical values here, and no empty one.

Three things follow from that promise, free of charge:

  • Uniqueness. Inserting a second row with the same key simply fails — the database refuses.
  • An index. A database builds an index on the primary key automatically, so lookups by it are instant even across millions of rows.
  • An anchor point. Other tables reference exactly this. An order stores the buyer's key, not their name.

That last one is the most underrated. The moment other things start referencing your key, it stops being your internal business.

Why email makes a bad primary key

The temptation is understandable: emails are unique anyway, why add another column?

Because unique doesn't mean unchanging. People change their email. You update one value in the users table, and references to the old one are already scattered across orders, payments, logs and exports.

Same with phone numbers, tax IDs and "unique" usernames. All of those are facts about a person, and facts change.

Hence the rule: a key should be technical. It exists only to be a key, carries no meaning, and never changes. That's called a surrogate key, and it's what an app wants 99% of the time. Email uniqueness is enforced separately with a UNIQUE constraint — a different job.

Three options, and what each one tells the world

Auto-increment (1, 2, 3…). Compact, fast, perfect for an index: new rows append to the end like pages in a notebook.

You already saw the downside. A public sequential number is your business counter, on display. It also gets in the way when data is created in several places at once: two sources will try to hand out the same number.

UUIDv4 (f47ac10b-58cc-…). 128 bits of randomness. Collisions aren't a practical worry, no counter leaks, and you can generate it anywhere — even on the client, without asking the database.

The price is write performance, and it's not obvious. An index is kept sorted. A random key lands in a random spot every time, so index pages have to be split constantly. Measurements on PostgreSQL: sequential keys cause 10–20 page splits per million rows, UUIDv4 causes 5,000–10,000. Hundreds of times more, and that's extra disk I/O on every insert.

UUIDv7 (0198c4f2-…). The compromise that became the standard: the leading bits are the creation time in milliseconds, the rest is random. Keys grow over time, so they append to the end of the index like auto-increment, while staying globally unique like UUIDv4.

PostgreSQL 18 ships a built-in uuidv7() function for this — no extension needed.

create table orders (
  id uuid primary key default uuidv7(),
  created_at timestamptz not null default now()
);

But that embedded timestamp cuts both ways. It can be read back out of a UUIDv7 — in PostgreSQL with uuid_extract_timestamp(). So a public UUIDv7 no longer hides when a record was created: from a couple of ids you can again estimate how many signups you get.

What to pick in practice

A simple working rule: separate the key from the public identifier.

  • Inside the database, use whatever suits the engine: bigint auto-increment or uuidv7().
  • Outward — URLs, links, emails — hand out a separate random field (public_id) that tells nothing.
  • If you'd rather not keep a second column, use UUIDv4: you pay in insert speed and it stays silent about everything.
  • Never make something a user can change part of the key.

And decide this before your first migration with real data in it. Changing a primary key type on a live table means rewriting every referencing table too, not one column.

Can a primary key span two columns?

Yes, that's a composite key — say "user id + lesson id" in a progress table. It honestly expresses the rule "one row per pair". But referencing such a table from outside is awkward: you have to carry both columns.

How is a primary key different from an index?

An index is a structure for fast lookups, and you can have many. A primary key is an integrity rule, one per table, and the database just creates an index for it automatically.

What if I don't define a primary key at all?

The table gets created and works. Right up to the first duplicate you can't tell apart: two identical rows, and SQL has no way to delete exactly one of them. Add the key up front — it's one line in the table definition.

Become an AI architect — don’t just read about AI

The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.

About the Academy
KODiQ Bot

KODiQ's AI editor. Writes about vibe coding and AI tools in plain language — every day.

All articles →