Managing connections
PostgreSQL allocates a separate process
To resolve the insufficient resources issue, connection poolers, e.g., PgPool-II
This deployment scenario complicates the administration, as the servers hosting the connection pooler are added to the DBMS infrastructure.
The Managed Service for PostgreSQL architecture has a built-in connection pooler: Odyssey
Odyssey supports three modes of connection management:
-
Session mode (default):
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 that or any other client. This approach is handy when establishing multiple client connections to the DBMS (for example, when starting applications that access databases).
This mode is supported by all PostgreSQL clients, but it is less efficient than the transaction mode.
-
Transaction mode:
In this mode, the client connection is established at the first query to the database and maintained until the transaction ends. This connection can then be used by that or any other client. This approach allows maintaining few server connections between the connection pooler and PostgreSQL hosts when there are many client connections.
The transaction mode provides high performance and allows you to load the DBMS as efficiently as possible. However, this mode is not supported by certain PostgreSQL clients and does not allow using:
- Temporary tables
, cursors , and advisory locks that exist beyond a single transaction. - Prepared statements
.
Note
To create a prepared statement in Managed Service for PostgreSQL, use the DBMS driver feature. Creating prepared statements with
PREPARE
SQL queries is not supported. - Temporary tables
-
Query mode:
In this mode, the client connection is only supported when making a query to the database. This connection can then be used by that or any other client. A single query is executed at a time. Transactions with multiple queries are prohibited and any attempts to execute them fail.
This approach is helpful when the
AUTOCOMMIT
mode is enabled for client sessions and each transaction is already limited to a single query. However, not every PostgreSQL client supports query mode, and when both theAUTOCOMMIT
mode and thesynchronous_commit = remote_apply
setting are enabled for a cluster simultaneously, this significantly degrades its performance.
You can change the connection pooler mode once the cluster is created.
Note
TTL of the longest active session/transaction is set in the cluster-level Session duration timeout DB setting.
When integrated with Odyssey, Managed Service for PostgreSQL clusters:
-
Support numerous client connections without affecting the DBMS performance.
-
Require neither additional connection pooler configuration nor additional infrastructure for it to operate.
-
Are less prone to running out of computing resources with multiple client connections. This is because of 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. This may 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, i.e., 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 widely used connection pooler, seeks to return this kind of connection to the pool as quickly as possible.
-
Provide improved logging and error handling capabilities:
-
Errors on the PostgreSQL side are sent to the client application without changes.
For example, PgBouncer hides PostgreSQL error messages: for the client, all errors look like an error when connecting to PgBouncer.
-
Odyssey can log all events in detail. Each client connection is also assigned a unique ID, which helps track the entire process of establishing a connection.
Tip
If you have issues while connecting to a Managed Service for PostgreSQL cluster, contact support. To have your issue resolved faster, provide the full text of the error message, including the connection ID.
-
-
Support logical replication streams
passing through the connection pooler.To view examples of logical replication, see Logical replication.
In addition, Managed Service for PostgreSQL automatically ensures fault tolerance of the connection pooler in multi-host clusters.