Connection Poolers¶
A connection pooler sits between an application and PostgreSQL, reusing a smaller pool of real database connections across many client requests, so the database itself does not have to manage huge numbers of connections directly.
The Processor must connect directly to PostgreSQL¶
The Processor is not compatible with transaction-mode connection poolers such as PgBouncer running in transaction pooling mode, or pgpool-II's load-balancing mode. It needs either a session-mode connection or a direct connection straight to the PostgreSQL backend.
Why transaction-mode poolers break pg_relay¶
The Processor uses a session-level advisory lock (pg_try_advisory_lock) to claim its instance slot (see The Processor). This lock is held for the Processor's entire session. With a transaction-mode pooler, the actual backend connection holding that lock can be swapped out between statements — the lock is silently lost, the Processor still believes it holds its instance slot, and the preflight check that would normally catch this problem is not re-run mid-session.
The FOR UPDATE SKIP LOCKED mechanism used in queue_pending_ids() and _queue_claim() also depends on statement-level locking with specific timing guarantees that transaction-mode pooling can break.
Session-mode poolers¶
A session-mode pooler (PgBouncer in session mode, or pgpool-II with a persistent connection) technically works, because each Processor session maps to exactly one backend connection for its whole lifetime. However, it provides no real benefit for pg_relay:
- The Processor opens at most 9 connections (one per worker).
- Each connection is long-lived — there is no rapid connection churn that a pooler would normally help smooth over.
- Running the Processor without a pooler — connecting directly to PostgreSQL — is simpler, and avoids one extra moving part in the chain.
Application connections¶
Your application connections — the ones that call pgrelay.notify() — can use any pooler you like, including transaction-mode poolers. The notify() call is a single statement with no session state, and it does not use advisory locks. Only the Processor itself has this constraint.
Continue to The Security Model to understand exactly what the pgrelay role can and cannot do.