Skip to content

CloudNativePG pooler or direct connections?

Last updated

View as Markdown

Quick answer: Most of a pooled failover is not the pooler. Measured across five failovers on CloudNativePG 1.30.0 and PostgreSQL 18.1, cluster dbg-cnpg-pool, 2026-09-20, the cluster’s own promotion took 19.13 seconds of a 30.27 second pooled outage, and one PgBouncer parameter closed almost all of the rest. The larger cost is backends rather than latency: four poolers on one cluster held 94 of 100 connections and locked direct clients out.

CloudNativePG can create a Pooler, which is a Deployment of PgBouncer pods plus a Service, sitting in front of one of the cluster’s roles:

apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
name: app-db-pooler-rw
spec:
cluster:
name: app-db
instances: 2
type: rw
pgbouncer:
poolMode: transaction
parameters:
max_client_conn: "500"
default_pool_size: "20"

PgBouncer works. The question is what it costs. The usual answers get both halves wrong: the failover cost is smaller and more fixable than it looks, and the capacity cost is larger.

clusterk3d dbg-cnpg-pool, context k3d-dbg-cnpg-pool, namespace pooltest
operatorCloudNativePG 1.30.0
databasePostgreSQL 18.1, ghcr.io/cloudnative-pg/postgresql:18.1, 3 instances
max_connections100
pooler imageghcr.io/cloudnative-pg/pgbouncer:1.25.1
date2026-09-20

That image version is not in the manifest above and cannot be read from it. The manifest pins nothing, so CloudNativePG deploys the image it was built against. Read the resolved one from the resource:

Terminal window
kubectl get pooler app-db-pooler-rw -o jsonpath='{.status.image}'

Three Services already exist, and they differ only in their label selector:

serviceselectsuse it for
app-db-rwcnpg.io/instanceRole: primaryeverything that writes
app-db-rocnpg.io/instanceRole: replicaread-only traffic that must stay off the primary
app-db-rcnpg.io/podRole: instanceanything that can use any instance

That gives you failover-aware routing at no extra cost. When the primary changes, the operator moves the label and the -rw endpoint follows.

One caveat on -ro: it is a routing primitive, not a consistency contract. Replicas are asynchronous by default, so a read sent there after a committed write can be stale.

Failover: measure the paths in the same failover

Section titled “Failover: measure the paths in the same failover”

The obvious experiment is to kill the primary, time the direct path, kill it again, and time the pooled path. That experiment does not work.

Promotion latency is not constant. It varied across the five trials on cluster dbg-cnpg-pool, CloudNativePG 1.30.0 and PostgreSQL 18.1, 2026-09-20, so two failovers measured separately differ by the path and by how long promotion took, and there is no way afterwards to say which. An early run of exactly that design produced 6.7 seconds direct against 32.4 pooled, a difference of 26 seconds, and the real difference is about 9.

So every path is probed at once, in one failover on cluster dbg-cnpg-pool, CloudNativePG 1.30.0 and PostgreSQL 18.1, 2026-09-20, with the cluster’s own promotion timestamp recorded alongside. The probe writes one row every 200 ms on a fresh connection with PGCONNECT_TIMEOUT=2, and the outage is measured from the last success before the failure block to the first success after it.

Five failovers on CloudNativePG 1.30.0 and PostgreSQL 18.1, cluster dbg-cnpg-pool, 2026-09-20, seconds:

trialpromotiondirect -rwpooled, defaultspooled, server_login_retry: "1"
119.1322.3930.3421.27
219.5422.5930.2721.02
319.3321.6230.6320.39
419.0519.3330.2018.84
518.9420.4530.2220.54
median19.1321.6230.2720.54

The promotion column is the new primary’s own log line, database system is ready to accept connections, from the CloudNativePG 1.30.0 instance on 2026-09-20.

The results separate the database, Service routing, and pooler delays.

Most of the outage is the database, on every path. On cluster dbg-cnpg-pool, 2026-09-20, promotion is a median 19.13 seconds of a 21.62 second direct recovery and a 30.27 second pooled one. If that number is too high for you, the pooler is not the thing to change.

Direct costs 2.49 seconds over promotion at the median, 21.62 against 19.13 in the table above, and Service endpoint propagation is the same size as that gap. Measured by polling the pool-db-rw EndpointSlice every 50 ms against the new primary’s own database system is ready to accept connections log line, over eight failovers separate from the five trials in the table, cluster dbg-cnpg-pool, CloudNativePG 1.30.0, PostgreSQL 18.1, 2026-09-20: 0.594, 0.926, 1.179, 1.187, 1.825, 2.026, 2.340 and 3.042 seconds from ready to the endpoint carrying the new primary. Seven of the eight are under the 2.49 second gap and one is over it, so on this cluster propagation can be the whole of it. There is not much to do about that part.

The pooler costs about 9 seconds over direct, and one parameter removes it. With server_login_retry at 1 the pooled median is 20.54 against 21.62 direct, on CloudNativePG 1.30.0 and PostgreSQL 18.1, cluster dbg-cnpg-pool, 2026-09-20. The pooled path became, within the spread of these five trials, as fast as the direct one.

The log explains that result. These are both pooler pods through a single failover, on PgBouncer 1.25.1 under CloudNativePG 1.30.0, 2026-09-20:

podcrash detectedfirst retry failedsecond retryreconnected
...st6rz14:13:06.44314:13:09.43014:13:24.46114:13:39.659
...9dl8414:13:06.44514:13:06.45814:13:21.61414:13:36.677

Two consecutive gaps of just over 15 seconds each, in both pods on cluster dbg-cnpg-pool, PgBouncer 1.25.1 under CloudNativePG 1.30.0 with PostgreSQL 18.1, 2026-09-20. That is server_login_retry, whose default is 15 seconds, applied twice. On that same cluster dbg-cnpg-pool, PgBouncer 1.25.1 under CloudNativePG 1.30.0 with PostgreSQL 18.1, 2026-09-20, the first retry lands almost at once: 2.987 seconds after the crash on one pod and 13 milliseconds on the other, well inside the promotion either way, so both fail. PgBouncer then waits the full 15 seconds before the second retry.

The second retry fails too, and that is the one that costs you. On cluster dbg-cnpg-pool, PgBouncer 1.25.1 under CloudNativePG 1.30.0 with PostgreSQL 18.1, 2026-09-20, the second retry landed 18.018 seconds after the crash on one pod and 15.169 on the other, and the slowest of the five promotions in the table above took 19.54 seconds while the fastest took 18.94, so on every one of them the database was still not there. The wait that runs past promotion is the second of the two, the interval that second failure triggered, and the pods reconnected 33.216 and 30.232 seconds after the crash, bracketing the pooled default median of 30.27 seconds in the table further up.

The interval is fixed, so it does not shrink when your database recovers quickly. You pay whole intervals until one of them happens to end after promotion, which is why the 11.14 second gap between the 19.13 median promotion and the 30.27 median pooled recovery is not a measurement of the database.

Two claims commonly made about this do not survive the log. The pods do not discover the change at meaningfully different times: the two crash-detection stamps above are two milliseconds apart, and it is the first retry that lands three seconds apart. There is no flapping period. Run-length encoding every probe trace in every configuration gives one contiguous block of failures and then success, with no alternation.

When reading your own logs, note that every retry that failed was on the cnpg_pooler_pgbouncer connection to the postgres database, which is the auth-query connection, not on app/app. The application pool cannot serve until that one succeeds.

server_connect_timeout is not what decides this, and the comparison that shows it needs its control named. Lowering it to 2 was measured on a pooler that also carried server_login_retry: "1", so the control is the pooler carrying only the login retry, not the pooled defaults. On cluster dbg-cnpg-pool, PgBouncer 1.25.1 under CloudNativePG 1.30.0 with PostgreSQL 18.1, 2026-09-20:

pooler settingsseconds to reconnect
server_login_retry: "1" alone, five trials18.84 to 21.27
server_login_retry: "1" and server_connect_timeout: "2", four trials20.99 to 24.71

Both rows are ranges because only the first row’s trials were kept individually, and its median is the 20.54 above. Adding the shorter connect timeout to the shorter login retry bought nothing on cluster dbg-cnpg-pool, PgBouncer 1.25.1 under CloudNativePG 1.30.0 with PostgreSQL 18.1, 2026-09-20. Neither row belongs beside the pooled defaults at 30.20 to 30.63 seconds, which had server_login_retry at its default of 15: that gap is the login retry.

A pooler is sold as protection for max_connections. Configured the obvious way it multiplies your backends instead, and the arithmetic is not in the manifest.

default_pool_size is the limit per user and database pair, and each PgBouncer pod applies it independently.

One Pooler, instances: 2, default_pool_size: "20", one pair, 60 clients each holding an open transaction, on PostgreSQL 18.1 and PgBouncer 1.25.1, 2026-09-20:

value
sv_active on pod 120
sv_active on pod 220
backends PostgreSQL reported41
clients queued20

Forty backends from a manifest whose only number is 20. The forty-first was a PgBouncer connection running DISCARD ALL.

Add a second role and database and you get a second set of pools, not a shared one. With 25 clients on each of two pairs through the same single Pooler, PostgreSQL reported 26 backends for one pair and 25 for the other, 51 in total, on PostgreSQL 18.1 and PgBouncer 1.25.1, 2026-09-20. Neither pair reached its own ceiling of 40, so 51 is what two pairs cost at this load, not what they can cost.

So the effective ceiling is:

default_pool_size x user/database pairs x pooler instances

With four poolers on this three-instance cluster, PostgreSQL held 82 app backends and 12 cnpg_pooler_pgbouncer backends, which is 94 of max_connections 100 on PostgreSQL 18.1, 2026-09-20. A direct client then got:

FATAL: sorry, too many clients already
FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute

The component added to protect the connection limit had consumed it, and it locked out the path that was not using it.

Neither max_db_connections nor max_user_connections was set, both reading 0 in SHOW CONFIG on PgBouncer 1.25.1, 2026-09-20. Those are the caps that make the ceiling a real one, and CloudNativePG does not set them for you.

Watch the queue, because that is what you bought

Section titled “Watch the queue, because that is what you bought”

PgBouncer does not create throughput. When every server connection is busy it converts backend contention into a client queue, and query_wait_timeout decides how long a client waits before it is dropped. It read 120 seconds on PgBouncer 1.25.1 under CloudNativePG 1.30.0, PostgreSQL 18.1, cluster dbg-cnpg-pool, 2026-09-20.

The SQL admin console is not reachable through the Service. Connecting to the pgbouncer database through it returns FATAL: not allowed, measured on the CloudNativePG 1.30.0 pooler, 2026-09-20, because CloudNativePG permits it only over local peer-authenticated connections.

That does not mean diagnostics are unavailable. Each pooler pod exposes a metrics port, and it publishes the pool counters:

Terminal window
kubectl port-forward <pooler-pod> 9127:9127 &
curl -s http://127.0.0.1:9127/metrics | grep cl_waiting

It is a Prometheus endpoint, so reaching it takes an HTTP request. The pgbouncer container’s shell has no HTTP client, which is why the port is forwarded rather than read from inside the pod.

Under 60 clients against a pool of 20, cnpg_pgbouncer_pools_cl_waiting read 11 on the CloudNativePG 1.30.0 exporter, 2026-09-20. That endpoint belongs to one pod, so 11 is one pod’s share of the 20 queued clients counted above, not the total. That counter, and maxwait, are what tell you the pool is too small. Alert on them.

For pod-local administration, the socket is there:

Terminal window
kubectl exec <pooler-pod> -c pgbouncer -- \
psql -U pgbouncer -h /controller/run pgbouncer -c 'SHOW POOLS'

Session state: transaction mode loses it, session mode does not

Section titled “Session state: transaction mode loses it, session mode does not”

Transaction pooling assigns a server connection for the length of a transaction and then hands it to somebody else. A statement outside an explicit transaction is its own transaction, so two statements from one client can run on two backends.

Sixty concurrent clients, each setting statement_timeout to a distinct value, then reading it back in a separate statement, on PostgreSQL 18.1 through PgBouncer 1.25.1, 2026-09-20:

pathread back their own valueread back something else
transaction pooling33 of 6027 of 60
session pooling60 of 600 of 60

One transaction-pooled client asked for 31038ms and read back 31042ms, which was another client’s setting. Sixty clients used 40 distinct backends.

The warning belongs to transaction pooling, not to poolers. Session pooling kept every one of the sixty, at the cost of holding a server connection for the life of the client connection, which puts you back to counting connections.

Why your own test may have passed. There are two reasons, and both are easy to hit.

The first is application_name. Set at connection startup it survived two transactions through the transaction pooler on 2026-09-20, because PgBouncer tracks the standard startup parameters and replays them. Test with it and nothing breaks.

The second is no contention. With two clients and a pool of twenty, server connections are not shared and nothing moves. The sixty-client run above is what forces the sharing.

Prepared statements: two different things with one name

Section titled “Prepared statements: two different things with one name”

The distinction decides the answer, because CloudNativePG ships max_prepared_statements at 200, read from SHOW CONFIG on PgBouncer 1.25.1, 2026-09-20. It is not zero, so one of the two kinds works.

Protocol-level named prepared statements work. The same named statement, parsed once and bound twice in two separate transactions through the transaction pooler, returned 42 and 101 on PostgreSQL 18.1, 2026-09-20. PgBouncer tracks it and re-prepares it on whichever backend it hands you.

SQL-level PREPARE and EXECUTE does not, across transactions, and it is the one that gives a false pass. PREPARE then EXECUTE in a later transaction succeeded on the first try here with a single client, because nothing forced the backend to change. Under real concurrency it will not.

Other session features behave as PgBouncer documents. LISTEN returns LISTEN in both modes, which does not make the client subscribed under transaction pooling, because the notification arrives on a connection that is no longer yours. Put listeners on a direct or session-pooled connection. NOTIFY works through the transaction pooler and does not need one.

It is not a binary choice. There are four workable shapes, and the question is which traffic goes where:

trafficpathwhy
few long-lived clients, any session statedirect, with a bounded pool in the applicationnothing to gain, and transaction pooling would break it
high fan-in, short transactions, statelesstransaction-mode poolerthis is the case it is for
LISTEN, session advisory locks, persistent temp tables, SQL PREPAREdirect or session-mode poolertransaction pooling loses these
an agent that holds a transaction open while it thinksfix the transaction scope firsta transaction pool only multiplexes if transactions are short

Both paths can exist at once: app-db-rw and app-db-pooler-rw are two Services in front of one database.

Count before you add anything:

SELECT usename,
application_name,
state,
count(*) AS connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, application_name, state
ORDER BY connections DESC;
SELECT (SELECT count(*) FROM pg_stat_activity
WHERE backend_type = 'client backend') AS in_use,
current_setting('max_connections')::int AS max_connections;

That query returns a snapshot, not an arrival rate or a peak, and connection cost is not only process count: every backend also carries its share of work_mem and the reserved slots are real, as the remaining connection slots are reserved error above shows. A comfortable snapshot is not proof you have headroom.

Add a pooler when many short-lived clients each open their own connection, when serverless or per-request connections make the churn itself expensive, or when you want an admission-control point with a queue you can watch.

“Put PgBouncer in front of it” is the standard answer to a connection problem and it is often right. What gets generated with it is the manifest at the top of this page, and that manifest contains one number, default_pool_size: "20", which reads like a cap and is not one. The failure arrives later, as a connection limit hit by the traffic that was not using the pooler.

Do the pool arithmetic before you apply the manifest. default_pool_size times pairs times instances, against max_connections, with headroom for superuser-reserved slots and the operator’s own connections. Set max_db_connections or max_user_connections, because CloudNativePG does not.

Set server_login_retry deliberately. The default is 15 seconds and it is applied more than once, which was about 9 of the 30 seconds measured on PgBouncer 1.25.1 under CloudNativePG 1.30.0, cluster dbg-cnpg-pool, 2026-09-20.

Measure failover on all paths in one failover. Separate runs attribute the cluster’s own promotion variance to whichever path you happened to test that time.

Test session state with a variable PgBouncer does not track, under concurrency. application_name at startup and a two-client test both pass when they should not.

Alert on cl_waiting and maxwait from the metrics port. The SQL admin console is local-only, but the counters are still available.

  • Does the CloudNativePG pooler slow down failover?

    Less than it looks. Measured on CloudNativePG 1.30.0 with PostgreSQL 18.1 on k3d cluster dbg-cnpg-pool, 2026-09-20, probing every path in the same failover across five trials, the cluster's own promotion took a median 19.13 seconds. Direct recovered at 21.62 and the pooler at 30.27, on cluster dbg-cnpg-pool. The pooler's own share is about 9 seconds, not the whole outage, on CloudNativePG 1.30.0 and PostgreSQL 18.1, cluster dbg-cnpg-pool, 2026-09-20.

  • Can I make the pooler fail over as fast as a direct connection?

    Nearly. Setting server_login_retry to 1 took the pooled median from 30.27 seconds to 20.54, against 21.62 direct and 19.13 for promotion itself, on CloudNativePG 1.30.0 and PostgreSQL 18.1, 2026-09-20. The default is 15 seconds and PgBouncer waits it twice, measured on cluster dbg-cnpg-pool, because the first two retries both land while the database is still promoting.

  • Is default_pool_size a cap on PostgreSQL connections?

    No, and this is the trap. It is per user and database pair, per PgBouncer pod. One Pooler with instances 2 and default_pool_size 20 held 41 backends: 20 per pod, plus one PgBouncer connection running DISCARD ALL. Four poolers on one cluster held 94 of max_connections 100, and direct clients were then refused with sorry, too many clients already.

  • Is transaction pooling safe for my application?

    Only if it holds no session state. With 60 concurrent clients each setting statement_timeout to a distinct value, 33 read back their own and 27 did not, on PostgreSQL 18.1 through PgBouncer 1.25.1, 2026-09-20. Through the session-mode pooler all 60 read back their own.

  • Do prepared statements work through transaction pooling?

    Protocol-level named prepared statements do, because CloudNativePG 1.30.0 on cluster dbg-cnpg-pool ships PgBouncer 1.25.1 with max_prepared_statements at 200. Measured 2026-09-20, the same named statement returned 42 and 101 across two separate transactions. SQL-level PREPARE and EXECUTE is the one that breaks, and it passes a single-client test because nothing forces the backend to change.