Several levels of brutality when rotating connections:
Low memory requirements (2k per connection by default). This is due to the fact that PgBouncer does not need to see full packet at once.
It is not tied to one backend server, the destination databases can reside on different hosts.
Supports online reconfiguration for most of the settings.
Supports online restart/upgrade without dropping client connections.
Supports protocol V3 only, so backend version must be >= 7.4.
Following table list various PostgreSQL features and whether they are compatible with PgBouncer pooling modes. Note that ‘transaction’ pooling breaks client expectations of server and can be used only if application cooperates by not using non-working features.
|Feature||Session pooling||Transaction pooling|
|Startup parameters||Yes 1||Yes 1|
|WITHOUT HOLD CURSOR||Yes||Yes|
|WITH HOLD CURSOR||Yes 2||Never|
|Protocol-level prepared plans||Yes 2||No 3|
|PREPARE / DEALLOCATE||Yes 2||Never|
|ON COMMIT DROP temp tables||Yes||Yes|
|PRESERVE/DELETE ROWS temp tables||Yes 2||Never|
|Cached plan reset||Yes 2||Yes 2|
Startup parameters are: client_encoding, datestyle, timezone and standard_conforming_strings. PgBouncer can detect their changes so it can guarantee they remain consistent for client. Available from PgBouncer 1.1. ↩ ↩2
It is possible to add support for that into PgBouncer. ↩