Connection pooling

ADPG/PostgreSQL implements a "process per user" client/server model. In this model, every client process connects to exactly one backend process. Since it is not determined how many connections will be made, the PostgreSQL server has to use a supervisor process that spawns a new backend process every time a connection is requested. This supervisor process is called postmaster and listens at a specified TCP/IP port for incoming connections. Whenever it detects a request for a connection, it spawns a new backend process. Those backend processes communicate with each other and with other processes of the server instance using semaphores and shared memory to ensure data integrity throughout concurrent data access. Each of these backend processes may grow depending on the data it is accessing.

Despite the fact that memory is allocated for each new backend process, the available memory is not the main limiting factor. A more subtle and important point is that postmaster and its internal processes share memory for communication, and some parts of this shared space are global bottlenecks. For example, here is a structure that keeps track of every current process and transaction. Operations that happen in any backend require walking the entire list of processes or transactions stored in this structure. An exclusive lock is used to add a new process to the structure. The cumulative effect is that the performance of any given backend is inversely proportional to the number of all active backends in the system.

The max_connections ADPG/PostgreSQL configuration parameter determines the maximum number of concurrent connections to the server. The default value is 100. It is highly likely that this number of connections may not be enough in a production environment.

PgBouncer is a PostgreSQL connection pooler. The client application can connect to PgBouncer as an ADPG/PostgreSQL server. The important point is PgBouncer caches the connections.

When PgBouncer receives a client connection, it performs authentication on behalf of the PostgreSQL server. PgBouncer supports all the authentication mechanisms that PostgreSQL provides.

If authentication is successful, PgBouncer checks for a cached connection with the same username and database combination. If a cached connection is found, it returns the connection to the client. Otherwise, PgBouncer creates a new connection. This operation must not violate the restrictions set in the following parameters: default_pool_size, max_client_conn, max_db_connections, and max_user_connections. All of these values can be defined in the PgBouncer settings. If creating a new connection would violate any limits specified in these settings, PgBouncer queues the connection until a new one can be created, unless the max_client_conn is exceeded. Violating the max_client_conn constraint aborts the connection.

In the transaction or statement pooling mode, the post-authentication operations are executed only when the client starts executing a transaction or statement, respectively. The pooling modes are described below.

Before returning a connection to the pool, PgBouncer performs a reset query to remove all session information — this makes it safe to share connections between clients.

PgBouncer can help you optimize:

PgBouncer does not support automated load balancing or high availability, but ADPG Enterprise Edition provides this functionality. See Load balancing.

PgBouncer has the following advantages:

PgBouncer modes allow users to decide when a connection should be returned to the pool. The following modes are available:

You can set the required mode on the Primary configuration tab of the ADPG service. See Configure PgBouncer.