Troubleshoot autovacuum blockers and wraparound risk

This guide answers one question: what is stopping vacuum from removing dead tuples?

Use it when autovacuum is running but bloat keeps growing. That combination, vacuum active and cleanup not happening, has a small, well-defined set of causes, and this guide checks all of them.

The mechanism, in one paragraph

Vacuum can't remove a row version that any open snapshot might still need. The oldest such snapshot across the whole server is the xmin horizon. Anything holding that horizon back, whether an open transaction, a prepared transaction, or a replication slot retaining WAL, freezes cleanup for every table, no matter how often autovacuum runs or how aggressively you tune it. Dead tuples accumulate, bloat grows, and transaction ID age climbs toward wraparound.

This is why tuning autovacuum harder doesn't help here. The problem isn't throughput. It's permission.

Symptoms this guide explains

Bloat growing while autovacuum runs normally
tuples dead but not removed persistently non-zero in Monitor autovacuum
Oldest backend xmin flat rather than advancing
Transaction ID age climbing steadily
Anti-wraparound or failsafe vacuums appearing

Before you start

Enable Sessions data and Remaining transactions. See Use the troubleshooting guides.

Come here from Monitor autovacuum once you confirm xmin isn't advancing. If xmin is advancing, you have a throughput problem, and that guide is the right one.

How this guide is organized

Tab	The question it answers
Emergency autovacuum and wraparound	How urgent is this?
Autovacuum blockers	What specifically is holding the horizon?

Under those two tabs, the guide breaks the analysis into named sections:

Section	Covers
Oldest active transaction visibility	Whether the guide can see the transactions holding the horizon, and what to enable if it can't.
Primary server long running transactions	Blocking transactions running on the primary.
Read replica long running transactions	Blocking transactions running on a replica, which hold back the primary when `hot_standby_feedback` is on.
Orphaned prepared transactions	Two-phase commit transactions left unresolved.
Replication lag	Standbys falling behind and holding the horizon.
Inactive replication slots	Slots retaining WAL with no consumer.

The guide's banners name the responsible object directly. For example, "Process ID N is running the transaction ID X preventing autovacuum from cleaning up the dead tuples" ties a specific PID to the blocked horizon.

The four causes

Every blocker falls into one of these categories. Work down the list. They're ordered by how often they're the answer and how easily you can resolve them.

Cause	Typical signature	Persists across restart
Long-running transaction	A PID with a very old `xact_start`, often `idle in transaction`	No
Orphaned prepared transaction	`max_prepared_transactions` above 0, entries in `pg_prepared_xacts`	Yes
Inactive replication slot	A slot with `active = false` and an old `xmin`	Yes
Replication lag with `hot_standby_feedback`	Replica queries holding back the primary	No

Orphaned prepared transactions and inactive slots survive restarts. If bloat resumed immediately after a restart "fixed" it, look at those two first.

Walkthrough: bloat that survived a restart

Assess urgency. The Emergency autovacuum and wraparound tab shows one database well past 200 million transaction IDs and rising. Not yet critical, but the trend has a hard deadline, because writes stop at wraparound.
Read the banner. The Autovacuum blockers tab reports no long-running transactions, which eliminates the most common cause. It flags that max_prepared_transactions is greater than zero.
Confirm. You query pg_prepared_xacts and find a prepared transaction from several days earlier, left behind by an application that crashed partway through a two-phase commit. Because prepared transactions survive restarts, the earlier restart didn't clear it, which explains why the problem came straight back.
Resolve. You connect to the database that created it, as its owner, and issue ROLLBACK PREPARED for that gid.
Verify. Back in Monitor autovacuum, the oldest backend xmin starts advancing again. Over the following hours, autovacuum works through the backlog and the bloat ratio falls.

Tab reference

Emergency autovacuum and wraparound

How close each database is to triggering emergency autovacuum or wraparound protection. This is your urgency signal: it tells you whether you have weeks to plan or hours to act.

PostgreSQL has roughly 2 billion usable transaction IDs. As age climbs, it escalates on its own, anti-wraparound vacuums first, then the failsafe at vacuum_failsafe_age (1.6 billion by default), which abandons cost throttling entirely. If the failsafe is engaged, treat it as an incident: the next stage is refusing writes.

Set a metric alert on maximum used transaction IDs so you learn about this from monitoring rather than from an outage.

Autovacuum blockers

Lists active blockers with recommended mitigations. If none are found, the guide says so explicitly, which is itself useful, because it redirects you back to Monitor autovacuum to treat this as a throughput problem.

Long-running transactions

This guide separates these transactions into Primary server long running transactions and Read replica long running transactions, because the mitigation differs. A blocker on the primary server is local. A blocker on a replica server reaches the primary server through hot_standby_feedback, so you have to go to the replica server to clear it. Before either, check Oldest active transaction visibility. If the guide can't see the transactions holding the horizon, the sections below look empty even when a blocker exists. Transactions that exceed the guide's threshold, two hours by default, are treated as long-running. The chart separates connection duration (collection_time - backend_start) from transaction duration (collection_time - xact_start), both from sampled pg_stat_activity. Transaction duration is the one that blocks cleanup; long connection duration alone is normal with pooling. Sessions sitting idle in transaction are the classic case: doing nothing, blocking everything.

Immediate mitigation. Cancel the running statement first. It's the less disruptive of the two options, because the session and its transaction survive:

SELECT pg_cancel_backend(<pid>);

If the session is idle in a transaction, or canceling doesn't release the resource, terminate the whole backend:

SELECT pg_terminate_backend(<pid>);

Caution

Terminating a backend rolls back its open transaction. Confirm what the session is doing before you end it. The PID might belong to a long-running migration or a batch job that restarts from the beginning.

Guardrails so it doesn't recur. Set limits on how long sessions and transactions can run:

Parameter	What it bounds	Notes
`statement_timeout`	A single statement	The broadest safety net. Set per role or per session rather than server-wide if some jobs legitimately run long.
`idle_in_transaction_session_timeout`	A session sitting idle with an open transaction	Defaults to `0` (never). This setting most often prevents blocked cleanup. `300000` (5 minutes) is a common starting point.
`idle_session_timeout`	A session idle with no transaction	PostgreSQL 14 and later. Reclaims connection slots from abandoned clients.
`transaction_timeout`	Total transaction duration	PostgreSQL 17 and later.

For a durable fix, use index tuning and EXPLAIN (ANALYZE, BUFFERS) to shorten the transactions that legitimately run long, and split large batch transactions into smaller ones.

Note

A long-running transaction on a read replica with hot_standby_feedback = on holds back the xmin horizon on the primary. If the primary shows a blocked horizon with no local culprit, check the Read replica long running transactions section. To populate it, enable metrics.collector_database_activity on the replica and stream its Sessions data to Log Analytics.

Orphaned prepared transactions

Prepared transactions are part of two-phase commit. A prepared transaction holds its XID and locks until you explicitly commit or roll back it, and survives server restarts. If a client crashes or disconnects between PREPARE TRANSACTION and its resolution, the transaction is orphaned and blocks freezing and cleanup indefinitely.

The guide checks whether max_prepared_transactions is greater than 0. To confirm, check Advisor recommendations under Flexible server > Settings, or query directly:

SELECT gid, prepared, owner, database
FROM pg_prepared_xacts
WHERE prepared <= NOW() - INTERVAL '1 hour';

Resolve by connecting to the database that created the transaction, as its owner or a superuser:

ROLLBACK PREPARED 'gid';   -- or, if the transaction should complete
COMMIT PREPARED 'gid';

Caution

Committing an orphaned prepared transaction applies changes that were staged days or weeks ago. Unless you know it should complete, roll it back.

If you don't use two-phase commit, set max_prepared_transactions to 0 so this can't recur.

Inactive replication slots

A replication slot retains WAL until its subscriber consumes it. That guarantee becomes a liability when the subscriber stops consuming: the slot holds the xmin horizon and retains WAL indefinitely, consuming storage as well as blocking cleanup.

The two types fail differently:

Logical slots bloat catalog tables and retain WAL.
Physical slots bloat user tables, retain WAL, and degrade replication.

List slots and their state:

SELECT slot_name, slot_type, database, active, age(xmin) AS xmin_age
FROM pg_replication_slots
ORDER BY age(xmin) DESC NULLS LAST;

Acting on what you find:

Inactive logical slots that are genuinely unused are safe to drop:
```
SELECT pg_drop_replication_slot('slot_name');
```
Inactive physical slots. Don't drop these manually. Open a support request to investigate and clean up, or drop the read replica if it's no longer needed.

Warning

Dropping a slot that's still in use breaks replication for its subscriber, which might then need a full resynchronization. Confirm a slot is truly abandoned before removing it.

Monitor slots routinely rather than only during incidents. An abandoned slot is silent until storage or bloat forces attention.

Replication lag

Replication lag occurs when a standby falls behind in writing, flushing, and applying changes. By using hot_standby_feedback = on, the replica reports its oldest open transaction to the primary, and the primary doesn't vacuum rows the replica still needs. This setting works as designed, since it prevents query cancellations on the replica, but it transfers the replica's transaction discipline onto the primary's cleanup.

Finding the blocker:

-- On the primary: oldest transaction needed per slot.
SELECT slot_name, slot_type, database, xmin, active
FROM pg_replication_slots
ORDER BY age(xmin) DESC;

-- On the replica: sessions holding an old snapshot.
SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

Cancel or terminate the blocking sessions with pg_cancel_backend() or pg_terminate_backend().

Reducing logical replication lag durably:

Minimize long-running transactions. Set statement_timeout and split large transactions.
Optimize queries and table design with EXPLAIN (ANALYZE, BUFFERS), indexing, and partitioning.
Keep primary and replica at compute parity. An undersized replica can't keep up by definition.
Tune replication parameters, testing in staging first: max_worker_processes, max_replication_slots, max_sync_workers_per_subscription, logical_decoding_work_mem, and max_parallel_apply_workers_per_subscription (PostgreSQL 16 and later).
Avoid REPLICA IDENTITY FULL. Prefer a primary key or unique index with REPLICA IDENTITY USING INDEX. FULL writes every column of every row to WAL.
Distribute workload across multiple slots.
Monitor pg_replication_slots, pg_stat_replication_slots, and pg_stat_replication, and watch the logs for replication errors.
Schedule bulk operations during off-peak times.
Keep statistics current with regular VACUUM and ANALYZE.

What good looks like

Signal	Healthy	Investigate
Oldest backend `xmin`	Advancing with throughput	Flat
Longest open transaction	Minutes	Hours
Sessions `idle in transaction`	None sustained	Any, sustained
`pg_prepared_xacts`	Empty, or resolving quickly	Entries older than an hour
Replication slots	All `active = true`	Any inactive with old `xmin`
Maximum used transaction IDs	Below 200 million	Above 200 million

What this guide can't tell you

Whether terminating a session is safe. The guide identifies the blocker; the impact of ending it is yours to assess.
What a replica is doing. Replica sessions need metrics.collector_database_activity enabled on the replica and its own Sessions data streamed.
How long recovery will take. Once a blocker is cleared, working through accumulated dead tuples can take hours on a badly bloated server.
Whether bloat will reverse on its own. Vacuum reclaims space for reuse but doesn't return it to the filesystem. Badly bloated tables might need VACUUM FULL or pg_repack.
Sub-sample blockers. Session data is sampled; a blocker that appears and clears between samples might not be captured.

Feedback

Was this page helpful?

Last updated on 2026-09-16