postgres index bloat detection and rebuild

TL;DR

Index bloat is dead space inside indexes left by updates and deletes; detect it with pgstattuple or a bloat-estimate query against pg_class, then rebuild with REINDEX CONCURRENTLY to avoid locking writes. If bloat keeps coming back, autovacuum is too slow or fillfactor is wrong, and rebuilding alone just buys time. Always measure before and after so you know the rebuild was worth it.

The query

postgres index bloat detection and rebuild

Use this when

• an index is much larger than the table data it covers
• you suspect autovacuum is not keeping up with updates
• you are planning a REINDEX and want the non-blocking variant

Not for

• table (heap) bloat, which needs VACUUM FULL or CLUSTER instead
• general autovacuum tuning without a bloat diagnosis first

Steps

1. Estimate bloat with a bloat query or pgstattuple on the suspect index. Compare index size to expected size for the row count.

Expected output: You have a bloat percentage or byte estimate for the index.

1. Check autovacuum stats first: if the table is rarely vacuumed, fix that before rebuilding, or the bloat returns.

Expected output: pgstatuser_tables shows recent vacuum activity on the table.

1. Rebuild with REINDEX INDEX CONCURRENTLY so reads and writes keep working during the rebuild.

Expected output: The rebuild finishes without blocking production traffic.

1. Consider lowering fillfactor on heavily updated indexes so future updates reuse page space instead of bloating.

Expected output: The index's fillfactor matches its update pattern.

1. Record sizes before and after, and set up a periodic bloat check so it does not silently regrow.

Expected output: You have a before/after number and a recurring check in place.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_3tLJlBQHSO3dBRC-ZrkYzg