Module 3: Advanced indexing

Module description

In guide #8 you learned to create basic B-tree indexes: CREATE INDEX ON books(author_id);. That solves 60% of cases. But the other 40% is what separates a backend dev who says "I'll add an index and that's it" from the one who says "this filter has low selectivity, a partial index conditioned on status = 'active' is worth it, and I'll validate with EXPLAIN that the planner chooses it over the composite that already exists".

This module teaches you that other 40%. You'll come out knowing how to design composite, covering, partial, and expression indexes, knowing when each one wins over the others, and always validating with EXPLAIN (ANALYZE, BUFFERS) that the planner is using them. You'll also learn the hidden cost few people teach: each index slows down your INSERT/UPDATE/DELETE, and an unused index is technical debt that costs disk, RAM, and write latency.

By the end of the module, you'll have a mini-project with five indexes designed over a simplified version of the Bookstore API, each one justified by a before/after query plan that demonstrates the improvement. That mini-project is the direct warmup for the final project of module 8.

Where are we? Where are we going?

What you already know (modules 1-2):

Module 1 taught you to make reproducible baselines and measure with wrk/locust. You know how to capture p50/p95/p99 before and after a change.
Module 2 taught you to read complete query plans with EXPLAIN (ANALYZE, BUFFERS, VERBOSE). You know how to walk a plan bottom-up, identify Seq Scan, Index Scan, Bitmap Heap Scan, read cost, actual time, loops, rows estimated vs actual, and Buffers.

What you build this module:

Advanced indexing. The tools the planner really rewards with better plans. Each technical capsule ends with an EXPLAIN that shows the plan change: from Seq Scan to Index Scan, or from Index Scan to Index Only Scan, or from Bitmap Heap Scan to a direct Index Scan.

Where we're going (modules 4 onward):

Module 4 changes layers: from the SQL level to the ORM level. SQLAlchemy's N+1 problem is invisible to EXPLAIN because each individual query is optimal — the problem is in how many queries are fired. But before attributing everything to the ORM, this module makes sure you're not confusing "slow ORM" with "missing index".
Module 5 teaches you to find the slow queries in production with pg_stat_statements.
The final project (module 8) integrates everything: a FastAPI API with planted problems that you'll diagnose, index, eliminate N+1s, tune the pool, and report improvements with benchmarks.

Professional objective

By the end of this module you'll be able to:

Diagnose why a query does a Seq Scan when you expected an Index Scan and propose the right index.
Design composite indexes understanding column order, the leftmost prefix rule, and selectivity.
Create covering indexes with INCLUDE to enable an Index Only Scan and eliminate the trip to the heap.
Create partial indexes that index only the subset of rows that matters, saving disk and improving reads.
Create expression indexes for queries with lower(), to_char(), and other functions — knowing which functions are IMMUTABLE and which aren't.
Identify when you need GIN (arrays, JSONB, full-text search) without going deep — that's guide #14.
Detect and eliminate unused indexes with pg_stat_user_indexes.
Reason about the reads vs writes trade-off: each index added slows down INSERT/UPDATE/DELETE.

These are exactly the topics a mid-to-senior backend technical interview asks about when they want to know if you "know databases" beyond CRUD.

Why does this module matter?

Three situations where advanced indexing is the difference between "the API works" and "the API scales":

1. Search endpoint with multiple filters.

GET /orders?status=pending&created_at>=2026-01-01 — two simultaneous filters. Without a composite index (status, created_at), PostgreSQL does a Seq Scan or a sub-optimal Bitmap Heap Scan combining two separate indexes. With the right composite, it goes straight to the rows that match. The difference: 8s vs 40ms on a 5M-row table.

2. Table with soft delete and 90% of rows deleted.

reviews has 10M rows, 9M with deleted_at IS NOT NULL. Every useful query filters WHERE deleted_at IS NULL. A normal B-tree index indexes all 10M (90% junk). A partial index WHERE deleted_at IS NULL indexes only the relevant 1M: 10x smaller, 10x faster to maintain, more efficient queries.

3. Case-insensitive email login.

SELECT id FROM users WHERE lower(email) = lower($1). Without an expression index on lower(email), PostgreSQL does a Seq Scan. With the index, it's an instant Index Scan. This is real: half the apps with a "slow login in production" bug have this problem without knowing it.

In your role as a backend dev, indexing is the first tweak when an endpoint gets slow in production. Before caching, before paginating differently, before migrating to another database — before all of that, you look at the plan and the index. This module gives you the tools for that conversation.

A scenario that illustrates the module

Imagine you come in on a Monday and the support team reports that GET /orders?status=pending&customer_id=42 is taking 6 seconds in production. Three months ago it took 200ms.

You apply what you learned in modules 1-2:

You reproduce locally with representative data (module 1).
You capture the plan: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status='pending' AND customer_id=42; (module 2).
The plan shows: Seq Scan on orders with Filter: ((status = 'pending') AND (customer_id = 42)) over 5,200,000 rows.

Up to here, modules 1-2 got you. Now comes this module:

You decide on the index. (status, customer_id) or (customer_id, status)? Capsule 03 teaches you: the most selective column first. customer_id has 50,000 distinct values; status has 4. The winning composite is (customer_id, status).
Does a partial index help? If status='pending' covers only 5% of the table, yes: CREATE INDEX ... ON orders(customer_id) WHERE status = 'pending'. You learn it in capsule 05.
And if you only need id, total, created_at from the result? A covering index with INCLUDE (total, created_at) enables an Index Only Scan that doesn't even touch the heap. Capsule 04.
You create the chosen index, capture the plan afterward. Index Scan using ... with actual time=12.345 ms. You document the change in BENCHMARKS.md: 6,200ms → 12ms, a 500x improvement.
Before closing the ticket, you check pg_stat_user_indexes and remove two old indexes nobody uses anymore. You learn that in capsule 07.

That's exactly the flow you'll practice in the module and replicate in the final project.

Module map

Capsule	Topic	What you'll learn
01	Module introduction	This capsule. Context, objectives, map.
02	B-tree fundamentals (revisited with an advanced lens)	How a B-tree index is structured internally, why `=` and `<` are fast and `LIKE '%foo'` isn't, what selectivity and cardinality are, when the planner ignores an index.
03	Composite indexes: order and selectivity	The leftmost prefix rule visualized, how to choose column order, two separate indexes vs one composite, validation with `EXPLAIN`.
04	Covering indexes with `INCLUDE`	`Index Only Scan` vs `Index Scan`, the `INCLUDE (col1, col2)` syntax (PostgreSQL 11+), the visibility map and why an `Index Only Scan` sometimes still touches the heap.
05	Partial indexes: indexing only what matters	The `WHERE` syntax, use cases (soft delete, skewed status enum, multi-tenant), the exact predicate-matching trap.
06	Expression indexes and a JSONB/GIN preview	Indexing `lower(email)`, `to_char(created_at, 'YYYY-MM')`, the `IMMUTABLE` rule, a preview of when you need GIN (without going deep — that's guide #14).
07	Index maintenance: bloat, REINDEX, and detecting unused ones	`pg_stat_user_indexes` to detect unused ones, what bloat is and when it matters, `REINDEX CONCURRENTLY`, the write cost per index.
08	Module project: indexing the Bookstore	Mini-project: 5 problematic queries, design the right index for each, capture before/after plans, justify choices in `INDICES.md`.

Connection with the capstone project

The final project (module 8) is optimizing a complete FastAPI API with planted problems. Three of the five endpoints of the final project are resolved mainly with techniques from this module:

GET /books?author=X — needs a composite + covering on books(author_id) INCLUDE (title, price).
GET /orders?status=pending — needs a partial index WHERE status = 'pending'.
Login with lower(email) — needs an expression index.

If you do this module well, you arrive at module 8 with those three solutions almost automatic. What you'll add in module 8 are the N+1 techniques (module 4), pool tuning (module 6), and anti-pattern refactoring (module 7-8).

What is NOT covered in this module

An explicit list so you know where to find each topic:

❌ N+1 problem with SQLAlchemy — module 4 of this same guide. If your endpoint takes a long time and the individual plan is fine, it's not an index — it's N+1.
❌ Deep GIN, JSONB indexes, full-text search — guide #14 (Advanced PostgreSQL Features). Here we only introduce when to recognize that you need GIN.
❌ Table partitioning — guide #14. When a table crosses 100M rows, indexing alone isn't enough.
❌ BRIN indexes, hash indexes, SP-GiST — very specific cases, not part of the common backend flow.
❌ Extended statistics (CREATE STATISTICS) and autovacuum tuning — module 7 of this guide.
❌ How the planner computes the estimated cost — module 2 introduced it; a deeper dive in module 7.

Traps to avoid while taking the module

1. "More indexes = faster." False. Each index slows down writes and consumes disk. Capsule 07 gets into this point in depth, but internalize it from day 1: each index is a decision, not a default solution.

2. "I created the index, done." Creating the index doesn't guarantee the planner will use it. You have to validate with EXPLAIN. If the plan still shows Seq Scan, the index is being ignored for some reason (bad selectivity, stale statistics, a query with a non-indexed expression, or a table that's too small). Capsule 02 teaches you to diagnose that.

3. "A composite is the same as two separate indexes." No. A composite (a, b) works for WHERE a, WHERE a AND b, but NOT for WHERE b. Two separate indexes work for all three cases, but individually. Capsule 03 shows you when each strategy wins.

4. "I'll index everything just in case." A classic anti-pattern. It leads to tables with 12 indexes where 8 are never used, INSERT/UPDATE 5x slower, and a full disk. Capsule 07 gives you pg_stat_user_indexes to clean up.

5. "If I put lower(email) in the WHERE, my normal index on email catches it." No. An index on email isn't used if you filter by lower(email) — you need an expression index on lower(email). Capsule 06.

6. "GIN is for everything B-tree doesn't solve." No. GIN is for containment (@>, ?, full-text). For equality and range, B-tree wins. Confusing them leads to monstrous indexes on disk that don't help the real queries.

7. "The index has been there for months, it must be fine." Indexes age. The app's queries change, the data changes, the statistics change. The periodic review of pg_stat_user_indexes is discipline, not an option.

Self-evaluation question

Before starting the module, answer these questions mentally. If you're unsure about any, review the suggested module or capsule.

Do you know how to read a complete query plan with EXPLAIN (ANALYZE, BUFFERS, VERBOSE)? If not, go back to module 2.
Do you know the difference between Seq Scan, Index Scan, and Index Only Scan? If not, module 2 capsule 03.
Do you know what Rows Removed by Filter in a node is and why it matters? If not, module 2 capsule 03.
Do you remember how to create a basic B-tree with CREATE INDEX ON table(column);? If not, guide #8.
Do you have a PostgreSQL 16+ database running locally with test data (at least 100k rows in one table)? If not, set one up with the seed.py script that comes in module-01/seed/ or create a new one.
Do you know how to capture query times with discipline (warmup + multiple runs)? If not, module 1 capsule 03.

If you answer all six with confidence, you're ready. If you're unsure about two or more, review before moving on.

Evidence of success

You'll know you succeeded in this module if by the end:

✅ You can look at a query and, without running it, propose at least one reasonable candidate index, with a justification for the column order.
✅ You can capture the plan before and after an index and explain what changed (which node is different, which actual time dropped, which buffers were saved).
✅ You know when INCLUDE gives you an Index Only Scan and when it doesn't (a fresh vs dirty visibility map).
✅ You know when a partial index wins over a full one (a significantly skewed subset).
✅ You know the trade-off: adding an index is a decision, not an automatism.
✅ You have a delivered mini-project: 5 indexes designed over a Bookstore dataset, each with a before/after plan documented in INDICES.md.

We start in the next capsule

The next capsule (02-fundamentos-indices-btree-revisited.md) reviews the B-tree fundamentals with an advanced lens. Even though guide #8 already taught you basic B-tree, here we open it up from the inside: how the tree is structured, why = and < are fast but LIKE '%foo' isn't, what selectivity is numerically, and which are the cases where the planner decides to ignore a perfectly valid index. Without that solid foundation, the following capsules (composite, covering, partial) are memorized recipes. With that foundation, they're logical consequences.

Before moving on, make sure you have:

PostgreSQL 16+ working locally.
A database with at least one large table (100k+ rows) — you can reuse the bookstore from the previous modules.
psql or a SQL client to run queries directly.

Resources for the module

Markus Winand — Use The Index, Luke! — the canonical reference for indexing in SQL. If you had to read a single resource from this whole guide, it's this one. It covers B-tree, composite, covering, expression, partial, all with excellent visualizations.
PostgreSQL Documentation — Indexes — the official chapter for version 16. It covers all the index types with exact syntax and use cases.
Hubert "depesz" Lubaczewski — depesz.com (Indexes category) — the blog of the author of explain.depesz.com. Real cases with before/after plans, very didactic.
Bruce Momjian — Performance Tuning Presentations — technical slides from the PostgreSQL core team. Especially "Mastering PostgreSQL Administration" and "PostgreSQL Performance".
PostgreSQL Wiki — Index Maintenance — useful queries for pg_stat_user_indexes, bloat detection, and maintenance.
Tom Lane — pgsql-hackers archives on the planner — for an extreme deep dive on how the planner makes decisions. It's not introductory reading, but useful when you see strange behaviors.

Module 3 — Database Performance & Query Tuning Guide

← PreviousModule project: diagnosing the Bookstore API Next →B-tree fundamentals revisited with an advanced lens