Table Bloat & Index Fragmentation Remediation Guide

Zero-downtime compaction of bloated tables and fragmented indexes caused by high-churn UPDATE/DELETE workloads.

Prerequisites & Safety Rules:

Free disk space equal to at least 1.5x of target table sizeOnline index rebuild / pg_repack utilities

Measure Bloat Percentage

List tables with dead tuple percentage > 20% that are not keeping pace with autovacuum workers.

SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size, n_dead_tup, n_live_tup, round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_pct FROM pg_stat_user_tables WHERE n_dead_tup > 10000 ORDER BY n_dead_tup DESC LIMIT 10;

Zero-Downtime Table Compaction (pg_repack)

Avoid `VACUUM FULL` which acquires an exclusive table lock. Use `pg_repack` or MySQL online DDL instead.

pg_repack -d production_db -t sales_transactions

Community Benchmark & Contribution

Share Your Restore Drill Results or Experience

Contribute real-world RTO/RPO telemetry, PostgreSQL version, backup methodology, and obstacles encountered from your infrastructure to help build industry benchmarks.