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.
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.
All lessons in this course
- Normalization vs. Denormalization Trade-offs
- Choosing Appropriate Data Types
- Partitioning Large Tables
- Designing Primary Keys and Surrogate Keys