34.2. How It Works
When running in the regular mode, Postgres Pro spawns a separate backend for each connection request. With connection pooling enabled, the number of backends that can be used for each database is limited by the session_pool_size value. Once this limit is reached, postmaster stops spawning new backend processes for the corresponding sessions and redirects further connection requests to one of the backends that has already been started. Since each Postgres Pro backend can work with a single database only, built-in connection pooler has to maintain separate connection pools for each database. The number of pools is unlimited: each time a new database needs to be served, a new pool is added.
To assign sessions to an appropriate pool, postmaster needs to know the target database for the client connection, which requires receiving and parsing a startup packet from the client. To avoid a bottleneck at this stage, postmaster delegates the task of reading the startup packet to one of the listener processes. The listener fetches the startup packet and returns the result back to postmaster. Based on the received information, postmaster chooses the pool for this client and redirects the connection to this pool using the file descriptor transfer mechanism.
Note
You can disable connection pooling for some databases and users by specifying them in the dedicated_databases and dedicated_users parameters, respectively. Such databases and users can use an unlimited number of dedicated backends, thus getting priority access to available system resources.
To avoid substantial changes in Postgres Pro locking mechanism, only transaction-level pooling policy is supported. In this mode, the backend can be rescheduled to another session only after it has completed the current transaction. However, the built-in pooler provides session semantics for pooled connections, so that all changes in the session context made by a client application, such as session configuration parameters, preparing statements, or creating temporary tables, are saved/restored by Postgres Pro when the backend is rescheduled to another session. By contrast, pgbouncer can provide session semantics only in the session pooling mode, which does not limit the number of launched backends.
Pooled sessions are bound to backends and cannot be migrated between them. Migrating a session would also require serialization and transfer of the complete session context, which is a non-trivial task. By default, pooled backends continue running even if all sessions assigned to them have been already terminated. To change this behavior, you can set restart_pooler_on_reload or idle_pool_backend_timeout configuration parameters.