Database indexing: partial indexes
This course studies partial indexes through a real submissions table. It starts with a plain index on the boolean success column that no query uses as intended. It then builds partial indexes whose columns and where: predicate match real queries. Lessons read EXPLAIN plans and test which conditions the planner can prove match a stored predicate. They measure index sizes with pg_relation_size, including the effect of B-tree deduplication. The course also weighs two mirror partial indexes against one full composite index, and looks at a boolean column no code queries.
What you'll learn
- ✓Reading a schema's indexes against the queries that run on the table
- ✓Writing a partial index whose columns and predicate match a real query
- ✓Which query conditions Postgres can prove imply an index predicate
- ✓Measuring partial index size fairly, and how B-tree deduplication affects it
- ✓Weighing two partial indexes against one full composite index
- ✓Checking that a query exists before building an index for it
An index that's barely pulling its weight
The junior course "ActiveRecord migrations: indexes" taught the syntax of where:, with examples this app didn't need. This course asks the harder mid-level question. Not "how do you write a partial index", but "how do you tell whether the index you already have is the wrong shape for how it's…
Read the full lesson free →Curriculum
7 lessonsAn index that's barely pulling its weight
A plain index on a boolean column loses to a more selective composite index in the query plan.
WHERE success = false: matching the real query
A partial index shaped like the failed-attempts query removes the run-time filter on success from the plan.
The planner's proof, not a promise
Five query conditions show that Postgres uses a partial index only when it can prove the predicate.
Measuring the real difference, honestly
A fair size comparison keeps the columns fixed, and B-tree deduplication explains why the saving shrinks.
Two partial indexes, one column pair
Two partial indexes with opposite predicates are compared with the single three-column index the app chose.
When the index solves a problem the code doesn't have
The quiz_options.correct column looks like an index candidate, but no code ever runs that query.
Putting it together: which index each query needs
A review of each index in the course, who queries it, and what decided its outcome.