Index Maintenance Best Practices

Indexes are write-bearing contracts. Maintain them with evidence from plans, usage stats, and bloat metrics on PostgreSQL 18.4.

How to Use This List

• Require EXPLAIN proof in index PRs.
• Review idx_scan quarterly on write-heavy tables.
• Pair index changes with ANALYZE in same change window.

A - Design Gates

• Map index to a specific query shape in the ticket. No speculative indexes.
• Prefer composite indexes over many single-column indexes. Reduce write amplification.
• Put equality columns before range columns in composites. Left-prefix discipline.
• Use partial indexes for stable selective predicates. Smaller, faster, cheaper writes.
• Avoid indexing low-cardinality booleans alone. Unless partial predicate is tiny slice.

B - Operations

• Use CREATE INDEX CONCURRENTLY in production migrations. Avoid long write blocks.
• Run ANALYZE after index creation on large tables. Planner adoption check.
• Monitor pg_stat_user_indexes.idx_scan before drops. Evidence-based cleanup.
• REINDEX CONCURRENTLY when bloat proven, not on schedule alone. Targeted rebuilds.
• Track index size growth vs table size. Early bloat detection.

C - Team Workflow

• Document rejected index requests with EXPLAIN. Prevent repeat debates.
• Audit redundant prefixes after composite adds. Drop superseded indexes.
• Include rollback DROP CONCURRENTLY in migration. Safe revert path.
• Load-test writes after index packs. Measure insert p95 impact.
• Align with query review standards for SQL text. ORM-hidden SQL still reviewed.

D - Anti-Patterns

• Do not index every foreign key blindly if unused. Index child joins you run.
• Do not duplicate UNIQUE constraint with plain INDEX. Catalog clutter.
• Do not add GIN on JSONB without containment queries. Huge maintenance cost.
• Do not fix seq scans with indexes before ANALYZE. Stats first.
• Do not leave INVALID indexes after failed CONCURRENTLY. Drop and retry.

FAQs

Related

• Index Design Basics - Design rules
• Duplicate & Redundant Indexes - Audits
• REINDEX & REINDEX CONCURRENTLY - Rebuilds
• Handling Index Requests - Team process

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+.