## Chapter 34. Built-In Connection Pooling

When establishing a connection, PostgreSQL spawns a separate backend process for each client. For a large number of clients, this model can cause high consumption of system resources and lead to significant performance degradation, especially on multicore systems. The reason is high contention for PostgreSQL resources between backends. Besides, the size of many PostgreSQL internal data structures is proportional to both the complexity of algorithms for these structures and the number of active backends.

Most production Postgres Pro installations reduce the number of spawned backends using external tools, such as J2EE, odyssey, or pgbouncer, one of the most popular connection poolers for Postgres Pro. However, external connection poolers require additional efforts for installation, configuration, and maintenance. If the pooler is single-threaded, like pgbouncer, you also have to launch multiple pooler instances as it can otherwise cause a bottleneck on high-load systems.

To address these challenges, Postgres Pro Enterprise provides an experimental built-in connection pooler. Unlike external solutions, it does not require any additional maintenance and does not introduce any limitations for clients. With built-in connection pooling enabled, clients can continue using session configuration parameters, prepared statements, and temporary tables as if there is no proxy.

The chapters that follow describe built-in connection pooler architecture and provide configuration instructions.

## 34.1. Limitations

When using connection pooling, take the following limitations into account:

- SSL connections are currently not supported.

- Long-running transactions can cause starvation of other sessions assigned to the same backend. If an application opens a transaction and then waits for user input or some other external event, the backend remains in the idle-in-transaction state and cannot be rescheduled for another session until this transaction is complete. With connection pooling enabled, it is recommended to avoid long-running transactions and set the idle_in_transaction_session_timeout configuration parameter to implicitly abort idle transactions after the specified interval has elapsed.

- Built-in connection pooler does not support advisory session-level locks (`pg_advisory_lock`) and returns an error if you try using them.

- Modules that maintain the session state internally, such as plantuner, are not guaranteed to work with connection pooling enabled.

- Connection pooling is incompatible with the following extensions:

  - in_memory

  - online_analyze

  - pg_variables