PgBouncer lets many app connections share a few Postgres connections, so adding pods at peak no longer locks you out of the database.
What breaks at peak
Postgres refuses logins past max_connections, which defaults to 100. The last superuser_reserved_connections slots, 3 by default, stay free for superusers. Your app gets one of these at startup or mid-request, and the request behind it fails:
FATAL: sorry, too many clients already
FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute
PostgreSQL 15 and older print a different second line:
FATAL: remaining connection slots are reserved for non-replication superuser connections
The pod math. Each app process opens its own pool. Total connections are pods × processes per pod × pool size. Four pods running two workers with a pool of 10 hold 80 connections. The autoscaler takes you to 12 pods at peak, the total hits 240, and logins start failing. Migrations, cron jobs and your monitoring want slots too. If you run several Node.js processes per pod, as in the Node.js cluster mode recipe, each one multiplies the pool again. MongoDB drivers multiply the same way, as the MongoDB maxPoolSize recipe shows.
Raising max_connections. Each connection is a separate operating system process with its own memory. It holds catalog and plan caches, and each sort or hash in a query can take up to work_mem more. Before PostgreSQL 14, building a transaction snapshot also slowed as the connection count grew, idle connections included. A higher limit trades a login error for memory pressure and contention between backends.
Connection churn. PHP, serverless functions and some job runners open a connection per unit of work. Each one forks a backend and runs authentication before its first query.
PgBouncer sits between the app and Postgres. Clients connect to it, and it keeps a small pool of real server connections open. It has three pool modes. Session mode holds a server connection for the client’s whole session, so it fixes churn but not the pod math. Transaction mode lends a connection for one transaction, which is what this config uses. Statement mode returns it after every statement and forbids multi-statement transactions, so I only use it for autocommit-only workloads.
The PgBouncer config
This pgbouncer.ini serves one database called app. Replace postgres.internal with your Postgres host.
[databases]
app = host=postgres.internal port=5432 dbname=app
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats
pool_mode = transaction
default_pool_size = 20
max_db_connections = 80
max_client_conn = 2000
reserve_pool_size = 5
reserve_pool_timeout = 3
query_wait_timeout = 30
max_prepared_statements = 200
ignore_startup_parameters = extra_float_digits
Point the app at PgBouncer. The database name in the URL must match the name in [databases]:
- DATABASE_URL=postgres://app:secret@postgres.internal:5432/app
+ DATABASE_URL=postgres://app:secret@pgbouncer.internal:6432/app
Users and passwords. userlist.txt lists the users PgBouncer accepts, one per line. On self-managed Postgres, a superuser can copy the app’s SCRAM secret from pg_authid:
SELECT format('"%s" "%s"', rolname, rolpassword)
FROM pg_authid
WHERE rolname = 'app';
The two admin users exist only inside PgBouncer, which checks them against this file. They don’t need Postgres roles. Add them with plain-text passwords below the app’s line, and save the file with mode 600:
"app" "SCRAM-SHA-256$4096:...the secret from the query..."
"pgbouncer_admin" "change-me-admin"
"pgbouncer_stats" "change-me-stats"
PgBouncer checks client passwords and reuses the app’s secret to log in to Postgres. That reuse works only while the [databases] entry has no user= and the two secrets match byte for byte. If you can’t read pg_authid, as on most managed databases, give the app a plain-text password too: "app" "secret".
auth_query instead of a file. To stop copying secrets for each user, give PgBouncer one lookup user. A superuser that can read pg_shadow creates this function in the app database. RDS, Cloud SQL and most managed services don’t give you such a role, so stay with the file there.
CREATE ROLE pgbouncer_auth LOGIN PASSWORD 'change-me';
CREATE FUNCTION public.pgbouncer_get_auth(p_usename text)
RETURNS TABLE (usename name, passwd text)
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS
$$ SELECT usename, passwd FROM pg_catalog.pg_shadow WHERE usename = p_usename $$;
REVOKE ALL ON FUNCTION public.pgbouncer_get_auth(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.pgbouncer_get_auth(text) TO pgbouncer_auth;
Then add two lines to [pgbouncer]:
auth_user = pgbouncer_auth
auth_query = SELECT usename, passwd FROM public.pgbouncer_get_auth($1)
userlist.txt then needs only pgbouncer_auth and the two admin users. Give pgbouncer_auth its plain-text password there. A SCRAM secret fails with password is SCRAM secret but client authentication did not provide SCRAM keys.
Admin console. Users in admin_users can run PAUSE, RESUME and RELOAD. Users in stats_users can run SHOW commands, which is all the Prometheus exporter needs. Connect to the virtual pgbouncer database:
psql -h pgbouncer.internal -p 6432 -U pgbouncer_admin pgbouncer -c 'SHOW POOLS;'
Pool sizing. default_pool_size sets the server connections per user and database pair. I start near 2 to 4 × the Postgres server’s CPU cores, so a 4-core database starts between 8 and 16. Then I let the load test move it. A bigger pool doesn’t add throughput once the CPUs are busy. It moves the queue from PgBouncer into Postgres, where it costs memory and lock contention.
max_db_connections caps the total for the database across all users. Keep it below max_connections minus the reserved slots, minus what migrations, replicas and monitoring use.
Bursts. reserve_pool_size = 5 lets a pool open five extra server connections once a client has waited reserve_pool_timeout, 3 seconds here. That absorbs a short spike while the base pool stays small. Count the reserve inside your max_connections budget too.
Client limits. max_client_conn sets how many app connections PgBouncer accepts, and it can sit far above max_connections. PgBouncer needs a file descriptor per client and per server connection, so raise its nofile limit to match. It logs max expected fd use at startup next to the kernel limit, so check that line.
Timeouts and parameters. query_wait_timeout = 30 disconnects a client that waited 30 seconds for a server connection. The default is 120. Set it below your request timeout so a stuck client fails fast. ignore_startup_parameters = extra_float_digits lets JDBC and other drivers connect.
Transaction-mode rules. A client may get a different server connection for each transaction, so most session state doesn’t carry over.
- Session-level
SET, session advisory locks,LISTENand temporary tables that outlive a transaction break.NOTIFYworks. UseSET LOCALand transaction-level advisory locks. - PgBouncer does track a few parameters per client, such as
TimeZoneandapplication_name, plussearch_pathon Postgres 18.track_extra_parametersadds others that Postgres reports to the client. - Protocol-level prepared statements work on PgBouncer 1.21 and later once
max_prepared_statementsis above zero. Since 1.24 it defaults to 200. SQLPREPAREstill doesn’t work. - On older PgBouncer, turn prepared statements off in the driver. Prisma takes
?pgbouncer=true, Rails takesprepared_statements: false, JDBC takesprepareThreshold=0and asyncpg takesstatement_cache_size=0. - Django’s
.iterator()uses server-side cursors, which need a session. SetDISABLE_SERVER_SIDE_CURSORS: Trueon the database. server_reset_queryisn’t used in transaction mode, so leave it at the default.- Run migrations straight against Postgres on port 5432. Migration tools take session advisory locks and run long DDL.
One core per process. PgBouncer is single-threaded. Once one process uses a full core, run several on the same port with so_reuseport = 1. Give each its own pidfile and unix_socket_dir, and set up [peers] so query cancels reach the right process. Pool limits apply per process, so divide max_db_connections between them.
Placement. A single PgBouncer is a single point of failure. On Kubernetes you have two choices, and I default to the second:
- Run it as a sidecar in each app pod. A PgBouncer outage takes down one pod. The pod math comes back, though, since each sidecar holds its own server pool.
- Run it as a central Deployment with two or more replicas behind a Service, plus a PodDisruptionBudget so a node drain leaves one running. You control the total server connections in one place. Each replica’s
max_db_connectionsadds up, so divide your budget between them.
Managed poolers do the same job. AWS has RDS Proxy. Supavisor and PgCat are multi-threaded open source poolers. Check each one’s support for prepared statements and session state before you switch.
If clients open new connections at a high rate, the client hosts can run out of local ports. Linux network tuning for web servers covers that limit.
Test it on your system
Run pgbench from a staging host against a staging database with production-like size and max_connections. Load the test data straight into Postgres, then compare direct and pooled runs at a client count above max_connections:
export PGPASSWORD=secret
pgbench -i -s 50 -h postgres.internal -p 5432 -U app app
# direct, expect "too many clients" once -c passes max_connections
pgbench -h postgres.internal -p 5432 -U app -c 400 -j 8 -T 300 -P 10 -S app
# through PgBouncer at a fixed rate, logging 10% of transactions
pgbench -h pgbouncer.internal -p 6432 -U app -c 400 -j 8 -T 300 -P 10 -S \
-R 2000 -L 200 -l --sampling-rate=0.1 app
# connection churn, a new connection per transaction with -C
pgbench -h pgbouncer.internal -p 6432 -U app -c 100 -j 8 -T 300 -P 10 -S -C -R 500 app
Without -R, pgbench is closed-loop: each client waits for its last transaction before it sends the next, so a slow database gets less load and the latency looks better than it is. -R holds a fixed rate, so set it to your peak transactions per second. -L counts transactions slower than that many milliseconds as late and reports them.
The -l run writes pgbench_log.* files whose third column is latency in microseconds. This prints p99 in milliseconds:
cat pgbench_log.* | awk '$3 ~ /^[0-9]+$/ {print $3}' | sort -n \
| awk '{v[NR]=$1} END {print "p99 ms:", v[int(NR*0.99)]/1000}'
pgbench aborts the whole run when one client can’t connect. A real app gets some connections and throws errors on the rest. For your own traffic shape, write your hottest queries into a script and pass it with -f.
While each run goes, watch both sides:
-- on PgBouncer's admin console
SHOW POOLS; -- cl_waiting and maxwait
-- on Postgres
SELECT state, count(*) FROM pg_stat_activity WHERE datname = 'app' GROUP BY state;
Write your targets down before the pooled run. It passes when neither error string appears and Postgres connections stay at or under max_db_connections. maxwait should stay under your latency budget, with cl_waiting draining between bursts. The p99 from the log and the late count from -L should both meet the targets you set.
A steady cl_waiting count means the pool is too small or the queries are too slow. I check pg_stat_statements for the slow queries before I grow the pool, because a bigger pool only hides them.
Keep it healthy
Scrape PgBouncer with pgbouncer_exporter, connecting as pgbouncer_stats, and Postgres with postgres_exporter.
groups:
- name: postgres-connections
rules:
- alert: PgBouncerClientsWaiting
expr: max by (instance, database) (pgbouncer_pools_client_maxwait_seconds) > 1
for: 2m
labels:
severity: critical
annotations:
summary: "Clients waited over 1 s for a server connection on {{ $labels.database }}"
description: "Requests are queueing in PgBouncer. Check pg_stat_activity for long transactions, then raise default_pool_size if Postgres has CPU to spare."
- alert: PgBouncerQueueBuilding
expr: sum by (instance, database) (pgbouncer_pools_client_waiting_connections) > 0
for: 10m
labels:
severity: warning
annotations:
summary: "Clients have queued for {{ $labels.database }} for 10 minutes"
description: "The pool runs at its limit for long stretches. Find the slow queries in pg_stat_statements, or plan a bigger pool before the next peak."
- alert: PostgresConnectionsNearMax
expr: sum by (instance) (pg_stat_activity_count) / on (instance) pg_settings_max_connections > 0.8
for: 5m
labels:
severity: warning
annotations:
summary: "{{ $labels.instance }} is using over 80% of max_connections"
description: "Find clients connecting around PgBouncer in pg_stat_activity, or lower max_db_connections so the pool fits."
Also alert on these log lines:
FATAL: sorry, too many clients already
FATAL: remaining connection slots are reserved
no more connections allowed (max_client_conn)
query_wait_timeout
The first two come from Postgres. They mean something connects around PgBouncer, or its limits don’t fit under max_connections. The last two come from PgBouncer and point to a pool that’s too small, slow queries or a low client limit.
Roll back
PgBouncer sits in the connection path, so rollback is a connection-string change. Point the app back at port 5432 and redeploy:
- DATABASE_URL=postgres://app:secret@pgbouncer.internal:6432/app
+ DATABASE_URL=postgres://app:secret@postgres.internal:5432/app
First check that the app’s total pool across all pods fits under max_connections. If it doesn’t, you’ll trade queueing for refused logins. If PgBouncer is causing errors, such as prepared statement failures after the switch, roll back now and scale the app down to fit. Otherwise pick an off-peak window, when the pod count is at its lowest.
For Postgres maintenance without dropping clients, run PAUSE app; on the admin console. PgBouncer waits for running transactions to finish and holds new ones. Run RESUME app; when you’re done.
More recipes
- PostgreSQL Autovacuum Tuning for Bloat and Wraparound
Give autovacuum the budget and per-table thresholds your write rate needs, and clear what blocks it, so tables stop bloating and wraparound never stops writes.
- Patroni PostgreSQL High Availability and Failover
Set up Patroni automatic failover with health-checked routing and test it under load, so a dead primary costs seconds of errors and no lost writes.
- PostgreSQL Load Testing with pgbench
Use pgbench for PostgreSQL load testing at fixed, rising rates with your own queries, so you find the database's breaking point before your users do.
- Monitor PostgreSQL with postgres_exporter
Run postgres_exporter with the right collectors and alert on PostgreSQL replication lag, connections and stuck transactions in Prometheus before users notice.