Stackbook Logo
← Back to Tools

pgbouncer Connection Pooling

Scale PostgreSQL connections with pgbouncer: pooling modes, configuration, monitoring through the admin console and prepared-statement pitfalls.

4 min read

What it is and when to use it

pgbouncer is a lightweight connection pooler for PostgreSQL. PostgreSQL forks a process per connection, so thousands of client connections waste memory and degrade throughput. pgbouncer accepts many client connections and multiplexes them over a small number of real server connections.

Use it when you have many application instances, serverless functions or short-lived workers, when you see too many connections errors, or when connection setup cost is visible in latency. A modest app with a few long-lived connections may not need it.

Quick start

docker run -d --name pgb -p 6432:6432 \
  -e DATABASE_URL="postgres://app:secret@db:5432/appdb" \
  -e POOL_MODE=transaction \
  -e DEFAULT_POOL_SIZE=20 \
  edoburu/pgbouncer

psql "postgres://app:secret@localhost:6432/appdb"

Applications then connect to port 6432 instead of 5432, with no other code changes in the common case.

Core configuration

A hand-written pgbouncer.ini:

[databases]
appdb = host=10.0.0.5 port=5432 dbname=appdb

[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 = 2000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
admin_users = pgbouncer_admin

The userlist.txt file holds quoted entries of user name and password hash. Copy the SCRAM verifier from pg_shadow rather than storing plain text, or set auth_query so pgbouncer looks users up in the database itself.

Pooling modes

Session mode assigns a server connection for the whole client session. It is the safest and the least efficient.

Transaction mode returns the server connection to the pool after each transaction. It gives the biggest multiplexing gain and is the usual choice for web applications.

Statement mode returns it after every statement and forbids multi-statement transactions. It is rarely appropriate.

Sizing

Active server connections should roughly match what the database can run efficiently, often two to four times the CPU core count. If the database has 8 cores, a total of about 20 to 30 server connections across all pools usually performs better than hundreds. Compute the sum of default_pool_size across every database and user pair, and keep it below PostgreSQL max_connections with room for admin and replication sessions.

Monitoring

Connect to the virtual admin database:

psql -p 6432 -U pgbouncer_admin pgbouncer

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;
RELOAD;
PAUSE appdb;
RESUME appdb;

In SHOW POOLS, a growing cl_waiting means clients are queued for a server connection, so increase the pool or speed up queries. maxwait shows how long the oldest client has waited.

Pitfalls

  • Session state in transaction mode. Features that rely on a persistent session break: SET commands, advisory locks held across transactions, temporary tables, LISTEN and NOTIFY, and session-level cursors. Use SET LOCAL inside transactions instead.
  • Prepared statements. Protocol-level prepared statements historically failed in transaction mode. Recent pgbouncer versions support them through max_prepared_statements, for example set to 100. Otherwise disable them in the driver.
  • Pool too large. More server connections do not mean more speed. They create contention.
  • Single instance as a single point of failure. Run two behind a load balancer or deploy it as a sidecar.
  • Migrations through the pool. Schema tools that use advisory locks should connect directly to PostgreSQL.
  • Idle in transaction clients hold a server connection. Set idle_transaction_timeout.
  • Password hash mismatches after rotating credentials cause authentication failures until the auth file is updated and reloaded.

How it fits an architecture

Place pgbouncer between the application tier and PostgreSQL, either on the database host, as a sidecar or as a small dedicated tier. Send writes to the primary pool and reads to a separate pool pointing at replicas. In Kubernetes, many pods connect to a few pgbouncer instances, which keeps the database connection count flat as you scale out. Combine it with query tuning and monitoring for the full picture.

Key takeaways

  • pgbouncer multiplexes many clients over few PostgreSQL connections.
  • Transaction mode is the default for web workloads, but it breaks session state.
  • Size pools to database cores, not to client count.
  • Watch cl_waiting and maxwait in SHOW POOLS.
  • Run migrations and long-lived session features over a direct connection.
  • Deploy redundantly so the pooler is not a single point of failure.