Module Overview

What You’ll Learn

• Create reusable database objects
• Improve query performance
• Analyze execution plans

💡 Why this module matters: A complex query is easy to write once and painful to repeat everywhere it’s needed, and even a correct query can be slow at real-world scale. This module covers packaging queries for reuse, and understanding why a query performs the way it does.

What’s Covered

• Views, saving a query as a reusable, named object.
• Materialized Views (overview), a view that stores its results instead of recalculating them every time.
• Indexes, speeding up lookups on specific columns.
• CREATE INDEX and DROP INDEX, adding and removing them.
• EXPLAIN and EXPLAIN ANALYZE, seeing exactly how PostgreSQL plans to run a query.
• Query optimization basics, reading an execution plan to spot a real problem.

Hands-On

By the end of this module, you’ll create views, create indexes, compare query performance before and after, and read a real execution plan.

Next module covers transactions, keeping several changes safe together as one all-or-nothing unit.