Primary Keys & IDs¶

Every row needs a unique, stable identifier — its primary key. It's how Read, Update, and Delete find a specific record.

What Makes a Good Primary Key?¶

1. 1.Unique — no two rows share it.
2. 2.Stable — it never changes once assigned.
3. 3.Meaningless — it shouldn't encode business data (don't use an email as a PK).

Option A: Auto-Increment Integers¶

1id BIGSERIAL PRIMARY KEY     -- 1, 2, 3, 4, ...

Pros: small, fast, human-readable, naturally ordered. Cons: guessable (/tasks/1, /tasks/2…), leaks how many rows exist, awkward across distributed systems.

Option B: UUIDs¶

1id UUID PRIMARY KEY DEFAULT gen_random_uuid()  -- 9f1c...

Pros: unguessable, generatable on the client, collision-free across databases. Cons: larger (16 bytes), not human-friendly, random UUIDv4 can fragment indexes.

Which Should You Use?¶

A common hybrid: keep a BIGSERIAL primary key internally, plus a public UUID or slug column for URLs.

Natural vs. Surrogate Keys¶

• A natural key is real data (an ISBN, a country code).
• A surrogate key is invented just to be the ID (an auto-int, a UUID).

Prefer surrogate keys. Real-world "unique" values change more often than you'd think — emails get reassigned, usernames get edited. A surrogate key never has to.