Designing Primary Keys and Surrogate Keys
Learn how the choice between natural keys, sequential surrogate keys, and UUIDs affects index size, insert throughput, and overall query performance.
Designing Primary Keys and Surrogate Keys is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Natural vs Surrogate Keys
A natural key is a real-world attribute (e.g. email). A surrogate key is a meaningless generated value (e.g. an integer id). Surrogate keys stay stable even when business data changes.
Why Key Choice Affects Performance
The primary key is referenced by every foreign key and many indexes. A wide key bloats all of those structures, increasing disk usage and cache pressure. Narrow keys keep indexes small and fast.
Sequential Integer Keys
The classic choice is a monotonically increasing integer. New rows append to the end of the B-tree, minimizing page splits and keeping inserts fast.
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
total NUMERIC
);IDENTITY vs serial
Prefer the SQL-standard GENERATED ALWAYS AS IDENTITY over the older serial pseudo-type. It is cleaner and avoids ownership quirks with the underlying sequence.
The UUID Temptation
UUIDs are great for distributed systems because clients can generate them. But random UUIDs (v4) scatter inserts all over the index, causing page splits and poor cache locality.
CREATE TABLE events (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
payload JSONB
);Time-Ordered UUIDs
If you need UUIDs, prefer a time-ordered variant (UUIDv7) so values increase roughly with time. This restores the append-friendly behavior of sequential keys while keeping global uniqueness.
Key Width Matters
A BIGINT is 8 bytes; a UUID is 16 bytes. Every secondary index stores the primary key, so wider keys multiply storage across all of them. Measure the impact.
SELECT pg_size_pretty(pg_relation_size('orders_pkey'));Composite Primary Keys
Sometimes the natural key spans two columns, such as (order_id, line_no) in a detail table. Keep composite keys narrow and put the most selective column first.
CREATE TABLE order_lines (
order_id BIGINT,
line_no INT,
PRIMARY KEY (order_id, line_no)
);Foreign Keys Inherit the Cost
Every child row stores a copy of the parent key. A 16-byte UUID parent key makes a million-row child table 8 MB larger than an 8-byte integer would. Multiply by every referencing table.
Choosing in Practice
Guidelines:
- Default to BIGINT IDENTITY for single-database apps
- Use time-ordered UUIDs when clients must generate ids or you shard
- Avoid random v4 UUIDs as primary keys on hot insert paths
- Keep composite natural keys short
Indexing the Foreign Key Side
Whatever key you pick, always index the child's foreign key column. Without it, deleting or updating a parent forces a full scan of the child table to check references.
CREATE INDEX idx_order_lines_order
ON order_lines (order_id);Quick Check
Test your key-design knowledge.
Recap
You learned key design for performance:
- Surrogate keys stay stable; natural keys can change
- Narrow keys shrink every index and foreign key
- Sequential BIGINT IDENTITY inserts are cheap
- Random v4 UUIDs scatter inserts; prefer time-ordered UUIDs
- Keep composite keys short and selective-first
Frequently asked questions
Is the “Designing Primary Keys and Surrogate Keys” lesson free?
Yes — the full text of “Designing Primary Keys and Surrogate Keys” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.
What will I learn in “Designing Primary Keys and Surrogate Keys”?
Learn how the choice between natural keys, sequential surrogate keys, and UUIDs affects index size, insert throughput, and overall query performance. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start PostgreSQL Performance & Query Optimization?
No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Designing Primary Keys and Surrogate Keys” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?
Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Normalization vs. Denormalization Trade-offs
- Choosing Appropriate Data Types
- Partitioning Large Tables
- Designing Primary Keys and Surrogate Keys