Database Architect

1NF (First Normal Form):
  - Each column contains atomic (indivisible) values
  - Each row is unique (has primary key)
  - No repeating groups

  VIOLATION: tags = "red,blue,green"  (comma-separated)
  FIX:       Create a separate tags table with one row per tag

2NF (Second Normal Form):
  - Meets 1NF
  - No partial dependencies (all non-key columns depend on the FULL primary key)
  - Only relevant with composite primary keys

  VIOLATION: (order_id, product_id) -> product_name
             product_name depends only on product_id, not the full key
  FIX:       Move product_name to the products table

3NF (Third Normal Form):
  - Meets 2NF
  - No transitive dependencies (non-key column depends on another non-key column)

  VIOLATION: user_id -> department_id -> department_name
             department_name depends on department_id, not directly on user_id
  FIX:       Create a departments table, reference via department_id

BCNF (Boyce-Codd Normal Form):
  - Meets 3NF
  - Every determinant is a candidate key
  - Handles edge cases where 3NF allows anomalies

4NF (Fourth Normal Form):
  - Meets BCNF
  - No multi-valued dependencies

5NF (Fifth Normal Form):
  - Meets 4NF
  - No join dependencies

PRACTICAL TARGET: 3NF for OLTP, denormalized for OLAP/reporting.

NORMALIZE when:
  - Data integrity is critical (financial, medical, legal)
  - Write-heavy workload (fewer update anomalies)
  - Storage space is a concern
  - Multiple applications share the database
  - Schema stability is important

DENORMALIZE when:
  - Read performance is the primary concern
  - Complex joins are causing query bottlenecks
  - Reporting/analytics queries need aggregated data
  - Caching at the database level is beneficial
  - Event-sourced or document-oriented data

STRATEGY                      USE CASE
-------------------------------------------------------------
Duplicated columns            User.email stored in Orders for fast reads
Precomputed aggregates        Product.review_count, Product.avg_rating
Materialized views            Complex reporting queries
JSON columns                  Flexible metadata, user preferences
Denormalized tables           Read-optimized copies of normalized data
Event snapshots               Store full state at point in time

-- Create materialized view for dashboard stats
CREATE MATERIALIZED VIEW dashboard_stats AS
SELECT
  DATE(created_at) AS date,
  COUNT(*) AS total_orders,
  SUM(total_amount) AS revenue,
  COUNT(DISTINCT user_id) AS unique_customers,
  AVG(total_amount) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_stats;

-- Create index on materialized view
CREATE UNIQUE INDEX idx_dashboard_stats_date ON dashboard_stats(date);

B-Tree (DEFAULT):
  - Equality and range queries
  - ORDER BY, GROUP BY
  - Most common index type
  CREATE INDEX idx_users_email ON users(email);

Hash:
  - Equality-only queries (= but not < > BETWEEN)
  - Faster than B-Tree for exact match
  CREATE INDEX idx_users_email ON users USING hash(email);

GIN (Generalized Inverted Index):
  - Full-text search
  - JSONB containment (@>, ?)
  - Array overlap (&&)
  CREATE INDEX idx_products_tags ON products USING gin(tags);

GiST (Generalized Search Tree):
  - Geometric/spatial data
  - Range types
  - Full-text search (alternative to GIN)
  CREATE INDEX idx_locations_coords ON locations USING gist(coordinates);

BRIN (Block Range Index):
  - Very large tables with naturally ordered data
  - Minimal storage overhead
  - Timestamp columns on append-only tables
  CREATE INDEX idx_logs_created ON logs USING brin(created_at);

Partial Index:
  - Index a subset of rows
  - Reduces index size and maintenance cost
  CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';

Covering Index (INCLUDE):
  - Include non-indexed columns to enable index-only scans
  CREATE INDEX idx_users_email ON users(email) INCLUDE (name, avatar_url);

RULE: The "leftmost prefix" rule
  Index on (a, b, c) can be used for:
    WHERE a = ?
    WHERE a = ? AND b = ?
    WHERE a = ? AND b = ? AND c = ?
    WHERE a = ? ORDER BY b

  CANNOT be used for:
    WHERE b = ?          (skips first column)
    WHERE b = ? AND c = ? (skips first column)

COLUMN ORDER: Equality columns first, then range/sort columns
  Query: WHERE status = 'active' AND created_at > '2025-01-01' ORDER BY name
  Index: (status, created_at, name)

CARDINALITY: Higher cardinality columns first (more unique values)
  Exception: unless low-cardinality column is always in WHERE clause

-- Find missing indexes (slow queries)
SELECT
  schemaname, tablename, seq_scan, idx_scan,
  seq_scan - idx_scan AS too_many_seqs,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
  AND pg_relation_size(schemaname || '.' || tablename) > 100000
ORDER BY seq_scan - idx_scan DESC;

-- Find unused indexes (candidates for removal)
SELECT
  indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Explain analyze for query optimization
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- AVOID: SELECT *
SELECT * FROM users WHERE id = 123;
-- PREFER: Select only needed columns
SELECT id, name, email FROM users WHERE id = 123;

-- AVOID: N+1 queries
-- Application code: for each order, query items separately
-- PREFER: JOIN or subquery
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.user_id = 123;

-- AVOID: Functions on indexed columns (prevents index use)
WHERE YEAR(created_at) = 2025
-- PREFER: Range comparison
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'

-- AVOID: OR on different columns (may prevent index use)
WHERE email = 'x' OR phone = 'y'
-- PREFER: UNION
SELECT * FROM users WHERE email = 'x'
UNION
SELECT * FROM users WHERE phone = 'y';

-- Use EXISTS instead of IN for large subqueries
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)
-- Instead of
WHERE id IN (SELECT user_id FROM orders)

-- Batch inserts
INSERT INTO items (name, price) VALUES
  ('A', 10), ('B', 20), ('C', 30);
-- Instead of three separate INSERTs

PARTITION TYPES:

Range Partitioning (most common):
  - Time-series data (logs, events, metrics)
  - Date ranges (monthly, yearly)

List Partitioning:
  - Categorical data (region, status, type)
  - Tenant-based partitioning

Hash Partitioning:
  - Even distribution when no natural range
  - Parallel query execution

-- Range partitioning by month
CREATE TABLE events (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  event_type TEXT NOT NULL,
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Automate partition creation with pg_partman or cron job
-- Index on each partition is created automatically from parent

-- List partitioning by region
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  region TEXT NOT NULL,
  total NUMERIC NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us-east', 'us-west');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('eu-west', 'eu-central');

WHY: Database connections are expensive to create (~50-100ms).
     Each connection uses ~5-10MB of server memory.
     PostgreSQL forks a process per connection.

POOL SIZING FORMULA:
  pool_size = (core_count * 2) + effective_spindle_count
  For SSD: pool_size = core_count * 2 + 1
  Example: 4 cores -> pool_size = 9

TOOLS:
  PgBouncer:     External connection pooler for PostgreSQL
  pgpool-II:     Connection pooler + load balancer
  Application:   Built-in pools (HikariCP, node-pg pool, SQLAlchemy pool)

CONFIGURATION:
  min_pool_size:  2-5    (keep warm connections ready)
  max_pool_size:  10-20  (per application instance)
  idle_timeout:   300s   (close idle connections)
  max_lifetime:   1800s  (recycle connections)
  connection_timeout: 5s (fail fast if pool exhausted)

1. ALWAYS use forward-only migrations in production
   - Never edit a migration that has been applied
   - Create a new migration to fix issues

2. SEPARATE schema changes from data changes
   - Schema migration: ALTER TABLE, CREATE INDEX
   - Data migration: UPDATE, INSERT (separate file)

3. MAKE migrations reversible when possible
   - Include UP and DOWN operations
   - Some changes are irreversible (DROP COLUMN with data)

4. TEST migrations against production-size data
   - A migration that runs in 1ms on dev may lock tables for hours in prod

5. USE zero-downtime migration patterns
   - Add column with DEFAULT (no table rewrite in PG 11+)
   - Create index CONCURRENTLY
   - Never DROP COLUMN in the same deploy as code removal

Phase 1: ADD (backward compatible)
  - Add new column (nullable or with default)
  - Add new table
  - Create index CONCURRENTLY
  - Deploy code that writes to BOTH old and new structures

Phase 2: MIGRATE
  - Backfill data to new structure
  - Deploy code that reads from new structure
  - Keep writing to both for safety

Phase 3: CLEANUP (after validation)
  - Remove old column/table (separate migration)
  - Deploy code that only uses new structure

EXAMPLE: Rename column "name" to "full_name"
  Phase 1: ALTER TABLE users ADD COLUMN full_name TEXT;
  Phase 2: UPDATE users SET full_name = name WHERE full_name IS NULL;
  Phase 3: ALTER TABLE users DROP COLUMN name;
  (Each phase is a separate deploy)

STRATEGY            ISOLATION     COMPLEXITY    COST
-------------------------------------------------------
Shared DB +         Low           Low           Low
  tenant_id column

Schema per tenant   Medium        Medium        Medium
  (PostgreSQL schemas)

Database per tenant High          High          High

SHARED DATABASE WITH tenant_id:
  - Add tenant_id to every table
  - Add tenant_id to every query (middleware/ORM level)
  - Row-level security (RLS) for defense in depth

  CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

SCHEMA PER TENANT (PostgreSQL):
  - Each tenant gets a schema (SET search_path = 'tenant_abc')
  - Shared tables in public schema
  - Connection middleware sets search_path
  - Easier data export/deletion for individual tenants

ARCHITECTURE:
  Primary (writes) -> Replica 1 (reads)
                   -> Replica 2 (reads)
                   -> Replica 3 (analytics)

ROUTING STRATEGY:
  Writes:  Always go to primary
  Reads:   Route to replicas
  Reads after write: Route to primary (avoid replication lag)

REPLICATION LAG HANDLING:
  - Monitor lag (SELECT pg_last_wal_replay_lsn())
  - Set maximum acceptable lag (e.g., 5 seconds)
  - Fall back to primary if lag exceeds threshold
  - Use session affinity for post-write reads

APPLICATION PATTERN:
  // Middleware/decorator approach
  @UseDatabase('replica')
  async getUsers() { ... }

  @UseDatabase('primary')
  async createUser() { ... }

  // Post-write read with primary fallback
  async getUserAfterUpdate(id) {
    await this.primary.update(...);
    return this.primary.findById(id);  // Read from primary after write
  }