PostgreSQL RDS Proxy Pinning Risks
RDS Proxy for PostgreSQL uses connection multiplexing at the session level. Certain PostgreSQL features create session state that prevents the proxy from reusing backend connections.
High Pinning Risk (defeats proxy purpose)
| Pattern | Why It Pins | Diagnostic Query |
|---|---|---|
| Prepared statements (PREPARE/EXECUTE) | Server-side prepared state is session-scoped | SELECT name, statement FROM pg_prepared_statements; (run per-session) |
| Advisory locks (pg_advisory_lock) | Lock is held on a specific backend | SELECT * FROM pg_locks WHERE locktype = 'advisory'; |
| LISTEN/NOTIFY | LISTEN registers on a specific backend connection | SELECT * FROM pg_listening_channels(); |
| SET (session parameters) | e.g., SET search_path, SET work_mem — session-scoped |
SHOW search_path; — if app sets this per-connection, every connection pins |
| Temporary tables | Session-scoped, can't be transferred | Check application code for CREATE TEMP TABLE |
| DECLARE CURSOR WITH HOLD (without CLOSE) | Holdable cursor survives the transaction and is session-scoped | Check for open holdable cursors: SELECT * FROM pg_cursors WHERE is_holdable = true; |
| Sequence manipulation (CURRVAL) | CURRVAL depends on session's last NEXTVAL call | Check application code for CURRVAL() usage |
Medium Pinning Risk
| Pattern | Notes |
|---|---|
| SET LOCAL (transaction-scoped) | Pins only for transaction duration. Less impactful than SET (session). |
| SAVEPOINT | Pins for transaction duration. Fine if transactions are short. |
| Large result sets with cursors | Pins until cursor is closed. Use LIMIT/OFFSET instead. |
| SET ROLE / SET SESSION AUTHORIZATION | Pins for session duration. |
Low / No Pinning Risk
| Pattern | Notes |
|---|---|
| Simple queries (SELECT, INSERT, UPDATE, DELETE) | No session state. Full multiplexing. |
| Autocommit single statements | No pinning. |
| PL/pgSQL functions (without session state) | Executed server-side, no pinning. |
| COPY (bulk load) | No pinning after completion. |
PostgreSQL-Specific Gotchas
search_path
Many ORMs and frameworks set search_path per connection. This pins every connection. Mitigation:
- Set
search_pathin the proxy's init query instead of per-connection - Or set it in the PostgreSQL parameter group as the default
Extended query protocol
PostgreSQL's extended query protocol (Parse/Bind/Execute) creates server-side prepared statements implicitly. Many drivers (libpq, JDBC, node-postgres) use this by default. This causes pinning.
Mitigation:
- JDBC: set
prepareThreshold=0to disable server-side prepared statements - node-postgres: avoid passing a
nameproperty in query config objects (named queries create persistent server-side prepared statements that pin connections) - Python psycopg2: uses simple query protocol by default (no pinning)
- Python psycopg3: uses extended protocol by default (pins) — set
prepare_threshold=None
PgBouncer vs RDS Proxy
If already using PgBouncer in transaction mode, RDS Proxy adds little value — both do connection multiplexing. RDS Proxy's advantage is managed infrastructure + IAM auth + automatic failover handling. But PgBouncer in transaction mode is more aggressive at multiplexing (no pinning on SET).
Diagnostic: Check Pinning Potential
Run these on the database to estimate pinning risk before deploying proxy:
-- Check for advisory locks
SELECT COUNT(*) AS advisory_locks FROM pg_locks WHERE locktype = 'advisory';
-- Check for active LISTEN channels
SELECT COUNT(*) AS listen_channels FROM pg_listening_channels();
-- Check for prepared statements (current session — ask app team to check during peak)
SELECT COUNT(*) AS prepared_stmts FROM pg_prepared_statements;
-- Check for temp tables in current sessions
SELECT COUNT(*) AS temp_tables FROM pg_class WHERE relpersistence = 't';
-- Check for open cursors
SELECT COUNT(*) AS open_cursors FROM pg_cursors WHERE is_holdable = true;Mitigation Strategies
- Move search_path to proxy init query or parameter group default
- Disable server-side prepared statements in the driver (see above)
- Replace advisory locks with application-level locking (Redis, DynamoDB)
- Replace LISTEN/NOTIFY with SQS, SNS, or EventBridge
- Avoid DECLARE CURSOR WITH HOLD — use LIMIT/OFFSET or keyset pagination
- Keep transactions short to minimize pin duration