EDB PostgreSQL Professional: Anti-wraparound vacuum that never finishes

Anti-wraparound vacuum that never finishes

The primary has been up 412 days. Monitoring fires: age(datfrozenxid) for the orders database is 1.94 billion while autovacuum_freeze_max_age is 200 million, and anti-wraparound workers have been cycling for nine days without finishing. pg_stat_activity shows three idle in transaction sessions from a reporting tool, open 30–60 hours each. pg_prepared_xacts lists one two-phase transaction prepared 71 hours ago by a JDBC driver and never resolved. Two replication slots report active = false: a physical slot for a standby decommissioned in March, and a logical slot feeding a CDC consumer. The largest relation is orders_2024_03, a 1.6 TB monthly partition, and pg_stat_progress_vacuum shows it restarting from the heap scan phase repeatedly. autovacuum_max_workers = 3, autovacuum_vacuum_cost_delay = 20 ms, max_connections = 800. Transaction ID consumption over the last week works out to roughly 100 xids/s, leaving about 19 days to the 2.1 billion shutdown point.

1. Explain why the anti-wraparound workers cannot reduce the age, and give the order in which you clear the blockers.

Read the worked answer

VACUUM can freeze tuples and raise relfrozenxid, but only up to the oldest xmin that any backend or slot still needs. Three independent things pin that horizon here:

the 71-hour prepared transaction holds its own xid, so nothing newer can be considered removable;
three idle in transaction sessions each hold a snapshot, forcing retention of every row version since they started;
two inactive slots pin progress: the physical slot through restart_lsn, the logical slot through confirmed_flush_lsn and catalog_xmin.

Order matters because the first two are cheap and reversible while the slots need an owner decision:

SELECT gid, prepared, owner, database FROM pg_prepared_xacts; then ROLLBACK PREPARED '<gid>' in the right database after confirming with the application team. A two-phase transaction that old is almost always an orphan.
Terminate the reporting sessions and fix the pooler so it cannot repeat - otherwise the horizon re-pins minutes later.
SELECT slot_name, active, restart_lsn, xmin, catalog_xmin FROM pg_replication_slots; Drop the decommissioned physical slot now. For the CDC slot, restart the consumer or advance it; pg_replication_slot_advance() discards changes, so get sign-off or plan a resync of the downstream table.
Only then run VACUUM (FREEZE, VERBOSE) on the oldest partitions and check in pg_stat_progress_vacuum that it moves past the phase where it previously restarted.

If you run step 4 first you will burn another night proving the same point.

2. Given 19 days to the shutdown point and a 1.6 TB partition, how do you drain the age to a safe margin without taking a weekend outage?

Read the worked answer

First sanity-check the arithmetic: about 207 million xids remain over 19 days, which is roughly 100 transactions/s. That is not a runaway cluster - the risk is the wall-clock time of freezing a cold 1.6 TB partition, not the rate. So a staged, partition-parallel plan works.

Rank by urgency: SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' ORDER BY 2 DESC LIMIT 20; The 40 static historic partitions are the easy win: no writes, no dead tuples, a freeze is close to a sequential read.
Parallelise across partitions, never expect parallelism inside one: vacuumdb --freeze --jobs=6 --verbose -t 'orders_2024_0*' -d orders. VACUUM of a single table does not use parallel workers for the heap scan; six partitions in six sessions gives the speedup.
SET vacuum_cost_delay = 0 in those manual sessions to remove throttling, but schedule the write-active recent partitions in low-traffic hours so you do not add I/O to the order path.
Measure after each batch with age(datfrozenxid) on pg_database and per table. If the number does not drop, a blocker is still present - recheck the horizon instead of rerunning.
Keep autovacuum enabled throughout and do not raise autovacuum_freeze_max_age to buy room; that just moves the deadline and grows pg_xact.

Target getting the cluster below 1.0 billion with at least a week of headroom before you declare the incident closed.

3. Which configuration and process changes prevent a repeat, and what will you monitor?

Read the worked answer

The failure was process as much as configuration, so fix both layers.

Configuration:

idle_in_transaction_session_timeout = '5min' and idle_session_timeout on the pooler's roles; statement_timeout for the reporting role so a 60-hour open transaction cannot happen again.
Cap WAL held by abandoned slots with max_slot_wal_keep_size on the primary so a dead consumer cannot fill the disk, and require active slots to be revisited in the decommission checklist.
Raise autovacuum_max_workers above 3: with three workers it is easy for anti-wraparound work to be starved behind ordinary table vacuums.
For the very large partition sets, set a lower per-table autovacuum_freeze_max_age (storage parameter) so each freeze pass covers a smaller, cheaper chunk rather than one 1.6 TB sweep at the last minute.

Monitoring and process:

Alert on age(datfrozenxid) at 1.0 billion (warning) and 1.5 billion (page), not at the 200 million trigger point, and show per-table age(relfrozenxid) so you know which partition is leading.
Alert on any slot with active = false for more than an hour, and on prepared transactions older than 15 minutes.
Rehearse the runbook: a dry run of vacuumdb --freeze --jobs on a restored copy tells you the real hours-per-terabyte number, which is the figure you actually negotiate the maintenance window with.