Surrogate, Natural, and Composite Keys

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

4. How It Works

-- SURROGATE KEY (recommended default)
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,  -- 8-byte auto-increment
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL
);
-- UUID surrogate key (for distributed systems)
CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    type TEXT NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);
-- NATURAL KEY
CREATE TABLE countries (
    iso_code CHAR(2) PRIMARY KEY,  -- 'US', 'UK', 'JP'
    name TEXT NOT NULL
);
-- COMPOSITE KEY
CREATE TABLE enrollment (
    student_id INT REFERENCES students(id),
    course_id INT REFERENCES courses(id),
    semester TEXT,
    grade TEXT,
    PRIMARY KEY (student_id, course_id, semester)
);
-- Hybrid: surrogate PK + natural unique key
CREATE TABLE products (
    id SERIAL PRIMARY KEY,          -- surrogate PK for joins
    sku TEXT UNIQUE NOT NULL,       -- natural key for business logic
    name TEXT NOT NULL
);

5. Internal Architecture

Key Type Performance Comparison:
┌──────────────┬────────┬────────────┬────────────┬──────────────┐
│ Key Type     │ Size   │ Index Size │ Join Speed │ Distribution │
├──────────────┼────────┼────────────┼────────────┼──────────────┤
│ INT (4B)     │ 4 bytes│ Smallest   │ Fastest    │ Single DB    │
│ BIGINT (8B)  │ 8 bytes│ Small      │ Very Fast  │ Single DB    │
│ UUID (16B)   │ 16B    │ 2-3x INT   │ Fast       │ Multi-DB     │
│ TEXT         │ Var    │ Largest    │ Slowest    │ N/A          │
│ Composite    │ Sum    │ Largest    │ Slowest    │ N/A          │
└──────────────┴────────┴────────────┴────────────┴──────────────┘
SERIAL vs BIGSERIAL:
  SERIAL:  1 to 2,147,483,647 (2.1B max)
  BIGSERIAL: 1 to 9.2 quintillion (practically unlimited)
  → Use BIGSERIAL for high-volume tables (events, logs)
UUID considerations:
  - Random UUIDs cause index fragmentation (random inserts)
  - UUIDv7 (time-ordered) maintains B-tree locality
  - 16 bytes vs 4 bytes = 4x larger indexes

6. Visual Explanation

7. Practical Example

-- Best practice: Surrogate PK + natural unique constraints
CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,       -- natural business key
    phone TEXT UNIQUE,                -- secondary natural key
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);
-- All joins use the small surrogate key:
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id  -- fast INT join
WHERE c.email = 'alice@example.com';       -- natural key for lookups
-- For distributed systems: ULID (time-ordered, sortable)
CREATE TABLE audit_log (
    id TEXT PRIMARY KEY,  -- ULID: 01HXYZ... (26 chars, time-ordered)
    action TEXT NOT NULL,
    entity_id BIGINT NOT NULL,
    performed_by BIGINT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW()
);

8. Common Mistakes

9. Quick Quiz

10. Scenario-Based Challenge

11. Debugging Exercise

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);
-- Table has 2.1 billion rows and INSERTs are failing

12. Interview Questions

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.