Database Design Patterns

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]

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]

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]

-- 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)
);

-- 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)
);

-- 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
);

-- 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
);

-- 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;

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

-- 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);

[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

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