PostgreSQL allocates a separate process for each established connection. With numerous client connections, the DBMS creates multiple processes and manages shared data structures. As a result, there may be insufficient computing resources, which affects the DBMS performance.
To solve the problem of resource insufficiency, connection poolers (for example, PgPool-II or PgBouncer) are often added before a PostgreSQL cluster. The poolers manage connections to allow a large number of clients to connect to the DBMS without affecting performance. A relatively small number of connections are maintained between the pooler and the DBMS and they can be reused. After the client is disconnected, the connection is returned to the pool and can be reused by the same or a new client.
This deployment method makes administration more difficult because servers hosting the pooler are added to the DBMS infrastructure.
The Managed Service for PostgreSQL architecture has the built-in connection pooler Odyssey from Yandex.
Odyssey supports two modes of connection management:
In this mode, the client connection is established at the first query to the database and maintained until the client terminates the session. This connection can then be used by another or the same client. This approach helps wait out the moment of establishing multiple client connections to the DBMS (for example, when starting applications that access databases).
This mode is supported by all PostgreSQL clients, but less productive than the transaction mode.
In this mode, the client connection is established at the first query to the database and maintained until the transaction end. This connection can then be used by another or the same client. This approach helps maintain a few server connections between the pooler and PostgreSQL hosts when there're multiple client connections.
The transaction mode provides high performance and allows loading the DBMS as efficiently as possible. However, this mode is not supported by all PostgreSQL clients and doesn't allow using:
The pooler mode can be changed after creating the cluster.
Integrated with Odyssey, Managed Service for PostgreSQL clusters:
Support numerous client connections without affecting DBMS performance.
Require no additional configuration of the connection pooler or additional infrastructure for its operation.
Are less prone to running out of computing resources because of multiple client connections thanks to asynchronous multithreading built into the Odyssey architecture. This is especially important if most client connections to the DBMS use SSL/TLS.
For example, PgBouncer uses a single-threaded architecture, which can lead to problems with resource consumption and scalability under high load.
Allow limiting the number of connections both globally at the cluster level and at the level of individual users.
Support advanced transaction pooling: automatic operation cancel and transaction rollback when the client connection is broken.
In addition, Odyssey strives to keep the client connection alive as long as possible after the transaction ends in order to re-use it if this client returns with a new transaction. Unlike Odyssey, PgBouncer, another popular pooler, seeks to return such a connection to the pool as quickly as possible.
Provide improved logging and error handling capabilities:
Errors on the PostgreSQL side are passed to the client application without changes.
For example, PgBouncer hides PostgreSQL error messages: for the client, all errors look like an error connecting to PgBouncer.
Odyssey logs all occurring events in detail. In addition, each client connection is assigned a unique ID, which helps track the entire process of establishing a connection.
If you have problems connecting to a Managed Service for PostgreSQL cluster, contact support. For faster solution of your problem, provide the full text of the error message, including the connection ID.
Support placing of logical replication threads via the puller.
See Logical PostgreSQL replication for examples of logical replication.
In addition, Managed Service for PostgreSQL automatically ensures fault tolerance of the connection pooler in clusters with multiple hosts.