Tune down idle_in_transaction_session_timeout
Currently, we timeout idle transactions after 1 minute. This allows a process to open a transaction and perform e.g. unrelated IO (or just sleep) for up to one minute before the transaction is cancelled. Since idle transactions basically consume one backend connection slot from pgbouncer, this becomes a problem.
I'd propose we decrease idle_in_transaction_session_timeout
to 15s
and step it down further after observing the impact (separate follow-up).
This in turn makes it harder to perform IO (gitaly, API calls etc.) while holding an open transaction - which is an anti-pattern. By tuning the above parameter down, we'd be able to observe pain points and direct our efforts to fixing those transactions.
The current rate of transactions aborted because of idle transaction is rather low, as expected: https://log.gitlab.net/goto/2a3a1508a2d03d96ba61c897fcf1c367