When thousands of clients connect simultaneously—or when serverless services create connections proportional to their scale—PolarDB for PostgreSQL's process-per-connection model strains the database. Transaction-level connection pooling lets multiple client connections share a smaller set of backend connections, reducing backend load without changing how your application connects.
Prerequisites
Before you begin, ensure that you have:
A database proxy at version 2.3.46 or later
How it works
PolarDB for PostgreSQL assigns a dedicated backend process to each client connection. This model degrades performance when clients maintain many persistent connections or frequently create new ones.
With transaction-level connection pooling, client requests connect to the PolarDB proxy instead of directly to the database. The proxy holds a pool of backend connections and lends them to client transactions on demand:
A client sends a request to the proxy.
The proxy checks the pool for an available connection whose
user,dbname, and system variable values match the request.If a match exists, the proxy reuses it. If not, it opens a new connection to the backend database.
After the transaction ends, the connection returns to the pool for the next request.
This means thousands of client-to-proxy connections can coexist while only tens or hundreds of proxy-to-backend connections are active. The proxy itself has no connection limit; the limit is determined by the specifications of your compute nodes.
Without pooling, each client connection creates a corresponding connection on both the primary node and every read-only node.
Enable transaction-level connection pooling
Log on to the PolarDB console.
In the upper-left corner, select the region where the cluster is located.
Find the target cluster and click the cluster ID.
In the Database Connections area, click Configure.
For Connection Pool, click Transaction-level.
Click OK.
Disable transaction-level connection pooling
Log on to the PolarDB console.
In the upper-left corner, select the region where the cluster is located.
Find the target cluster and click the cluster ID.
In the Database Connections area, click Configure.
In the Connection Pool row, click Off.
Click OK.
Limitations
Locked connections
In some situations, the proxy cannot safely return a connection to the pool after a transaction ends. Instead, the connection is locked until it is closed. A locked connection is not returned to the connection pool for other clients to use.
The following actions trigger a locked connection:
| Action | Notes |
|---|---|
Executing a PREPARE statement | SQL-level prepared statements |
| Sending or receiving messages over 16 MB | Large message threshold |
| Using Copy Mode | PostgreSQL COPY protocol |
| Using Flush Mode | Mid-stream flush requests |
| Using temporary tables, sequences, or views | Session-scoped objects |
| During an active transaction | Open transaction block |
| Declaring a cursor | — |
Compatibility issues
The following PostgreSQL features have semantic incompatibilities with transaction-level connection pooling. Disable pooling before using them to avoid unexpected behavior:
| Feature | Issue |
|---|---|
Sequences (Sequence) | Session state is not preserved across pooled connections |
Advisory locks (advisory lock) | Locks are tied to backend connections, not client sessions |
Listeners and notifications (LISTEN/NOTIFY) | Requires a persistent backend connection |
Holdable cursors (Holdable Cursor) | Cursors must persist beyond transaction boundaries |
Other behaviors
| Behavior | Details |
|---|---|
CANCEL requests | A CANCEL request may cause a session to hang. Disable pooling to use the CANCEL feature. |
| Backend process ID | The pid returned by SELECT pg_backend_pid() may change between transactions because connections are reused. |
| Client IP and port | The IP address and port shown in pg_stat_activity or SQL Explorer may not match the client's actual IP address and port. |
When to use transaction-level connection pooling
Use the following table to decide whether to enable pooling for your workload:
| Scenario | Recommendation | Why |
|---|---|---|
| Application with a small number of persistent connections | Don't enable | Pooling is not needed |
| Application that already uses an effective connection pool | Don't enable | An existing pool already manages backend connections |
| Serverless service where connections scale linearly with instances | Enable | Prevents connection count from overwhelming the backend |
| Application with tens of thousands of concurrent connections | Enable | Reduces backend load significantly |
Workload using PREPARE, cursors, or advisory locks | Don't enable | These features trigger locked connections or have compatibility issues |
Before enabling pooling, confirm that your workload is not affected by the limitations described in this topic.