Connection Pooling with PgBouncer, Setup That Survives 10K Clients

TL;DR — PgBouncer 1.23 finally supports prepared statements in transaction pooling mode. With the right pool_mode, default_pool_size, and OS limits, one process handles 10k client connections to a few hundred server connections. Don’t forget server_reset_query and TLS.

Every Postgres deployment past a few hundred concurrent clients hits the same wall. Postgres allocates roughly 10 MB of backend memory per connection and forks a process for each one. Your app, meanwhile, has 500 web workers each holding 20 connections in its own internal pool. That’s 10,000 connections, 100 GB of memory, and a kernel doing more context switching than work.

PgBouncer is the answer almost everyone reaches for, and it earns the reputation. But the default config is for a developer laptop. I’ve inherited too many clusters where someone copied pgbouncer.ini from a 2014 blog post, set pool_mode = session, and wondered why Postgres still falls over.

This is the config I actually run in front of Postgres 17 for workloads pushing 10k client connections through a few hundred server backends. PgBouncer 1.23 is the first release I’d recommend for new deployments — it ships prepared statement support in transaction mode, which removes the largest historical reason to avoid it.

The pool mode decision

PgBouncer offers three pool modes, and the choice constrains everything else.

Session pooling assigns one server connection to each client for the life of the client connection. It’s a TCP proxy with auth. You get nothing out of it except SSL termination and accounting. Skip it.

Transaction pooling assigns a server connection only for the duration of a transaction. Between transactions, the server connection returns to the pool and can serve other clients. This is the mode that makes 10k clients possible on 200 servers.

Statement pooling assigns per statement. It breaks multi-statement transactions and almost nothing supports it cleanly. Don’t use it.

The catch with transaction pooling has always been that session state — prepared statements, temporary tables, SET LOCAL outside a transaction, advisory locks — doesn’t survive between transactions. PgBouncer 1.23 fixed the prepared statement half of this with protocol-level support: it tracks which prepared statements each client has issued and re-prepares them on whichever server connection the next transaction lands on.

# pgbouncer.ini
[databases]
app = host=10.0.0.10 port=5432 dbname=app pool_mode=transaction

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

max_prepared_statements is new in 1.23 and defaults to 0 (off). Set it to roughly the number of distinct prepared statements your app issues. Too low and PgBouncer falls back to deallocating on each transaction, which is slow.

Sizing the pool

The math is easier than people make it.

# servers Postgres can sustain. tune Postgres max_connections to match
max_client_conn = 10000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

default_pool_size is the number of server connections per (database, user) pair. With 25 servers per pool and four pools, you’ve got 100 server connections heading to Postgres. Add reserve and admin slack, set Postgres max_connections = 150, and you’re done.

The intuition for 25 comes from queueing theory and a lot of production data. If your average transaction takes 5 ms, 25 server connections can serve 5000 transactions per second per pool. Most workloads are nowhere near that.

The number to actually watch is cl_waiting in SHOW POOLS:

-- from psql connected to the pgbouncer admin db
SHOW POOLS;
-- database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used

If cl_waiting is non-zero for more than a few seconds, you need a bigger pool. If sv_idle is always near default_pool_size, you can shrink it.

OS and kernel tuning

Default file descriptor limits will bite you long before PgBouncer does. Each client connection is one fd. For 10k clients plus servers plus admin plus headroom, you want at least 32k.

# /etc/security/limits.d/pgbouncer.conf
pgbouncer  soft  nofile  32768
pgbouncer  hard  nofile  65536

Under systemd:

# /etc/systemd/system/pgbouncer.service.d/override.conf
[Service]
LimitNOFILE=65536

The other knob is the kernel’s TCP backlog. PgBouncer accepts on one socket, so the kernel queue between SYN-ACK and the application’s accept() matters at burst times:

sysctl -w net.core.somaxconn=4096
sysctl -w net.ipv4.tcp_max_syn_backlog=4096

Set listen_backlog = 4096 in pgbouncer.ini to match.

TLS, auth, and the auth_query trap

In 2024 there’s no excuse for cleartext between app and pooler.

client_tls_sslmode = require
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_key_file = /etc/pgbouncer/server.key
server_tls_sslmode = verify-full
server_tls_ca_file = /etc/ssl/certs/ca.crt

For auth, the old pattern was a static userlist.txt with bcrypt hashes. The modern pattern is auth_query, which lets PgBouncer ask Postgres about user credentials:

auth_type = scram-sha-256
auth_user = pgbouncer
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename = $1

You need to create the pgbouncer role in Postgres and grant it pg_read_server_files plus access to pg_shadow. The trap: auth_query results are cached in PgBouncer’s memory, and there’s no built-in invalidation. If you rotate a password, run RELOAD on the admin console or clients with the old password will keep authenticating until the process restarts.

Postgres’ own client authentication docs cover the SCRAM specifics worth reading once.

What breaks in transaction pooling

Even with 1.23’s prepared statement support, a few things still don’t survive transaction pooling. Know them before you switch.

• SET outside a transaction. Use SET LOCAL inside BEGIN ... COMMIT, or set GUCs in connect_query.
• Session-level advisory locks. Switch to transaction-level (pg_advisory_xact_lock).
• LISTEN/NOTIFY. The notify session belongs to whichever server connection happened to receive it. Use a dedicated session-pooled DB entry for listeners, or move to a real message bus.
• Temporary tables outside a transaction. Use ON COMMIT DROP or schema-qualified work tables.
• Cursors held outside a transaction. Use WITH HOLD cursors or fetch inside the transaction.

I always create a second database entry in pgbouncer.ini with pool_mode = session for the rare client that genuinely needs session state.

Gotchas

A handful of operational landmines I’ve stepped on.

• server_reset_query clears too little or too much. Default DISCARD ALL works for most apps. If you use SET ROLE for row-level security, DISCARD ALL blows away the role, which is what you want. If you set GUCs in connect_query, DISCARD ALL keeps them since they’re per-session defaults — fine.
• ignore_startup_parameters. Modern clients send extra_float_digits, application_name, and friends. Add them or connections fail with cryptic errors.
• Online restart isn’t free. pgbouncer -R re-execs the process passing fds, which works, but client transactions in flight get cancelled. Drain with PAUSE first.
• max_db_connections is per-database, not global. Easy to misconfigure and starve a busy DB.
• PgBouncer is single-threaded. One process maxes around 30k connections at line rate. Past that, run multiple PgBouncer instances behind HAProxy or use SO_REUSEPORT.

If you want to verify the pool is doing its job, the queries that matter are SHOW POOLS, SHOW STATS, and from Postgres SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'. The last number should be close to your pool size, not your client count.

For a deeper look at what makes those backend processes expensive, see reading EXPLAIN ANALYZE like a senior DBA.

What’s Next

PgBouncer 1.23 is the first release I’d put in front of a brand new cluster without reservations. The prepared statement support closes the biggest historical footgun and the SCRAM-SHA-256 implementation is solid.

There are still workloads where you’ll want something else. Citus and pgcat handle multi-tenant routing more cleanly. AWS RDS Proxy gives you IAM-based auth out of the box. Supabase’s Supavisor handles named statements differently. For a self-hosted Postgres 17 cluster behind your own infra, PgBouncer remains the boring, well-understood choice, and boring is what you want at 3 AM.

Get the config right once, monitor cl_waiting, and you’ll forget the pooler exists. Which is the goal.