Category · Databases
How databases work inside
A 10 lesson follow up to the SQL and PostgreSQL course. Indexes and the B-tree, transactions and isolation levels, normalization, replication with partitioning and sharding, NoSQL models and choosing a database. Then PostgreSQL in production: the query planner and EXPLAIN ANALYZE, locks and deadlocks, connection pooling with PgBouncer, backups and PITR, zero downtime migrations.
10 lessons147 min
To the first lessonCheatsheet nowTopics, each as its own lesson
01How an index works insideDemoThe B-tree, column order in a composite index, covering indexes, and the cases where an index does not help.
14 min
02Transactions, ACID and isolation levelsWhat a transaction guarantees, which anomalies appear under concurrency, and what each isolation level gives you in PostgreSQL.
16 min
03Normalization and when to denormalizeNormal forms remove duplication. Denormalization brings it back on purpose for fast reads.
13 min
04Replication, sharding and partitioningHow to spread reads across replicas, a large table across partitions, and data across several servers.
16 min
05NoSQL models and how to choose a databaseDocument, key-value, wide-column and graph models, plus CAP and PACELC without formulas.
15 min
06The query planner and EXPLAIN ANALYZEScan types, join algorithms, estimates versus reality, and the statistics the planner bases its decisions on.
16 min
07Locks and deadlocksRow and table locks, SELECT FOR UPDATE, SKIP LOCKED queues, deadlock detection and advisory locks.
15 min
08Connection pooling and PgBouncerWhy PostgreSQL connections are expensive, PgBouncer modes, how to size a pool and serverless pitfalls.
13 min
09Backups and point in time recoverypg_dump versus physical backups, WAL archiving, PITR, restore drills, RPO and RTO.
14 min
10Zero downtime migrationsExpand and contract, new columns and indexes CONCURRENTLY, NOT VALID constraints and backfills in batches.
15 min0/10 topics open. This is the course intro, not the whole course on one page.
In the knowledge base
Reference pages for the technologies on this topic: who maintains them, when to use them and what interviews ask.
