Start From the Query, Not the Table
An index is a bet that a particular access path will happen often enough to justify write cost. Do not index a column because it "looks important." Index the filters, joins, and sorts you can see in production.
Pull the slowest queries from pg_stat_statements. Sort by total time, not mean time. A query that runs 10ms a million times is the fire, not the 800ms query that runs twice a day.
The Indexes We Reach For
- B-tree on equality and range filters that already appear in WHERE and JOIN
- Composite indexes that match left-prefix usage:
(org_id, created_at)if you always filter org first - Partial indexes for hot subsets:
WHERE status = 'open' - Covering indexes only after you have proof the heap fetch is the cost
What We Avoid
Indexing every foreign key "just in case." Duplicate indexes that differ by column order nobody uses. Indexes on low-cardinality flags with no other columns. Each extra index slows writes and autovacuum.
Prove It
EXPLAIN (ANALYZE, BUFFERS) before and after. If the index is not used, drop it. Unused indexes are not free insurance. They are ballast.
Frequently Asked Questions
How Should You Choose a Postgres Index?
Start from the slowest production queries, then index the filters, joins, and sorts those queries actually use. Do not index a column because it looks important.
Which Index Types Matter Most Day to Day?
B-tree for equality and range filters, composite indexes that match left-prefix usage, and partial indexes for hot subsets. Add covering indexes only after proving heap fetch cost.
When Should You Drop an Index?
If EXPLAIN ANALYZE shows it is unused after a realistic workload window, drop it. Extra indexes slow writes and autovacuum.
From Guesswork to Evidence
Keep a short index changelog with the query fingerprint that justified each add. Revisit quarterly. Teams that treat indexes as dated bets stay faster than teams that treat them as permanent decorations on every column that ever appeared in a JOIN clause during a rushed incident.