Anti-Wraparound Vacuum and Plan Impact #

At 02:00 every query on the system slows by a factor of five. No deployment happened, no plan changed, and the slow queries have nothing in common except that they all read from disk. pg_stat_activity shows a vacuum that has been running for six hours with (to prevent wraparound) after the table name, and it cannot simply be cancelled.

Ordinary autovacuum behaviour is covered in autovacuum and plan stability. This page is about the emergency variety and what it does to the plans running alongside it.

The Condition #

PostgreSQL identifies row versions with 32-bit transaction IDs, which wrap around. To keep old rows visible, tuples older than vacuum_freeze_min_age are eventually frozen — marked as visible to everyone regardless of transaction ID. Ordinary vacuums do some of this opportunistically; when a table’s oldest unfrozen transaction ID gets within autovacuum_freeze_max_age (default 200 million) of the current one, autovacuum launches an anti-wraparound vacuum for that table.

It differs from an ordinary vacuum in ways that matter operationally:

The plan impact is indirect but severe. The vacuum reads and dirties pages at a high rate, so shared buffers churn: plans that relied on a warm cache now read from storage, index scans that were microseconds become milliseconds, and estimated costs — which assume effective_cache_size worth of caching — stop matching reality. Nothing in the plan changed; everything the plan assumed did.

The cliff, and how it is reached A timeline. Transaction age grows steadily as the table is written to. Ordinary vacuums freeze little because vacuum_freeze_min_age is high and the visibility map lets pages be skipped. When age reaches autovacuum_freeze_max_age an anti-wraparound vacuum starts, scanning the entire table and saturating storage for hours. age 0 table written age 50M vacuums skip pages age 150M debt accumulating age 200M anti-wraparound starts hours later cache cold, plans slow the work is the same; the timing is what hurts

Annotated Evidence #

Identifying it while it runs:

SELECT pid, now() - xact_start AS runtime, query, wait_event_type, wait_event
FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
  pid  |  runtime  |                         query                          | wait_event_type | wait_event
-------+-----------+--------------------------------------------------------+-----------------+------------
 82104 | 06:12:44  | autovacuum: VACUUM public.events (to prevent wraparound)| IO              | DataFileRead
-- "(to prevent wraparound)" is the marker; six hours in and still reading

How much freeze debt each table carries:

SELECT relname,
       age(relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class WHERE relkind = 'r' ORDER BY age(relfrozenxid) DESC LIMIT 5;
   relname   |  xid_age  |  size
-------------+-----------+---------
 events      | 198412004 | 412 GB    ← 1.6 million transactions from forcing a scan of 412 GB
 order_lines |  84120044 |  38 GB
 orders      |  12048820 |  14 GB

What it does to an unrelated query:

-- same plan, during the anti-wraparound vacuum
Index Scan using orders_pkey on orders  (actual time=0.412..84.208 rows=1 loops=1)
  Buffers: shared hit=1 read=4
-- and normally
Index Scan using orders_pkey on orders  (actual time=0.018..0.021 rows=1 loops=1)
  Buffers: shared hit=5
-- identical plan; the buffers moved from hit to read because the cache was evicted
Three signals during the storm Three items are annotated. The phrase to prevent wraparound in the autovacuum worker's query identifies the emergency vacuum. A transaction ID age approaching the freeze max age on a large table predicts it. Unchanged plans whose buffers move from hit to read show the cache eviction that slows everything else. query: … (to prevent wraparound) the emergency vacuum age(relfrozenxid) near 200M on a big table the next one, predicted same plan, buffers hit → read cache evicted by the scan none of the slow queries have a plan problem

Step-by-Step Resolution #

  1. Confirm what is running. (to prevent wraparound) in pg_stat_activity. Do not cancel it: it will restart, and the underlying deadline does not move.

  2. Let it finish faster, not slower. Temporarily raise the worker’s throughput with ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 4000 and reload; the vacuum picks up the new limit. Counter-intuitively, finishing sooner does less total damage than throttling it across a whole business day.

  3. Check for blockers. Long transactions, stale replication slots and prepared transactions prevent freezing, so the vacuum may run to completion without advancing relfrozenxid at all. Look at pg_stat_activity, pg_replication_slots and pg_prepared_xacts.

  4. Rank the remaining debt with the age(relfrozenxid) query and address the largest tables before they reach the threshold.

  5. Freeze proactively and on your own schedule. VACUUM (FREEZE) during a maintenance window, on the largest tables, converts an unpredictable emergency into planned work. For partitioned data, freeze each partition once it stops being written.

  6. Lower vacuum_freeze_min_age on large, slow-changing tables so ordinary vacuums freeze as they go and the debt never accumulates.

  7. Monitor the age. An alert at 50% of autovacuum_freeze_max_age gives months of warning; an alert at 90% gives none.

Planned freezing against the emergency An anti-wraparound vacuum at the default cost limit takes about nine hours during business traffic. Raising the cost limit brings it to three hours. Freezing the table in a maintenance window takes four hours but affects nobody. Freezing partitions as they go cold takes minutes at a time and never affects the live workload. emergency, default limit ~9 h, in business hours emergency, raised limit ~3 h, in business hours planned VACUUM FREEZE ~4 h, in a window freeze per cold partition minutes, continuously the total work is similar; when it happens is the variable

Before and After #

-- BEFORE: freeze debt left to accumulate
autovacuum: VACUUM public.events (to prevent wraparound)   06:12:44 and counting
unrelated point lookups: 0.02 ms → 84 ms

-- AFTER: partitions frozen as they go cold, vacuum_freeze_min_age lowered
max age(relfrozenxid) across all tables: 41,204,880 — well below the threshold

Why the deadline cannot be negotiated #

If a database’s oldest unfrozen transaction ID approaches wraparound without being frozen, PostgreSQL first warns, then refuses new transactions entirely to prevent data loss. That single-user recovery mode is one of the few genuinely alarming states a cluster can reach, and it is why anti-wraparound vacuums ignore the settings that would otherwise defer them.

The practical reading is that the work is compulsory and only its scheduling is under your control. A team that treats age(relfrozenxid) as a monitored metric and freezes large tables deliberately never meets an anti-wraparound vacuum at all. A team that does not will meet one at the worst possible time, because the tables that accumulate debt fastest are the ones being written to most, which are the ones the business depends on.

There is one more subtlety on very large tables: freezing is per relation, so partitioning is a freeze strategy as well as a query strategy. A 412 GB table has a single deadline for all of it; the same data in fifty partitions has fifty small, independent deadlines, and forty-nine of those partitions are cold and can be frozen permanently in advance.

What the plans look like afterwards #

Once the vacuum finishes, two things change and one does not. The cache refills over minutes to hours depending on the workload, so timings return to normal gradually rather than instantly — which is worth knowing before concluding that a fix did not work. Statistics may also have been refreshed, since an anti-wraparound vacuum on a table that also qualified for analyze does both, so some plans genuinely change at that moment for a reason unrelated to freezing.

What does not change is cost estimation. effective_cache_size is a planner parameter, not a measurement, so the planner priced index scans as though the cache were warm throughout the storm. That is the right behaviour — plans should be chosen for steady state rather than for a transient — but it means the plans running during the vacuum were the plans for a machine that temporarily did not exist. Re-measuring anything during an anti-wraparound vacuum produces numbers that describe the vacuum, not the query.

Common Pitfalls #

Cancelling the vacuum. It restarts, having made no progress. Diagnostic signal: repeated cancellations of the same table. Fix: let it run, raise the cost limit to shorten it.

Throttling it hard. It spreads the damage over a longer period. Diagnostic signal: a slow vacuum lasting all day. Fix: raise the limit so it finishes.

Missing a blocker. The vacuum runs but relfrozenxid does not advance. Diagnostic signal: the age unchanged after a complete vacuum. Fix: find the long transaction, slot or prepared transaction.

Disabling autovacuum to avoid it. Anti-wraparound runs anyway, with more debt. Diagnostic signal: autovacuum = off in the configuration. Fix: never disable it; tune it.

Frequently Asked Questions #

What is an anti-wraparound vacuum? #

A vacuum autovacuum launches when a table’s oldest unfrozen transaction ID approaches the wraparound limit. It runs even when autovacuum is disabled, scans every unfrozen page, and cannot be skipped, because the alternative is the cluster refusing new transactions.

Can I cancel an anti-wraparound vacuum? #

You can, but it restarts immediately and the deadline does not move, so nothing is gained. Raising autovacuum_vacuum_cost_limit so it finishes sooner is usually the better response.

Why do unrelated queries slow down during one? #

The vacuum reads and dirties a large fraction of the table, evicting other data from shared buffers. Plans do not change, but buffer hits become storage reads, so execution times rise across the system.

Up: Autovacuum and Plan Stability