PostgreSQL Index Bloat: Measurement, REINDEX CONCURRENTLY, and Prevention

-- Using pgstattuple extension
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstatindex('idx_orders_created_at');
-- leaf_fragmentation > 30% indicates significant bloat

-- For all indexes on a table
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'orders'
ORDER BY pg_relation_size(indexname::regclass) DESC;

-- Estimate index bloat across all indexes
SELECT schemaname, tablename, indexname,
       pg_size_pretty(real_size) AS real_size,
       pg_size_pretty(bloat_size) AS bloat_size,
       round(bloat_ratio::numeric, 2) AS bloat_ratio
FROM (
  SELECT *, (real_size - estimated_useful_size) AS bloat_size,
            100 * (real_size - estimated_useful_size)::float / real_size AS bloat_ratio
  FROM pgstatindex_approx()
) t
WHERE bloat_ratio > 20
ORDER BY bloat_size DESC
LIMIT 20;

-- Rebuilds index without locking table (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_orders_created_at;

-- Reindex all indexes on a table
REINDEX TABLE CONCURRENTLY orders;

-- Build new index first
CREATE INDEX CONCURRENTLY idx_orders_created_at_new
  ON orders (created_at);

-- Swap names atomically
BEGIN;
ALTER INDEX idx_orders_created_at RENAME TO idx_orders_created_at_old;
ALTER INDEX idx_orders_created_at_new RENAME TO idx_orders_created_at;
COMMIT;

-- Drop old index
DROP INDEX CONCURRENTLY idx_orders_created_at_old;

-- Per-table autovacuum settings for high-churn tables
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_delay = 2
);