warning database must be vacuumed within N transactions — 01000

Symptoms

PostgreSQL warns that a database is approaching transaction-ID wraparound and must be vacuumed within a limited number of transactions. SQLSTATE 01000 (warning).

• The database is nearing the wraparound horizon.
• A countdown of remaining transactions is shown.
• Ignoring this leads to a forced read-only shutdown.

What the server log shows

WARNING:  database "production" must be vacuumed within 10000000 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.

Why PostgreSQL raises this — what the manual says

Section 24.1.5 Preventing Transaction ID Wraparound Failures:

“If for some reason autovacuum fails to clear old XIDs from a table, the system will begin to emit warning messages like this when the database’s oldest XIDs reach forty million transactions from the wraparound point:”

Transaction IDs are finite; old rows must be frozen before the XID counter wraps. As the oldest unfrozen XID nears the limit, PostgreSQL warns (01000) with a countdown so an operator can vacuum before it forces a protective shutdown.

Common causes

• Autovacuum disabled, throttled, or unable to keep up.
• Long-running transactions holding back the xmin horizon.
• Very high write volume on large tables.

How to fix it

1. Run a database-wide VACUUM (or targeted VACUUM on the oldest tables) promptly.
2. Identify the oldest tables via age(relfrozenxid) and vacuum them.
3. Tune autovacuum and remove long-running transactions blocking cleanup.

Diagnostic query

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

The database with the highest xid_age is closest to wraparound and needs vacuuming first.

Related & next steps

• SQLSTATE 01000 — Warning (full reference)
• Message: database is not accepting commands to avoid wraparound
• Glossary: Transaction ID wraparound

Reference: PostgreSQL 18 Section 25.1 “Routine Vacuuming”.