Transaction-level connection pool

Prerequisites

The transaction-level connection pool requires PolarProxy version 2.3.46 or later.

Background information

In and PolarDB for PostgreSQL, each database connection requires a dedicated backend process. This model can cause high database pressure and severe performance degradation when clients maintain many long-lived connections or frequently create new ones.

To improve performance in these scenarios, and PolarDB for PostgreSQL provide a transaction-level connection pool. This feature allows multiple client connections to share a single backend connection, reducing the number of new and maintained backend connections and improving throughput.

How it works

The transaction-level connection pool is designed to reduce the number of direct connections to the database and minimize the overhead from frequently creating short-lived connections.

When the transaction-level connection pool is enabled, a client request first establishes a connection with PolarProxy. Instead of immediately creating a new backend connection, PolarProxy searches the transaction-level connection pool for an available one. A connection is considered available if its user, dbname, and system variable settings match the request. If a matching connection is found, it is reused. If not, PolarProxy creates a new connection to the database. After the transaction ends, PolarProxy returns the connection to the pool for the next request. This process reduces the total number of connections between PolarProxy and the database.

With the transaction-level connection pool enabled, you can have thousands of connections between clients and PolarProxy, while PolarProxy maintains only dozens or hundreds of connections to the backend database.

PolarProxy itself does not have a maximum connection limit. The specifications of the compute nodes in the backend database determine the connection limit. When the transaction-level connection pool is disabled, each client connection creates a corresponding connection on the primary node and on each read-only node.

Instructions

Enable the transaction-level connection pool

1. Log on to the PolarDB console.

2. In the upper-left corner of the console, select the region of your cluster.

3. Find the target cluster and click the cluster ID.

4. In the URL section, click Configuration.

5. To the right of Connection Pool, click Transaction-level.

6. Click OK.

Disable the transaction-level connection pool

1. Log on to the PolarDB console.

2. In the upper-left corner of the console, select the region of your cluster.

3. Find the target cluster and click the cluster ID.

4. In the URL section, click Configuration.

5. To the right of Connection Pool, click Disable.

6. Click OK.

Limitations

• When the transaction-level connection pool is enabled, sending a CANCEL request may cause the session to hang (Hang). We recommend that you disable the transaction-level connection pool when you use the CANCEL feature.

• The following actions cause connection pinning, where a connection is locked to a client session and is not returned to the pool until the session ends:

  • Executing a PREPARE statement

  • Processing large packets (for example, larger than 16 MB)

  • Using copy mode

  • Using flush mode

  • Using a temporary table, sequence, or view

  • Running within a transaction

  • Declaring a cursor

• The following PostgreSQL features are not fully compatible with the transaction-level connection pool and may cause unexpected behavior. We recommend disabling the transaction-level connection pool when you use these features to ensure correct semantics:

  • sequence

  • advisory lock

  • LISTEN/NOTIFY

  • holdable cursor

• Because connections may be reused, the pid returned by select pg_backend_pid() may change.

• Because connections are reused, the IP address and port displayed in pg_stat_activity or SQL Explorer may not match the client's actual IP address and port.

Choosing a connection pool

Use the following recommendations to decide whether to enable the connection pool:

• If your workload primarily uses a small number of long-lived connections, or if your application already has a well-configured connection pool, you may not need the PolarDB connection pool feature.

• If your workload requires a large number of connections (for example, tens of thousands), or if you use a Serverless service where the number of connections increases linearly as your service scales out, you can enable the transaction-level connection pool. Ensure your workload is unaffected by the limitations described in the Limitations section.