Phase 4 · Databases
Modeling, indexing, pooling, replication, sharding and DB firefighting.
- SQL Fundamentals: Joins, Query Execution Order, Normal Forms & Schema Design: Every join type on one tiny dataset, NULL traps, the logical order a SELECT is evaluated in, how Postgres actually executes it (nested loop / hash / merge), window functions, CTEs, 1NF→5NF on one evolving order sheet, deliberate denormalization, an interview-grade e-commerce schema and the classic SQL round problems.
- SQL vs NoSQL, Data Models, ACID & Isolation: Relational, document, key-value, wide-column, graph and time-series models; normalization trade-offs; transactions, ACID vs BASE; isolation levels, anomalies and MVCC; choosing a database.
- Indexing & Query Optimization: B-tree vs LSM vs hash vs GIN/GiST/BRIN, composite indexes and the leftmost-prefix rule, covering and partial indexes, reading EXPLAIN ANALYZE, finding slow queries, N+1 and DataLoader, keyset pagination, write amplification and timeouts.
- Connection Pooling, Lock Contention & Deadlocks: Why DB connections are expensive, sizing pools with Little's Law, the pods × pool explosion, PgBouncer modes, leaks and timeouts, backpressure, row/table locks, SKIP LOCKED, deadlocks and hot-row contention — plus the "connections exhausted" incident in depth.
- Replication, Read Replicas & Replication Lag: Single-leader, multi-leader and leaderless replication; sync vs async vs semi-sync; WAL streaming vs logical replication; read replicas and routing; read-your-writes and monotonic reads; failover, split brain; lag causes and fixes; why replicas do not scale writes.
- Partitioning, Sharding & Zero-Downtime Migration: Split data without splitting your team: shard keys, hot shards, cross-shard queries, resharding with logical shards, and moving a live table with no downtime.
- Database Firefighting: Six Production Scenarios: An on-call playbook: symptoms → diagnosis (real Postgres/MySQL queries and metrics) → fixes in order of cost, for the six database incidents interviewers love to ask about.
- Inside PostgreSQL: The Life of a Write and a Read: Postmaster and backends, the background workers, shared buffers and clock-sweep, heap page and tuple layout, TOAST, the WAL-before-data rule, full-page writes and torn pages, checkpoints and crash recovery, the commit path, the read path through planner, executor and visibility checks, and the tunables that matter when it hurts in production.