The warning said the database must be vacuumed within eleven million transactions, and every VACUUM anybody ran changed nothing

Reported symptoms

Every connection to the orders database logs a warning that it must be vacuumed within 11,482,119 transactions. That number has been falling steadily for three weeks.

Autovacuum has been running a to prevent wraparound job on the same large table continuously. It completes. It starts again.

Two engineers have run manual VACUUM FREEZE across the database, each taking several hours. age(datfrozenxid) did not move.

A VACUUM FULL was proposed and rejected — the table is 800 GB and the maintenance window is two hours.

The cluster is otherwise healthy. Normal latency, normal throughput, no errors reaching the application.

pg_wal has also grown to 340 GB. That has been tracked as a separate, unrelated problem.

Evidence provided

age(datfrozenxid) for orders is 2,135,517,881, falling by roughly three million per day. autovacuum_freeze_max_age is the default:

$ psql -c "SELECT name, setting FROM pg_settings WHERE name IN ('autovacuum_freeze_max_age','vacuum_freeze_min_age','vacuum_freeze_table_age','vacuum_failsafe_age') ORDER BY name;"

             name              |  setting   
-------------------------------+------------
autovacuum_freeze_max_age     | 200000000
vacuum_failsafe_age           | 1600000000
vacuum_freeze_min_age         | 50000000
vacuum_freeze_table_age       | 150000000
(4 rows)

The manual freeze runs produced this, and it is the whole incident:

$ psql -c "VACUUM (FREEZE, VERBOSE) frz;"

INFO:  vacuuming "postgres.public.frz"
tuples: 0 removed, 50000 remain, 0 are dead but not yet removable
removable cutoff: 200768, which was 100001 XIDs old when operation ended
INFO:  vacuuming "postgres.pg_toast.pg_toast_16386"
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 200768, which was 100001 XIDs old when operation ended

Compare that with a run where nothing was holding the horizon:

$ psql -c "VACUUM (FREEZE, VERBOSE) frz;"

frozen: 516 pages from table (100.00% of total) had 50000 tuples frozen
new relfrozenxid: 100766, which is 100013 XIDs ahead of previous value

pg_stat_activity has no old backend_xmin. pg_prepared_xacts is empty. And then:

$ psql -c "SELECT slot_name, slot_type, active, xmin, catalog_xmin, age(catalog_xmin) AS catalog_xmin_age, wal_status FROM pg_replication_slots;"

   slot_name    | slot_type | active | xmin | catalog_xmin | catalog_xmin_age | wal_status 
----------------+-----------+--------+------+--------------+------------------+------------
abandoned_slot | logical   | f      |      |       300769 |           100000 | reserved
(1 row)

In the incident the slot is named debezium_orders, its catalog_xmin is 12,043,771, and its wal_status is extended. The team that owned the connector was reorganised out of existence in October.

Work the evidence before reading on

1. Two multi-hour VACUUM FREEZE runs exited successfully and changed nothing. What does removable cutoff, which was 2135517881 XIDs old tell you?
2. What can hold a freeze horizon? Name every category before you look.
3. Is the 340 GB of WAL a second incident?
4. Would VACUUM FULL have helped?

Root cause

A logical slot pins the freeze horizon, absolutely

Here is each holder demonstrated in isolation on 18.6, with 100,000 transaction IDs burned between each freeze attempt:

Weeks of autovacuum accomplished nothing

The wraparound jobs ran, completed, advanced nothing, and started again because their triggering condition was still true. That is not a bug — autovacuum has no way to know the horizon is pinned, so it keeps trying. The I/O cost was real and the benefit was zero.

VACUUM FULL would have been the same, plus an ACCESS EXCLUSIVE lock on 800 GB. It rewrites the table; it cannot rewrite the horizon.

The WAL was never a separate incident

A slot retains WAL as well as pinning catalog_xmin. wal_status = 'extended' says exactly that. Two symptoms, one cause, tracked as two incidents for three weeks — which is what happens when the freeze horizon and the WAL volume are owned by different dashboards.

Resolution

Find the holder before doing anything else. One query covers all three categories:

SELECT 'open transaction' AS holder, pid::text AS what, age(backend_xmin) AS xid_age
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'prepared transaction', gid, age(transaction) FROM pg_prepared_xacts
UNION ALL
SELECT 'replication slot', slot_name, greatest(age(xmin), age(catalog_xmin))
FROM pg_replication_slots WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL
ORDER BY 3 DESC NULLS LAST;

Whatever tops that list is why vacuum is not helping. Running another vacuum before answering this question is wasted work — three weeks of it, here.

Confirm the slot is genuinely abandoned. active = false means nothing is connected now, not that nothing will reconnect:

SELECT slot_name, plugin, slot_type, database, active, active_pid,
       catalog_xmin, age(catalog_xmin) AS catalog_xmin_age,
       wal_status, safe_wal_size
FROM pg_replication_slots;

Then drop it:

SELECT pg_drop_replication_slot('debezium_orders');

The horizon is released immediately. Freeze the oldest large tables first, rather than running a database-wide VACUUM FREEZE that spends most of its time on tables that are not the problem:

SELECT c.relname, age(c.relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','m') AND n.nspname NOT IN ('pg_catalog','information_schema')
ORDER BY age(c.relfrozenxid) DESC LIMIT 20;

Verification

age(datfrozenxid) falls sharply and keeps falling. In the isolated reproduction, dropping the slot took it from 100,000 to 1 on the next freeze:

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

VACUUM (FREEZE, VERBOSE) now emits a new relfrozenxid line with a large XIDs ahead of previous value. That line is the proof.

The must be vacuumed within warnings stop — because the number went back up, not because anybody suppressed them.

pg_wal shrinks toward max_wal_size after a checkpoint, confirming the two symptoms were one cause.

Autovacuum stops repeating wraparound jobs on the same table.

Prevention

Alert on age(datfrozenxid) per database, well below autovacuum_freeze_max_age. By the time PostgreSQL warns you, the problem is months old.

Alert on the holder query, not only on the age. The age says something is wrong; the holder query says what — and that is the whole difference between three weeks and five minutes.

Alert on inactive slots and on wal_status <> 'reserved':

SELECT slot_name, active, wal_status, age(catalog_xmin)
FROM pg_replication_slots
WHERE NOT active OR wal_status <> 'reserved';

Give every slot a named owner recorded outside the database. This one outlived the team that created it, which is exactly how a slot becomes permanent.

Alert on long-running and idle in transaction sessions — the other two holders, and the ones that arrive fastest.

Alert on pg_prepared_xacts being non-empty unless you genuinely run a distributed transaction manager. Restarting does not clear one.

Read VACUUM VERBOSE output, not its exit status. Both failed runs here exited zero. The information that they had achieved nothing was a line that was not printed.