PostgreSQL Indexing Strategies Beyond the Default B-Tree

An index is not free — it slows every write on that table and costs storage — so "index everything" is not a strategy, it is a way to make inserts slow without knowing which reads actually got faster. The right approach starts from the query, not from the column.

Composite indexes: column order is the whole decision

An index on (status, created_at) serves a query that filters on status and sorts by created_at, but it does not efficiently serve a query that sorts by created_at alone — Postgres can only use a composite index as a prefix match. The rule of thumb: put the column used in equality filters first, the column used for range filtering or sorting second.

-- Serves: WHERE status = 'active' ORDER BY created_at DESC
CREATE INDEX idx_products_status_created_at
  ON products (status, created_at DESC)
  WHERE deleted_at IS NULL;

-- Does NOT efficiently serve a query with no status filter at all
-- (Postgres would need a full index scan, not a range scan)

Partial indexes: only index the rows a query actually hits

Soft-deleted rows, draft posts, or cancelled orders are usually excluded from every real query by a WHERE deleted_at IS NULL or WHERE status != 'cancelled'. A partial index that carries the same WHERE clause is smaller, faster to scan, and cheaper to maintain than a full-table index that wastes space indexing rows no query will ever touch.

• GIN with pg_trgm is the only realistic option for ILIKE '%term%' search — a plain B-tree cannot help with a wildcard on both sides of the term.
• A UNIQUE index doubles as a fast lookup path; do not add a second plain index on a column already covered by a unique constraint.
• Foreign key columns need an index explicitly — Postgres does not create one automatically, unlike the primary key side of the relationship.
• An index only helps if the planner chooses to use it; a query returning more than roughly 5-10% of the table is often faster with a sequential scan, and Postgres knows this from its statistics.
• Run ANALYZE after a large data load — the planner's row-count estimates come from statistics that a bulk insert does not refresh automatically.

Prove it with EXPLAIN ANALYZE, do not guess

EXPLAIN ANALYZE runs the query for real and reports both the planner's estimate and the actual row counts and timing at every step. A Seq Scan on a large table where an Index Scan was expected is the single most common sign that an index exists but is not being used — often because the query filters on a computed expression, a different column, or a type that requires an implicit cast.

EXPLAIN ANALYZE
SELECT * FROM products
WHERE status = 'active' AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 20;

-- Look for: Index Scan using idx_products_status_created_at
-- Not:      Seq Scan on products (Cost=... rows=50000)

A missing index shows up in EXPLAIN ANALYZE as a sequential scan on a table too large to justify one. A wrong index shows up as an index that exists, is even scanned, but still returns far more rows than the query needed — a sign the column order or the WHERE clause does not match the real query shape.

Index maintenance: bloat is a real, ongoing cost

Postgres indexes accumulate bloat from updates and deletes the same way tables do — a B-tree page half-emptied by deleted entries still takes the same time to read as a full one — and a heavily written table can carry indexes that are two or three times their necessary size without anyone noticing until a query that used to be fast slowly stops being fast.

-- Estimate index bloat (approximate, but good enough to flag candidates)
SELECT
  schemaname, tablename, indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- Rebuild a bloated index without holding a long-lived lock on writes
REINDEX INDEX CONCURRENTLY idx_products_status_created_at;

REINDEX CONCURRENTLY (available since Postgres 12) rebuilds the index in the background without blocking writes to the table, at the cost of taking longer and briefly needing double the disk space for that one index — a reasonable trade on a production table that cannot afford a maintenance window.

• A high n_dead_tup count relative to n_live_tup in pg_stat_user_tables is the table-level signal that autovacuum is not keeping up, which usually means index bloat is accumulating too.
• Never run a plain REINDEX (without CONCURRENTLY) on a production table during business hours — it takes an exclusive lock for its entire duration.
• An unused index still needs the same maintenance as a used one; dropping it is not just about write speed, it also removes one more thing that can bloat.

Covering indexes: when Postgres never touches the table at all

A normal index scan finds the matching row locations in the index, then still has to visit the actual table (a "heap fetch") to read any column not part of the index. A covering index — adding the extra columns a query only ever reads, not filters or sorts by, via INCLUDE — lets Postgres answer the query entirely from the index itself, skipping the heap fetch, when the visibility map allows it.

-- Filters and sorts by (status, created_at); also needs to read "name"
-- and "slug" for the listing — INCLUDE avoids a heap fetch for those.
CREATE INDEX idx_products_listing_covering
  ON products (status, created_at DESC)
  INCLUDE (name, slug)
  WHERE deleted_at IS NULL;

EXPLAIN ANALYZE
SELECT name, slug, created_at FROM products
WHERE status = 'active' AND deleted_at IS NULL
ORDER BY created_at DESC LIMIT 20;
-- Look for: Index Only Scan (not Index Scan) in the plan

An "Index Only Scan" in the EXPLAIN output is the tell that this worked; a plain "Index Scan" still visiting the heap means either the visibility map is not up to date (a fresh VACUUM usually fixes this) or a selected column genuinely is not in the index yet.

Indexes are a maintenance decision, not a one-time setup task

An index that made sense when a table had ten thousand rows and one query pattern can become the wrong index once the table has ten million rows and three new query patterns the original schema never anticipated — indexing is not something to get right once at launch and never revisit, particularly for a table at the center of the product's core workflow.

Revisiting pg_stat_user_indexes and the slow-query log on a quarterly cadence, not only when a specific complaint arrives, catches the slow drift where a once-useful index quietly stops matching how the application actually queries the table.

Not every slow query deserves a new index — sometimes the honest fix is changing what the query asks for, such as adding a LIMIT to a report that was accidentally pulling every row before filtering in application code, or splitting one query that joins six tables into two smaller ones the application composes itself. Reaching for an index first, before checking whether the query itself is asking a reasonable question, is how a schema ends up with a dozen indexes that all exist to paper over one badly shaped query.

Conclusion

Index the columns and column combinations your slowest real queries actually filter and sort by, scope partial indexes to the subset of rows those queries touch, reach for pg_trgm only for genuine substring search, and verify every assumption with EXPLAIN ANALYZE instead of trusting that an index you added is the one the planner chose to use.