ModelingIntermediate
Natural key or surrogate key?
“Would you use email as a primary key? UUID or bigint?”
What this tests
- Key stability
- Index performance implications of key choice
Answers by level
Read the beginner answer first and notice what is missing.
Use a surrogate primary key and put a UNIQUE constraint on the natural key. The reason is change: emails get corrected, and a primary key that changes must be updated in every referencing table. A surrogate never changes, so foreign keys never do.
bigint identity is compact, sorts by insertion, and keeps B-tree inserts sequential. UUIDs are generatable without coordination and hide row counts, at the cost of 16 bytes and random insert positions that fragment the index — UUIDv7 (time-ordered) recovers most of that locality.
Green flags · Red flags
Strong green flag · Frames the decision around what may change.
Green flags
- Surrogate PK + UNIQUE natural key
- Knows UUID insert-locality issue and UUIDv7
- bigint not int
Red flags
- Email as PK
- Random UUID PK on a hot table with no awareness of fragmentation
- int for a large key
Follow-up questions
F1
Why can a random UUID primary key hurt write performance?
Scenario
A table uses email as its primary key and marketing wants to let users change their email. What breaks?