Database Design Patterns

Database Design Patterns

Relational database schema design expert. Covers normalization decisions, index selection, migration safety, and connection pooling — the structural foundations that determine whether a database performs well at scale or becomes a maintenance burden.

When to Use

Use for:

• Designing new schemas or refactoring existing ones
• Deciding whether to normalize or denormalize for a specific query pattern
• Choosing index types (B-tree, GIN, GiST, hash, partial, covering)
• Planning migrations that must not break running production systems
• Implementing soft deletes, polymorphic associations, or composite keys
• Configuring PgBouncer or Prisma connection pools

NOT for:

• Running EXPLAIN ANALYZE or reading query plans → use postgresql-optimization
• Document model design (MongoDB, DynamoDB) → use a NoSQL skill
• Database provisioning, replicas, or infrastructure → use a cloud/infra skill
• ORM-specific code generation → use the relevant ORM skill

---

Normalize vs. Denormalize Decision Tree

flowchart TD
    A[New data or query performance problem?] --> B{New data design?}
    B -->|Yes| C[Start normalized: 3NF]
    B -->|No — query too slow| D{Measured with EXPLAIN?}
    D -->|No| E[Measure first. Never guess.]
    D -->|Yes — proven join bottleneck| F{Read-heavy or write-heavy?}
    F -->|Read-heavy, joins are the bottleneck| G[Controlled denormalization:\nmaterialized view or cached column]
    F -->|Write-heavy or balanced| H[Keep normalized.\nOptimize query or add index first.]
    C --> I{Any repeated groups in a row?}
    I -->|Yes| J[1NF: Move to child table]
    I -->|No| K{Non-key cols depend on part of PK?}
    K -->|Yes| L[2NF: Extract to separate table]
    K -->|No| M{Transitive dependencies?}
    M -->|Yes| N[3NF: Extract lookup table]
    M -->|No| O[Schema is 3NF — ship it]

The rule: Start at 3NF. Denormalize only after measuring, and only the specific join that is provably too slow. Never denormalize speculatively.

---

Index Selection Decision Tree

flowchart TD
    A[Which index type?] --> B{Data type and query pattern}
    B -->|Equality or range on scalar| C[B-tree — default choice]
    B -->|Full-text search, arrays, JSONB| D[GIN — inverted index]
    B -->|Geometric / PostGIS types| E[GiST — generalized search]
    B -->|Exact equality only, very high cardinality| F[Hash — rare, limited utility]
    C --> G{Subset of rows frequently queried?}
    G -->|Yes, e.g. status = 'active'| H[Partial index:\nWHERE status = 'active']
    G -->|No| I{Query selects only indexed columns?}
    I -->|Yes| J[Covering index:\nINCLUDE additional columns]
    I -->|No| K[Standard B-tree index]

Always index:

• Every foreign key column (prevents full table scans on joins)
• Columns that appear in WHERE, ORDER BY, or JOIN ON clauses in frequent queries
• Composite indexes: put the most selective column first

Consult references/indexing-strategies.md when choosing between partial vs. covering indexes or tuning multi-column index column order.

---

Migration Safety Decision Tree

flowchart TD
    A[Schema change needed] --> B{Breaking change?}
    B -->|No: add nullable column, add index| C[Single migration, safe to run]
    B -->|Yes: rename column, change type, drop column| D[Expand-Contract pattern]
    D --> E[Phase 1 — Expand:\nAdd new column/table, keep old]
    E --> F[Deploy app: write to both old and new]
    F --> G[Backfill existing rows to new column]
    G --> H[Phase 2 — Contract:\nRemove old column once all reads use new]
    H --> I[Deploy app: read only from new]
    I --> J[Drop old column in final migration]
    C --> K{Large table?}
    K -->|Yes| L[CREATE INDEX CONCURRENTLY\nALTER TABLE with minimal lock]
    K -->|No| M[Standard migration]

Consult references/migration-patterns.md for expand-contract templates, lock timeout settings, and rollback strategies.

---

Normalization Reference

1NF — No Repeating Groups

Each column holds one value. No comma-separated lists in a column.

-- Bad: tags stored as CSV
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  tags TEXT  -- "sql,indexing,performance"
);

-- Good: normalized to child table
CREATE TABLE article_tags (
  article_id INT REFERENCES articles(id),
  tag TEXT NOT NULL,
  PRIMARY KEY (article_id, tag)
);

2NF — No Partial Dependencies (composite PKs only)

Every non-key column depends on the whole primary key, not just part of it.

-- Bad: product_name depends only on product_id, not on (order_id, product_id)
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  product_name TEXT,  -- should be in products table
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

3NF — No Transitive Dependencies

Non-key columns depend only on the primary key, not on each other.

-- Bad: zip_code determines city/state (transitive)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  zip_code TEXT,
  city TEXT,    -- derivable from zip_code
  state TEXT    -- derivable from zip_code
);

-- Good: extract lookup table
CREATE TABLE zip_codes (
  zip TEXT PRIMARY KEY,
  city TEXT,
  state TEXT
);

---

Key Design Decisions

Surrogate vs. Composite Keys

Use surrogate keys (serial/UUID) when:

• The natural key is multi-column and would be repeated in child tables as FK
• The natural key can change (email addresses, usernames)
• The table will be referenced by many other tables

Use composite primary keys when:

• The table is a pure join/association table with no additional attributes
• The combination is truly stable and globally unique

-- Pure join table: composite PK is correct
CREATE TABLE user_roles (
  user_id INT REFERENCES users(id),
  role_id INT REFERENCES roles(id),
  PRIMARY KEY (user_id, role_id)
);

-- Association with attributes: add surrogate key
CREATE TABLE user_project_memberships (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  project_id INT REFERENCES projects(id),
  joined_at TIMESTAMPTZ DEFAULT NOW(),
  role TEXT
);

Soft Deletes

-- Pattern: deleted_at nullable timestamp
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ;

-- Partial index makes "active" queries fast
CREATE INDEX idx_orders_active ON orders (user_id, created_at)
WHERE deleted_at IS NULL;

-- View hides soft-deleted rows for application code
CREATE VIEW active_orders AS
  SELECT * FROM orders WHERE deleted_at IS NULL;

Warning: Soft deletes complicate unique constraints. A unique email column allows only one deleted user with that email. Use partial unique indexes:

CREATE UNIQUE INDEX idx_users_email_active ON users (email)
WHERE deleted_at IS NULL;

Polymorphic Associations

Two approaches — avoid the naive pattern:

-- Bad: nullable FK columns for each possible parent type
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INT REFERENCES posts(id),      -- nullable
  article_id INT REFERENCES articles(id), -- nullable
  video_id INT REFERENCES videos(id),     -- nullable
  body TEXT
);

-- Good: separate association tables (referential integrity preserved)
CREATE TABLE post_comments (
  comment_id INT REFERENCES comments(id),
  post_id INT REFERENCES posts(id),
  PRIMARY KEY (comment_id, post_id)
);

-- Or: single-table inheritance with a type column + CHECK constraint
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  parent_type TEXT NOT NULL CHECK (parent_type IN ('post', 'article', 'video')),
  parent_id INT NOT NULL,
  body TEXT
);
CREATE INDEX idx_comments_parent ON comments (parent_type, parent_id);

Connection Pooling

PgBouncer configuration for typical web applications:

[pgbouncer]
pool_mode = transaction        ; Best for short-lived web requests
max_client_conn = 1000         ; Total client connections pooler accepts
default_pool_size = 20         ; DB connections per database/user pair
server_idle_timeout = 600      ; Close idle server connections after 10 min

Prisma with PgBouncer — set pgbouncer=true in the connection URL:

DATABASE_URL="postgresql://user:pass@host:6432/db?pgbouncer=true&connection_limit=1"

Note: PgBouncer transaction mode does not support prepared statements, SET, or LISTEN/NOTIFY. Use session mode if your ORM requires prepared statements and pool size is manageable.

---

Anti-Patterns

Anti-Pattern: Premature Denormalization

Novice: "Joins are slow, so I'll copy data into the main table to avoid them."

Expert: Joins are fast when indexes exist. Copying data creates update anomalies — the same fact stored in two places that can diverge. The correct sequence is: normalize first, measure query time under real load, identify the specific join bottleneck with EXPLAIN ANALYZE, then consider a materialized view or a single cached denormalized column as a last resort.

Detection: Look for columns like user_name on an orders table alongside a user_id FK to a users table. If users.name can change, orders.user_name will drift.

LLM mistake: Training data contains many tutorials that denormalize early as a "performance optimization." These predate widespread index-aware ORMs and assume manual query writing.

---

Anti-Pattern: Missing Indexes on Foreign Keys

Novice: "The database will figure out how to join — I just need the FK constraint."

Expert: A foreign key constraint enforces referential integrity but creates no index. A JOIN orders ON orders.user_id = users.id with no index on orders.user_id causes a full sequential scan of the orders table for every user. On a table with millions of rows this is catastrophic.

Detection:

-- Find FK columns with no index (PostgreSQL)
SELECT
  tc.table_name,
  kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
LEFT JOIN pg_indexes pi
  ON pi.tablename = tc.table_name
  AND pi.indexdef LIKE '%' || kcu.column_name || '%'
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND pi.indexname IS NULL;

Fix: Add an index on every FK column, always with CONCURRENTLY on a live table.

---

Anti-Pattern: SELECT * in Production Queries

Novice: "SELECT * is fine — the database only fetches what I need."

Expert: SELECT * fetches all columns including large TEXT, JSONB, and BYTEA columns you don't use. It prevents index-only scans (the query must hit the heap even if an index covers the query). It breaks when columns are added or reordered in ORMs that rely on positional column binding. Always name columns explicitly.

Detection: Search application code for SELECT * in any query that runs in a hot path. In ORMs, check if .findAll() or equivalent selects all columns by default and add explicit field selection.

---

References

• references/indexing-strategies.md — Consult when choosing between B-tree, GIN, GiST, hash, partial, and covering indexes; includes index-only scan prerequisites and multi-column index ordering rules.
• references/migration-patterns.md — Consult when planning zero-downtime migrations; covers expand-contract pattern, lock timeout settings, backfill chunking, and rollback strategies.

Indexing Strategies Reference

Index Type Selection

B-tree (Default)

The correct choice for 95% of indexes. Supports equality (=), range (<, >, BETWEEN), pattern prefix (LIKE 'foo%'), and IS NULL queries. Supports ORDER BY without a sort step.

-- Standard B-tree
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

-- Composite B-tree: leftmost columns can satisfy partial queries
-- This index serves: WHERE user_id = ? AND status = ?
-- Also serves:       WHERE user_id = ? (leftmost prefix only)
-- Does NOT serve:    WHERE status = ? (skips leftmost column)
CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders (user_id, status);

-- With sort direction for ORDER BY optimization
CREATE INDEX CONCURRENTLY idx_orders_created_desc ON orders (created_at DESC);

Column ordering in composite indexes: Most selective column first is a heuristic, but the actual rule is to match your query's WHERE clause. If you always filter by user_id and sometimes also by status, put user_id first.

Hash

Only supports equality (=). Never use in PostgreSQL for new indexes. B-tree is equally fast for equality lookups and additionally supports range queries. Hash indexes were not WAL-logged before PostgreSQL 10 (2017) and are still not useful in practice.

GIN (Generalized Inverted Index)

For multi-valued data where you need to check containment or overlap:

• Full-text search (tsvector)
• JSONB containment (@>, ?, ?|, ?&)
• Array containment (@>, &&)
• Range type overlap

-- Full-text search
CREATE INDEX CONCURRENTLY idx_articles_search ON articles
USING GIN (to_tsvector('english', title || ' ' || body));

-- JSONB containment queries
CREATE INDEX CONCURRENTLY idx_products_metadata ON products
USING GIN (metadata jsonb_path_ops);  -- jsonb_path_ops is smaller, faster for @> queries

-- Array column
CREATE INDEX CONCURRENTLY idx_posts_tags ON posts
USING GIN (tags);

GIN vs. GiST for full-text: GIN is faster to query; GiST is faster to build and smaller. For text search on a table that doesn't update frequently, prefer GIN.

GiST (Generalized Search Tree)

For geometric types and specialized data structures:

• PostGIS geometry columns
• Range types with overlap queries
• ltree hierarchical data
• tsvector (slower to query than GIN, faster to update)

-- PostGIS spatial index
CREATE INDEX CONCURRENTLY idx_locations_geom ON locations
USING GiST (geom);

-- Range type: find rows whose date range overlaps a given range
CREATE INDEX CONCURRENTLY idx_bookings_dates ON bookings
USING GiST (date_range);

---

Partial Indexes

Index only the rows you actually query. This is one of the highest-leverage optimizations available.

-- Only index pending orders (status = 'pending' is a hot query path)
-- Table might have 10M rows; only 50K are pending — index is tiny and fast
CREATE INDEX CONCURRENTLY idx_orders_pending_created ON orders (created_at DESC)
WHERE status = 'pending';

-- Index non-deleted rows for soft-delete pattern
CREATE INDEX CONCURRENTLY idx_users_email_active ON users (email)
WHERE deleted_at IS NULL;

-- Partial unique constraint: allow multiple deleted rows with same email
CREATE UNIQUE INDEX idx_users_email_unique_active ON users (email)
WHERE deleted_at IS NULL;

When to use: When a large fraction of rows are systematically excluded from most queries (status filters, soft deletes, boolean flags). A partial index with WHERE is always smaller and faster than a full index on the same column.

---

Covering Indexes (INCLUDE Clause)

Include additional columns in the index leaf nodes so the query engine never needs to visit the heap. Enables index-only scans.

-- Query: SELECT name, email FROM users WHERE user_id = ?
-- Without covering index: look up user_id in index, then fetch heap page
-- With covering index: look up user_id, get name and email directly from index
CREATE INDEX CONCURRENTLY idx_users_id_covering ON users (user_id)
INCLUDE (name, email);

-- For range queries that select a few columns
CREATE INDEX CONCURRENTLY idx_orders_user_created_covering ON orders (user_id, created_at DESC)
INCLUDE (status, total_amount);

Prerequisites for index-only scan: 1. The index covers all columns referenced in SELECT and WHERE 2. The visibility map shows the page is all-visible (vacuumed recently) 3. No concurrent writes happening on those rows

Check if an index-only scan is happening with EXPLAIN ANALYZE — look for "Index Only Scan" in the plan. If you see "Heap Fetches: N" where N is high, the visibility map is stale; run VACUUM to fix.

---

Multi-Column Index Column Ordering

The rule that overrides all heuristics: put equality columns before range columns.

The index is structured as a sorted tree. A range condition on column 1 breaks the sequential access pattern for column 2.

-- Query: WHERE user_id = 5 AND created_at > '2024-01-01'
-- Good: equality column (user_id) first, range column (created_at) second
CREATE INDEX ON orders (user_id, created_at);

-- Bad: range column first — can only use user_id after scanning the whole date range
CREATE INDEX ON orders (created_at, user_id);  -- wrong order for this query

-- Query: WHERE status = 'active' AND score > 80 AND user_id = 5
-- user_id is equality, status is equality, score is range
-- Correct: put both equality columns before the range
CREATE INDEX ON records (user_id, status, score);

---

Expression Indexes

Index a computed value instead of a raw column.

-- Case-insensitive email lookup
CREATE INDEX CONCURRENTLY idx_users_email_lower ON users (LOWER(email));
-- Query must use LOWER() to hit the index:
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- JSONB property extraction
CREATE INDEX CONCURRENTLY idx_events_type ON events ((payload->>'event_type'));

-- Extracted date for date-only queries on a timestamp column
CREATE INDEX CONCURRENTLY idx_orders_date ON orders (DATE(created_at));

---

Index Maintenance

-- Create without locking writes (preferred for production)
CREATE INDEX CONCURRENTLY idx_name ON table_name (column);

-- Check index size and usage
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS times_used,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

-- Find unused indexes (no scans since last stats reset)
SELECT indexname, tablename, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild bloated index without locking
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

Over-indexing warning: Every index adds write overhead (INSERT, UPDATE, DELETE must update each index). Drop indexes that have idx_scan = 0 after a representative sample period (at least a week of normal traffic). Keep all indexes needed for constraint enforcement (UNIQUE, PK).

Migration Patterns Reference

Expand-Contract Pattern

The fundamental pattern for zero-downtime schema changes. Every breaking change goes through three phases: expand (add new), migrate (backfill), contract (remove old).

Phase 1 — Expand

Add the new structure. The old structure still exists and the application still writes to it. Both old and new must be valid at this point.

-- Example: rename column users.full_name to users.display_name

-- Phase 1: add new column, make it nullable (no default required)
ALTER TABLE users ADD COLUMN display_name TEXT;

Phase 2 — Dual-Write (Application Deploy)

Deploy application code that writes to both old and new column. Reads still use the old column.

-- Backfill existing rows: chunk it to avoid long locks
DO $$
DECLARE
  batch_size INT := 10000;
  last_id BIGINT := 0;
  max_id BIGINT;
BEGIN
  SELECT MAX(id) INTO max_id FROM users;
  WHILE last_id < max_id LOOP
    UPDATE users
    SET display_name = full_name
    WHERE id > last_id AND id <= last_id + batch_size
      AND display_name IS NULL;
    last_id := last_id + batch_size;
    PERFORM pg_sleep(0.01);  -- brief pause to reduce lock contention
  END LOOP;
END $$;

Phase 2b — Cutover (Application Deploy)

Deploy application code that reads from the new column. Still writes to both.

Phase 3 — Contract (Application Deploy + Migration)

Deploy application code that reads and writes only to the new column. Then drop the old.

-- Phase 3: safe to drop old column now
ALTER TABLE users DROP COLUMN full_name;

---

Backward-Compatible Changes (Safe, Single Migration)

These changes do not require expand-contract:

---

Adding NOT NULL Without Downtime

Adding NOT NULL to an existing column requires all existing rows to have a non-null value. The naive ALTER TABLE ... SET NOT NULL locks the table while it validates every row.

-- Step 1: Add column as nullable, backfill, then add constraint as NOT VALID
ALTER TABLE orders ADD COLUMN confirmed_at TIMESTAMPTZ;

-- Step 2: backfill (chunked)
UPDATE orders SET confirmed_at = created_at WHERE confirmed_at IS NULL;

-- Step 3: Add NOT NULL constraint as NOT VALID (skips existing row validation)
ALTER TABLE orders ADD CONSTRAINT orders_confirmed_at_not_null
  CHECK (confirmed_at IS NOT NULL) NOT VALID;

-- Step 4: Validate constraint in background (ShareUpdateExclusiveLock, non-blocking)
ALTER TABLE orders VALIDATE CONSTRAINT orders_confirmed_at_not_null;

-- Step 5: (Optional) Convert to true NOT NULL — only safe after validation
ALTER TABLE orders ALTER COLUMN confirmed_at SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_confirmed_at_not_null;

---

Lock Timeout Settings

Always set a lock timeout before DDL statements in production migrations. Without it, a migration waiting for a lock can block all reads and writes on the table indefinitely.

-- Set per-session lock timeout before each DDL statement
SET lock_timeout = '5s';
SET statement_timeout = '30s';

-- The migration will fail fast if it can't acquire a lock in 5 seconds
-- Retry later when the blocking query completes
ALTER TABLE orders ADD COLUMN status TEXT;

Retry strategy: If a migration fails due to lock timeout, wait for the blocking query (visible in pg_stat_activity) to complete and retry. Do not increase the timeout to avoid blocking the table longer.

---

Index Migration Safety

-- Always use CONCURRENTLY for new indexes on live tables
-- CONCURRENTLY takes 2-3x longer but does not block reads or writes
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

-- If CONCURRENTLY fails partway through, the index is left as INVALID
-- Find invalid indexes:
SELECT indexname, tablename FROM pg_indexes
JOIN pg_class ON pg_class.relname = indexname
WHERE pg_class.relkind = 'i'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index WHERE pg_index.indexrelid = pg_class.oid
      AND pg_index.indisvalid
  );

-- Drop the invalid index and recreate
DROP INDEX CONCURRENTLY idx_orders_status;
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

---

Migration File Organization

migrations/
├── 0001_create_users.sql
├── 0002_create_orders.sql
├── 0003_add_status_to_orders.sql      # expand phase
├── 0004_backfill_order_status.sql     # data migration
└── 0005_drop_old_status_column.sql    # contract phase (deploy after app)

Convention: Separate structural changes (DDL) from data migrations (DML). Run structural migrations at deploy time. Run data migrations as background jobs or chunked scripts when the table is large.

---

Rollback Strategies

Not all migrations are trivially reversible. Plan rollback before executing forward.

Rule: Never drop a column in the same deploy window as the application code change that stops using it. Wait one deploy cycle to confirm the old column is unused, then drop it in a follow-up migration. This gives you a clean rollback path if the deploy goes wrong.

---

Testing Migrations

# Test migration against a clone of production data
pg_dump $PROD_URL | psql $TEST_URL
psql $TEST_URL -f migrations/0042_new_change.sql

# Time the migration — if it takes > 1s, it may cause issues in production
time psql $TEST_URL -f migrations/0042_new_change.sql

# Check for lock contention by running under load
# Use pgbench to simulate concurrent queries while migration runs
pgbench -T 30 -c 50 $TEST_URL &
psql $TEST_URL -f migrations/0042_new_change.sql