Postgres Indexes: A Field Guide

The Problem#

Indexes speed reads but slow writes and consume storage. The right index depends on the query shape, selectivity, sort order, and update rate.

Why It Matters#

Most Postgres performance work is matching indexes to real access patterns. A good index can remove seconds from a request. A bad index can add write overhead and still never be used.

Production teams use index review as part of feature design because every new dashboard, API filter, and background job changes the query workload.

Project Example#

A tenant activity table often runs:

SELECT *
FROM events
WHERE tenant_id = $1
ORDER BY created_at DESC
LIMIT 50;

A useful index matches both filter and order:

CREATE INDEX CONCURRENTLY idx_events_tenant_created
ON events (tenant_id, created_at DESC);

More Examples#

Use a partial index when only a subset is queried often:

CREATE INDEX CONCURRENTLY idx_orders_open
ON orders (tenant_id, created_at DESC)
WHERE status = 'open';

Use GIN for JSONB containment:

CREATE INDEX CONCURRENTLY idx_events_payload
ON events USING gin (payload jsonb_path_ops);

Implementation Checklist#

• Start from the exact query and parameters.
• Use B-tree for equality, ranges, and sorting.
• Use GIN for arrays, JSONB containment, and full-text search.
• Use BRIN for very large naturally ordered tables.
• Use partial indexes for common filtered subsets.
• Use EXPLAIN (ANALYZE, BUFFERS) before and after.
• Create indexes concurrently on large production tables.
• Track unused indexes and remove them carefully.

Production Notes#

An index that helps reads can hurt writes. On tables with heavy inserts or updates, measure insert latency and vacuum behavior after adding indexes.

Common Mistakes#

• Adding one index per column without matching query shape.
• Forgetting CREATE INDEX CONCURRENTLY in production.
• Keeping unused indexes forever.
• Ignoring write overhead and vacuum cost.
• Building indexes for rare admin queries on hot transactional tables.

How To Validate#

After adding an index, compare the before and after plans for the exact production query. Also check pg_stat_user_indexes after a few days to confirm the index is actually used. If an index is never scanned but is updated on every write, it is technical debt with storage cost.

Summary#

Postgres indexes are design choices. Build them from real queries, verify with plans, and remove indexes that no longer earn their cost.