Idle Transactions

Impact

Transactions left open can hold locks, blocking other queries, and can prevent VACUUM (including autovacuum) from cleaning up dead rows, leading to index or table bloat.

Common Causes

• Application bugs

  If there is an error (either in the application or in the database) while processing a query, the application must handle that correctly and roll back the transaction (and possibly close the connection). If it fails to do that, the idle transaction may be left open as long as the application keeps running.

  If your application sets application_name on its connections or uses a distinct role, you can find more details by querying pg_stat_activity. Review the code path that triggered the idle transaction (you can also check the last query on the connection in pg_stat_activity), and make sure that any errors are handled appropriately (e.g., with rescue or catch) and roll back the transaction.

• Interactive sessions

  If someone is accessing the database directly in an interactive session and they manually open a transaction with BEGIN or START TRANSACTION, they must close it with COMMIT or ROLLBACK, or disconnect entirely (which will roll back the transaction).

Solution

For an immediate solution, you can kill a connection with an idle transaction with SELECT pg_terminate_backend('<session_pid>');. Note that this only treats the symptom: unless this is due to a one-off manual session, you may run into this problem again.

You can also change the idle_in_transaction_session_timeout setting to automatically roll back idle transactions older than a specified threshold.