When to Rebuild PostgreSQL Indexes? Key Factors & Best Practices

Database administrators often debate whether to automate PostgreSQL index rebuilding. While some assume fragmentation harms performance, others argue it’s rarely needed. This article clarifies when index reconstruction is justified and how to measure its impact.

B-Tree Index Degradation: What You Need to Know

PostgreSQL’s B-tree indexes degrade gradually due to DELETE/UPDATE operations. Unlike tables, indexes don’t automatically reclaim space:

• ​Fragmentation​ occurs when pages split unevenly.
• ​Fillfactor​ (default 90%) reserves space for updates, causing intentional 10% bloat.

While minor bloat is harmless, excessive fragmentation slows ​range scans​ (e.g., ORDER BY or multi-row queries). Key metrics to monitor:

• ​**avg_leaf_density**: Lower values (<70%) indicate bloated pages.
• ​**leaf_fragmentation**: High values (>20%) mean fragmented leaf pages.

-- Example: Check index health with pgstattuple
SELECT 
  tree_level, 
  avg_leaf_density, 
  leaf_fragmentation 
FROM pgstatindex('large_idx');

‍

When Rebuilding Makes Sense

Rebuilding is rarely urgent. Prioritize these scenarios:

1. ​Persistent bloat: If avg_leaf_density drops below 60% consistently.
2. ​Performance regression: Observable slowdowns in range scans.
3. ​Maintenance windows: Use CONCURRENTLY to minimize downtime.

Avoid rebuilding prematurely—frequent rebuilds waste resources and disrupt caching.

Other Index Types

• ​GIN/GiST/SP-GiST: Rarely need rebuilding; use REINDEX only for corruption.
• ​Hash Indexes: Obsolete in modern PostgreSQL; avoid in favor of BRIN or B-tree.

Final Recommendation

Leverage PostgreSQL’s adaptive storage:

• Rely on autovacuum for routine maintenance.
• Use pgstattuple weekly to track metrics.
• Rebuild only when empirical evidence (not assumptions) warrants it.

By prioritizing data-driven decisions, you’ll maintain peak performance without unnecessary overhead.

‍