How to fix autovacuum: VACUUM table (to prevent wraparound) in PostgreSQL

SELECT datname, age(datfrozenxid) AS xid_age,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

SELECT c.oid::regclass AS table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY age DESC
LIMIT 20;

SELECT pid, usename, state, backend_xid, backend_xmin,
       now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND (backend_xid IS NOT NULL OR backend_xmin IS NOT NULL)
ORDER BY age(backend_xmin) DESC NULLS LAST;

-- Try gentle termination first
SELECT pg_cancel_backend(pid);

-- If that doesn't work after a few seconds
SELECT pg_terminate_backend(pid);

SELECT pid, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%wraparound%'
  AND query NOT LIKE '%pg_stat_activity%';

SELECT p.pid, p.datname, p.relid::regclass AS table_name,
       p.phase, p.heap_blks_total, p.heap_blks_scanned,
       p.heap_blks_vacuumed,
       round(100.0 * p.heap_blks_scanned /
         NULLIF(p.heap_blks_total, 0), 1) AS percent_complete
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON p.pid = a.pid
WHERE a.query LIKE '%wraparound%';

-- Increase number of autovacuum workers
ALTER SYSTEM SET autovacuum_max_workers = 4;  -- default is 3

-- Reduce autovacuum delay for faster processing
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 10;  -- default is 2ms

-- Increase cost limit for faster vacuuming
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000;  -- default is 200

-- For very large databases, consider adjusting freeze age
-- (Only increase if you have good monitoring)
-- ALTER SYSTEM SET autovacuum_freeze_max_age = 300000000;

-- Apply changes
SELECT pg_reload_conf();

ALTER TABLE large_table SET (
  autovacuum_vacuum_scale_factor = 0.05,  -- vacuum more frequently
  autovacuum_vacuum_cost_delay = 5        -- vacuum faster
);

-- Alert when any table is over 80% of autovacuum_freeze_max_age
SELECT c.oid::regclass AS table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS age,
       setting::bigint AS freeze_max_age,
       round(100.0 * greatest(age(c.relfrozenxid), age(t.relfrozenxid)) /
         setting::bigint, 1) AS percent_to_wraparound
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
CROSS JOIN pg_settings s
WHERE c.relkind IN ('r', 'm')
  AND s.name = 'autovacuum_freeze_max_age'
  AND greatest(age(c.relfrozenxid), age(t.relfrozenxid)) >
      setting::bigint * 0.8
ORDER BY age DESC;