How do I stop idle in transaction sessions?

Set idle_in_transaction_session_timeout to a duration no legitimate transaction would ever reach, and the server ends those sessions itself. It is disabled by default, which is why nothing has been ending them so far. Setting it per role or per database, rather than globally, is what keeps a migration tool that really does hold long transactions from being killed alongside the application.

Picking the right timeout, because there are four

They are frequently confused and they fire on different conditions.

The idle-in-transaction one ends a session that has an open transaction and is waiting on the client. This is the one you almost always want, and it aborts the transaction and drops the connection.

idle_session_timeout ends a connection that is idle with no transaction open. It is a different problem, mostly a pooling one, and using it in place of the first will not touch a single session that is holding a snapshot.

statement_timeout bounds one statement. A session that runs a fast statement and then goes quiet inside a transaction is never touched by it, which is how servers with a statement timeout still accumulate held locks.

From PostgreSQL 17 there is also transaction_timeout, which bounds the whole transaction regardless of whether the session is idle or working. It is the setting people were describing when they asked for the first one, and it closes the gap where a transaction stays technically busy forever.

The reason a timeout is the second fix, not the first

Ending these sessions is a backstop. Something is creating them, and in nearly every case it is an application or a pool that opens a transaction before it knows whether it needs one, or a framework that treats a checked-out connection as transactional by default. A timeout turns a silent held lock into a visible error in the application log, which is an enormous improvement and is still not a fix.

Killing them by hand in the meantime is safe: the transaction has done nothing since its last statement, so the rollback costs nothing and the client sees a broken connection it should already be prepared for. What is not safe is assuming the pool will notice.

Connection pooling and PgBouncer covers which pooling mode makes this structurally impossible and what that mode costs you in return. For what one of these sessions is actually holding, and why the vacuum consequence people quote first is conditional while the lock consequence never is, see idle in transaction.