database-engineering-mastery

# Database Engineering Mastery > Complete database design, optimization, migration, and operations system. From schema design to production monitoring — covers PostgreSQL, MySQL, SQLite, and general SQL patterns. ## Phase 1 — Schema Design ### Design Brief Before writing any DDL, fill this out: ```yaml project: "" domain: "" primary_use_case: "OLTP | OLAP | mixed" expected_scale: rows_year_1: "" rows_year_3: "" concurrent_users: "" read_write_ratio: "80:20 | 50:50 | 20:80" compliance: [] # GDPR, HIPAA, PCI-DSS, SOX multi_tenancy: "none | schema-per-tenant | row-level | database-per-tenant" ``` ### Normalization Decision Framework | Form | Rule | When to Denormalize | |------|------|---------------------| | 1NF | No repeating groups, atomic values | Never skip | | 2NF | No partial dependencies on composite keys | Never skip | | 3NF | No transitive dependencies | Reporting tables, read-heavy aggregations | | BCNF | Every determinant is a candidate key | Rarely needed unless complex key relationships | **Denormalization triggers:** - Query joins > 4 tables consistently - Read latency > 100ms on indexed queries - Cache invalidation complexity exceeds denormalization maintenance - Reporting queries block OLTP workloads ### Naming Conventions ``` Tables: snake_case, plural (users, order_items, payment_methods) Columns: snake_case, singular (first_name, created_at, is_active) PKs: id (bigint/uuid) or {table_singular}_id FKs: {referenced_table_singular}_id Indexes: idx_{table}_{columns} Constraints: chk_{table}_{rule}, uq_{table}_{columns}, fk_{table}_{ref} Enums: Use VARCHAR + CHECK, not DB enums (easier to migrate) Booleans: is_, has_, can_ prefix (is_active, has_subscription) Timestamps: _at suffix (created_at, updated_at, deleted_at) ``` ### Column Type Decision Tree ``` Text < 255 chars, fixed set? → VARCHAR(N) + CHECK Text < 255 chars, variable? → VARCHAR(255) Text > 255 chars? → TEXT Whole numbers < 2B? → INTEGER Whole numbers > 2B? → BIGINT Money/financial? → NUMERIC(precision, scale) — NEVER float True/false? → BOOLEAN Date only? → DATE Date + time? → TIMESTAMPTZ (always with timezone) Unique identifier? → UUID (distributed) or BIGSERIAL (single DB) JSON/flexible schema? → JSONB (Postgres) or JSON (MySQL) Binary/file? → Store in object storage, reference by URL IP address? → INET (Postgres) or VARCHAR(45) Geospatial? → PostGIS geometry/geography types ``` ### Essential Table Template ```sql CREATE TABLE {table_name} ( id BIGSERIAL PRIMARY KEY, -- domain columns here -- created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), created_by BIGINT REFERENCES users(id), version INTEGER NOT NULL DEFAULT 1, -- optimistic locking -- soft delete (optional) deleted_at TIMESTAMPTZ, -- multi-tenant (optional) tenant_id BIGINT NOT NULL REFERENCES tenants(id) ); -- Updated_at trigger (PostgreSQL) CREATE OR REPLACE FUNCTION update_modified_column() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); NEW.version = OLD.version + 1; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_{table_name}_updated BEFORE UPDATE ON {table_name} FOR EACH ROW EXECUTE FUNCTION update_modified_column(); ``` ### Relationship Patterns **One-to-Many:** ```sql -- Parent CREATE TABLE departments (id BIGSERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL); -- Child CREATE TABLE employees ( id BIGSERIAL PRIMARY KEY, department_id BIGINT NOT NULL REFERENCES departments(id) ON DELETE RESTRICT, -- ON DELETE options: RESTRICT (safe default), CASCADE (children die), SET NULL ); CREATE INDEX idx_employees_department_id ON employees(department_id); ``` **Many-to-Many:** ```sql CREATE TABLE user_roles ( user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE, granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), granted_by BIGINT REFERENCES users(id), PRIMARY KEY (user_id, role_id) ); ``` **Self-Referencing (hierarchy):** ```sql CREATE TABLE categories ( id BIGSERIAL PRIMARY KEY, parent_id BIGINT REFERENCES categories(id) ON DELETE CASCADE, name VARCHAR(100) NOT NULL, depth INTEGER NOT NULL DEFAULT 0, path TEXT NOT NULL DEFAULT '' -- materialized path: '/1/5/12/' ); CREATE INDEX idx_categories_parent ON categories(parent_id); CREATE INDEX idx_categories_path ON categories(path text_pattern_ops); ``` **Polymorphic (avoid if possible, use if you must):** ```sql -- Preferred: separate FKs CREATE TABLE comments ( id BIGSERIAL PRIMARY KEY, post_id BIGINT REFERENCES posts(id), ticket_id BIGINT REFERENCES tickets(id), body TEXT NOT NULL, CONSTRAINT chk_one_parent CHECK ( (post_id IS NOT NULL)::int + (ticket_id IS NOT NULL)::int = 1 ) ); ``` --- ## Phase 2 — Indexing Strategy ### Index Type Selection | Index Type | Use When | Example | |-----------|----------|---------| | B-tree (default) | Equality, range, sorting, LIKE 'prefix%' | `CREATE INDEX idx_users_email ON users(email)` | | Hash | Equality only, no range | `CREATE INDEX idx_sessions_token ON sessions USING hash(token)` | | GIN | JSONB, full-text search, arrays, tsvector | `CREATE INDEX idx_products_tags ON products USING gin(tags)` | | GiST | Geospatial, range types, nearest-neighbor | `CREATE INDEX idx_locations_geom ON locations USING gist(geom)` | | BRIN | Very large tables with natural ordering (time-series) | `CREATE INDEX idx_events_created ON events USING brin(created_at)` | | Partial | Subset of rows | `CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending'` | | Covering | Include columns to avoid table lookup | `CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE (status, total)` | ### Indexing Rules 1. **Always index:** Foreign keys, columns in WHERE/JOIN/ORDER BY 2. **Never index:** Low-cardinality columns alone (boolean, status with 3 values) — combine in composite 3. **Composite order:** Most selective column first, then left-to-right matches query patterns 4. **Watch write overhead:** Each index slows INSERT/UPDATE. >8 indexes on a write-heavy table = review 5. **Unused index audit:** Run monthly — drop indexes with 0 scans ### Find Unused Indexes (PostgreSQL) ```sql SELECT schemaname, tablename, indexname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) as size FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexrelid NOT IN ( SELECT conindid FROM pg_constraint WHERE contype IN ('p', 'u') ) ORDER BY pg_relation_size(indexrelid) DESC; ``` ### Find Missing Indexes (PostgreSQL) ```sql SELECT relname, seq_scan, seq_tup_read, idx_scan, seq_tup_read / GREATEST(seq_scan, 1) as avg_tuples_per_scan FROM pg_stat_user_tables WHERE seq_scan > 100 AND seq_tup_read > 10000 ORDER BY seq_tup_read DESC; -- High seq_scan + high seq_tup_read = missing index candidate ``` --- ## Phase 3 — Query Optimization ### EXPLAIN Interpretation ```sql EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...; ``` **Red flags in query plans:** | Pattern | Problem | Fix | |---------|---------|-----| | Seq Scan on large table | Missing index | Add appropriate index | | Nested Loop with large outer | O(n×m) join | Add index on join column, consider Hash Join | | Sort with high cost | Missing index for ORDER BY | Add index matching sort order | | Hash Join spilling to disk | work_mem too low | Increase work_mem or reduce result set | | Bitmap Heap Scan with many recheck | Low selectivity index | More selective index or partial index | | SubPlan (correlated subquery) | Executes per row | Rewrite as JOIN or lateral | | Rows estimate wildly wrong | Stale statistics | ANALYZE table | ### Query Anti-Patterns & Fixes **1. SELECT * in production:** ```sql -- Bad: fetches all columns, breaks covering indexes SELECT * FROM orders WHERE user_id = 123; -- Good: explicit columns SELECT id, status, total, created_at FROM orders WHERE user_id = 123; ``` **2. N+1 queries:** ```sql -- Bad: 1 query for users + N queries for orders SELECT id FROM users WHERE active = true; -- returns 100 rows SELECT * FROM orders WHERE user_id = ?; -- called 100 times -- Good: single JOIN or IN SELECT u.id, o.id, o.total FROM users u JOIN orders o ON o.user_id = u.id WHERE u.active = true; ``` **3. Functions on indexed columns:** ```sql -- Bad: can't use index on created_at WHERE EXTRACT(YEAR FROM created_at) = 2025 -- Good: range scan uses index WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01' -- Bad: can't use index on email WHERE LOWER(email) = 'user@example.com' -- Good: expression index CREATE INDEX idx_users_email_lower ON users(LOWER(email)); ``` **4. OR conditions killing indexes:** ```sql -- Bad: often causes Seq Scan WHERE status = 'pending' OR status = 'processing' -- Good: IN uses index WHERE status IN ('pending', 'processing') ``` **5. Pagination with OFFSET:** ```sql -- Bad: OFFSET 10000 scans and discards 10000 rows SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 10000; -- Good: keyset pagination SELECT * FROM products WHERE id > :last_seen_id ORDER BY id LIMIT 20; ``` **6. COUNT(*) on large tables:** ```sql -- Bad: full table scan SELECT COUNT(*) FROM events; -- Good: approximate count (PostgreSQL) SELECT reltuples::bigint FROM pg_class WHERE relname = 'events'; -- Or maintain a counter cache table ``` ### Window Functions Reference ```sql -- Running total SELECT id, amount, SUM(amount) OVER (ORDER BY created_at) as running_total FROM payments; -- Rank within group SELECT *, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank FROM employees; -- Previous/next row SELECT *, LAG(amount) OVER (ORDER BY created_at) as prev_amount, LEAD(amount) OVER (ORDER BY created_at) as next_amount FROM payments; -- Moving average SELECT *, AVG(amount) OVER (ORDER BY created_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as ma_7 FROM daily_sales; -- Percent of total SELECT *, amount / SUM(amount) OVER () * 100 as pct_of_total FROM line_items WHERE order_id = 1; ``` ### CTE Patterns ```sql -- Recursive: org chart traversal WITH RECURSIVE org AS ( SELECT id, name, manager_id, 1 as depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, o.depth + 1 FROM employees e JOIN org o ON e.manager_id = o.id WHERE o.depth < 10 -- safety limit ) SELECT * FROM org ORDER BY depth, name; -- Data pipeline: clean → transform → aggregate WITH cleaned AS ( SELECT *, TRIM(LOWER(email)) as clean_email FROM raw_signups WHERE email IS NOT NULL ), deduped AS ( SELECT DISTINCT ON (clean_email) * FROM cleaned ORDER BY clean_email, created_at DESC ) SELECT DATE_TRUNC('week', created_at) as week, COUNT(*) FROM deduped GROUP BY 1 ORDER BY 1; ``` --- ## Phase 4 — Migrations ### Migration Safety Rules 1. **Never** rename columns/tables in production without a multi-step process 2. **Never** add NOT NULL without a DEFAULT on existing tables with data 3. **Never** drop columns that application code still references 4. **Always** test migrations on a copy of production data first 5. **Always** have a rollback plan (down migration) 6. **Always** take a backup before schema changes in production ### Safe Migration Patterns **Add column (safe):** ```sql -- Step 1: Add nullable column ALTER TABLE users ADD COLUMN phone VARCHAR(20); -- Step 2: Backfill (in batches!) UPDATE users SET phone = '' WHERE phone IS NULL AND id BETWEEN 1 AND 10000; -- Step 3: Add NOT NULL after backfill ALTER TABLE users ALTER COLUMN phone SET NOT NULL; ALTER TABLE users ALTER COLUMN phone SET DEFAULT ''; ``` **Rename column (safe multi-step):** ```sql -- Step 1: Add new column ALTER TABLE users ADD COLUMN full_name VARCHAR(200); -- Step 2: Dual-write in application code (write to both old + new) -- Step 3: Backfill UPDATE users SET full_name = name WHERE full_name IS NULL; -- Step 4: Switch application to read from new column -- Step 5: Drop old column (after confirming no reads) ALTER TABLE users DROP COLUMN name; ``` **Add index without locking (PostgreSQL):** ```sql CREATE INDEX CONCURRENTLY idx_orders_customer ON orders(customer_id); -- Takes longer but doesn't lock the table ``` **Large table backfill (batched):** ```sql -- Don't: UPDATE millions of rows in one transaction -- Do: batch it DO $$ DECLARE batch_size INT := 5000; affected INT; BEGIN LOOP UPDATE users SET normalized_email = LOWER(email) WHERE normalized_email IS NULL AND id IN ( SELECT id FROM users WHERE normalized_email IS NULL LIMIT batch_size ); GET DIAGNOSTICS affected = ROW_COUNT; RAISE NOTICE 'Updated % rows', affected; EXIT WHEN affected = 0; COMMIT; END LOOP; END $$; ``` ### Migration File Template ```sql -- Migration: YYYYMMDDHHMMSS_description.sql -- Author: [name] -- Ticket: [JIRA/Linear ID] -- Risk: low|medium|high -- Rollback: see DOWN section -- Estimated time: [for production data volume] -- Requires: [prerequisite migrations]