WARNING: oldest xmin is far in the past; FATAL: database is not accepting commands to avoid wraparound data loss
ID: database/pg-bloat-autovacuum-blocked
Version Compatibility
| Version | Status | Introduced | Deprecated | Notes |
|---|---|---|---|---|
| 16 | active | — | — | — |
Root Cause
PostgreSQL is running out of transaction IDs due to autovacuum being unable to clean up dead tuples. This happens when long-running transactions hold back the oldest xmin, preventing VACUUM from freezing old tuples. If not resolved, PostgreSQL will eventually shut down to prevent transaction ID wraparound, which would cause data corruption. The table bloat also degrades query performance significantly.
genericWorkarounds
-
85% success Terminate long-running transactions and run manual VACUUM FREEZE
1. Find long-running transactions: SELECT pid, xact_start, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY xact_start; 2. Terminate them: SELECT pg_terminate_backend(pid); 3. Run VACUUM FREEZE on affected tables: VACUUM (FREEZE, VERBOSE) tablename; 4. Monitor progress: SELECT relname, last_autovacuum, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC; 5. Check remaining XIDs: SELECT datname, age(datfrozenxid) FROM pg_database;
-
82% success Tune autovacuum to be more aggressive on high-write tables
Per-table settings for high-write tables: ALTER TABLE hot_table SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_delay = 2, autovacuum_vacuum_cost_limit = 1000). Global settings: increase autovacuum_max_workers (default 3, set to 5-6), reduce autovacuum_naptime (default 1min, set to 15s). Ensure maintenance_work_mem is large enough (1-2GB) for vacuum to be efficient.
Dead Ends
Common approaches that don't work:
-
Killing autovacuum workers to free up system resources
90% fail
Autovacuum is the mechanism that prevents transaction ID wraparound. Killing autovacuum workers delays the critical vacuum process and makes the wraparound situation worse. The system will eventually reach the hard limit and refuse all commands.
-
Increasing autovacuum_freeze_max_age to delay the problem
75% fail
This only delays when autovacuum triggers anti-wraparound vacuum, but does not increase the total transaction ID space (2 billion). With a higher threshold, when vacuum finally runs, it has more work to do and takes longer. If it cannot complete before the hard limit, the database shuts down.