Lesson 25 · Senior PostgreSQL backend engineering

Query shape and index choice

How senior engineers choose the right index for the real SQL, not for a generic idea of the table.

Your win: explain index choice the senior way: start from the real SQL, then justify whether you need a B-tree, composite, partial, covering, or expression index.

In plain English Plain English: a good index is not “an index on an important column.” It is an index whose shape matches how the real query filters, joins, and sorts rows.

The problem: choosing indexes by column prestige

Many index mistakes happen before Postgres gets involved. A team decides that a column is important, adds an index, and later wonders why the planner still scans more rows than expected. The real question is not “what column do we care about?” It is “what work does this query ask the database to do?”

The rule to memorise Start with the SQL: equality filters, range filters, join keys, sort order, returned columns, and selectivity. Then ask what index shape serves that exact access pattern cheapest.

The pattern: think from the query outward

B-tree for equality, range, ordering, and most everyday lookups
Composite when the same column combination appears repeatedly in useful order
Partial when only one subset of rows deserves indexing
Covering / INCLUDE when reducing heap fetches matters on hot read paths
Expression when the query consistently transforms the value before filtering

The senior move is to choose from these patterns after naming the query shape clearly, not before.

Anchor — where this shows up here This repo already shows why query shape matters: tenant filters on resource_path, soft-delete predicates like deleted_at IS NULL, trigram search, array overlap, and hand-written SQL builders all create very specific index needs instead of generic “index the table” needs.

Common mistake Creating an index with the right columns in the wrong order, or assuming a present index must be usable even when the query wraps the column in a function or returns too much of the table.

Official index docs + Use The Index, Luke!

These are still the best sources for reasoning from SQL shape to index shape. Use the official docs for exact behaviour, and Luke for the practical query-first intuition.

→ PostgreSQL indexes
→ Use The Index, Luke!

Check yourself (from memory)

Q1. The first step in index choice is…

Good index design begins with SQL access patterns, not with table prestige.

What is the senior one-sentence rule for index design?

recall, then click to reveal

Choose an index whose shape matches the real query’s filters, joins, sort order, and selectivity — not just the table’s important-looking columns.

Want me to turn one real repo query into an index-design exercise, column by column? Ask me.

Sources. PostgreSQL Indexes; Use The Index, Luke!.