Setting the file. One moment.
Subchapter 74.9
references/proxy-pinning-postgresql.mdMarkdown4 KBView on GitHub
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.
References
Bluegreen Advisor Workflow| 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 |
| 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. |
| 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. |
Many ORMs and frameworks set search_path per connection. This pins every connection. Mitigation:
search_path in the proxy’s init query instead of per-connectionPostgreSQL’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:
prepareThreshold=0 to disable server-side prepared statementsname property in query config objects (named queries create persistent server-side prepared statements that pin connections)prepare_threshold=NoneIf 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).
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;