Connection Pooling and Query Optimization in Node.js

Every TCP handshake, authentication, and memory allocation for a database connection costs milliseconds and resources. Without proper pooling, your application bleeds connections until the database refuses new ones.

Why Database Connections Are Expensive

Creating a fresh database connection requires three heavyweight operations:

• ✔ TCP handshake (three-way handshake = latency)
• ✔ Authentication (password validation, SSL negotiation)
• ✔ Memory overhead (each connection reserves ~5-10MB of server memory)

Opening 100 connections per request will exhaust your database long before your Node.js process feels the pain.

Configuring pg Pool Correctly

Use pg with a properly tuned connection pool:

import Pool from 'pg';

const pool = new Pool({
  user: 'postgres',
  password: process.env.DB_PASSWORD,
  host: 'localhost',
  port: 5432,
  database: 'myapp',
  max: 20,                    // max connections to maintain
  min: 5,                     // min idle connections
  idleTimeoutMillis: 30000,   // close idle connections after 30s
  connectionTimeoutMillis: 2000, // fail fast if can't acquire
});

const result = await pool.query('SELECT * FROM users WHERE id = $1', [1]);

min keeps warm connections ready. max prevents runaway growth. idleTimeoutMillis reclaims memory. connectionTimeoutMillis exposes pool exhaustion immediately instead of hanging requests.

The N+1 Query Killer

This pattern destroys database performance:

// BAD: N+1 queries
const users = await pool.query('SELECT id, name FROM users');
for (const user of users.rows) {
  const posts = await pool.query(
    'SELECT * FROM posts WHERE user_id = $1',
    [user.id]
  );
  user.posts = posts.rows;
}

Each user triggers a separate query. With 1000 users, that’s 1001 queries. Replace with a single JOIN:

// GOOD: Single query with JOIN
const result = await pool.query(`
  SELECT
    u.id, u.name,
    json_agg(p.*) as posts
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  GROUP BY u.id
`);

One query. One round trip. Massive speed improvement.

Detecting Connection Pool Exhaustion

Monitor pool health in production:

setInterval(() => {
  console.log({
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount, // queries waiting for a connection
  });
}, 10000);

If waitingCount climbs above zero consistently, your max is too low or queries are too slow. Increase max gradually (more connections = more memory) or optimize slow queries first.

Prepared Statements for Repeated Queries

Reuse query plans:

const stmt = await pool.query(
  'PREPARE get_user AS SELECT * FROM users WHERE id = $1'
);

// Use the prepared statement multiple times
for (let i = 1; i <= 100; i++) {
  await pool.query('EXECUTE get_user($1)', [i]);
}

The database parses and plans the query once, then reuses it. Measurable gains for hot paths.

Index Selection with EXPLAIN ANALYZE

Identify slow queries:

const explain = await pool.query(`
  EXPLAIN ANALYZE
  SELECT * FROM posts
  WHERE user_id = $1 AND created_at > $2
`, [userId, '2025-01-01']);

console.log(explain.rows);

Look for Seq Scan — it means a full table scan. Add an index:

CREATE INDEX idx_posts_user_created
ON posts(user_id, created_at DESC);

PgBouncer for External Pooling

When your application runs multiple instances, use PgBouncer as a pooling middleware:

[pgbouncer]
databases = mydb = host=localhost port=5432 dbname=mydb
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

PgBouncer multiplexes thousands of client connections into a smaller set of database connections. Each instance doesn’t need max=20 anymore — PgBouncer handles it.

Summary

Connections cost real resources. Pool them aggressively. Min connections stay warm. Max prevents memory bleed. Idling timeouts reclaim memory. EXPLAIN ANALYZE exposes bottlenecks. JOIN queries instead of looping. Monitor waitingCount. Use prepared statements for hot paths. Deploy PgBouncer when scaling horizontally.

Do this, and your database will thank you.