There are two main configuration parameters to manage connection pooling: session_pool_size and max_sessions. To enable connection pooling, set the session_pool_size parameter to a positive integer value. If set, Postgres Pro uses shared pools of backends for working with all databases, except for those that use dedicated backends.

The session_pool_size variable defines the maximal number of backends per connection pool, which is used for each specific database. Thus, the total number of backends running in the pooling mode is limited by session_pool_size * N, where N is the number of databases. The optimal session_pool_size value highly depends on your system resources. If the number of backends is too small, the server will not utilize all available resources, but a too large value can cause performance degradation because of large snapshots and lock contention.

The max_sessions parameter specifies the maximal number of sessions that can be handled by a single backend. Thus, the maximal number of connections for one database is limited by the session_pool_size * max_sessions value. The max_sessions setting affects only the potential size of the queue on each backend and does not cause any essential negative impact on resource consumption. The default value is 1000.

Additionally, you can configure the number of listeners that read the startup packets to determine the connection pool the client needs to connect to. By default, two workers are used. You can change this value using the connection_pool_workers configuration parameter.

If you are going to use prepared transactions (2PC) in pooled sessions, make sure to enable the hold_prepared_transactions configuration parameter, which forbids rescheduling the backend to another session until all prepared transactions in the current session are committed or rolled back. It prevents conflicts between prepared transactions of several sessions on the same backend, which can cause undetectable deadlocks. However, you must ensure that prepared transactions are completed by the same session that has prepared them; otherwise, using 2PC in pooled sessions is impossible.

Postgres Pro enables you to override the pooling mode for some databases and users, so that a separate backend is spawned for each connection. Such databases and users do not use the shared pool of backends, but have their own dedicated backends that each serve a single connection. The number of dedicated backends is unlimited, so clients do not have to wait until one of the shared backends becomes available and can get connected to a database right away.

By default, dedicated backends are used for connections to postgres, template0, and template1 databases, as well as for all connections established on behalf of the postgres user. If you would like to use this behavior for other databases or users, modify dedicated_databases and dedicated_users configuration parameters, respectively, and call pg_reload_conf().

Since postmaster assigns client sessions to backends at connection time, workload imbalance can occur: while some backends are idle, other backends could be overloaded with work, so clients connected to these backends suffer from long latencies. To better distribute sessions between backends, set the session_schedule configuration parameter that defines the scheduling policy. You can choose between round-robin (default), random and load-balancing policies.

Both random and round-robin policies work well for uniform workloads. For unbalanced workloads, you can opt for the load-balancing policy. In this case, postmaster chooses the backend with the smallest workload when establishing a new connection. The workload is measured by the number of sessions waiting for transaction execution on this backend.

You can check the current workload of a backend by calling the pg_backend_load_average(pid) function, where pid is the process identifier of this backend (see Table 27.22). Unfortunately, even perfect scheduling at connection time does not guarantee that the workloads remain the same in the future: some sessions can get terminated, while idle sessions can become active any time.

Once started, pooled backends continue running even if all its clients get disconnected. While it allows to reuse the same backends for future connections, it may sometimes be required to shut down a backend that is no longer in use. For example, you cannot drop a database or a user while at least one backend in the corresponding connection pool is still running.

To terminate backends that are no longer required without a server restart, do the following:

Alternatively, you can set the idle_pool_worker_timeout configuration parameter to automatically terminate unused backends and release system resources after the specified timeout.