What are "idle in transaction" sessions, and why are they bloating my PostgreSQL tables?

query shows the last statement the session ran, which is usually the best clue to which code path left it open.

To check whether it's holding back vacuum:

SELECT pid, backend_xmin, age(backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

Also check pg_replication_slots and pg_prepared_xacts: an abandoned replication slot or prepared transaction pins the horizon in the same way.

Kill it safely

SELECT pg_terminate_backend(12345);

This is safe for data: the transaction never committed, so it's rolled back. The client gets a connection error, which a correct application retries. Within seconds the horizon moves forward and autovacuum can clean up.

Prevent it with a timeout

Let PostgreSQL do this for you:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

You can also set it per role, for example tighter for the web app and looser for the analytics user:

ALTER ROLE webapp SET idle_in_transaction_session_timeout = '10s';

Pair it with statement_timeout for queries that are running but running away, and lock_timeout on migrations so a blocked ALTER gives up instead of stalling all traffic behind it:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;

Fix the code

The timeout is a safety net. The real fix is keeping transactions short and focused on the database:

# Bad: transaction held open while waiting on the network
with conn.transaction():
    order = create_order(conn, cart)
    charge_card(order)          # 3 seconds of HTTP
    mark_paid(conn, order)

# Better: do slow I/O outside the transaction
order = create_order(conn, cart)   # short tx
charge = charge_card(order)        # no tx open
mark_paid(conn, order, charge)     # short tx

Make sure every error path rolls back before the connection goes back to the pool. Most pools have a "reset on return" option; turn it on.

Checklist

1. Query pg_stat_activity for idle in transaction and old backend_xmin.
2. Terminate offenders, then find the code path from application_name and the last query.
3. Set idle_in_transaction_session_timeout globally or per role.
4. Keep network calls and user think-time out of transactions.

Best database for a web app: how to choose

How to choose between PostgreSQL, MySQL, SQLite and MongoDB for a web app, where Redis and a search engine fit, a decision table by app type, and when the PostgreSQL default is wrong.

#data-modeling#scalability#schema-design