Why are long-running or idle in transaction sessions harmful in PostgreSQL, and how do you prevent them?

They hold locks and an old snapshot for a long time, and this slows down the whole database. An idle in transaction session has opened a transaction but is not running any query.

Problem 1: locks. An open transaction keeps its locks until it ends. A migration that needs an exclusive lock must wait behind it. Then other queries wait in a queue behind the migration.

Problem 2: an old snapshot. A snapshot is the view of the data a transaction can see. The open transaction keeps an old snapshot. VACUUM is the process that removes dead row versions (old copies of changed or deleted rows). VACUUM cannot remove dead rows that this snapshot might still need. On a busy database, this causes:

• bloat (tables full of dead rows)
• slower queries
• tables that keep growing everywhere, not only the tables the transaction touched

The usual cause is application code that opens a transaction and then does slow work, such as an HTTP call or waiting on a queue. Another cause is code that forgets to commit on an error path.

How to prevent it:

• Keep transactions short, and make external calls outside them.
• Set idle_in_transaction_session_timeout and statement_timeout.
• Watch pg_stat_activity for old xact_start values (the time each transaction started).