Welcome to Optimizing PostgreSQL — the module where you stop asking how do I write this query? and start asking why is this query slow, and how do I make it fast? Up to now you have focused on getting correct results. Here you shift to performance: the difference between a report that returns in milliseconds and one that times out and frustrates everyone who depends on it.

You will work as a data engineer on a real database from the U.S. Department of Housing and Urban Development (HUD), built around a homeless_by_coc table with more than 86,000 rows. Starting from a blank slate — a database whose structure nobody has documented for you — you will learn to inspect Postgres from the inside: the internal catalog tables that store metadata about every table, column, type, and index. From there you will meet EXPLAIN, the command that reveals exactly how Postgres plans to run a query, and EXPLAIN ANALYZE, which measures the real time each step takes.

With those diagnostic tools in hand, you will speed things up. You will build your first index and measure a four-fold speedup, then learn the trade-offs — disk space and slower writes — that every index introduces. You will go further with multi-column indexes, expression indexes, and partial indexes, and learn to read the Bitmap Heap Scan and Index Scan nodes that tell you whether your index is actually being used. Finally, you will keep your database healthy with VACUUM, understand why deleted rows do not disappear immediately, and connect it all to the ACID guarantees that make databases reliable under concurrent use.

This is an advanced module, so every concept stays rigorous: real catalog queries, real EXPLAIN output, and real timings you can reproduce. Start with Lesson 1, where you will open up Postgres and read the metadata it keeps about itself.