A 40 GB table holds about 2 GB of live rows. Autovacuum runs constantly and reclaims nothing, sequential scans have got slower every week, and one session has been idle in transaction for nine days. Explain the chain and the fix.

The first three signals

1. Dead tuple count against live tuple count for the table. If dead tuples are high and not falling while autovacuum is running, vacuum is being prevented rather than failing.
2. The age of the oldest transaction in the system, which is the single number that explains it.
3. Table size against live data size, which quantifies the damage: 40 GB holding 2 GB means every sequential scan reads twenty times the pages it needs to.

The diagnosis

Under multi-version concurrency control, an UPDATE does not overwrite a row. It writes a new version and leaves the old one in place, marked with the transaction that superseded it, so that transactions holding older snapshots still see a consistent view. Deleted rows work the same way: the row is marked, not removed.

Vacuum reclaims those dead versions - but only the ones no running snapshot could still need. The boundary is the oldest snapshot in the system. A session that has been idle in transaction for nine days is holding a snapshot nine days old, so:

• The boundary cannot advance past that snapshot.
• Every dead version created in the last nine days is still potentially needed.
• Autovacuum runs, examines the table, and correctly removes almost nothing while reporting success.

The bloat is not a vacuum failure. It is vacuum doing exactly what it must, in the presence of a transaction nobody closed.

The misleading clue

Autovacuum activity looks healthy, so attention goes to tuning it - autovacuum_vacuum_cost_delay, worker counts, thresholds. None of that helps, because the limit is not vacuum's throughput but its permission. The second misleading clue is that the idle session is doing nothing at all, which makes it look harmless on a connection dashboard: it holds no locks a blocked query would report and consumes no CPU.

The fix

1. Terminate the idle session, then watch dead tuples fall on the next autovacuum pass.
2. Prevent the recurrence with a timeout, not a policy document: idle_in_transaction_session_timeout, available since Postgres 9.6 (2016), ends sessions that hold a transaction open while idle, and a statement_timeout bounds the other direction. Most occurrences come from a connection pool or an interactive session where BEGIN was issued and autocommit was off. Choose the value deliberately: a few minutes is safe for an application pool and will terminate a long-running analyst session, which is the trade-off to make explicit rather than discover.
3. Reclaim the space deliberately. Plain vacuum returns space for reuse inside the table and does not shrink the file. Returning 38 GB to the filesystem needs a rewrite - VACUUM FULL under an exclusive lock, or an online repack tool if the table cannot be locked.
4. Alert on oldest transaction age (minutes, not days) and on dead-tuple ratio per large table.

The second-order risk

The same stalled boundary blocks transaction-id freezing. If the oldest snapshot never advances, transaction ids are consumed and never reclaimed, and the engine will eventually refuse writes to protect itself against wraparound - converting a performance problem into a full stop. The nine-day session is not just making scans slow; it is spending a budget with a hard floor.

When this is the wrong answer

If there is no long-running transaction and dead tuples still accumulate, the cause is different: autovacuum genuinely cannot keep up with the write rate, there are too few workers for the number of large tables, or a replication slot or prepared transaction is holding the boundary instead. Check for abandoned replication slots specifically - they hold the horizon for the same reason and no session list will show them.