Indexes Best Practices
Indexes are not free decorations. Prove each one with workload evidence.
How to Use This List
- Capture query patterns from pg_stat_statements.
- Review unused indexes quarterly.
A - Design
- Index predicates and join keys you actually filter. Not every column.
- Match multicolumn order to queries. Leading column rule.
- Use partial indexes for hot subsets. Smaller and faster.
B - Validation
- EXPLAIN ANALYZE before and after. Prove benefit.
- Check index bloat and invalid indexes. REINDEX when needed.
- Drop idx_scan = 0 indexes after observation window. Document drop in migration.
C - Operations
- CREATE INDEX CONCURRENTLY in prod. Avoid blocking writes.
- Plan fillfactor on heavy UPDATE tables. Reduce page splits.
- Keep autovacuum healthy for index-only scans. Visibility map matters.
FAQs
How many indexes per table?
As few as possible meeting SLA; typical OLTP 3-8 depending on access paths.
Related
- B-tree Indexes - multicolumn
- Index Design Best Practices - lifecycle
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.