Idle in Transaction: The Postgres Sessions Quietly Bloating Your Database

Your database is getting slower and nobody deployed anything. Queries that were instant last week now crawl. You check disk and a table that holds ten thousand live rows is somehow twenty gigabytes on disk. You run VACUUM and it finishes, but nothing shrinks and nothing gets faster.

Somewhere, a connection is sitting perfectly still with a transaction open. It is running no query. It looks idle, and most monitoring will call it idle, which is exactly why you have not looked at it. But it opened a transaction, did one SELECT, and then the application forgot to commit. That open transaction is holding a snapshot of the database as it was minutes or hours ago, and as long as it holds that snapshot, Postgres is not allowed to clean up dead rows anywhere.

The thing wrecking your database is not doing anything. That is the whole trap.

SELECT pid, state, now() - xact_start AS tx_age, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

What "idle in transaction" actually means

In pg_stat_activity, the state column has a few values that people conflate. The distinction is the entire article:

A plain idle session is a connection resting between transactions. Pool it, keep it, no harm. An idle in transaction session issued a BEGIN (or your ORM did), ran something, and then stopped, without COMMIT or ROLLBACK. The transaction is still open. Postgres has no way to know the app is never coming back, so it keeps every guarantee that open transaction is entitled to.

Why one still session bloats the whole database

Postgres uses multiversion concurrency control (MVCC). When you update or delete a row, it does not overwrite or erase it. It writes a new version and marks the old one dead. The old version has to stick around until no transaction could possibly still need to see it. Autovacuum is the background job that later reclaims those dead versions once they are invisible to everyone.

"Invisible to everyone" is decided by the oldest snapshot still in use, the xmin horizon. Every running transaction pins that horizon at the moment it began. Autovacuum will not remove any dead tuple newer than the oldest live snapshot, because that ancient transaction is theoretically still allowed to read the pre-delete state.

So a session that began a transaction an hour ago and went idle holds the horizon an hour in the past. Every row updated or deleted across the whole database in that hour cannot be reclaimed. The dead tuples pile up, tables grow on disk, indexes bloat, and the query planner starts making bad choices because the tables are mostly corpses. This is why the bloat appears on tables the idle session never even touched. It is not a lock on those tables; it is a snapshot that pins cleanup everywhere. If your autovacuum already runs too conservatively, this turns a slow leak into a flood; my write-up on why the default autovacuum settings are too passive covers the other half of that.

The fix, step by step

Step 1: Find the offenders and how old they are

SELECT pid,
       state,
       now() - xact_start  AS tx_age,
       now() - state_change AS idle_for,
       wait_event_type,
       left(query, 60)     AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

The last_query column shows the last statement the session ran before it went idle, which is your single best clue for which piece of application code left the transaction open. A large tx_age is the smoking gun.

Step 2: Confirm this is what is holding the horizon

SELECT max(now() - xact_start) AS oldest_open_tx
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active');

If oldest_open_tx is hours and it lines up with an idle session, that session is your bloat source. You can cross-check against reclaimable rows with SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;, which will be climbing while the horizon is stuck.

Step 3: Terminate the stuck session

SELECT pg_terminate_backend(12345);   -- the pid from step 1

This rolls back the open transaction and drops the connection. It is safe: the transaction never committed, so no durable data is lost. Within moments the xmin horizon advances and autovacuum is free to work again. Do not use pg_cancel_backend here; that cancels a running query, and there is no query running, so it does nothing to an idle-in-transaction session.

Step 4: Stop it recurring with a timeout

Make Postgres enforce a ceiling so a leaked transaction cannot live for hours. This has existed since PostgreSQL 9.6:

-- session or role level, or in postgresql.conf for the whole server
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

After this, any session that sits idle in transaction for 60 seconds is terminated by Postgres automatically, releasing its snapshot. You can also scope it to a specific role with ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s'; so interactive admin sessions keep more slack than your web app.

Verification

Prove the horizon moved and cleanup resumed. Right after terminating the session:

-- should now return no long-lived idle transactions
SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction';

-- dead tuples on the worst table should start dropping after autovacuum runs
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 5;

Watch last_autovacuum update to a fresh timestamp and n_dead_tup fall on the next autovacuum cycle. To reclaim disk that is already bloated, a plain VACUUM marks the space reusable; only VACUUM FULL (which takes an exclusive lock and rewrites the table) or pg_repack actually returns it to the operating system.

What people get wrong

"Idle connections are harmless, just raise the connection limit." This conflates idle with idle in transaction. Raising max_connections gives you room for more stuck transactions, not fewer. If you are hitting connection limits too, the real fix is pooling and short transactions, which I cover in why raising max_connections is the wrong move.

Running VACUUM FULL on a schedule to fight the bloat. This treats the symptom and takes an exclusive lock that blocks your application while it rewrites the table. If an idle transaction is still holding the horizon, the bloat comes straight back. Kill the session first; the vacuum is downstream.

Blaming autovacuum for "not working". Autovacuum is working exactly as designed. It is refusing to remove rows an open transaction can still see, which is the correct and safe behaviour. The bug is in the application that left the transaction open, not in the janitor who is not allowed into the room.

When it is still broken

• The timeout is not firing. Confirm it is actually set with SHOW idle_in_transaction_session_timeout;. A value of 0 means disabled. If you set it per-role, a session connecting as a different role will not inherit it.
• The horizon is held by something other than an idle transaction. A long-running active query, an abandoned replication slot, or a prepared (two-phase) transaction also pins xmin. Check SELECT slot_name, active FROM pg_replication_slots; and SELECT * FROM pg_prepared_xacts;. An inactive replication slot is a classic silent horizon-holder.
• Find the code that leaks it. The usual culprit is a framework or ORM that opens a transaction at the start of a request and only commits on the happy path, so any early return or unhandled exception leaves it open. Ensure every request path commits or rolls back, ideally in a finally or middleware. Connection-pool leaks under load show a similar shape; the QueuePool exhaustion write-up walks through the pooling side of the same mistake.
• You are close to a bigger problem. If the horizon has been pinned for a very long time, you can march toward transaction ID wraparound. If Postgres starts warning about that, stop and read the transaction ID wraparound runbook before doing anything else.