PostgreSQL Connection Pooling: What Nobody Taught You

I remember my first Node.js app deployed to production. Using PostgreSQL with the pg library directly - creating a new connection for every request. Development: 10 users, worked fine. Production: 500 users, crash.

Error: too many clients already. Postgres has a default max_connections of 100. My app created 100 connections in 2 seconds, then errored.

My first "fix": increase max_connections to 1000. For 2 weeks it worked. Then the server ran out of RAM - each Postgres connection uses ~10MB. 1000 connections = 10GB RAM. My server had 4GB. Crash again.

That's when I learned: connection pooling isn't optional - it's mandatory. But almost no tutorial explains how it works, the different pooling strategies, or the gotchas. This is what I learned.

---

Database Connections Are Expensive

It's not just about max_connections. Each Postgres connection:

• Separate process - Postgres forks a new process per connection (not a thread). Forking is expensive.
• Memory: ~5-10MB per connection - for shared_buffers, work_mem, sort buffers, etc.
• Time: 10-50ms - TCP handshake + SSL negotiation + auth.

If your app creates a new connection per request:
• Time: +20ms per request just to connect
• Memory: 100 concurrent users = 500-1000MB just for connections
• Scalability: hits max_connections ceiling fast

// No pooling - new connection per request
const { Client } = require('pg')

app.get('/users', async (req, res) => {
const client = new Client() // +10-50ms setup
await client.connect()
const result = await client.query('SELECT * FROM users')
await client.end() // +5ms teardown
res.json(result.rows)
})

In development: 1 request per second, invisible.
In production: 1000 RPS with a new connection each time → chaos.

---

The Solution: Connection Pool

Connection pool = a set of connections that are reused. App borrows from the pool, uses it, returns it. No need to create new connections per request.

// With pooling - connections are reused
const { Pool } = require('pg')

const pool = new Pool({
max: 20, // Max 20 connections - not 100
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 5000,
})

app.get('/users', async (req, res) => {
const client = await pool.connect() // Borrow from pool - 200 concurrent users → pgbouncer

---

Session Pooling vs Transaction Pooling

PgBouncer has 2 modes. The difference is critical.

1. Session pooling - 1 Postgres connection per 1 app connection.

Default mode. App connects to pgbouncer → pgbouncer pins 1 Postgres connection - the app holds it until disconnect. Simple, compatible with all queries. But wasteful - if the app holds the connection idle, the Postgres connection is wasted.

App connects → pgbouncer pins 1 PG connection → held until disconnect
App idle for 5 minutes → PG connection stays open, wasted

2. Transaction pooling - 1 Postgres connection per 1 transaction.

This is the efficient one. App connects → pgbouncer gives a Postgres connection. When the query finishes (commit/rollback), pgbouncer returns it to the pool. Next app uses the same connection.

App queries → pgbouncer pins 1 PG connection → query done → back to pool
App idle → no PG connection used

With transaction pooling, 20 Postgres connections can handle 200+ app connections.

But there's a catch: prepared statements (PREPARE ... EXECUTE) don't work with transaction pooling. Prepared statements are cached per session. If the session changes on the next transaction, the prepared statement is gone → error.

In pgbouncer.ini - choose mode
[databases]
mydb = host=localhost port=5432 dbname=mydb pool_mode=transaction
pool_mode: session, transaction, or statement

---

The Most Common Mistake: Prepared Statements + Transaction Pooling

This error had me stuck for days.

// Using a prepared statement (parameterized query)
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId])

In Node.js pg library, pool.query(text, params) = PREPARE + EXECUTE + DEALLOCATE. But with transaction pooling, PREPARE happens in session A, EXECUTE happens in session B - error: prepared statement "p1" does not exist.

Solution: disable prepared statements.

const pool = new Pool({
max: 20,
})
// Use regular queries, not prepared
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId])

---

Connection Leak: Silent Killer

A connection leak happens when an app borrows from the pool but never returns it. The pool runs out → app hangs.

// LEAK - no release
app.get('/users', async (req, res) => {
const client = await pool.connect()
const result = await client.query('SELECT * FROM users')
res.json(result.rows)
// Forgot: client.release() - connection lost from pool
})

10 such requests → pool exhausted → app can't query.

Fix: always use try/finally.

app.get('/users', async (req, res) => {
const client = await pool.connect()
try {
const result = await client.query('SELECT * FROM users')
res.json(result.rows)
} finally {
client.release()
}
})

Or use pool.query() directly (auto release):

const result = await pool.query('SELECT * FROM users') // auto release

---

Monitoring Pool Health

console.log({
totalCount: pool.totalCount,
idleCount: pool.idleCount,
waitingCount: pool.waitingCount,
})

setInterval(() => {
if (pool.waitingCount > 5) {
console.error('Pool queue growing - possible leak?')
}
}, 10000)

If waitingCount keeps increasing, you likely have a connection leak.

---

Quick Comparison

| Approach | Connections per 1000 RPS | Memory | Setup |
|---|---|---|---|
| No pool | 100+ | 500-1000MB | Simple (but crashes at scale) |
| App-level pool | 10-20 | 50-100MB | Easy - built-in pg Pool |
| PgBouncer (session) | 20-50 | 100-250MB | Need to set up pgbouncer |
| PgBouncer (transaction) | 5-10 | 25-50MB | Optimal - prepared stmt limitations |

---

Common Misconceptions

"Pool size should be large for speed." - No. Too large causes contention. Rule of thumb: pool_size = (core_count × 2) + effective_spindle_count. For most apps: 10-20.

"PgBouncer automatically fixes all connection issues." - No. Pgbouncer manages connections, but connection leaks are still the app's problem.

"Transaction pooling is the same as session pooling." - Different. Transaction pooling is more efficient but doesn't support prepared statements, LISTEN/NOTIFY, or advisory locks.

"Pool size of 1 is enough for a small app." - Careful. 1 connection means 1 query at a time. If one query is slow (1 second), all other requests wait. Minimum: 2-5.

---

An Honest Closing

I learned connection pooling from errors - not documentation. too many clients already → increase max_connections → out of memory. I only realized pooling isn't optional after crashing twice. And pgbouncer isn't a magic bullet - prepared statements and transaction pooling don't mix, and connection leaks still need to be handled in code.

The philosophy I learned: database connections are finite and expensive resources. You can't treat them like "just create one, close it later." They must be deliberately managed - pooling strategy, pool size, health monitoring.

If you're still using new Client() per request or have never configured pool size, that's a clear sign: you haven't hit the problem yet - but you will. Set up pooling from the start, not after production crashes.

---

Sources
• Supabase: Connecting to Postgres
• Supabase: Connection Management
• node-postgres: Pooling