What is connection pooling and why does it matter in Node?

Answer: Establishing a database connection involves a TCP handshake, authentication, and setup — too slow to do per request. A connection pool maintains a set of open connections that are borrowed for a query and returned for reuse.

How it works:

• The pool opens up to max connections.
• A query acquires an idle connection, runs, and releases it back.
• If all are busy, requests queue until one frees up (or time out).

const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                    // max connections
  idleTimeoutMillis: 30000,   // close idle ones
  connectionTimeoutMillis: 5000,
});

// Reuse the pool for every query — do NOT create a client per request
const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [id]);

Why it matters:

• Performance: amortizes connection setup across many queries.
• Resource protection: databases have a hard connection limit (e.g., Postgres default ~100). Without a cap, a spike could open thousands of connections and crash the DB.
• Concurrency control: the pool naturally throttles DB load.

Common pitfalls:

• Creating a new pool/client per request — defeats pooling and exhausts connections. Create one shared pool at startup.
• Not releasing a manually-acquired client (e.g., forgetting client.release() in a finally) — leaks connections until the pool is exhausted and requests hang.
• Sizing: with multiple app instances, total connections = pool size × instances — size against the DB's limit. For serverless (many short-lived instances), use an external pooler like PgBouncer or a serverless driver.