Databases at Scale

About this course

Most apps start with one Postgres database, and for a long time that is the right choice. Then the sales table passes a billion rows, the evening dashboard slows down the checkout tills, somebody runs a DELETE on the wrong server, and the team starts asking about sharding. This course shows you what to do at each of those moments, in the order a careful team would do it.

You will partition a big table so queries read only the months they need, move reads to replicas without the classic stale-read bug, learn when sharding is really needed and how consistent hashing routes keys, set recovery targets and restore a database to the minute before a mistake, run the daily health checks a database administrator relies on, and use JSONB, full-text search and materialized views before you add another database.

Every lesson follows Mangrove Market, a fictional grocery chain with 320 stores, an app and 1,800 partner shops, so the numbers stay concrete. Each one has checkpoints, practice tasks that run in your browser on a real PostgreSQL engine or in JavaScript, flashcards and a podcast episode. The course ends with a capstone design for the Partner app's order database and a final exam.

What you will be able to do

• Partition a large table by month so queries prune to the months they need and old data leaves with DETACH and DROP
• Route reads between a primary and its replicas, measure replication lag and plan a failover that cannot cause split brain
• Decide whether sharding is really needed, choose a shard key, and explain consistent hashing with virtual nodes
• Agree RPO and RTO targets and meet them with WAL archiving, point-in-time recovery and timed restore drills
• Run a daily Postgres health check covering connections, long transactions, vacuum, transaction ID age and roles
• Use JSONB, full-text search and materialized views first, and add another data store only for a measured need

Syllabus

6 stops, about 3.5 h in total. Each stop fills in when you complete the lesson.

1. 1

   Partitioning big tables

   When one table holds years of sales, every query, vacuum and clean-up gets slower. Partitioning splits that table into smaller tables behind one name, so Postgres reads only the months a query needs and you can remove an old month in about a second.

   35 minIntermediateEpisodeSimulator2 exercises

   Table partitioningPartition pruningData retention

2. 2

   Replication and read replicas

   A replica is a second Postgres server that keeps a live copy of your database. It can take heavy reads off the primary and stand ready to take over if the primary dies. It is always a little behind, though, and this lesson shows you how to design for that.

   35 minIntermediateEpisode2 exercises

   Read replicasReplication lagFailover

3. 3

   Sharding and consistent hashing

   Sharding splits one database across several servers, so each server holds part of the rows and takes part of the writes. It is the most powerful scaling tool in this course, and the most expensive one to live with. This lesson shows when you really need it and how the routing works.

   40 minAdvancedEpisodeSimulator1 exercise

   ShardingConsistent hashingShard key design

4. 4

   Backups and disaster recovery

   A backup is only worth something on the day you restore it. This lesson shows you how to set targets for how much data you may lose and how long you may be down, which backups meet those targets, and how to bring a database back to the minute before someone's mistake.

   35 minIntermediateEpisode1 exercise

   Backup and recoveryRPO and RTOPoint-in-time recovery

5. 5

   Running Postgres in production

   Most Postgres outages come from a few everyday things: too many connections, a transaction left open, vacuum falling behind, or one role with far too much power. This lesson gives you the daily habits and the exact settings and queries a DBA uses to keep a busy database healthy.

   40 minAdvancedEpisode2 exercises

   Postgres operationsConnection poolingVacuum and MVCCLeast privilege

6. 6

   Beyond SQL: choosing the right data store

   Postgres can do far more than most teams use it for, and a second database brings a second set of backups, failures and skills to learn. This lesson helps you tell when Postgres is enough, when another kind of store really earns its place, and how to move data into analytics without slowing down the store tills.

   35 minIntermediateEpisode2 exercises

   Choosing data storesJSONB and full-text searchOLTP and OLAPChange data capture

7. Capstone: Scale and protect the Partner app's order database

   A realistic project with a rubric and a model answer.

8. Final exam and certificate

   10 questions drawn at random, 70% to pass.

Capstone project

Check what you know

Questions