🏊 Connection Pooling

Why Pooling?

Every time a client connects to PostgreSQL, a lot happens under the hood: a TCP handshake, TLS negotiation (in production), password authentication, and the server forking a backend process with its own memory. That setup can take tens of milliseconds — often longer than the query itself. Do it on every HTTP request and your database spends more time greeting visitors than serving them.

Picture a busy restaurant. Without reservations, every arriving guest waits while a table is dragged out, set, and dressed — then torn down the moment they leave. A connection pool is a row of tables kept set and ready: guests are seated instantly, and when they leave, the table is wiped and reused for the next party. Same tables, far less overhead.

The Checkout / Return Model

A pool owns a fixed maximum number of physical connections. When your code needs one, it checks out an idle connection; while checked out, that connection is exclusively yours. When you're done, you return it to the pool, wiped and ready for the next request. If every connection is busy, new requests wait in a queue until one frees up.

Two things make this reliable: connections are returned promptly (so the queue keeps moving), and max is set so the pool never demands more connections than PostgreSQL will allow. Break either and you get the failure modes we cover below.

Configuring the Pool

The pg Pool constructor takes your connection details plus pool-tuning options. Here's a production-shaped configuration with event handlers and graceful shutdown.

// db.js
require('dotenv').config();
const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,

  max: 20,                        // most connections the pool will open
  idleTimeoutMillis: 30000,       // close a connection after 30s idle
  connectionTimeoutMillis: 2000,  // fail fast if none free within 2s
  maxUses: 7500,                  // recycle a connection after 7500 checkouts
  // ssl: { rejectUnauthorized: false },  // required by most managed hosts
});

// The pool emits events you can log or alert on
pool.on('connect', () => console.log('New physical connection opened'));
pool.on('error', (err) => console.error('Idle client error:', err.message));

// Graceful shutdown: drain the pool before the process exits
process.on('SIGINT', async () => {
  await pool.end();
  console.log('Pool drained, exiting');
  process.exit(0);
});

module.exports = {
  query: (text, params) => pool.query(text, params),
  getClient: () => pool.connect(),   // for transactions
  pool,                              // exported for metrics / advanced use
};

What each option does

Sizing the Pool

Pool size is a Goldilocks problem. Too small and requests queue behind a handful of connections; too large and you overwhelm PostgreSQL — each backend consumes memory, and blowing past max_connections makes the whole server refuse work. Bigger is not better.

The key insight most beginners miss: every Node instance has its own pool. Four app servers each with max: 25 can demand 100 connections. Your budget is the database's max_connections (minus a reserve for admin tools), divided across all instances.

A widely cited starting heuristic for CPU-bound query workloads is connections = (cpu_cores * 2) + 1 — but treat it as a floor to measure from, not gospel. Real sizing comes from watching waitingCount under real load.

Usage Patterns

Pattern 1 — Simple query (let the pool manage it)

For a one-shot query with no transaction, use pool.query(). It checks out a connection, runs the query, and returns it for you — the common case.

const db = require('./db');

async function getUserById(id) {
  const result = await db.query('SELECT * FROM users WHERE id = $1', [id]);
  return result.rows[0];
}

Pattern 2 — Dedicated client for a transaction

When statements must share one connection (a transaction), check out a client and — critically — release it in finally.

const db = require('./db');

async function transferFunds(fromId, toId, amount) {
  const client = await db.getClient();
  try {
    await client.query('BEGIN');
    const debit = await client.query(
      `UPDATE accounts SET balance = balance - $1
        WHERE id = $2 AND balance >= $1 RETURNING *`,
      [amount, fromId]
    );
    if (debit.rowCount === 0) throw new Error('Insufficient funds');

    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );
    await client.query('COMMIT');
    return { success: true };
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();   // return it to the pool no matter what
  }
}

Pattern 3 — Watching the queue

The pool exposes live counters you can read at any moment to understand its state.

const { pool } = require('./db');

function poolStatus() {
  return {
    total: pool.totalCount,     // connections currently open
    idle: pool.idleCount,       // open but not checked out
    waiting: pool.waitingCount, // requests queued for a connection
  };
}

Monitoring & Troubleshooting

The three pool counters tell you almost everything. Expose them on a health endpoint so your monitoring system (or just curl) can watch them.

// A lightweight metrics endpoint
const { pool } = require('./db');

app.get('/health/db', async (req, res) => {
  try {
    await pool.query('SELECT 1');   // prove the database answers
    res.json({
      ok: true,
      total: pool.totalCount,
      idle: pool.idleCount,
      waiting: pool.waitingCount,
    });
  } catch (err) {
    res.status(503).json({ ok: false, error: err.message });
  }
});

Reading the signals

Beyond Node: PgBouncer

The built-in pool is per-process. Once you run many Node instances — or serverless functions that each spin up their own pool — the total connection count can overwhelm PostgreSQL even though each pool looks modest. PgBouncer is a tiny external pooler that sits between your apps and the database and manages one shared set of real connections.

Your Node pools connect to PgBouncer instead of directly to Postgres, and PgBouncer multiplexes many client connections onto a small pool of backend connections. In transaction pooling mode — the norm for web apps — a real backend is held only for the duration of each transaction, so a handful of Postgres connections can serve thousands of clients.

Practice & Quiz

const { pool } = require('./db');

app.get('/health/db', async (req, res) => {
  try {
    await pool.query('SELECT 1');
    res.json({
      ok: true,
      total: pool.totalCount,
      idle: pool.idleCount,
      waiting: pool.waitingCount,
    });
  } catch (err) {
    res.status(503).json({ ok: false, error: err.message });
  }
});

Best Practices & Pitfalls

✅ Do

• Use one shared Pool per process via a single db.js module
• Set connectionTimeoutMillis so overload fails fast instead of hanging
• Release every checked-out client in a finally block
• Size max from the DB budget ÷ instances, then trim to measured demand
• Drain the pool with pool.end() on graceful shutdown
• Monitor waitingCount — a persistent queue is your signal to act

❌ Don't

• Create a new Pool per request (that defeats the entire point)
• Leave max so high that all instances together exceed max_connections
• Nest getClient() calls or check out inside a loop
• Assume "add more connections" fixes a leak — find the missing release first
• Enable server-side prepared statements against PgBouncer transaction pooling

Summary

📚 Additional Resources

• node-postgres — Pool API
• node-postgres — Pooling guide
• PostgreSQL — Connection settings (max_connections)

🚀 What's Next?

You've now covered relational databases end to end — design, setup, querying, and pooling. Next we change paradigms entirely: MongoDB Concepts introduces document-oriented NoSQL, where data lives as flexible JSON-like documents instead of rows and columns, and you'll see when each model is the right tool.