How to Set Up Database Connection Pooling with PgBouncer

Why you need connection pooling

Each PostgreSQL connection forks a backend process and reserves memory, often several megabytes. Postgres handles a few hundred connections comfortably, but modern apps, especially serverless and autoscaled containers, can open thousands of short-lived connections. When you exceed max_connections, new connections are rejected and your app throws errors under exactly the load you most need it to survive.

Connection pooling sits between your app and Postgres, maintaining a small set of reusable backend connections and multiplexing many client connections over them. PgBouncer is the lightweight, battle-tested standard.

The three pooling modes

This is the part people get wrong. PgBouncer offers three modes, and choosing the wrong one will silently break features.

Transaction mode is the right default for most web applications and gives you the best connection reuse. But it has a critical caveat: anything that depends on session state across transactions, SET that persists, session-level advisory locks, LISTEN/NOTIFY, certain prepared-statement patterns, will not behave as expected because the next transaction might land on a different server connection.

Installing and configuring PgBouncer

A minimal pgbouncer.ini:

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000      ; how many clients can connect to PgBouncer
default_pool_size = 20      ; server connections per (user, db) pair
reserve_pool_size = 5
server_idle_timeout = 600

The userlist.txt maps usernames to password hashes:

"myapp_user" "SCRAM-SHA-256$4096:..."

Then your application connects to PgBouncer (port 6432) instead of Postgres (5432):

postgresql://myapp_user:***@pgbouncer-host:6432/myapp

Sizing the pool correctly

The most common mistake is setting default_pool_size too high. The pool size is how many *actual* Postgres connections PgBouncer will open. A good starting heuristic for a CPU-bound workload is roughly (number of CPU cores * 2) + effective spindle count, but for most web apps you'll land between 10 and 25 per instance. The whole point is that you can serve 1000 clients with 20 backend connections.

Keep default_pool_size * (number of PgBouncer instances) comfortably below Postgres max_connections, leaving headroom for migrations, admin sessions, and monitoring.

The prepared-statement gotcha in transaction mode

Many ORMs and drivers use server-side prepared statements by default. In transaction mode these can break because the prepared statement lives on a server connection the next transaction may not get. Solutions:

• Use a driver/ORM setting to disable server-side prepared statements (e.g., in some Node and Python drivers).
• Or use a client library that's PgBouncer-aware. Recent PgBouncer versions added improved prepared-statement support in transaction mode via max_prepared_statements, set it if your version supports it:

max_prepared_statements = 200

Test this explicitly, it's the single most common production surprise with PgBouncer.

Verifying it works

PgBouncer exposes an admin console over the same protocol:

-- Connect to the special 'pgbouncer' database
SHOW POOLS;    -- active/waiting clients and servers per pool
SHOW STATS;    -- request rates and traffic
SHOW CLIENTS;  -- connected clients

If cl_waiting is consistently high, your pool is too small or queries are too slow. If sv_idle is always large, you've over-provisioned.

Pooling on a managed platform

You don't always have to run PgBouncer yourself. On PandaStack, managed PostgreSQL (14.x and 16.x, via KubeBlocks on GKE) comes with connection limits per plan, Free includes 50 connections, Pro 300, Premium 1000. Because connections are a managed, finite resource, pooling is still the right pattern: run a pooler (PgBouncer in transaction mode) in front of your database so a fleet of autoscaled app pods don't each grab raw connections.

A clean pattern on any Kubernetes-based platform is to run PgBouncer as a sidecar or a small dedicated service, and point all app instances at it. Since PandaStack injects DATABASE_URL into your service, you can route it through your pooler and keep the app code unchanged.

# App talks to PgBouncer; PgBouncer talks to the managed Postgres.
# DATABASE_URL (injected) is consumed by PgBouncer's config, not the app directly,
# or the app points at the pooler's host:6432.

Checklist before production

• [ ] Chose transaction mode (unless you truly need session features).
• [ ] Verified prepared statements work with your driver, or disabled them.
• [ ] Sized default_pool_size well under Postgres max_connections.
• [ ] Pointed the app at the pooler port (6432), not Postgres directly.
• [ ] Watched SHOW POOLS under realistic load.
• [ ] Set sane timeouts (server_idle_timeout, query_wait_timeout).

Conclusion

Connection pooling is one of those things that's invisible until traffic spikes and your database starts refusing connections. PgBouncer in transaction mode, sized conservatively and tested against your driver's prepared-statement behavior, solves the problem cheaply. Set it up before you need it, not during the incident.

If you'd rather not operate the database layer at all, PandaStack's managed PostgreSQL handles backups and connection limits for you, and the free tier includes a database to experiment with pooling. Start at https://dashboard.pandastack.io.

References

• PgBouncer documentation: https://www.pgbouncer.org/config.html
• PgBouncer pooling modes: https://www.pgbouncer.org/features.html
• PostgreSQL connections and authentication: https://www.postgresql.org/docs/current/runtime-config-connection.html
• PostgreSQL max_connections: https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS