Resolving RDS Proxy Session Pinning

This guide is part of AWS RDS Proxy Connection Pooling. Session pinning is the failure mode that quietly converts a multiplexing proxy back into a plain 1:1 connection passthrough. You enabled RDS Proxy to serve thousands of client sessions over a small backend pool, but throughput collapses and you start seeing the CloudWatch log line:

The client session was pinned to the database connection [C-id] for the
remainder of the session. The proxy can't reuse this connection until the
session ends. Reason: A SET statement was detected.

That message means the proxy detected connection-scoped state and locked a backend connection to one client until that client disconnects. Each pinned session removes a connection from the shared pool. Enough pinning and the proxy delivers zero pooling benefit while still adding a network hop and latency. This guide diagnoses why sessions pin, removes the causes, and verifies the fix with the DatabaseConnectionsCurrentlySessionPinned metric.

Rapid incident diagnosis

Start with the multiplexing ratio. In CloudWatch, plot ClientConnections against DatabaseConnections for the proxy. A healthy transaction-multiplexed workload runs a ratio comfortably above 5:1. When the two lines converge toward 1:1, pinning is the cause until proven otherwise.

Confirm with the dedicated metric: DatabaseConnectionsCurrentlySessionPinned. Any sustained value above roughly 5–10% of DatabaseConnections is a problem. A value tracking DatabaseConnections almost exactly means nearly every session is pinned.

Then read the proxy’s CloudWatch Logs. RDS Proxy emits an explicit log entry for every pin with the reason. The common reasons, in rough order of frequency:

Map each log reason to the code path that emits it. The biggest offenders are ORMs and drivers that issue SET search_path or SET TIME ZONE on every new connection, and frameworks that lean on server-side prepared statements. The same prepared-statement hazard under transaction multiplexing is dissected in Using Prepared Statements with PgBouncer Transaction Mode, and the underlying mechanics are the same for RDS Proxy.

Causes of pinning, ranked

Session variables (SET). Any SET that is not wrapped as SET LOCAL inside a transaction sets session-scoped state and forces a pin. Frameworks frequently emit SET search_path = "$user", public or SET TIME ZONE 'UTC' on connection init. Each of those pins the session for its entire lifetime.

Server-side prepared statements. A PREPARE creates a named statement bound to one backend connection. The PostgreSQL extended-query protocol (unnamed/portal-based parameter binding within a single round trip) does not pin, but explicit PREPARE name AS ... or a driver configured to cache named server-side statements does. JDBC’s prepareThreshold and node-postgres’s named prepared statements both trip this.

Temporary tables. CREATE TEMP TABLE is inherently session-scoped; the data lives on one backend connection. The proxy has no choice but to pin.

Advisory locks and SET ROLE. Session-level advisory locks and persistent role switches both leave state on the connection. pg_advisory_lock() (the session-scoped variant) holds until explicitly unlocked or the session ends, so the proxy pins for the whole session. The transaction-scoped pg_advisory_xact_lock() releases on COMMIT and does not pin. Likewise SET ROLE and SET SESSION AUTHORIZATION persist past the transaction and pin; if you must switch roles, do it as SET LOCAL ROLE inside the transaction.

Listen/notify and cursors. LISTEN registers a session-scoped subscription and pins immediately, which makes Postgres LISTEN/NOTIFY fundamentally incompatible with transaction multiplexing — route those connections around the proxy. A WITH HOLD cursor that outlives its transaction also pins, whereas a normal cursor closed at COMMIT does not.

The practical impact is multiplicative. Each pinned session subtracts one connection from a pool whose ceiling is max_connections × MaxConnectionsPercent. At 30% pinning on a pool that would otherwise sustain a 10:1 ratio, effective capacity falls by roughly a third and borrow latency climbs non-linearly as the remaining shared connections saturate — the mechanism behind Diagnosing RDS Proxy Borrow Timeouts.

Exact remediation & configuration

The remediation hierarchy is: eliminate the state first, scope it to the transaction second, and only relax the proxy filters as a last resort.

1. Move SET into the transaction with SET LOCAL. SET LOCAL is reset at transaction end, so the connection returns clean and is not pinned.

BEGIN;
SET LOCAL search_path = "$user", public;
SET LOCAL statement_timeout = '5s';
-- queries
COMMIT;

2. Stop the driver from emitting connection-init SET. For PostgreSQL JDBC, push search_path and timezone into the connection URL parameters or the database role default instead of a runtime SET:

ALTER ROLE app_user SET search_path = "$user", public;
ALTER ROLE app_user SET timezone = 'UTC';

Role-level defaults are applied by the backend on connect without a pinning SET from the client.

3. Disable server-side named prepared statements. Use protocol-level (unnamed) parameter binding so statements do not pin. In PgJDBC, set prepareThreshold=0 to keep statements unnamed. In node-postgres, avoid passing a name to queries.

jdbc:postgresql://app-proxy...:5432/app?prepareThreshold=0

4. As a last resort, relax the proxy pinning filter. If a SET is genuinely safe to leak across sessions (or is reset every transaction), tell the proxy not to pin on it:

connection_pool_config {
  session_pinning_filters = ["EXCLUDE_VARIABLE_SETS"]
}

EXCLUDE_VARIABLE_SETS suppresses pinning on SET of session variables. Use it only when you have verified the variables are harmless to inherit — otherwise one client’s search_path leaks into another’s queries. It does nothing for temp tables or prepared statements; those still pin.

Apply changes with zero downtime: the role defaults and proxy filter changes take effect on the next connection, so trigger a rolling restart of the application (or let the client pool’s max-lifetime rotate connections) to flush already-pinned sessions.

The remediation options trade safety against effort. Prefer the higher-safety options and reserve filter relaxation for cases you have explicitly verified:

Many frameworks pin on connection initialization without any application code change — the ORM or driver issues SET search_path or SET TIME ZONE on every new connection. Detecting that hidden init SET is the same class of problem as the leaks covered in Detecting ORM Connection Leaks in Production; the fix is to move the setting into the role default so the runtime SET disappears.

Validation & verification

After deploying the fix, watch DatabaseConnectionsCurrentlySessionPinned trend toward zero and the multiplexing ratio climb back above 5:1.

Confirm on the database side that backend connections are being reused rather than dedicated. On PostgreSQL, count distinct backends serving the proxy login:

SELECT count(*) AS proxy_backends,
       count(DISTINCT client_addr) AS client_ips
FROM pg_stat_activity
WHERE usename = 'app_secrets_user'
  AND backend_type = 'client backend';

A small proxy_backends count relative to your client fan-out confirms multiplexing is working again.

Grep the proxy CloudWatch log group for residual pin events after the deploy:

fields @timestamp, @message
| filter @message like /pinned to the database connection/
| stats count() by bin(5m)

The count per 5-minute bin should collapse to near zero once the offending SET, prepared statement, or temp table is removed.

Frequently Asked Questions

Related

• AWS RDS Proxy Connection Pooling — the parent guide covering multiplexing, sizing, and IAM auth.
• Diagnosing RDS Proxy Borrow Timeouts — what happens when pinning exhausts the backend pool.
• Using Prepared Statements with PgBouncer Transaction Mode — the same prepared-statement hazard under transaction multiplexing.
• Configuring Connection Validation Queries for AWS RDS Proxy — validation behavior through the proxy.