pg.Pool vs PgBouncer: When Client-Side Pooling Is Not Enough

Every Node.js PostgreSQL tutorial shows you pg.Pool. You set max: 20, you get 20 reusable connections, and that works fine for a single-server app with moderate traffic. The problems start when you add a second server, or scale to 10 pods, or deploy to Lambda, or hit the PostgreSQL default connection limit of 100.

pg.Pool is per-process. PgBouncer is per-database. That distinction determines everything about when you need one versus the other.

This guide covers exactly when pg.Pool alone is sufficient, when PgBouncer becomes necessary, how to configure each correctly, and the tradeoffs you accept when adding PgBouncer's transaction pooling mode.

What pg.Pool Actually Does

pg.Pool maintains a pool of TCP connections from a single Node.js process to PostgreSQL. When your code calls pool.query(), it borrows a connection from the pool, runs the query, and returns the connection. No connection setup overhead on every query — the TCP handshake and authentication happen once at pool startup.

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max:             20,   // 20 connections from THIS process to Postgres
});

The key word is "this process." If you run 5 Node.js processes (5 pods, 5 PM2 workers, 5 Lambda cold starts), you have 5 × 20 = 100 connections to PostgreSQL. Postgres's default max_connections is 100. You just hit the ceiling on the first query burst.

The Connection Math That Breaks Production

PostgreSQL, by default, allows 100 simultaneous connections. On a 2GB server, each connection uses around 5–10MB of memory — meaning a theoretical maximum of around 180–200 connections before memory becomes the constraint, though conservative guidance is 20–30 application connections with PgBouncer handling the multiplexing.

Scenario: 10 Node.js pods, each with pool.max = 20

10 pods × 20 connections = 200 connections → exceeds Postgres default limit

Result: "FATAL: sorry, too many clients already"

A pool is not global — it is per-process and often per-container. Pools multiply with pods and processes. PgBouncer can smooth spikes, but it fundamentally changes what "pooling" means.

The moment you run more than one process, pg.Pool alone is no longer sufficient to control the total connection count to Postgres.

What PgBouncer Actually Does

PgBouncer sits between your application and Postgres as a TCP proxy. Your application connects to PgBouncer (port 6432). PgBouncer maintains a small pool of actual Postgres connections and queues application requests against them.

10 Node.js pods × 20 connections = 200 connections to PgBouncer
PgBouncer                         = 20 connections to Postgres

Postgres sees 20 connections regardless of how many pods you run

PgBouncer in transaction mode handles the multiplexing — you trade an extra network hop for a hard ceiling on actual Postgres connections. The things you lose — session-level SET, advisory locks, LISTEN/NOTIFY — are real, but for standard API workloads they are not typically on the critical path.

The Three Pooling Modes

Session mode — one Postgres connection per client session. Same as no pooling. Not useful.

Transaction mode — one Postgres connection per transaction. Released back to the pool after COMMIT or ROLLBACK. This is the mode to use. It prevents connection-aging issues with certain Postgres extensions and forces natural, gradual connection recycling rather than a single big reset event.

Statement mode — one Postgres connection per statement. Breaks anything using multi-statement transactions. Avoid.

When pg.Pool Alone Is Sufficient

You do not need PgBouncer if all of the following are true:

• You run a single Node.js process (one server, one container, no horizontal scaling)

• Your total connections (max × process count) stay *under 80% of Postgres *max_connections

• You do not use serverless or autoscaling (Lambda, Fargate, Cloud Run)

• Your queries are short-lived (under 5 seconds) — long queries holding connections are less dangerous with a single process

For a single VPS running one Node.js container with pool.max = 20, pg.Pool is all you need. Keep it simple.

When PgBouncer Becomes Necessary

Add PgBouncer when any of these apply:

Multiple processes or pods. Even two PM2 workers double your connection count. Four PM2 workers with connection_limit = 22 each = 88 connections. With PgBouncer, all 4 workers share 20 server connections in transaction mode. PostgreSQL is never overwhelmed.

Serverless or autoscaling. When deployed on Lambda, which opens a new connection on every cold start, adding PgBouncer in front of Postgres to multiplex connections drops connection overhead from 50ms per request to near-zero. Lambda functions cannot maintain persistent pools — PgBouncer provides the persistence layer.

Connection count approaching **max_connections.** The signal that tells you something is wrong is pg_stat_activity showing a pile of idle connections next to a handful of active ones. Check it:

SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = 'yourdb'
GROUP BY state;
-- If idle >> active, you have idle connections wasting Postgres memory

Configuring pg.Pool Correctly

The pool size formula: (CPU cores × 2) + effective spindle count, as a starting point — but for most web API workloads, 10–20 per process is the right range.

// src/lib/db.ts
import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,

  // Maximum connections this process will open
  // For a single-process app: 10-20 is the right range
  // For multi-process: (Postgres max_connections × 0.8) / process_count
  max: parseInt(process.env.DB_POOL_SIZE || '20'),

  // Kill queries running longer than 5 seconds
  // The most important guard against connection exhaustion
  statement_timeout: 5000,

  // Fail fast if no connection available — do not queue indefinitely
  connectionTimeoutMillis: 3000,

  // Release idle connections after 30 seconds
  idleTimeoutMillis: 30_000,

  // Recycle connections after 7,500 queries
  // Prevents slow memory drift in long-running Postgres backends
  // Documented 2026 recommendation: set this for stable long-running servers
  maxUses: 7500,
});

// Log pool errors — do not let them disappear silently
pool.on('error', (err) => {
  console.error('Pool error:', err.message);
});

maxUses prevents memory leaks — PostgreSQL backend processes can slowly leak memory over thousands of queries. Setting maxUses: 7500 recycles connections regularly, keeping memory stable. This is especially important for long-running server processes.

Also: match pool idle timeouts to infrastructure — if your load balancer has a 60-second idle timeout, set your pool's idleTimeoutMillis to 50 seconds (slightly lower). Mismatched timeouts cause "connection terminated unexpectedly" errors.

Configuring PgBouncer

# /etc/pgbouncer/pgbouncer.ini

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

[pgbouncer]
listen_port    = 6432
listen_addr    = 127.0.0.1       # Localhost only — never expose to internet
auth_type      = md5
auth_file      = /etc/pgbouncer/userlist.txt

; Transaction mode — one connection per transaction
pool_mode = transaction

; Max connections your app sends to PgBouncer (all pods combined)
max_client_conn = 500

; Max actual Postgres connections PgBouncer opens
; Rule: (Postgres max_connections × 0.8) - reserved_for_superuser
default_pool_size = 75

; Minimum connections kept open (warm pool for faster response)
min_pool_size = 5

; Kill server connections idle longer than this
server_idle_timeout = 600

; Kill client connections idle longer than this
client_idle_timeout = 0    ; 0 = disabled (app manages its own connections)

; Silence logs for expected events
log_connections    = 0
log_disconnections = 0
log_pooler_errors  = 1

# /etc/pgbouncer/userlist.txt
# Format: "username" "md5hash"
# Generate hash: echo -n "passwordusername" | md5sum → prefix with "md5"
"appuser" "md5abc123..."

Your pg.Pool then points at PgBouncer instead of Postgres directly:

const pool = new Pool({
  // Port 6432 = PgBouncer, not 5432 = Postgres directly
  connectionString: process.env.DATABASE_URL,  // Points to PgBouncer host:6432
  max: 20,      // Per-process limit — PgBouncer handles the Postgres side
  statement_timeout: 5000,
});

What You Lose in Transaction Mode

Transaction pooling mode is not free. These features require a persistent server connection across multiple transactions — they break or behave unexpectedly:

Session-level **SET commands** — SET search_path = myschema applies to the session. In transaction mode, the connection is returned to the pool after each transaction, so session settings are lost. Use schema-qualified names instead: SELECT * FROM myschema.users.

Advisory locks — pg_advisory_lock() is session-scoped. Does not work in transaction mode. Use row-level locks with SELECT FOR UPDATE instead.

LISTEN/NOTIFY — requires a persistent connection. Route LISTEN/NOTIFY through a dedicated connection that bypasses PgBouncer, or use a separate Redis pub/sub instead.

Prepared statements — PREPARE is session-scoped. In transaction mode, disable them: options=-c statement_cache_mode=describe in the PgBouncer database string.

# pgbouncer.ini — disable prepared statements for transaction mode
[databases]
yourdb = host=127.0.0.1 port=5432 dbname=yourdb options=-c statement_cache_mode=describe

Or in your pg.Pool config:

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  // Disable prepared statements — not supported in PgBouncer transaction mode
  // When using Prisma, set: datasources.db.url contains ?pgbouncer=true
});

Monitoring Connection Health

-- Check current connection state in Postgres
SELECT
  state,
  COUNT(*)                        AS count,
  MAX(EXTRACT(EPOCH FROM (NOW() - query_start)))::int AS max_age_secs
FROM pg_stat_activity
WHERE datname = 'yourdb'
GROUP BY state
ORDER BY count DESC;

-- Connections by application
SELECT application_name, state, COUNT(*)
FROM pg_stat_activity
WHERE datname = 'yourdb'
GROUP BY application_name, state
ORDER BY COUNT(*) DESC;

What to look for:

• idle count much higher than active → over-provisioned pool or idle connections not being released

• idle in transaction count growing → queries running inside open transactions that have not committed

• active count at max_connections → pool exhaustion imminent

Add to Prometheus (from the earlier metrics section) and alert on pg_pool_waiting > 5 for 30 seconds.

The Decision

Single process or container?
└── pg.Pool alone, max: 10-20, statement_timeout: 5000

Multiple processes/pods but total connections < 80% of max_connections?
└── pg.Pool alone, size it correctly per-process

Multiple pods OR serverless OR total connections near max_connections?
└── Add PgBouncer in transaction mode
    └── pg.Pool → PgBouncer (500 client connections)
    └── PgBouncer → Postgres (20-75 server connections)
    └── Audit: no advisory locks, no LISTEN/NOTIFY, no session SET

PgBouncer adds one more thing to operate and debug. Do not add it before you need it. Do add it before you hit FATAL: sorry, too many clients already in production.

---

Originally published on ZyVOP