PgBouncer Connection Pooling for Postgres, A Practical Setup

TL;DR — Run PgBouncer in transaction-pooling mode in front of Postgres. App connects to PgBouncer with N connections; PgBouncer holds M (< N) connections to Postgres and multiplexes them. Per-connection memory drops, max_connections stops being your ceiling. One file of config + a sidecar in Compose/k8s.

Last item on the memory-saving side of the perf checklist before we shift to indexes: connection pooling. Postgres is process-per-connection. Each connection eats roughly 10 MB of base memory plus whatever work_mem it allocates per query. 200 connections idle = 2 GB gone before any work happens.

PgBouncer is the answer. It’s a tiny C process that sits between your app and Postgres. Apps open lots of cheap connections to PgBouncer; PgBouncer holds a small pool of expensive connections to Postgres. Even at startup it’s a 5× reduction in physical connections for typical workloads.

Pool modes — pick the right one

PgBouncer supports three pool modes. Order from “most permissive, least efficient” to “least permissive, most efficient”:

Session pooling. PgBouncer assigns a server connection for the whole client session. Same as no pooling, basically — just centralizes the connections. Rarely worth it.

Transaction pooling. PgBouncer assigns a server connection for one transaction, then releases it back to the pool. This is the mode 95% of people want. Server connections are shared across many clients.

Statement pooling. PgBouncer assigns a server connection for one statement. Doesn’t allow multi-statement transactions. Almost no app supports this. Skip.

Pick transaction pooling. Be aware of the constraints (next section).

Transaction-pooling constraints

In transaction mode, PgBouncer rotates server connections between clients. That means certain Postgres features that require session state across transactions break:

• SET outside a transaction doesn’t persist. Use SET LOCAL inside transactions.
• Server-side prepared statements (those compiled and named via PREPARE) can leak between clients. Disable in your driver. Most modern drivers (pgx, libpq, JDBC) have a flag.
• LISTEN/NOTIFY doesn’t work reliably. Use a separate dedicated session connection.
• Temporary tables scoped to session don’t persist between transactions. Use transaction-scoped temp tables (ON COMMIT DROP).
• Cursors that span transactions are right out.

For 99% of OLTP code, none of this matters. If you’re using Rails ActiveRecord, Django ORM, Laravel Eloquent, Go’s database/sql w/ pgx, or similar — you’re fine after disabling prepared statements where applicable.

For pgx specifically:

config, _ := pgx.ParseConfig(dsn)
config.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol
db := stdlib.OpenDB(*config)

That switches to the simple protocol, no server-side prepares. Slightly higher per-query CPU, but it’s the price for pooler-safety.

The config

A working pgbouncer.ini for a small-to-medium production setup:

[databases]
app = host=postgres port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction

max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

server_lifetime = 3600
server_idle_timeout = 600

log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

stats_period = 60

admin_users = pgadmin
stats_users = pgmon

Key knobs:

• max_client_conn = 1000 — apps can open up to 1000 connections to PgBouncer
• default_pool_size = 25 — PgBouncer opens at most 25 actual connections to Postgres per (database, user) pair
• reserve_pool_size = 5 — 5 extra connections allowed if reserve_pool_timeout is exceeded
• server_lifetime = 3600 — close any server connection older than 1 hour; prevents stale-cache issues
• pool_mode = transaction — the mode we just argued for

The userlist file:

"app"    "md5<hash-of-password>"
"pgmon"  "md5<hash-of-password>"

Generate the hash:

echo -n "passwordapp" | md5sum
# Take the hex output, prefix with "md5"

Sizing the pool

The formula that works for most setups:

default_pool_size ≈ (Postgres max_connections - reserved_overhead) / number_of_databases / number_of_pgbouncer_instances

For a Postgres allowing 100 connections, with 5 reserved for admins/replication, 2 databases, 2 PgBouncer instances:

(100 - 5) / 2 / 2 ≈ 23 → round to 25

Counterintuitively, the right pool size is often much smaller than you’d guess. Most workloads need 2–4 active connections per CPU core to saturate the database. A 4-core Postgres rarely benefits from >25 server connections.

Add reserve_pool_size = 5 for burst handling.

Running PgBouncer

In Docker Compose, as a sidecar to your Postgres:

services:
  pgbouncer:
    image: edoburu/pgbouncer:1.16.1
    environment:
      DB_HOST: postgres
      DB_USER: app
      DB_PASSWORD: app
      DB_NAME: app
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 1000
      DEFAULT_POOL_SIZE: 25
    ports:
      - "6432:6432"
    depends_on:
      postgres:
        condition: service_healthy

Your app now connects to pgbouncer:6432 instead of postgres:5432. Everything else stays the same.

In Kubernetes, PgBouncer runs as a Deployment with 2+ replicas, fronted by a Service. Apps point at the Service. Sample manifest is straightforward; the trick is to keep PgBouncer close to your app pods (same zone, ideally same node) to minimize network latency.

Watching it work

PgBouncer has an admin shell:

psql -h pgbouncer -p 6432 -U pgadmin pgbouncer

Useful commands:

SHOW POOLS;       -- per-database pool stats
SHOW SERVERS;     -- per-server-connection state
SHOW CLIENTS;     -- per-client-connection state
SHOW STATS;       -- aggregated stats

In SHOW POOLS, watch:

• cl_active — how many clients are actively using a server connection
• cl_waiting — how many clients are waiting for a server connection (should be ~0)
• sv_active / sv_idle — server connections in use / available

If cl_waiting is persistently > 0, your default_pool_size is too small.

Common Pitfalls

Leaving session-level state with transaction pooling. Random SET statements outside transactions silently do nothing. Always SET LOCAL. If you need session-scoped state (rare), use a separate non-pooled connection.

Not disabling driver prepared statements. Easiest way to see “prepared statement "S_1" already exists” errors mysteriously appearing under load. Driver flag varies; for pgx see above, for JDBC use prepareThreshold=0.

Running PgBouncer on the same node as Postgres. Defeats half the point. Pooler should be near the app, not near Postgres. App → pooler (local) → Postgres (network) is the right topology.

Over-sizing the pool. “More connections must be better” is wrong. A pool of 100 server connections will gladly OOM your Postgres on work_mem allocations. Smaller pool + waiting clients > bigger pool + OOM.

Forgetting server_lifetime. Without it, connections live forever and accumulate per-connection state (planner caches, etc.) that occasionally causes weird perf regressions. 1 hour is sane.

Treating PgBouncer as an HA solution. It’s not. PgBouncer fails over poorly. Run 2+ instances behind a load balancer. For high availability of the data plane, use Postgres replication separately.

Wrapping Up

PgBouncer is one config file and a sidecar. Get it in front of Postgres before your connection count becomes a problem, not after. With it, your apps can scale horizontally without each new pod adding 10 MB × N connections to Postgres’s memory. Next week we shift gears entirely: index types in Postgres — B-tree, GIN, GiST, BRIN, when each one wins.