PostgreSQL Connection Pooling with PgBouncer

PgBouncer in transaction mode lets hundreds of app connections share a small pool of Postgres connections, so scaling out pods stops hitting max_connections.

Published

Applies to PgBouncer 1.21 and later (prepared statements on by default from 1.24); PostgreSQL 14 and later

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, LISTEN and temporary tables that outlive a transaction break. NOTIFY works. Use SET LOCAL and transaction-level advisory locks.
  • PgBouncer does track a few parameters per client, such as TimeZone and application_name, plus search_path on Postgres 18. track_extra_parameters adds others that Postgres reports to the client.
  • Protocol-level prepared statements work on PgBouncer 1.21 and later once max_prepared_statements is above zero. Since 1.24 it defaults to 200. SQL PREPARE still doesn’t work.
  • On older PgBouncer, turn prepared statements off in the driver. Prisma takes ?pgbouncer=true, Rails takes prepared_statements: false, JDBC takes prepareThreshold=0 and asyncpg takes statement_cache_size=0.
  • Django’s .iterator() uses server-side cursors, which need a session. Set DISABLE_SERVER_SIDE_CURSORS: True on the database.
  • server_reset_query isn’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_connections adds 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

Further reading