TK
All writing
Backend

Postgres indexing for application developers

You don't need to be a DBA to stop writing slow queries. A practical guide to the indexes that matter: B-tree, composite column order, partial and covering indexes, and reading EXPLAIN ANALYZE.

Tanveer Khan11 min read

Most application-side slowness is a missing or wrong index, not a slow database. You do not need DBA-level depth to fix it — you need a handful of rules and the ability to read a query plan. Here is the practical set.

Read the plan before you add anything

EXPLAIN ANALYZE runs the query and shows what Postgres actually did. The words to look for: a Seq Scan on a large table in a hot query is the smell; an Index Scan is usually what you want.

explain.sql
sql
EXPLAIN ANALYZE
SELECT * FROM bookings
WHERE guest_id = '...' AND status = 'confirmed'
ORDER BY check_in DESC
LIMIT 20;

Composite index column order is everything

A multi-column index is usable left-to-right only. An index on (guest_id, status, check_in) serves a query filtering on guest_id, or guest_id + status, or all three — but NOT one filtering on status alone. Put equality columns first, then the range/sort column last.

composite.sql
sql
-- serves the query above: equality cols first, sort col last
CREATE INDEX idx_bookings_guest_status_checkin
  ON bookings (guest_id, status, check_in DESC);

Putting the ORDER BY column last in the index (matching its direction) lets Postgres satisfy the sort from the index itself — no separate sort step. That is often the difference between 2 ms and 200 ms on a LIMIT query.

Partial indexes for skewed data

If you almost always query one slice of a table — active rows, unpaid invoices — a partial index covers just that slice. It is smaller, faster to maintain, and perfectly targeted.

partial.sql
sql
-- only index the rows you actually query, keeping the index tiny
CREATE INDEX idx_invoices_unpaid
  ON invoices (due_date)
  WHERE paid_at IS NULL;

Rules of thumb

  • Index foreign keys you JOIN or filter on — Postgres does NOT create them automatically.
  • Every index slows down writes; don't index columns you never filter/sort by.
  • A composite index can replace several single-column ones — prefer fewer, well-ordered composites.
  • Watch for Seq Scans in hot paths; a Bitmap Heap Scan on a huge table often means a missing index.

Indexing is not dark art — it is matching the index shape to the query shape. Read the plan, order composite columns equality-then-range, reach for partial indexes on skewed data, and most of your 'the database is slow' problems turn out to be one CREATE INDEX away from solved.