Primary, Foreign, Composite, & Surrogate Keys

Keys uniquely identify records and enforce referential relationships across tables:

• Primary Key (PK): A column (or set of columns) that uniquely identifies each row in a table. It cannot contain NULL values and is automatically backed by a unique B-Tree index.
• Natural Key (Domain Key): A key composed of attributes that naturally exist in the real-world business domain (e.g., Social Security Number, National ID, International Standard Book Number / ISBN).
• Surrogate Key (Synthetic Key): An artificial, unique identifier generated by the database system (e.g., auto-incrementing integer, UUID, or Snowflake ID) with no intrinsic business meaning.
• Composite Key (Compound Key): A primary key consisting of two or more columns combined (e.g., (order_id, product_id) in an order_items table).
• Foreign Key (FK): A column in a child table that points to the Primary Key of a parent table, enforcing referential integrity.

Choosing the correct Primary Key type is one of the most critical decisions in system design, as it permanently dictates database write throughput and index memory efficiency:

1. Auto-Incrementing BIGINT (64-bit Integer)

• Pros: Ultra-compact (8 bytes). Sequential values ensure new rows are always appended to the right-most leaf page of the clustered B-Tree index, achieving 100% B-Tree page fill factor and maximum write throughput.
• Cons: Single-node bottleneck (cannot easily generate sequential IDs across multi-region sharded databases without coordination). Exposes business metrics (e.g., an attacker creating an account and seeing user_id: 5000 knows your startup has 5,000 users).

2. Random UUIDv4 (128-bit Random)

• Pros: Generatable client-side or across thousands of distributed servers without any central coordination. Obfuscates business numbers.
• Cons: 16 bytes (2x larger than BIGINT). Catastrophic B-Tree Fragmentation: Because UUIDv4 values are completely random, inserting new rows requires writing to random B-Tree pages scattered across disk. This forces the database to read cold pages into RAM, execute frequent B-Tree Page Splits, and evict hot cache lines, reducing write throughput by up to 90%!

3. Time-Ordered UUIDv7 / Twitter Snowflake ID (The Industry Standard)

• Combines a 48-bit UNIX millisecond timestamp prefix with a 74-bit random/node suffix.
• Best of Both Worlds: Globally unique in distributed systems, impossible to guess, and monotonically increasing. New inserts always append sequentially to the B-Tree index, delivering identical write speeds to auto-incrementing integers!

-- PostgreSQL 17+ native UUIDv7 generation
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Modern UUIDv7
    customer_id BIGINT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexing composite keys: Column order matters!
-- This index satisfies queries filtering on (tenant_id) AND (tenant_id, status)
CREATE INDEX idx_orders_tenant_status ON orders(customer_id, status);