Skip to main content

PgBouncer connection pooler in a PostgreSQL cluster

Last update:

In PostgreSQL, a separate process is created to handle each client connection. The higher the number of connections, the more processes are used, which consume RAM. The maximum number of connections to a PostgreSQL process is determined by the parameter max_connections.

To optimize resource consumption, you can use a connection pooler. Clients do not connect to PostgreSQL directly but to the connection pooler. This maintains a small number of connections between the pooler and the PostgreSQL server — the pooler creates a new connection or reuses an existing one. The number of connections between the pooler and the database on each cluster node is determined by the pool size (parameter pool_size).

PostgreSQL Managed Database clusters use the PgBouncer connection pooler. Three PgBouncer pooling modes are available. Learn more about the connection pooler in the PgBouncer documentation.

Maximum number of connections to a PostgreSQL process (max_connections)

The PostgreSQL DBMS parameter max_connections determines the maximum number of simultaneous connections to a PostgreSQL process. The default value is 100 connections.

You can change the parameter value in the DBMS settings in the control panel to increase the maximum number of connections under load. Keep in mind that every connection consumes RAM resources.

Changing the max_connections value will restart the PostgreSQL cluster nodes.

Pool size (pool_size)

The pool size (parameter pool_size) is the maximum number of connections between the connection pooler and each PostgreSQL database on each cluster node.

The pool size can be selected when creating a cluster and changed in an existing cluster. Available values range from 1 to 500.

Pooling modes

A pooling mode is the strategy used for a client to connect to PostgreSQL. PostgreSQL Managed Database clusters use the PgBouncer connection pooler. Learn more about the connection pooler in the PgBouncer documentation.

PgBouncer supports three modes:

By default, cloud databases use the transaction mode (transaction).

The pooling mode can be selected when creating a cluster and changed in an existing cluster.

Transaction mode (transaction)

The connection to PostgreSQL is maintained until the transaction completes. Once the transaction completes, the pooler returns the connection to the pool. This connection can later be reused by the same client for other connections or by another client.

The total number of client connections to PgBouncer can reach 10,000, but the number of active transactions determines the size of the pool. For example, if the pool size is 30, there will be 30 active transactions.

The number of connections between the connection pooler and each PostgreSQL database on each cluster node is also determined by the pool size.

A client can perform multiple transactions on different connections simultaneously. In this case, every connection between the connection pooler and the PostgreSQL server can execute transactions from different clients during its lifecycle.

Transaction mode reduces the load on DBMS resources if there is a large number of client connections with low load.

Transaction mode limitations

Transaction mode interferes with some PostgreSQL mechanisms. Choose a different mode if clients use these options. Some connection flags can be distributed among different clients, which can lead to unpredictable behavior and incorrect results.

The following do not work in transaction mode:

More details on incompatible options in the PgBouncer documentation.

Session mode (session)

In session mode, a client can continue sending requests as long as the session lasts — the connection between the connection pooler and the PostgreSQL server will be maintained until the client disconnects from the database.

The number of connections between the connection pooler and the PostgreSQL server is determined by the pool size. Each client connection uses one connection between the pooler and the PostgreSQL server. The connection is returned to the pool and can only be reused after the previous client disconnects from the database.

Unlike transaction mode (transaction), this mode is safe, replicates a direct connection to PostgreSQL, supports all mechanisms, and is suitable for all PostgreSQL clients. When using this mode, the load on resources is not reduced.

This connection mode is useful for clients with many short-lived database connections, as it increases the connection speed to the DBMS.

Statement mode (statement)

The pooler returns the connection to the pool as soon as the first request is processed — transactions with multiple statements are aborted, and the pooler returns an error.

This mode allows for more client connections than transaction mode. This mode is suitable if it is known that each transaction is limited to a single request (AUTOCOMMIT enabled).

Change pooling mode

For your information

If you select transaction mode (transaction), read about the limitations of this mode.

  1. In the control panel, click Products in the top menu and select Managed Databases.
  2. Open the Active tab.
  3. Open the cluster page → Settings tab.
  4. In the Connection pooler block, click Edit and select the pooling mode.
  5. Click Save.

Change pool size

  1. In the control panel, click Products in the top menu and select Managed Databases.
  2. Open the Active tab.
  3. Open the cluster page → Settings tab.
  4. In the Connection pooler block, click Edit and change the pool size.
  5. Click Save.