## 34.3. Configuring Built-in Connection Pooler

### Enabling Connection Pooling

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. As long as the `max_sessions` parameter is also greater than zero, Postgres Pro uses shared pools of backends for working with all databases except for `postgres`, `template0`, and `template1` databases, which are declared dedicated by default.

The `session_pool_size` variable defines the maximal number of backends per connection pool, which is used for a specific database/user combination. Thus, the total number of backends running in the pooling mode is limited by `session_pool_size` * `N` * `M`, where `N` is the number of databases and `M` is the number of users working with these 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/user 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.

### Setting up a Dedicated Database

Postgres Pro enables you to override the pooling mode for some databases, so that a separate backend is spawned for each connection. Such databases are called dedicated. The number of backends spawned for such databases is unlimited, so clients do not have to wait until one of the shared backends becomes available and can access these databases right away.

To make a database dedicated, specify the name of this database in the dedicated_databases configuration parameter and call `pg_reload_conf()`. By default, `postgres`, `template0`, and `template1` databases are declared dedicated.

### Choosing the Scheduling Policy

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. 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.

### Releasing Pooled Resources

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:

1. Set the `restart_pooler_on_reload` variable to `true`.

2. Call the `pg_reload_conf()` function to reload the server configuration.

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