Skip to main content

PgBouncer

In PostgreSQL, a separate process is created to handle each client connection. The larger the number of connections, the more processes that use RAM. The maximum number of connections to a PostgreSQL process is defined by the max_connections parameter.

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

PostgreSQL cloud database clusters use the PgBouncer connection pooler. Three PgBouncer pooling modes are available. Read more about connection pooler in the PgBouncer documentation.

Maximum number of connections to PostgreSQL process (max_connections)⁠

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

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

Changing the max_connections value causes the PostgreSQL cluster nodes to restart.

Pool size (pool_size)⁠

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

Pool size can be selected when creating a cluster and can be changed in the created cluster. The available values are from 1 to 500.

Pooling modes⁠

Pooling mode is the client's connection strategy to PostgreSQL. PostgreSQL cloud database clusters use the PgBouncer connection pooler. Read more about the connection pooler in the PgBouncer documentation.

PgBouncer supports three modes:

By default, cloud databases use the transaction mode.

Pooling mode can be selected when creating a cluster and the mode can be changed in the created cluster.

Transaction mode (transaction)⁠

The connection to PostgreSQL is maintained until the transaction completes. When 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 be up to 10,000, but the number of active transactions determines the pool size. 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 size of the pool.

A client can simultaneously execute multiple transactions on different connections. Each connection between the connection pooler and PostgreSQL server can execute transactions of different clients during its lifecycle.

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

Limitations of the transaction mode⁠

Transaction mode disrupts some PostgreSQL mechanisms. Choose a different mode if clients use these options. Some connection flags may be distributed among different clients — this may lead to unpredictable behavior and incorrect results.

They do not work in transaction mode:

Read more about incompatible options in the PgBouncer documentation.

Session mode (session)⁠

In session mode, the client can continue sending queries as long as the session continues — 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 PostgreSQL server is determined by the pool size. For each client connection, a connection between the connection pooler and PostgreSQL server is used. The connection is returned to the pool and can be reused only after disconnecting the previous client from the database.

Unlike transaction mode, 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 that have many short-lived database connections, because this mode increases the DBMS connection speed.

Operator mode (statement)⁠

The pooler will return the connection to the pool as soon as the first request is processed — transactions with multiple statements will abort, the pooler will return an error.

This mode allows you to use more client connections than in transaction mode. This mode is suitable if it is known that each transaction is limited to only one request (AUTOCOMMIT enabled).

Change the pooling mode⁠

For your information

If you select transaction mode, familiarize yourself with the limitations of this mode.

  1. In the Dashboard, on the top menu, click Products and select Cloud Databases.
  2. Open the Active tab.
  3. Open the cluster page → Settings tab.
  4. In the Connection Pooler block, click Edit and select a pooling mode.
  5. Click Save.

Change pool size⁠

  1. In the Dashboard, on the top menu, click Products and select Cloud 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.