Overview
free

Appendix A - Diagram Templates by Step
Appendix B - Technology Mapping Guide
Appendix C - Readiness Assessments (Step N to Step N+1)
Appendix D - Glossary

Course Setup and the Incremental Ladder
Why "Rows to Replicas": databases as system memory, and how guarantees get expensive fast
How to Use This Course: steps as "storage slices" (model -> mechanism -> guarantees -> failures)
The Incremental Ladder (Step 0 -> Step 7): what each rung adds
The Course Lenses: data model and API, indexing and execution, transactions and concurrency, replication and distribution, recovery, operations, evolution

Mental Models: What a Database System Is
System Types: OLTP vs OLAP vs key-value vs object storage (and what "system of record" means)
DB vs Storage Engine vs Cache: who owns truth, who owns speed
Guarantees as Product Features: latency, throughput, freshness, durability

Workloads and Access Patterns
Read/Write Shapes: point lookups, range scans, joins, aggregates
Logical vs Physical: schemas, views, projections, denormalization
Constraint Mapping: latency SLOs, throughput targets, storage growth, failure expectations

Diagramming and Notation for Databases
Schema Shapes: ER diagrams, document shapes, key spaces
Index and Plan Diagrams: B+ trees/LSM at a conceptual level; operator pipelines
Cluster Topologies: leaders/replicas/shards/routers; trust and failure boundaries

Step 0 Data Modeling: Relational, Document, Key–Value
Relational Basics: keys, constraints, normalization vs denormalization (conceptual)
Document Modeling: embedding vs referencing; shape evolution pressures
Key-Value Modeling: key design, scans, and "opaque value" tradeoffs

Step 0 Query Interfaces and Baseline Execution
Query Languages: SQL-ish SELECT/JOIN/GROUP BY; document filters; KV get/put/scan
Full Scans as Baseline: filters, projections, ordering
Materialization Choices: early vs late materialization (high-level intuition)

Step 1 Index Structures
B+ Trees (Conceptual): what they optimize, what they cost
Hash Indexes: where they win, where they fail (ranges, ordering)
Clustered vs Non-Clustered; Primary vs Secondary Indexes

Step 1 Planning, Operators, and Performance
Index Design and Maintenance: prefixes, composite keys, write amplification
Query Planning (Conceptual): logical vs physical plans; selectivity and cardinality intuition
Execution Operators: scans, joins (nested/merge/hash), sorts, aggregates (conceptual)
Query Anti-Patterns: N+1, unbounded scans, missing indexes, ad hoc query chaos

Step 2 Transaction Guarantees
ACID (Conceptual): what app developers actually get from each letter
Transaction Lifecycle: begin -> read/write -> commit/rollback; savepoints
Transaction Boundaries in Applications: where invariants live (and where they leak)

Step 2 Isolation Levels and Application Patterns
Isolation Levels (Conceptual): read uncommitted -> serializable
Anomalies: dirty/non-repeatable/phantoms and why "mostly works" is dangerous
App Patterns: idempotency, retries, invariant enforcement, saga-like compensations

Step 3 Lock-Based Concurrency
Shared vs Exclusive Locks; what blocks what (conceptual)
Granularity: row/page/table/partition; escalation and hierarchies
Practical Conflict Reduction: shorten transactions, order operations, avoid hot rows

Step 3 MVCC, OCC, and Conflict Handling
MVCC (Conceptual): snapshots, visibility rules, cleanup/vacuum pressure
OCC (Conceptual): read -> compute -> validate -> commit; where it shines/falls down
Deadlocks and Starvation: detection, timeouts, avoidance strategies
Distributed Preview: why cross-node coordination changes everything (latency + partial failure)

Step 4 Replication Fundamentals
Logical vs Physical Replication: change streams, log shipping, snapshots (conceptual)
Why Replicate: HA, read scaling, locality, disaster recovery
The Enemy: lag, divergence windows, and stale reads

Step 4 Replication Topologies
Single-Leader: write path, read options, read-your-writes pitfalls
Failover (Conceptual): detecting failure, promoting leaders, split-brain hazards
Multi-Leader and Leaderless (Conceptual): conflicts, resolution pressure, operational complexity

Step 4 Sharding and Routing
Partitioning: horizontal vs vertical; range vs hash vs directory
Rebalancing and Hotspots: uneven keys, hot partitions, adaptive strategies
Routing Layers: proxies/service discovery; client-side vs server-side routing; metadata control plane

Step 5 Failure Modes and Consistency Models
Failure Modes: node loss, slow nodes, partitions, partial vs total outages
Consistency Models (Conceptual): strong/eventual/causal/session guarantees
CAP-ish Thinking: tradeoffs as design choices, not labels; graceful degradation under partitions

Step 5 Quorums and “App-Level Truth”
Quorum Intuition: W+R>N and what it buys you (high-level)
Tunable Consistency: latency vs safety vs throughput; where knobs backfire
Application Design for Imperfect Consistency: semantic merges, idempotency, UX patterns for eventual correctness

Step 6 WAL and Durability
Write-Ahead Logging: log-then-data ordering and why it matters
Redo vs Undo vs Logical Logs (Conceptual): what each makes easy/hard
Group Commit and fsync Strategy: throughput vs latency tradeoffs

Step 6 Recovery and Engine Internals
Checkpointing and Snapshots: recovery speed vs write overhead
Crash Recovery Flows: what happens after power loss (conceptual walkthroughs)
Maintenance as a Feature: background work, compaction/vacuum, "hidden" system load

Step 6 Storage Engine Families and Layout
B-Tree Engines: pages, splits/merges, fragmentation; random vs sequential implications
LSM Engines: memtables/SSTables, compaction strategies; write/read amplification tradeoffs
Layout and Compression: row vs column (conceptual), encoding, OLTP vs OLAP alignment
Data Lifecycle: TTLs, archival, cold storage, capacity planning

Step 7 Shared-Nothing and Distributed Execution
Shared-Nothing Locality: why data placement is performance
Distributed Query Planning (Conceptual): scatter/gather, pushing down filters/aggregates
Cross-Shard Joins/Sorts/Groups: why "simple SQL" gets complicated fast

Step 7 Distributed Transactions and Coordination
2PC Concepts: what it guarantees and why it is operationally heavy
Consensus-Based Commits (High-Level): where coordination moves and what it costs
Avoiding Distributed Transactions: denormalization, async workflows, sagas, per-entity ownership

Step 7 Global Indexes, Metadata, and Geo Distribution
Global Secondary Indexes: maintaining them without killing write throughput
Metadata Services: partition maps, schema state, routing truth
Multi-Region Architectures: active-passive vs active-active; residency and latency constraints

Step 7 Operating Database Platforms
Observability: latency/throughput, queue depth, compaction pressure, replication lag
Multi-Tenancy: isolation models, noisy neighbors, limits, governance/billing
Schema Evolution and Migrations: online backfills, dual writes, safe rollouts
Security and Compliance (Data-Centric): roles, encryption (conceptual), audit logs, access tracking
Reference Architectures: OLTP + replicas + cache; lake/warehouse pipeline; tunable-consistency KV store

Data Modeling and API Design Patterns
Lessons on modeling for access patterns, invariants placement, denormalization boundaries, read/write isolation strategies

Indexing and Query Performance Patterns
Lessons on composite keys, covering indexes, pagination shapes, join strategies, plan stability, and performance anti-patterns

Transaction and Consistency Patterns
Lessons on idempotency and retries, outbox/inbox-style integration, conflict resolution semantics, session guarantees, eventual UX

Replication, Sharding, and Resilience Patterns
Lessons on failover playbooks, hotspot mitigation, resharding and rebalancing, quorum tuning, and degraded-mode design

Operations, Observability, and Evolution Patterns
Lessons on SLOs for databases, capacity planning, backup and restore drills, migration playbooks, tenancy guardrails, and auditability

/rows-to-replicas/rows-to-replicas

Indexing and Query Performance Patterns

Sign in to access this lesson.

Sign in Create account