ReviseAlgo Logo

Keys & Constraints

Surrogate, Natural, and Composite Keys

Identifying best column configurations for primary index keys.

Last Updated: June 15, 2026 • 15 min read

1. Introduction

Choosing the right primary key strategy is one of the most impactful schema design decisions. Surrogate keys are auto-generated IDs with no business meaning (e.g., SERIAL, UUID). Natural keys are real-world identifiers (e.g., email, SSN, ISBN). Composite keys combine multiple columns to form a unique identifier. Each approach has trade-offs in performance, simplicity, and maintainability.

2. Why It Matters

  • Join performance: Integer surrogate keys are 4 bytes; UUID is 16 bytes; composite keys can be even larger. Smaller keys = smaller indexes = faster joins.
  • Immutability: Primary keys should never change. Natural keys (email, phone) can change; surrogate keys are stable.
  • Distributed systems: Auto-increment keys don't work across multiple databases. UUIDs or snowflake IDs enable distributed key generation.
  • ORM compatibility: Most ORMs assume single-column integer primary keys. Composite or natural keys require special configuration.

3. Real-World Analogy

Think of employee identification. A surrogate key is an auto-assigned employee number (E-1234) — meaningless outside the company, never changes. A natural key would be the employee's email — meaningful but can change when they marry or rebrand. A composite key would be (department, hire_date, sequence) — unique only in combination, and cumbersome to reference.

4. How It Works

5. Internal Architecture

6. Visual Explanation

7. Practical Example

8. Common Mistakes

Using natural keys as primary keys

Email, SSN, and phone numbers can change. When a PK changes, every FK referencing it must be updated (CASCADE) or the update is blocked. Use a surrogate PK and keep the natural key as a separate UNIQUE column.

Using random UUIDs on high-volume tables

Random UUIDs cause random inserts into the B-tree index, leading to page splits and fragmentation. For high-volume tables, use UUIDv7 (time-ordered) or ULID to maintain insert locality.

Interview Insight

"When would you use a composite primary key?" — Junction tables (many-to-many), time-series data (device_id + timestamp), and partitioned tables. For most entity tables, prefer a single surrogate key.

9. Quick Quiz

Q1: Why are surrogate keys preferred for distributed systems?

Answer: Auto-increment keys require a central sequence (single DB). UUIDs and snowflake IDs can be generated independently on any node without coordination, enabling horizontal scaling across multiple database instances.

10. Scenario-Based Challenge

Challenge: Multi-Region E-Commerce Key Strategy

Your e-commerce platform runs in 3 regions (US, EU, APAC) with separate databases. Design a key strategy for:

  1. Orders table: IDs must be globally unique across regions.
  2. Products table: SKU must be consistent across regions.
  3. Users table: email uniqueness must be enforced globally.
  4. Order items: decide between surrogate and composite keys, justifying your choice.

11. Debugging Exercise

This table is running out of IDs. Why?

Issue:

  1. SERIAL max value: SERIAL uses INT (4 bytes), max value is 2,147,483,647. At 2.1B rows, you're at the limit. Fix: migrate to BIGSERIAL (8 bytes) or use UUIDs. Prevention: always use BIGSERIAL for high-volume tables.

12. Interview Questions

Q1: What are the pros and cons of UUID vs integer primary keys?

A: Integers: smaller (4/8 bytes), faster joins, sequential inserts, but require central generation. UUIDs: globally unique (no coordination), secure (not guessable), but 4x larger indexes, random inserts cause fragmentation. Use integers for single-DB OLTP, UUIDs for distributed systems.

Q2: How do you generate unique IDs in a sharded database?

A: Options: (1) UUID v4 (random) or UUID v7 (time-ordered), (2) Snowflake IDs (timestamp + machine ID + sequence), (3) Ticket server (central ID generator), (4) Database sequences with shard-specific offsets.

13. Production Considerations

  • Default to BIGSERIAL: Use BIGSERIAL (8 bytes) instead of SERIAL (4 bytes) for all new tables. The extra 4 bytes per row prevents the 2.1B limit issue and costs negligible storage.
  • UUID v7 for distributed: If using UUIDs, prefer UUID v7 (time-ordered) over v4 (random) to maintain B-tree insert locality and reduce index fragmentation.
  • Sequence tuning: For high-insert tables, increase sequence cache: ALTER SEQUENCE orders_id_seq CACHE 100. This pre-allocates IDs in memory, reducing sequence contention.
  • Hybrid approach: Use surrogate PK for joins and internal references, plus a UNIQUE natural key (email, SKU) for business logic and external APIs.
  • Composite key indexing: Column order matters in composite keys. Put the most selective column first for the best index performance.