One idle-in-transaction session: how it stops VACUUM on every table

A table keeps growing even though autovacuum runs every minute. The log says it ran. n_dead_tup doesn't go down. VACUUM VERBOSE prints a line that's easy to skim past: 79867 are dead but not yet removable. Somewhere in the cluster something still needs to see those old row versions, so VACUUM has to keep them, on every table in the database, for as long as that something stays open.

The usual suspect is a session sitting idle in transaction. But not every idle transaction holds VACUUM back, and some of the worst offenders aren't sessions at all. We tested twelve possible holders on PostgreSQL 18.6, each the same way: open the holder, update 100,000 rows of a 1M-row table from another connection, run VACUUM (VERBOSE) and read how many dead rows it could remove. Then we measured what a single forgotten session does to a table's size in three minutes.

Why VACUUM has to wait

An UPDATE in Postgres writes a new row version and leaves the old one in place. The old version can only be removed once no transaction could still need it. VACUUM works this out from the xmin horizon: the oldest transaction id that any snapshot in the database, or anything else Postgres must respect, still cares about. Row versions that died after that point are "dead but not yet removable". One old snapshot pins the horizon for the whole database, so a session that only ever touched one small table still stops cleanup of the big one.

Which idle sessions actually block it

The common advice is "open BEGIN; SELECT 1; in psql and walk away, and you'll block VACUUM". On a default setup that turned out not to be true:

In READ COMMITTED, the default isolation level, each statement takes its own snapshot and gives it up when the statement ends. A transaction that has only read something and is now idle holds no snapshot, and pg_stat_activity shows both backend_xmin and backend_xid as NULL. It still holds locks, which matters for DDL, but it doesn't hold back VACUUM.

It does once the transaction has written anything, even a single row in an unrelated table, or called txid_current(). From then on it has a transaction id (backend_xid), and every row version that died after it started has to stay until it ends. That's the realistic case: an app that runs an UPDATE, then waits on an HTTP call or a user, or a worker that crashed between a write and the commit while its connection lingered in a pool.

A REPEATABLE READ or SERIALIZABLE transaction holds its snapshot until it ends, so it blocks even if all it did was SELECT 1. That's what pg_dump uses, and what many reporting tools and ORMs' "read-only transaction" settings use. An open cursor in READ COMMITTED keeps its snapshot too, which catches out code that iterates a large result set slowly with a server-side cursor.

Sessions in another database of the same cluster didn't block anything here, even with a transaction id: since PostgreSQL 14 the horizon for ordinary tables is calculated per database. And a prepared transaction (PREPARE TRANSACTION, used by two-phase commit in XA transaction managers and some queue libraries) blocked VACUUM with no session attached at all. It survives restarts and stays until someone runs COMMIT PREPARED or ROLLBACK PREPARED. You can only create them if max_prepared_transactions is above 0, which isn't the default; if yours is, check pg_prepared_xacts.

Holders that aren't on the primary

Two more come from replication. We rebuilt a streaming replica with a physical replication slot and hot_standby_feedback = on, the setting people turn on to stop long replica queries being cancelled:

With feedback on, a long query on the replica holds back VACUUM on the primary, as if it were running there. That part is documented; the next one surprises people. When the replica is shut down, its slot keeps the last xmin it reported. A replica decommissioned without pg_drop_replication_slot() blocks cleanup on the primary indefinitely, and its slot retains WAL too, so the disk fills from two directions. A logical slot (from Debezium, a CDC tool or a logical replica) only holds back the system catalogs: user tables were cleaned normally, but 6,000 dead pg_attribute rows from temporary tables stayed until the slot was dropped. An unread logical slot is still a problem because of the WAL it keeps, just not for your tables' bloat.

What one session costs in three minutes

To see the size effect: a 1M-row table with an index on updated_at, autovacuum set to run after 1% of rows change, and 1,000 single-row updates a second for 180 seconds. Once with nothing else open, once with one session that ran an UPDATE on another table and then sat idle:

Autovacuum did its job three times and accomplished nothing. Each run logged the reason:

"140394 XIDs old" is the useful number: the horizon was stuck at one transaction while 140,000 newer ones committed. Then we terminated the session. The next VACUUM removed all 179,690 dead rows, and the table and indexes stayed at 102 MB. Plain VACUUM makes space reusable inside the files; it doesn't give it back to the operating system. That table now carries 45% of slack it will slowly fill again, and after a session stuck over a weekend on a busy table, the slack can be bigger than the data. Getting the space back means VACUUM FULL (an exclusive lock for the whole rewrite) or pg_repack. Preventing it is much cheaper.

Find the holder

One query lists everything that can pin the horizon, sorted by how far behind it is: sessions with a transaction id or a snapshot, prepared transactions, and replication slots.

Exclude your own session (pid <> pg_backend_pid()): the query's own snapshot inherits the old horizon and would otherwise show up at the top. Here it is in Pilotbase with three holders open at once: a forgotten prepared transaction, an unread logical slot and a "billing-worker" connection idle in transaction, all 20,000 transactions behind.

Then clear it: SELECT pg_terminate_backend(pid) for a session (the client gets a FATAL error and its transaction rolls back), ROLLBACK PREPARED 'gid' for a prepared transaction once you know what it was, pg_drop_replication_slot('name') for a slot nobody reads. If the holder is a replica query with feedback on, end it on the replica. lockTree reads the same pg_stat_activity view and is the quicker picture when the stuck session is also blocking other queries on locks.

Stop it from happening: two timeouts

idle_in_transaction_session_timeout ends a session that has sat idle inside a transaction for longer than the limit. transaction_timeout, new in PostgreSQL 17, ends any transaction that has been open longer than the limit, busy or not. We set each to 5 seconds and ran two kinds of transaction:

The idle session was terminated on the server at 5 seconds (FATAL: terminating connection due to idle-in-transaction timeout); the application only found out on its next statement, at 8 seconds, as an exception and a closed connection. A transaction that keeps issuing short queries never trips the idle timeout or statement_timeout; only transaction_timeout catches it. That's the batch job that loops over a million rows in one transaction.

Set them per role rather than server-wide, because the right number depends on who's connecting:

A server-wide transaction_timeout would kill a long pg_dump and any migration that runs in one transaction, so keep it to roles that only do short work. For the holders a timeout can't reach (replicas with feedback, slots, prepared transactions), run the holder query on a schedule and alert when xid_age passes a few million or open_for passes an hour.

A forgotten session stops cleanup entirely, which is one reason autovacuum can look like it's falling behind. The other is that the defaults start too late and run too slowly for a busy table: autovacuum falling behind measures that side. For the backup side of long transactions, see a pg_dump you have never restored.

Sources

PostgreSQL 18 documentation: routine vacuuming, idle_in_transaction_session_timeout and transaction_timeout, pg_stat_activity, hot_standby_feedback, replication slots, pg_prepared_xacts, PREPARE TRANSACTION, PostgreSQL 17 release notes (transaction_timeout). Measured in October 2026 on PostgreSQL 18.6 with a streaming replica on the same machine, psycopg 3.3 and pgbench.