high riskservice affecting~75 min

Runbook: Put a Connection Pooler in Front of a Cluster

1 · Prerequisites

Confirm every item is in place before any state change.

A measured reason: the connection count, the pool arithmetic, and the knee of the cluster throughput curve
An audit of every session-scoped feature each application uses, because transaction pooling breaks them
A host for the pooler with low network latency to the database, since every query gains a hop
The SCRAM verifiers for the roles the pooler will authenticate, or an auth_query configured against the database
A test environment where the applications can be exercised under concurrency, not with one client
Agreement that this is a capacity and stability change, not a performance optimisation

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

· Measure the problem first. SELECT usename, application_name, client_addr, count(*), count(*) FILTER (WHERE state = 'idle') AS idle FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY 1,2,3 ORDER BY 4 DESC; A pooler is the answer to too many connections, not to slow queries.
· **Compute pods x pool_max against max_connections.** If that number is under the limit and the problem is still connection pressure, the cause may be a storm rather than a shortfall.
· Measure the knee of the throughput curve for this hardware. A short pgbench sweep at increasing client counts gives you the number to size the pool at. It turns pool sizing from an argument into arithmetic.
· Audit session-scoped features per application. SET outside a transaction, CREATE TEMP TABLE, pg_advisory_lock, LISTEN, server-side prepared statements, and WITH HOLD cursors. This audit is the bulk of the work and it cannot be skipped.
· Check the driver's prepared-statement behaviour. This is the most common silent incompatibility. Read the driver's documentation rather than assuming; many can be told not to use server-side prepared statements.
· Confirm nobody expects this to make queries faster. Measured at 80 clients where a direct connection works: 5124 tps direct against 2627 through the pool. The pool adds a hop and funnels everything through a smaller number of server connections.
· Plan the cutover. A pooler in front of a cluster is a new single point of failure unless it is deployed redundantly, and that decision belongs before the cutover rather than after.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1Install the pooler and configure the database entry. [databases] orders = host=db-primary-01.internal port=5432 dbname=orders
2Choose the pool mode from the audit, not by default. session mode preserves everything and provides connection reuse without the semantic change. transaction mode gives the large reduction in backend count and breaks session state. Do not choose transaction mode before the audit is complete.
3**Size default_pool_size at the measured knee**, not at the client count. max_client_conn can be much larger — that is the whole point.
4Configure authentication. Copy the SCRAM verifiers from pg_authid into the auth file, or configure auth_query so the pooler reads them from the database. Verifiers are password-equivalent for the purposes of protecting the file.
5Run the pooler as a non-root user. PgBouncer refuses to run as root with FATAL PgBouncer should not run as root, and its runtime and log directories must be owned by that user.
6**Set query_wait_timeout deliberately.** A client waits by default up to 120 seconds for a server connection. Whether that is a graceful queue or a hidden outage depends on the application's own timeouts, and the two should be chosen together.
7Fix the application's session-scoped usage before cutting over. Replace pg_advisory_lock with pg_advisory_xact_lock; replace SET with SET LOCAL or a role setting; scope temporary tables to a transaction or use ON COMMIT DROP; move LISTEN clients to a session-mode pool or a direct connection.
8Test under concurrency, not with one client. With one client and an idle pool the same server connection is handed back every time, so session state appears to survive. That test will pass and it proves nothing.
9Cut over one application at a time, watching backend count and error rate for each before starting the next.
10Confirm the property you bought. SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'; should stay near default_pool_size regardless of how many clients are connected. Measured at 400 clients: 465 backends direct against 65 through a pool of 64.
11Watch the pooler's own views. SHOW POOLS; and SHOW SERVERS; from the pooler's admin console show waiting clients and server connection states.
12Alert on advisory locks held by idle backends, which is the direct detection of the leak transaction pooling can produce: SELECT count(*) FROM pg_locks l JOIN pg_stat_activity a USING (pid) WHERE l.locktype = 'advisory' AND l.granted AND a.state = 'idle';

4 · Verification

Confirm the procedure actually fixed the problem.

✓Backend count on the database stays near default_pool_size while client connections vary. This is the property being bought and it is directly observable.
✓Every application works under concurrency in a test environment before it is cut over in production.
✓No advisory lock is held by a backend in state idle. A leaked session lock blocks other clients on a lock no running query holds.
✓Applications that use SET see the values they expect, checked from inside their own transactions rather than from a separate session.
✓Applications that use temporary tables succeed under concurrency, not merely when run alone.
✓Latency is measured and recorded on both sides, so nobody is surprised later. On a 1.4 ms query at 400 clients the pool was 10 percent faster; on a sub-millisecond query it was three times slower.
✓SHOW POOLS; shows no sustained cl_waiting, or the waiting is understood and bounded by query_wait_timeout.
✓The pooler is monitored and, if it is a single point of failure, that has been recorded as an accepted risk with an owner.

5 · Rollback

If verification fails, undo the procedure in reverse order.

↶Point the application back at the database directly. The pooler is a proxy; removing it from the path is a connection-string or DNS change and nothing in the database needs to change.
↶Confirm the database can accept the resulting connection count before doing so. Reverting a pooler that was introduced because max_connections was exhausted will exhaust it again.
↶If reverting because one application broke, prefer giving that application its own session-mode pool over reverting the whole estate.
↶If session state leaked — an advisory lock held by an idle pooled connection — terminate that server connection through the pooler's admin console or with pg_terminate_backend, and fix the application before re-enabling transaction mode.
↶If the pooler was sized too small and clients are queuing, raise default_pool_size and reload rather than reverting. SHOW POOLS; shows cl_waiting, which is the number to act on.
↶Record what broke and why. The audit that missed it is the artefact to correct, because the same gap will be there next time.

6 · Escalation

When the runbook isn't enough, contact:

· An application uses LISTEN/NOTIFY: escalate to its owner. These cannot work through transaction pooling at all, and the answer is a session-mode pool or a direct connection rather than a configuration adjustment.
· The driver uses server-side prepared statements and cannot be configured otherwise: escalate to the application owner. This is the most common blocker and it is a driver decision.
· The pooler becomes the throughput bottleneck: escalate to the platform owner. PgBouncer is single-threaded per process; on very high query rates that matters, and the answer is more processes with so_reuseport or a different topology.
· The pooler is a single point of failure and nobody has agreed to that: escalate before the cutover. A proxy in front of the database inherits the database's availability requirement.
· Somebody proposes a pooler to make queries faster: escalate with the measurements. It is a capacity and stability tool, and deploying it for speed will disappoint everybody involved.
· The audit cannot be completed because an application's behaviour is not documented and its owners cannot say: escalate rather than cutting over and finding out. The failures are intermittent and hard to attribute.

A pooler’s product is the backend count, not the throughput. Deploy it knowing that, or the first benchmark will look like a failure.

What it actually buys, measured

Two measurements, and they point in opposite directions

On a 1.4 ms query, 400 clients:

direct : 2042 tps, 195.9 ms, 465 server backends
pooled : 2243 tps, 178.4 ms,  65 server backends

Ten percent faster, and the database saw 86 percent fewer connections.

On a sub-millisecond pgbench -S workload, 400 clients:

direct : 109497 tps,  3.65 ms
pooled :  34743 tps, 11.51 ms

Three times slower. PgBouncer is a single-threaded proxy; at 100,000 trivial queries per second it becomes the bottleneck itself.

And at 100 clients against a cluster with max_connections = 100, the direct connection failed outright while the pool served the workload on 10 server connections.

The consistent property across all three is the backend count. That is what you are buying: the server’s position on its own throughput curve stops depending on how many clients happen to arrive.

Transaction mode is a change to application semantics

Everything scoped to a session stops belonging to the client that created it

In session mode a client holds one server connection while it stays connected. In transaction mode the pooler hands out a server connection for one transaction and takes it back — so the next transaction may land somewhere else, and the one just released may go to somebody else.

Broken by transaction pooling:

SET outside a transaction
Temporary tables
Session-level advisory locks
Server-side prepared statements, unless the driver re-prepares
LISTEN/NOTIFY — these cannot work at all
WITH HOLD cursors

server_reset_query = DISCARD ALL clears most of this between transactions, which is why the symptoms are intermittent failures rather than cross-client leakage.

The advisory lock is the exception, and it does leak.

The test that will pass and prove nothing

With one client and an idle pool, the same server connection comes back every time

Measured through a transaction-mode pool with a single client:

SET work_mem = '64MB';
SHOW work_mem;
 work_mem 
----------
 64MB

The SET persisted. Nothing forced a reassignment, so the pool handed the same server connection to both statements.

Transaction pooling only breaks session state when the pool has a reason to reassign connections — which is exactly what does not happen in a single-client test. Test under concurrency or you have tested nothing.

The advisory lock leak

Measured: a session-scoped advisory lock taken through the pool was still held after the client’s transaction ended, with pg_locks reporting it, while the server connection sat idle in the pool. A direct connection then blocked waiting on it and had to be abandoned after 120 seconds.

That produces a symptom worth recognising: clients waiting on a lock that no running query holds.

-- the direct detection
SELECT count(*) FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE l.locktype = 'advisory' AND l.granted AND a.state = 'idle';

The fix is at the application: pg_advisory_xact_lock instead of pg_advisory_lock. That is a strict improvement even without a pooler, because it cannot be leaked by an awkward client disconnect either.

Blast radius

Action	Reversible?	What it costs if wrong
Deploying the pooler alongside	Yes	Nothing until traffic moves
Cutting one application over	Yes, point it back	That application’s session-scoped behaviour
`pool_mode = transaction` without the audit	Yes, but the failures are intermittent	Silent, hard-to-attribute breakage
Sizing `default_pool_size` too small	Yes, reload	Clients queuing up to `query_wait_timeout`
Making the pooler a single point of failure	Deferred	The database’s availability, inherited by a proxy

Give incompatible applications their own pool

[databases]
orders          = host=db1 dbname=orders pool_mode=transaction
orders_sessions = host=db1 dbname=orders pool_mode=session

Better than reverting the estate for one job, and better than leaving one application quietly broken.

Two settings people leave at their defaults

default_pool_size — size it at the measured knee of the throughput curve for your hardware, not at the client count.

query_wait_timeout — 120 seconds by default. Whether that is a graceful queue or a hidden outage depends entirely on the application’s own timeouts. Choose the two together.