Free course · 20 lessons · Advanced
Scaling Databases & Data Consistency
Scale relational databases the way high-traffic products do: find the queries that matter, index for real workloads, fix N+1 and deep pagination, use read replicas without stale reads, pool connections at scale, build read models, partition and shard with the right key, run zero-downtime migrations and backfills, and keep other systems in sync with outbox and CDC — with real case studies, runnable simulations and code for every major stack.
1. Finding the Bottleneck
Measure before scaling: find the queries that matter, read their plans, index for the real workload, and fix N+1 and deep pagination.
- Finding the Queries That Matter — Use pg_stat_statements and the slow query log to find what really loads your database — usually not the slowest query.
- Reading Query Plans — EXPLAIN ANALYZE in practice: sequential scans, bad estimates, and confirming that an index is used.
- Indexing for Real Workloads — Composite column order, covering and partial indexes, and the write cost of every index you add.
- N+1 Queries and ORM Query Explosions — How an order list that looks like one query runs 101, and how to load relations in one or two queries in every ORM.
- Pagination at Scale: OFFSET vs Keyset — Why page 5,000 is slow and inconsistent, and how keyset (seek) pagination fixes both.
2. Scaling Reads
Replicas and their lag, read-your-writes, poolers at scale, read models and multi-region reads.
- Read Replicas and Replication Lag — Offload reads to replicas — and handle the stale data, lag spikes and failovers that come with them.
- Read-Your-Writes and Monotonic Reads in Code — Sticky-to-primary windows and LSN/GTID waits: making users always see their own changes.
- Connection Poolers at Scale — Many services, many pods, serverless functions — and one database's connection budget.
- Read Models, Summary Tables and Materialized Views — Stop recomputing dashboards on every request: precompute what reads need.
- Multi-Region Reads and Failover — Serving users far from the primary: follower reads, write latency, and what goes wrong in a cross-region failover.
3. Scaling Writes and Data Size
Partitioning big tables, choosing shard keys, cross-shard IDs and queries, and moving data between shards online.
- Partitioning Big Tables and Archiving Old Data — Time-range partitions make deleting a month instant and keep hot data small.
- Sharding Strategies and Shard Keys — Splitting data across databases: hash, range and directory sharding, choosing the key, and hot shards.
- IDs, Cross-Shard Queries and Transactions — Globally unique IDs without a central sequence, scatter-gather queries, and keeping transactions on one shard.
- Resharding and Moving Data Online — Adding capacity without downtime: consistent hashing, logical-shard moves, and the double-write → backfill → verify → switch recipe.
4. Change Without Downtime and Eventual Consistency
Schema changes, backfills, keeping other systems in sync, and designing UX around eventual consistency.
- Zero-Downtime Schema Changes (Expand/Contract) — Rename a column, add NOT NULL, build an index — while old and new code run side by side.
- Safe Backfills and Large Data Updates — Update 50 million rows without locking tables, lagging replicas or filling the disk.
- Keeping Other Systems in Sync: Outbox and CDC — Writing to the database and a message broker (or search index) without losing or inventing events.
- Designing UX Around Eventual Consistency — Search indexes, read models and caches lag behind — design screens and APIs that stay honest.
5. Capstone Case Studies
Two end-to-end scaling exercises: a read-heavy catalog growing 100×, and sharding a multi-tenant SaaS.
- Capstone: Scale a Read-Heavy Catalog 100× — A marketplace catalog must go from 2,000 to 200,000 reads per second — without rewriting the product.
- Capstone: Shard a Multi-Tenant SaaS Database — Plan the move from one big Postgres to a sharded fleet without downtime — including the whale customers.