PostgreSQL Connection Pooling and PgBouncer

I once handled a production incident where traffic tripled one day, database CPU immediately maxed out, large numbers of requests stalled on authentication timeout, and the OOM killer eventually took out PG processes in batches. The root cause turned out to be embarrassingly basic: every application instance opened 50 connections, eight instances were deployed, and cron jobs, migration scripts, and BI reports pushed the number of direct PG connections above 500. PG's default max_connections is only 100. This article explains why PG connections are expensive, how application-side pooling works, and how PgBouncer provides middleware-level pooling, so a connection storm never wakes you up in the middle of the night again.

1. Why PG Connections Are So Expensive

Many beginners assume a database connection is just a cheap TCP socket. But PostgreSQL's architecture determines one thing: every incoming connection causes it to fork a separate backend process. Not a thread, but a process.

That gives every connection a very real cost:

• Memory: Each backend process has its own work_mem, maintenance_work_mem, temporary tablespace, and cache context. An idle connection consumes 5-10 MB, while a complex query can easily consume tens of megabytes. With 300 connections, process metadata alone starts at several gigabytes.
• CPU: Processes do not share CPU caches, and the cost of TLB misses and context switches rises linearly with the number of connections.
• Fork overhead: TCP handshake + fork + permission checks + memory initialization means creating a connection takes at least tens of milliseconds. Under high concurrency, that latency appears directly in P99.

PG connections are therefore heavyweight resources, not something you can open without restraint. The official default of max_connections=100 is not stinginess; it is an empirical value. Increase it too far and single-node performance actually declines. MySQL uses a thread-based connection model and is relatively lightweight; PG's process model makes it inherently more sensitive to connection counts.

In short: for PG, the connection count is the ceiling on throughput, and a few hundred connections can bring even a capable machine to its knees.

2. Application-Side Connection Pools: Do Not Create a Connection for Every Request

Because connections are expensive, they should be reused. Every respectable driver includes a connection pool, whose core mechanism consists of two things: warm up a batch of connections and keep them in a pool, then lend one to each request and return it afterward, instead of closing it after use and creating another one next time.

Common choices in the Node ecosystem include:

// node-pg (pg) -- the lowest-level option; Pool is the connection pool
const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                  // Pool size: at most 10 connections
  idleTimeoutMillis: 30000, // Reclaim after 30s of idleness
  connectionTimeoutMillis: 2000,
});
// Always release after use, or the connection will leak (see Section 7)
pool.query('SELECT 1').then(() => pool.end());

// Drizzle -- includes a pool based on pg / postgres.js
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
const pool = new Pool({ connectionString, max: 10 });
const db = drizzle(pool);

# Prisma -- control it with the connection_limit parameter
DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=10&pool_timeout=10"

Regardless of the ORM, you must explicitly configure pool size, timeout, and idle connection recycling instead of accepting defaults. The ORM manages the lifecycle, but you must decide the parameters.

3. Pool Size: Bigger Is Not Better and Can Actually Be Slower

This is counterintuitive: a pool of a few dozen connections is enough; increasing it to hundreds only makes things slower.

The author of HikariCP, the most popular pool in the JDBC ecosystem, published a classic derivation whose central conclusion approximates the optimal pool size as:

connections = (number of CPU cores × 2) + number of effective disks

For a machine with four cores and an SSD, the theoretical optimum is only around nine connections. Why so few? Because a database's bottlenecks are always CPU and disk I/O. When the connection count far exceeds its parallel execution capacity, the extra connections do not run in parallel; they merely wait in line. They compete for locks and the buffer pool and trigger context switches, slowing down the queries that are actually running.

In practice, 10-25 connections per application instance is the sweet spot. To handle more concurrency, scale out by adding instances, each with its own small pool, rather than enlarging a single pool. Teams that set a pool size of 100 are usually masking another problem: slow queries.

Remember: a connection pool provides reuse, not capacity expansion.

4. PgBouncer: Add a Pool in Front of PG

Application-side pooling solves the problem of repeatedly creating connections within one instance, but it cannot solve instance count × pool size > max_connections. Deploy 20 Pods with 10 connections in each pool and direct PG connections already total 200, exceeding the default limit. Add BI tools, temporary scripts, and migration jobs, and failure is only a matter of time.

That is what PgBouncer addresses. It is lightweight PG connection-pooling middleware placed between applications and PG:

Application A ─┐
Application B ─┼─→ PgBouncer(:6432) ─→ PostgreSQL(:5432)
Application C ─┘     accepts hundreds of client connections    opens only dozens of server connections

It presents itself externally as a database, and applications connect to it just as they would connect to PG. Internally, it shares a small set of server connections among all clients. A minimal configuration (pgbouncer.ini) looks like this:

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

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction           # Critical: see Section 5
max_client_conn = 1000            # Maximum client connections (cheap, can be high)
default_pool_size = 25            # Server connections that actually reach PG (expensive, keep this restrained)
reserve_pool_size = 5             # Emergency pool when the queue overflows

max_client_conn can be set in the thousands because these are only cheap TCP connections. Keep default_pool_size restrained, because it controls the connections that PG actually handles. This layer decouples the number of client connections from the number of PG connections, ensuring that PG sees only a few dozen.

5. Three Pool Modes: session / transaction / statement

PgBouncer's most important setting is pool_mode, which determines the granularity at which a server connection is reused:

• session (per session): Once a client connects to PgBouncer, it exclusively holds one server connection until it disconnects. This is effectively just a proxy rather than real pooling. Unless your application maintains extensive SET values or other session-level state, do not use this mode because it will not help you handle concurrency.

• transaction (per transaction, recommended): A client borrows one server connection for the duration of a transaction and returns it to the pool immediately when the transaction ends with COMMIT or ROLLBACK. This is the best choice for most web applications. It offers the highest reuse rate, and the number of connections on PG remains strictly equal to default_pool_size.

• statement (per statement): The connection is returned after each SQL statement. This maximizes reuse, but prohibits multi-statement transactions, because BEGIN and subsequent statements may land on different server connections. Use it only for read-only workloads without transactions.

session:      connection ────────── entire session ────────── connection
transaction:  connection ─[BEGIN..COMMIT]─ return ─[next transaction]─
statement:    connection ─[SQL]─ return ─[SQL]─ return ─[SQL]─

Transaction mode is the defining feature of PgBouncer and the fundamental reason PG can see only dozens of connections while PgBouncer serves hundreds of clients.

A Major Pitfall in Transaction Mode: Prepared Statements

In transaction mode, a connection returns to the pool when the transaction ends, and the next transaction may receive a different server connection. If the client caches a named prepared statement through PREPARE or a driver's statement cache, that statement will not exist on the next connection, and the query will fail with prepared statement "xxx" does not exist.

Choose one of three solutions:

1. Disable named prepared statement caching in the driver. For Prisma, add pgbouncer=true to the connection string.
2. Upgrade to PgBouncer 1.21+, which can track prepared statements at the protocol layer, and configure max_prepared_statements.
3. Switch to statement mode, but only when transactions are not needed.

This issue is especially common with serverless functions and short-lived connections, so consider it early during diagnosis.

6. How to Calculate the Connection Count

This is the step that most needs to be written down. First, list every source that connects to PG:

All sources connecting directly to PG:
  application instance count × pool size per instance
  + migration scripts (cron / CI)
  + BI / reporting tools
  + direct developer connections (debugging)
  + backup/monitoring connections

Here is a counterexample: 20 application Pods × 10 connections per pool = 200, plus 5 for BI, 10 for migrations, and 5 for manual use, totaling 220. PG's default max_connections=100 is immediately exceeded.

There are two solutions:

A. Direct connections (without PgBouncer): Strictly satisfy Σ(instances × pool size) + miscellaneous ≤ max_connections × 0.7. Reserve 30% for operations and bursts. Keep pools small as described in Section 3, and enforce a hard upper limit on the number of instances.

B. Add PgBouncer (recommended, especially for multiple instances / Serverless): Applications connect to PgBouncer, while the connection count seen by PG equals default_pool_size × database count × user count. For example, with one database, one user, and default_pool_size=25, PG has only 25 server connections and remains rock solid. The front end can handle thousands of client connections.

A useful rule of thumb is:

Actual PG-side connections ≈ default_pool_size × (database count × user count) + reserve_pool_size
Application-side pool connected to PgBouncer: 5-10 per instance is enough because PgBouncer provides another backstop

The key mental model for two pooling layers is that the application pool manages reuse, while PgBouncer enforces the cap. The application-side pool can be smaller because PgBouncer's default_pool_size sets the actual concurrency limit.

7. Common Pitfalls: Leaks, Undersized Pools, and Serverless Connection Storms

Finally, these are four frequent sources of production incidents:

1. Connection leaks: borrowing without returning. A connection obtained manually with pool.connect() is never released, especially along exception paths. Acquire a connection inside a try, return from catch, and omit finally, and that connection remains permanently marked as borrowed. The pool is gradually drained until every request waits for pool_timeout. The standard pattern is:

const client = await pool.connect();
try {
  await client.query('...');
} finally {
  client.release();   // Must be in finally so exceptions also return it
}

ORMs encapsulate this lifecycle in methods such as db.query(). Use a higher-level API instead of calling connect manually whenever possible.

2. An undersized pool makes requests queue. Set the pool to 5 and let 20 requests arrive at once: 15 wait in line and fail with Timed out fetching connection when pool_timeout expires. The symptom is 5xx errors whenever traffic rises slightly, even though PG's CPU remains mostly idle. The bottleneck is the client pool, not the database. Monitor waiting clients; if it remains above 0, the pool is too small or queries are too slow.

3. Serverless cold starts cause connection storms. With FaaS platforms such as Lambda / Vercel / Cloudflare Workers, every instance is a separate process with an independent pool and an unpredictable lifecycle. Rising concurrency can start hundreds of instances at once, each opening several connections and instantly exhausting PG's max_connections. The standard solution for Serverless + PG is to use PgBouncer, or a cloud equivalent such as RDS Proxy or Supabase Pooler, in transaction mode. Anything else merely postpones the incident.

4. Migration scripts consume every connection. A CI migration may leave connections open, or the migration script may create a large pool of its own and prevent the production application from connecting. Give migrations separate, small, short-lived connections that terminate as soon as the job finishes. Do not share a pool with the production application, and never leave a migration script running after its work is complete.

Summary

1. PG connections are heavyweight resources: Each connection gets a separate process. A few hundred can exhaust memory and overwhelm CPU, and the default max_connections=100 is an empirical value rather than stinginess.
2. Application-side pools manage reuse: A pool size of 10-25 is the sweet spot. Scale horizontally by adding instances instead of enlarging the pool; connection pooling is reuse, not capacity expansion.
3. PgBouncer enforces the cap: Use transaction mode to decouple client connections from PG connections so PG always sees only a few dozen server connections. This is the standard configuration for multiple instances / Serverless.
4. Connection counts must be calculated explicitly: Σ(instances × pool size) + miscellaneous must remain below max_connections × 0.7. With PgBouncer, the PG-side count is approximately default_pool_size × database count × user count.
5. Connection leaks, undersized pools, Serverless cold starts, and migrations consuming connections are the four most common incident sources. Release in finally, monitor waiting clients, require pooling for Serverless, and give migrations separate short-lived connections.

There is an engineering maxim about connection pools worth putting on the wall: databases are not killed by traffic; they are exhausted by connection counts. Before optimizing a single SQL statement, first make sure your connection model is healthy. It is the highest-leverage layer and also one of the easiest to overlook.