Databases

The model that underlies every database, no matter the engine. Relational algebra, schema design, indexes from first principles, ACID, and concurrency control.

• Indexes from First Principles(8 concepts)

  • Index Types: B-tree, Hash, GIN (Overview)
  • Selectivity & Cardinality
  • Composite Indexes & Column Order
  • B-Tree Mechanics — Draw One
  • When Indexes Fail: Functions, Wildcards, NULLs
  • Covering Indexes (Index-Only Scan)
  • Indexes Aren't Free: Write Amplification
  • Partial Indexes
• Concurrency Control — Locking & MVCC(8 concepts)

  • Locking and MVCC: The Two Strategies
  • Row Versions: xmin and xmax
  • Deadlocks — Detection and Resolution
  • Shared (S) and Exclusive (X) Locks
  • Why Vacuum Exists
  • Lost Updates — and How to Prevent Them
  • Intent Locks — How Hierarchical Locking Works
  • Optimistic vs Pessimistic Concurrency
• The Relational Model & SQL Foundations(14 concepts)

  • Relations, Tuples, and Attributes
  • Primary, Foreign, and Candidate Keys
  • DDL Lock Levels (Reference)
  • What NULL Actually Means
  • Relational Algebra Operations
  • Surrogate vs Natural Keys
  • DML: SELECT, INSERT, UPDATE, DELETE
  • Three-Valued Logic (TRUE / FALSE / UNKNOWN)
  • Set Semantics vs Bag Semantics
  • Integrity Constraints
  • JOINs: Inner, Outer, Cross, Self
  • NULL in Aggregates, JOINs, and ORDER BY
  • Entity-Relationship Modeling
  • Aggregation, GROUP BY, and HAVING
• Schema Design & Normalization(10 concepts)

  • Functional Dependencies
  • 1NF, 2NF, 3NF, BCNF — and What Each Removes
  • The Read/Write Tradeoff
  • Schema Design Patterns (Reference)
  • Update, Insert, and Delete Anomalies
  • Lossless and Dependency-Preserving Decomposition
  • Materialized Columns, Generated Columns, Counter Caches
  • EAV (Entity-Attribute-Value) — and Why It Hurts
  • Polymorphic Foreign Keys — and Why They Hurt
  • God Tables and Premature Inheritance
• Transactions & ACID(9 concepts)

  • ACID: Atomicity, Consistency, Isolation, Durability
  • Dirty Read
  • The Four Isolation Levels
  • Write-Ahead Logging (WAL) — How Durability Works
  • Non-Repeatable Read
  • Snapshot Isolation in MVCC Engines
  • Phantom Read
  • Serializable Snapshot Isolation (SSI) and Its Cost
  • Write Skew (the Anomaly Snapshot Isolation Allows)

The engine you use every day. Architecture and internals, advanced SQL, indexing strategies, EXPLAIN mastery, and the power features (JSONB, FTS, partitioning) that make Postgres special.

• Indexing Strategies in PostgreSQL(9 concepts)

  • Indexes: B-tree, GIN, and Partial (Reuse)
  • Expression Indexes
  • Detecting and Measuring Bloat
  • GiST — Generalized Search Trees
  • INCLUDE Columns & Index-Only Scans
  • CREATE INDEX CONCURRENTLY & REINDEX CONCURRENTLY
  • BRIN — Block Range Indexes
  • HOT Updates — The Quiet Performance Win
  • Hash and SP-GiST — When They Show Up
• Advanced SQL with PostgreSQL(10 concepts)

  • Window Functions and CTEs (Reuse)
  • Recursive CTEs (Trees, Graphs, Generators)
  • LATERAL Joins — Per-Row Subqueries
  • Range Types & Exclusion Constraints
  • Window Frames — ROWS vs RANGE vs GROUPS
  • MATERIALIZED vs NOT MATERIALIZED — the Optimization Fence
  • GROUPING SETS, ROLLUP, CUBE
  • Array Columns (Deliberate Use)
  • Subqueries vs CTEs (Reuse)
  • FILTER, GENERATED, IS DISTINCT FROM
• PostgreSQL Architecture & Internals(9 concepts)

  • Postmaster, Backends, and Auxiliary Processes
  • Page Layout (Heap & Index)
  • The WAL Write Path
  • Query Execution Plans (Reference)
  • shared_buffers vs work_mem (Memory Tuning Map)
  • TOAST — Storing Large Values
  • Checkpoints — When Dirty Pages Hit Disk
  • Planner Statistics & ANALYZE
  • Autovacuum — The Background Janitor
• Query Performance & EXPLAIN Mastery(9 concepts)

  • EXPLAIN and Query Tuning (Reuse)
  • Cost Estimation — How the Planner Decides
  • Parallel Query — When It Helps
  • When Estimated Rows ≠ Actual Rows
  • Nested Loop Join
  • Query Optimizer Basics (Reuse)
  • Hash Join
  • When to Reach for pg_hint_plan
  • Merge Join
• PostgreSQL Power Features(8 concepts)

  • JSONB for Semi-Structured Data (Reuse)
  • Full-Text Search in PostgreSQL (Reuse)
  • Range, List, and Hash Partitioning
  • Foreign Data Wrappers (FDW)
  • Indexing JSONB — jsonb_path_ops vs Default
  • Ranking & Phrase Search
  • Partition Pruning & Constraint Exclusion
  • The Extension Toolkit

Running a database in production. Transactions in the wild, locking patterns and queues, online migrations, replication and HA, backups and PITR, connection pooling, observability, and security.

• Backups, PITR & Disaster Recovery(8 concepts)

  • Backup & Restore (Reuse)
  • Continuous WAL Archiving
  • RTO vs RPO
  • Running a Restore Drill
  • pgBackRest, WAL-G, Barman
  • recovery_target_time and Friends
  • The 3-2-1 Rule
  • Detecting Backup Corruption Before You Need It
• Connection Pooling & Capacity Planning(7 concepts)

  • Connection Pooling (Reuse)
  • Session Pooling — Safe but Limited
  • Pool Size from Little's Law
  • The Process-per-Connection Cost
  • Transaction Pooling — The Default Choice
  • Queue Pressure & Pool Wait Time
  • Statement Pooling — Rarely the Right Choice
• Observability & Performance Engineering(6 concepts)

  • Monitoring Performance (Reuse)
  • Configuring auto_explain
  • The 12 Metrics
  • Per-Table Autovacuum Settings
  • Reading pg_stat_statements
  • Autovacuum Starvation
• Database Security(8 concepts)

  • Role Architecture
  • RLS Policies — USING and WITH CHECK
  • TLS Connection Encryption
  • pgaudit — Forensic-Grade Logging
  • Default Privileges and Schema Ownership
  • RLS Pitfalls and Performance
  • Column-Level Encryption with pgcrypto
  • SQL Injection — What Parameterization Does and Doesn't Prevent
• Transactions in Production(6 concepts)

  • Long Transactions Block Vacuum
  • Idempotency-Key Pattern
  • Retrying 40001 / 40P01
  • statement_timeout, lock_timeout, idle_in_transaction_session_timeout
  • Idempotency Across External Side Effects
  • Savepoints — Nested Transactions That Aren't
• Replication, HA & Failover(8 concepts)

  • Replication Strategies (Reference)
  • What Causes Replica Lag
  • Synchronous Replication
  • Patroni — Leader Election + WAL Streaming
  • How Streaming Replication Works
  • Read-After-Write & Replica Routing
  • Async Replication's Data-Loss Window
  • Split Brain & Fencing
• Schema Migrations at Scale(8 concepts)

  • DDL Lock Levels (Reference)
  • Expand → Migrate → Contract
  • Batched Backfill Pattern
  • pg_repack — Online Table Rebuild
  • ADD COLUMN with DEFAULT — Old vs New Postgres
  • Schema Migrations in Production (Reuse)
  • pg-osc — Online Schema Change
  • The lock_timeout Pattern for Migrations
• Locking Patterns & Job Queues(6 concepts)

  • The Four Row-Level Lock Modes
  • FOR UPDATE SKIP LOCKED — The Queue Pattern
  • Advisory Lock Types
  • Diagnosing Lock Waits with pg_locks
  • When Postgres-as-Queue Is Right (and When It Isn't)
  • Real-World Uses for Advisory Locks

Patterns that recur in every product — multi-tenancy, soft deletes, audit trails, event sourcing, hierarchies, materialized read models. Where most domain bugs actually live.

• Modeling Real-World Domains(6 concepts)

  • Aggregate as Transaction Boundary
  • State Storage — The Default
  • Transaction Time vs Valid Time
  • Bounded Contexts at the Schema Level
  • Event Storage — The Alternative
  • Effective-Date Pattern
• Multi-Tenancy Patterns(5 concepts)

  • Multi-Tenant Isolation Strategies (Reuse)
  • Setting Tenant Context per Request
  • Schema-Per-Tenant Mechanics
  • tenant_id as a Universal Discriminator
  • Database-Per-Tenant — When the Customer Demands It
• API-Shaped Data & Materialized Views(5 concepts)

  • Materialized View Basics
  • The N+1 Query Problem (Reuse)
  • Building API Shapes with JSON Aggregation
  • Refresh Strategies
  • The DataLoader Pattern
• Event Sourcing, CQRS & the Outbox Pattern(5 concepts)

  • Event Sourcing — Introduction (Reuse)
  • Command-Query Responsibility Segregation
  • Transactional Outbox — How It Works
  • Snapshots — Bounded Replay Cost
  • Outbox via CDC — No Polling Required
• Hierarchies & Graph Modeling in SQL(5 concepts)

  • Adjacency List with Recursive CTE
  • Materialized Path
  • ltree, lquery, ltxtquery
  • When Postgres Isn't Enough
  • Nested Set (Lft/Rgt) — Cheap Reads, Expensive Writes
• Soft Deletes, Audit Trails & Temporal Data(5 concepts)

  • Soft Delete Pattern (Reuse)
  • Audit Trail Patterns (Reuse)
  • System-Versioned Tables
  • Soft Delete vs Hard Delete (GDPR)
  • Capturing 'Who Made the Change'

What lives next to your primary database, and when each is the right tool. NoSQL families, distributed SQL and sharding, data warehouses and OLAP, streaming and CDC, vector databases for AI workloads, and caching strategies.

• Distributed SQL & Sharding(8 concepts)

  • CAP Theorem (Reuse)
  • Sharding and Partitioning (Reuse)
  • CockroachDB
  • PACELC — The Underused Refinement
  • Hot Shards & Cross-Shard Transactions
  • Consensus Protocols: Raft and Paxos (Reuse)
  • Eventual vs Strong Consistency (Reuse)
  • When Distributed SQL Is Worth It
• NoSQL Families & When to Use Them(8 concepts)

  • Key-Value Stores: Redis (Reuse)
  • Wide-Column: Cassandra / ScyllaDB (Reuse)
  • Time-Series: Influx, Timescale, Prometheus
  • SQL vs NoSQL Decision Rubric (Reuse)
  • Document Stores: MongoDB (Reuse)
  • Graph Databases: Neo4j (Reuse)
  • Search: Elasticsearch / OpenSearch
  • DynamoDB — Managed KV at AWS Scale
• Caching Strategies(4 concepts)

  • Cache-Aside, Read-Through, Write-Through, Write-Behind
  • TTL, Event-Based, and Version-Based Invalidation
  • The Cache Stampede
  • When Caching Is the Wrong Answer
• Vector Databases & AI Workloads(8 concepts)

  • What an Embedding Is
  • HNSW — Hierarchical Navigable Small World
  • pgvector — Setup and Indexing
  • Reciprocal Rank Fusion (RRF)
  • Cosine, Dot Product, Euclidean — Which When
  • IVF and Product Quantization
  • When pgvector Isn't Enough
  • Designing RAG Retrieval
• Data Warehouses & OLAP(8 concepts)

  • Row Stores vs Column Stores
  • Fact Tables and Dimension Tables
  • Snowflake vs BigQuery
  • ELT vs ETL
  • Parquet & ORC — File Formats for Analytics
  • Slowly Changing Dimensions (SCD Types 1, 2, 6)
  • Redshift, Databricks, and the Lakehouse
  • dbt — SQL as a First-Class Modeling Layer
• Streaming, CDC & Eventual Consistency(6 concepts)

  • Topics, Partitions, and Offsets
  • Debezium Reading the Postgres WAL
  • Stream-Processed Materialized Views
  • At-Most-Once, At-Least-Once, Exactly-Once
  • CDC vs Outbox — Which When?
  • Backfill Strategies for Derived Stores