PostgreSQL VACUUM, Bloat, and Autovacuum Tuning

-- 1. Find bloated tables and stale autovacuum timestamps
SELECT relname,
       n_live_tup,
       n_dead_tup,
       ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- 2. Find the long-running transaction blocking cleanup
SELECT pid, usename, state,
       now() - xact_start AS xact_age,
       now() - query_start AS query_age,
       wait_event_type, wait_event,
       query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 10;

-- 3. Global safety net — kill idle transactions that hold xmin back
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
ALTER SYSTEM SET statement_timeout = '30s';
SELECT pg_reload_conf();

-- 4. Manual VACUUM with live progress (PG 12+)
VACUUM (VERBOSE, ANALYZE) orders;
SELECT * FROM pg_stat_progress_vacuum;

-- 5. Per-table autovacuum tuning for hot tables
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor  = 0.02,   -- vacuum after 2% dead vs 20% default
    autovacuum_vacuum_threshold     = 1000,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_limit    = 2000,   -- let autovacuum do more work per round
    autovacuum_vacuum_cost_delay    = 2       -- ms
);

-- 6. Global autovacuum knobs worth revisiting (postgresql.conf)
-- autovacuum_max_workers = 6             -- parallel autovacuum processes
-- autovacuum_naptime     = 30s           -- check interval
-- maintenance_work_mem   = 1GB           -- bigger batches per worker
-- autovacuum_vacuum_cost_limit = 2000    -- default 200 is far too low
-- autovacuum_freeze_max_age    = 500M    -- when anti-wraparound vacuum triggers

-- 7. Online rebuild when a table is genuinely bloated
-- pg_repack (extension + binary) rewrites the table with minimal locking
-- pg_repack -d app -t public.orders --jobs=4

-- 8. Index bloat (separate from table bloat)
REINDEX INDEX CONCURRENTLY idx_orders_user_created;

-- 9. Wraparound monitoring — the existential threat
SELECT datname, age(datfrozenxid) AS xid_age,
       ROUND(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_until_wraparound
FROM pg_database
ORDER BY xid_age DESC;

-- 10. Emergency: manual anti-wraparound vacuum
VACUUM (FREEZE, VERBOSE, ANALYZE) orders;