1. 9

  2. 2

    For PgBouncer there are a few modes it can be run in: session pooling and transaction pooling.

    There is also the statement pooling mode. Which is like transaction pooling mode, but one that doesn’t allow use of multi-statement transactions.

    What you want to more efficiently manage your connections is transaction pooling. Transaction pooling will grant a connection when you run BEGIN; and return the transaction when you COMMIT;

    I don’t know what the intention of the above sentence was. Certainly a pgBouncer instance running in transaction pooling mode will not require you to wrap every statement in a BEGIN-COMMIT block. Every statement in Postgres is already wrapped in a transaction, even simplest ones like SELECT 1;

    Note: with transaction pooling you do need to make sure you turn off prepared statements which Rails turns on by default.

    There are more Postgres features that transaction pooling mode will not let you use properly or at all. SET/RESET, LISTEN, currval()/lastval() are some of the more popular ones. Annoyingly, it’s popular for web frameworks to use lastval() to retrieve the id of the last inserted row (instead of utilizing Postgres’s INSERT … RETURNING …).