April 14, 2024
Summary: The PgBouncer is a lightweight connection pooler for PostgreSQL.
Table of Contents
Description
pgbouncer is a PostgreSQL connection pooler. Any target application can be connected to pgbouncer as if it were a PostgreSQL server, and pgbouncer will create a connection to the actual server, or it will reuse one of its existing connections.
The aim of pgbouncer is to lower the performance impact of opening new connections to PostgreSQL.
In order not to compromise transaction semantics for connection pooling, pgbouncer supports several types of pooling when rotating connections:
-
Session pooling
Most polite method. When a client connects, a server connection will be assigned to it for the whole duration the client stays connected. When the client disconnects, the server connection will be put back into the pool. This is the default method.
-
Transaction pooling
A server connection is assigned to a client only during a transaction. When PgBouncer notices that transaction is over, the server connection will be put back into the pool.
-
Statement pooling
Most aggressive method. The server connection will be put back into the pool immediately after a query completes. Multi-statement transactions are disallowed in this mode as they would break.
The administration interface of pgbouncer consists of some new SHOW
commands available when connected to a special “virtual” database pgbouncer.
Major features
-
Several levels of brutality when rotating connections:
-
Session pooling
Most polite method. When a client connects, a server connection will be assigned to it for the whole duration it stays connected. When the client disconnects, the server connection will be put back into pool. This mode supports all PostgreSQL features.
-
Transaction pooling
A server connection is assigned to a client only during a transaction. When PgBouncer notices that the transaction is over, the server will be put back into the pool. This mode breaks a few session-based features of PostgreSQL. You can use it only when the application cooperates by not using features that break. See the table below for incompatible features.
-
Statement pooling
Most aggressive method. This is transaction pooling with a twist: Multi-statement transactions are disallowed. This is meant to enforce “autocommit” mode on the client, mostly targeted at PL/Proxy.
-
-
Low memory requirements (2 kB per connection by default). This is because PgBouncer does not need to see full packets at once.
-
It is not tied to one backend server. The destination databases can reside on different hosts.
-
Supports online reconfiguration for most settings.
-
Supports online restart/upgrade without dropping client connections.
SQL feature map for pooling modes
The following table list various PostgreSQL features and whether they are compatible with PgBouncer pooling modes. Note that “transaction” pooling breaks client expectations of the server by design and can be used only if the application cooperates by not using non-working features.
Feature | Session pooling | Transaction pooling |
---|---|---|
Startup parameters [1] | Yes | Yes |
SET/RESET | Yes | Never |
LISTEN | Yes | Never |
NOTIFY | Yes | Yes |
WITHOUT HOLD CURSOR | Yes | Yes |
WITH HOLD CURSOR | Yes | Never |
Protocol-level prepared plans | Yes | Yes [2] |
PREPARE / DEALLOCATE | Yes | Never |
ON COMMIT DROP temp tables | Yes | Yes |
PRESERVE/DELETE ROWS temp tables | Yes | Never |
Cached plan reset | Yes | Yes |
LOAD statement | Yes | Never |
Session-level advisory locks | Yes | Never |
[1]. Startup parameters are: client_encoding
, DateStyle
, IntervalStyle
, Timezone
, standard_conforming_strings
, and application_name
. PgBouncer detects their changes and so it can guarantee they remain consistent for the client. If you need PgBouncer to support more than these, take a look at track_extra_parameters
and ignore_startup_parameters
.
[2]. You need to change max_prepared_statements
to a non-zero value to enable this support.