🎛️Index tuning
Hypothesis, plan, verify writes
Indexes align physical order with filter, join, and sort keys you actually use. Each new index speeds some reads and taxes every insert/update/delete, tune on real predicates with EXPLAIN, production-like skew, and a budget to drop experiments that fail.
Find unused indexes (PostgreSQL)
SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 5;| table_name | index_name | idx_scan |
|---|---|---|
| orders | ix_orders_legacy | 0 |
| plates | ix_plates_old_barcode | 0 |
Add supporting index
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_orders_customer_date
ON orders (customer_id, order_date DESC);| result |
|---|
| CREATE INDEX |
Index sprawl
- Tuning on laptops with a fraction of prod rows, plans lie quietly.
- Indexes nobody remembers to drop after the feature pivot.
- Ignoring autovacuum bloat and replay lag after bulk loads.
Own and retire
- Capture before/after plans, row counts, and insert/update p99 when adding aggressive indexes.
- Assign an owner and review date for every new index in the migration notes.
- Correlate index deploys with replica apply lag in HA setups.
🧹Maintenance
Fragmentation and stats matter as much as CREATE INDEX.
Rebuild/reorganize policies (SQL Server), vacuum thresholds (PostgreSQL), and statistics refresh after large data shape changes keep plans honest, an index nobody maintains becomes a museum exhibit.
Covering paths
INCLUDE columns and partial indexes are powerful, justify them with measured heap-fetch elimination, not only a prettier EXPLAIN screenshot.
TL;DR
Index tuning is iterative: capture top queries by total time, read their plans, add or adjust indexes and statistics, then re-measure write cost and maintenance. Prefer narrow composite keys aligned with filters, avoid redundant indexes, and fix ORM “N+1” patterns before adding dozens of single-column indexes that overlap.