Understanding Connection Pooling in Node.js with PostgreSQL

Database connections are an essential aspect of backend development. However, handling them efficiently is crucial to maintaining a scalable and performant application. This is where connection pooling comes into play.

What is Connection Pooling?

Connection pooling is a technique where a predefined number of database connections (TCP connections) are maintained and shared among multiple clients. Instead of opening and closing a database connection for each request, which is expensive, a pool of connections is created and reused to handle multiple database queries efficiently.

Why is Connection Pooling Important?

• Reduces Connection Overhead: Establishing and tearing down a connection is costly.
• Efficient Resource Utilization: Databases have a limited number of connections. Pooling optimizes usage.
• Faster Query Execution: Pre-established connections reduce query time.
• Prevents Connection Exhaustion: Too many simultaneous new connections can overwhelm the DB server.

How Database Connections Work

Traditional Flow:

1. Client Sends Request
2. Server establishes TCP connection
3. Authentication & Authorization
4. Query Execution
5. Connection Termination

This introduces overhead when handling many concurrent requests.

Database Installation and Setup

sudo apt update
sudo apt install postgresql postgresql-contrib -y
sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo -u postgres psql

Inside psql:

ALTER USER postgres PASSWORD 'postgres';
CREATE DATABASE testdb;
\c testdb
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL
);
INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');

Setting up Node.js Project

npm init -y
npm install express pg dotenv

Create a .env file:

DB_USER=postgres
DB_HOST=localhost
DB_NAME=testdb
DB_PASSWORD=password
DB_PORT=5432

index.js - Traditional Approach

require('dotenv').config();
const express = require('express');
const { Client } = require('pg');

const app = express();
const PORT = 3000;

app.get('/users', async (req, res) => {
    const client = new Client({
        user: process.env.DB_USER,
        host: process.env.DB_HOST,
        database: process.env.DB_NAME,
        password: process.env.DB_PASSWORD,
        port: process.env.DB_PORT,
    });

    const start = Date.now();
    await client.connect();
    const result = await client.query('SELECT * FROM users');
    await client.end();
    const duration = Date.now() - start;

    res.json({ data: result.rows, time_taken: `${duration}ms` });
});

app.listen(PORT, () => console.log(`Server running on port ${PORT}`));

index.js - Connection Pooling Approach

require('dotenv').config();
const express = require('express');
const { Pool } = require('pg');

const app = express();
const port = 3000;

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
  max: 10,
  idleTimeoutMillis: 30000,
});

app.get('/users', async (req, res) => {
  try {
    const start = Date.now();
    const result = await pool.query('SELECT * FROM users');
    const duration = Date.now() - start;
    res.json({ data: result.rows, time_taken: `${duration}ms` });
  } catch (err) {
    console.error('Database error:', err);
    res.status(500).json({ error: 'Database query failed' });
  }
});

app.listen(port, () => console.log(`Server is running on http://localhost:${port}`));

Performance Comparison Code

require('dotenv').config();
const express = require('express');
const { Pool, Client } = require('pg');

const app = express();
const PORT = 3000;

let oldReqCount = 0, oldReqSum = 0;
let poolReqCount = 0, poolReqSum = 0;

const dbConfig = {
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
};

const pool = new Pool({ ...dbConfig, max: 20 });

const queryWithClient = async () => {
  const client = new Client(dbConfig);
  await client.connect();
  const results = await client.query("SELECT * FROM users");
  await client.end();
  return results.rows;
};

const queryWithPool = async () => {
  return (await pool.query("SELECT * FROM users")).rows;
};

app.get("/old", async (req, res) => {
  const start = Date.now();
  oldReqCount++;
  try {
    const rows = await queryWithClient();
    const elapsed = Date.now() - start;
    oldReqSum += elapsed;
    res.send({ rows, elapsed, oldReqCount, avg: Math.round(oldReqSum / oldReqCount), method: "old" });
  } catch (error) {
    console.error("Client error:", error);
    res.status(500).send("Client DB error");
  }
});

app.get("/pool", async (req, res) => {
  const start = Date.now();
  poolReqCount++;
  try {
    const rows = await queryWithPool();
    const elapsed = Date.now() - start;
    poolReqSum += elapsed;
    res.send({ rows, elapsed, avg: Math.round(poolReqSum / poolReqCount), poolReqCount, method: "pool" });
  } catch (error) {
    console.error("Pool error:", error);
    res.status(500).send("Pool DB error");
  }
});

app.listen(PORT, () => console.log(`Server is Running on ${PORT}`));

Simulating 1000 Requests (in browser console or script)

for (let i = 0; i < 1000; i++) {
  fetch('http://localhost:3000/old').then(res => res.json()).then(console.log).catch(console.error);
}

for (let i = 0; i < 1000; i++) {
  fetch('http://localhost:3000/pool').then(res => res.json()).then(console.log).catch(console.error);
}

Conclusion

Using a connection pool in Node.js with PostgreSQL significantly improves performance, optimizes resource utilization, and prevents connection exhaustion.