PolarDB:Transaction-level connection pooling

Prerequisites

The transaction-level connection pooling feature requires the database proxy to be version 2.3.46 or later.

Background information

PolarDB for PostgreSQL uses a connection model where each connection requires a dedicated process. This model can cause the database load to increase and performance to degrade significantly when clients maintain many persistent connections or frequently create new connections.

To improve performance in these scenarios, PolarDB for PostgreSQL offers the transaction-level connection pooling feature. This feature allows multiple frontend connections to share a single backend connection. This reduces the number of backend connections that are created and maintained, which improves the database's processing power in these scenarios.

How it works

Transaction-level connection pooling reduces the number of direct connections to the database. It also reduces the load from frequent connection creation in scenarios with short-lived connections.

When you enable transaction-level connection pooling, client requests first connect to the PolarDB proxy. The proxy does not immediately connect to the backend database. Instead, it checks the connection pool for an available connection. A connection is available if its `user`, `dbname`, and system variable values match the request. If a matching connection does not exist, the proxy creates a new connection to the database. If a matching connection exists, the proxy reuses it. After the transaction ends, the connection returns to the pool for the next request. This process reduces the number of connections between the database proxy and the database.

With transaction-level connection pooling enabled, there can be thousands of connections between clients and the PolarDB proxy, but only tens or hundreds of connections between the proxy and the backend database.

The PolarDB proxy itself does not have a maximum connection limit. The connection limit is determined by the specifications of the compute nodes in the backend database. When transaction-level connection pooling is disabled, each connection from a client creates a corresponding connection on the backend primary node and on all read-only nodes.

Usage instructions

Enable transaction-level connection pooling

1. Log on to the PolarDB console.

2. In the upper-left corner of the console, select the region where the cluster is located.

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

4. In the Endpoints area, click Configure.

5. For Connection Pool, click Transaction-level.

6. Click OK.

Disable transaction-level connection pooling

1. Log on to the PolarDB console.

2. In the upper-left corner of the console, select the region where the cluster is located.

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

4. In the Endpoints area, click Configure.

5. In the Connection Pool row, click Shutdown.

6. Click OK.

Limits

• A CANCEL request can cause a session to hang if transaction-level connection pooling is enabled. To use the CANCEL feature, you must disable transaction-level connection pooling.

• When you perform the following actions, the connection is locked until it is closed. A locked connection is not returned to the connection pool for other clients to use.

  • Executing a PREPARE statement

  • Sending or receiving large messages, such as those over 16 MB

  • Using Copy Mode

  • Using Flush Mode

  • Temporary tables (Temporary Table), sequences (Sequence), and views (View)

  • During a transaction

  • Declaring a Cursor

• The following PostgreSQL features have compatibility issues with transaction-level connection pooling. Using them may cause unexpected behavior. To ensure correct semantics, you must disable transaction-level connection pooling when you use these features:

  • Sequences (Sequence)

  • Application-level lock (advisory lock)

  • Listeners and notifications (LISTEN/NOTIFY)

  • Holdable Cursors (Holdable Cursor)

• Because connections may be reused, the pid returned by a select pg_backend_pid() query for the current connection may change.

• Because connections may be reused, the IP address and port shown 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 suggestions to decide whether to enable transaction-level connection pooling:

• If your application uses a small number of persistent connections, or if it already has an effective connection pool, you do not need to enable the PolarDB connection pool feature.

• If your application uses many connections (such as tens of thousands) or is a serverless service where the number of connections increases linearly with server scale-out, you can enable transaction-level connection pooling. Before you enable the feature, confirm that your application is not affected by the limits described in this topic.