การออกแบบคีย์หลักและคีย์ตัวแทน

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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