What are advantages of using transaction pooling with pgbouncer?
I'm having trouble finding a good summary of the advantages/disadvantages of using pgbouncer for transaction pooling vs session pooling.
Does it mean that a transaction heavy workload is somehow better load balanced? Is it to prevent as many connections being required to connect from pgbouncer to the database?
Answer
Transaction-level pooling will help if you have apps that hold idle sessions. PgBouncer won't need to keep sessions open and idle, it just grabs one when a new transaction is started. Those idle sessions only cost you a pgbouncer connection, not a real idle Pg session with a backend sitting around wasting memory & synchronisation overhead doing nothing.
The main reason you'd want session pooling instead of transaction pooling is if you want to use named prepared statements, advisory locks, listen/notify, or other features that operate on a session level not a transaction level.