Every PostgreSQL connection is a separate server process using several megabytes of memory and some CPU to manage. Applications that open many connections — PHP-FPM with dozens of workers, several app servers, serverless functions, Django with CONN_MAX_AGE=0 — can overwhelm PostgreSQL with connections that mostly sit idle. PgBouncer is a lightweight proxy that keeps a small pool of real connections and shares them among many clients.

How it works

App (hundreds of client connections) ──► PgBouncer :6432 ──► PostgreSQL :5432 (e.g. 20 server connections)

PgBouncer accepts client connections cheaply and hands each client a real server connection only when needed.

Pool modes

Mode Server connection is assigned… Compatibility
session For the whole client session Everything works; least pooling benefit
transaction Only for the duration of a transaction Best efficiency; session-level features don’t work across transactions
statement Per statement Multi-statement transactions not allowed; rarely used

Transaction mode gives the real benefit. Things that rely on session state may break in it: SET (use SET LOCAL inside the transaction), session-level advisory locks, LISTEN/NOTIFY, temporary tables across transactions, and (historically) prepared statements. PgBouncer 1.21+ supports protocol-level prepared statements in transaction mode (max_prepared_statements), which helps many drivers.

Install

sudo apt install -y pgbouncer

Config: /etc/pgbouncer/pgbouncer.ini.

Configure

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

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

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
max_prepared_statements = 100

server_reset_query = DISCARD ALL
ignore_startup_parameters = extra_float_digits

admin_users = pgbouncer_admin
stats_users = pgbouncer_stats
  • max_client_conn — how many app connections PgBouncer accepts.
  • default_pool_size — real server connections per database/user pair. Start around 2–4× the number of CPU cores of the database server.
  • ignore_startup_parameters — some drivers send parameters PgBouncer doesn’t understand; list them here.

Authentication

userlist.txt contains users and their password hashes, copied from PostgreSQL:

SELECT concat('"', rolname, '" "', rolpassword, '"') FROM pg_authid WHERE rolname = 'myapp';

Put the output into /etc/pgbouncer/userlist.txt (owned by postgres, mode 600). For many users, auth_query lets PgBouncer look up passwords in PostgreSQL directly via a dedicated lookup function instead.

sudo systemctl restart pgbouncer
psql -h 127.0.0.1 -p 6432 -U myapp myapp

Point your application at PgBouncer

Change the port from 5432 to 6432:

postgres://myapp:[email protected]:6432/myapp

Framework notes for transaction mode:

  • Django: with transaction pooling, set DISABLE_SERVER_SIDE_CURSORS = True in the database settings.
  • Prisma, node-postgres, Rails, Laravel: generally fine; check docs for prepared statement settings if you’re on an older PgBouncer.
  • Migrations and admin tasks that need session features: connect directly to PostgreSQL on 5432.

Keep the app’s own connection pool small too — PgBouncer is now doing the heavy lifting.

Monitor

Connect to the admin console:

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer
SHOW POOLS;     -- cl_active, cl_waiting, sv_active, sv_idle per pool
SHOW STATS;     -- queries, transactions, bytes, average times
SHOW CLIENTS;
SHOW SERVERS;
RELOAD;         -- apply config changes without dropping connections

cl_waiting > 0 for long periods means clients are queuing for a server connection — the pool is too small or queries are too slow.

Sizing the whole stack

  • max_connections on PostgreSQL ≥ sum of all pool sizes + a margin for admin and replication.
  • Don’t over-size pools: more concurrent queries than the database has cores leads to contention, not speed.

When you need it

  • More than ~100 concurrent client connections.
  • Many short-lived connections (PHP without persistent connections, serverless).
  • Several application servers each with their own pools.

For a single app with a modest built-in pool, you may not need PgBouncer at all.

Related: PostgreSQL performance tuning and database connection pooling.

  • PostgreSQL
  • PgBouncer
  • Performance