Connection Pool: Reusing Database Connections

Why I Started Learning About Connection Pooling

In the early days of my service, there were few users, so no problems arose. But as traffic gradually increased, strange phenomena started occurring. API responses suddenly slowed down, then "Too many connections" errors crashed the service. Restarting the server would fix it temporarily, but the same problem kept repeating.

Monitoring tools showed DB connection counts continuously increasing until hitting a limit and crashing. Initially, I thought "Is the DB server underpowered?" and considered scaling up. But a senior developer looked at my code, sighed, and said, "You're not using connection pooling?"

Until then, I thought creating and closing a new DB connection for every request was normal. But it didn't take long to realize how inefficient and dangerous this approach was. After introducing connection pooling, DB connection counts remained stable under the same traffic, and response times noticeably improved.

What Confused Me Initially

When I first encountered connection pooling, the most confusing part was "Why reuse connections?" HTTP requests are created and closed fresh each time, so why can't DB connections work the same way?

My initial code looked like this:

app.get('/users/:id', async (req, res) => {
  // Create new connection for every request
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydb'
  });
  
  const [rows] = await connection.execute('SELECT * FROM users WHERE id = ?', [req.params.id]);
  
  // Close connection
  await connection.end();
  
  res.json(rows[0]);
});

This code worked, but had many problems. With even slight traffic increases, accumulated connection creation/teardown costs caused performance to plummet. Especially when concurrent requests exceeded 100, the DB couldn't handle it.

Another confusion was "How big should the pool be?" Too small and requests wait, too large and the DB gets overwhelmed, but I had no sense of the right criteria.

The 'Aha!' Moment

The decisive analogy that helped me understand connection pooling was "public bike rental stations."

Nobody buys a new bike every time they want to ride, then throws it away when done. Instead, you borrow a bike from a rental station, ride it, and return it. The next person borrows that same bike. This approach:

1. Saves money: No need to buy a new bike each time
2. Saves time: Can ride immediately without buying
3. Manages resources: Only maintain bikes up to station capacity

Connection pooling works exactly the same way. Pre-create DB connections (bike station), borrow when needed (rental), return when done (return). The next request reuses that connection.

When I heard this analogy, it clicked immediately. Ah, that's why it's called a "pool." It means a reservoir of connections.

In code:

// Create connection pool (once at app startup)
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  connectionLimit: 10 // Maintain max 10 connections
});

app.get('/users/:id', async (req, res) => {
  // Borrow connection from pool
  const connection = await pool.getConnection();
  
  try {
    const [rows] = await connection.execute('SELECT * FROM users WHERE id = ?', [req.params.id]);
    res.json(rows[0]);
  } finally {
    // Return connection (not close!)
    connection.release();
  }
});

Use createPool instead of createConnection, and call release() instead of end(). You're returning the connection to the pool, not closing it. After this change, DB connection count stayed at 10 under the same traffic and operated stably.

Problems Connection Pooling Solves

1. Connection Creation/Teardown Cost

Creating a DB connection is more expensive than you'd think. It requires multiple steps: TCP handshake, authentication, session initialization, etc.

My measurements:

• New connection creation: Average 50-100ms
• Getting connection from pool: Average <1ms

A 100x difference. With 1000 requests per second, connection creation alone takes 50-100 seconds. But with connection pooling, it takes less than 1 second.

2. DB Server Load

DB servers have limits on concurrent connections. MySQL's default is 151. If you have 3 web servers, each handling 100 concurrent requests:

• Without connection pooling: 300 connection attempts → DB server overload
• With connection pooling (pool size 10 per server): Max 30 connections → stable

Connection pooling protects the DB server by limiting connection counts.

3. Preventing Connection Leaks

Manually managing connections leads to accidentally not closing them:

const connection = await mysql.createConnection({...});
const [rows] = await connection.execute('SELECT ...');

// connection.end() won't execute if error occurs!
if (rows.length === 0) {
  throw new Error('Not found');
}

await connection.end();

Accumulated code like this keeps connections open, eventually causing "Too many connections" errors.

Connection pooling enables safe management with try-finally:

const connection = await pool.getConnection();
try {
  // Perform work
} finally {
  connection.release(); // Always executes even on error
}

Configuring Connection Pools

Determining Pool Size

The most important setting is connectionLimit. How do you decide?

Wrong approaches:

• "More is better" → DB overload
• "Less is better" → Increased request wait times

Right approach:

There's a formula:

connections = ((core_count * 2) + effective_spindle_count)

For example, if the DB server has a 4-core CPU + SSD (spindle = 1):

connections = (4 * 2) + 1 = 9

But in production, monitoring and load testing matter more than formulas.

My approach:

1. Initial value: Start with 10
2. Monitor: Check pool utilization (active connections / total connections)
3. Adjust:
   • Always 100% utilization → Increase (to 20)
   • Always <20% utilization → Decrease (to 5)

Timeout Settings

Configure how long to wait for connections:

const pool = mysql.createPool({
  connectionLimit: 10,
  queueLimit: 0, // Queue limit (0 = unlimited)
  waitForConnections: true, // Whether to wait for connections
  acquireTimeout: 10000, // Connection acquisition timeout (10s)
  timeout: 60000 // Idle connection timeout (60s)
});

acquireTimeout is important. Too long and requests wait forever, too short and unnecessary errors occur. I usually set it to 10 seconds.

Connection Validation

Old connections can break. You need to verify connections from the pool are valid:

const pool = mysql.createPool({
  connectionLimit: 10,
  enableKeepAlive: true, // Enable keep-alive
  keepAliveInitialDelay: 10000 // Ping every 10 seconds
});

Or validate directly when getting connections:

const connection = await pool.getConnection();
try {
  await connection.ping(); // Verify connection validity
  // Perform work
} finally {
  connection.release();
}

Connection Pooling in ORMs

Since most people use ORMs nowadays, knowing how to configure connection pooling in ORMs is important.

Prisma

Prisma automatically manages connection pooling:

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// Include pool settings in DATABASE_URL
// postgresql://user:password@localhost:5432/mydb?connection_limit=10

Or configure in code:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
  log: ['query', 'info', 'warn', 'error'],
});

// Prisma manages pool internally
// No need to explicitly get or return connections
const users = await prisma.user.findMany();

Sequelize

Sequelize explicitly configures pools:

const { Sequelize } = require('sequelize');

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql',
  pool: {
    max: 10,        // Max connections
    min: 0,         // Min connections
    acquire: 30000, // Connection acquisition timeout
    idle: 10000     // Idle connection release time
  }
});

TypeORM

TypeORM is similar:

import { DataSource } from 'typeorm';

const AppDataSource = new DataSource({
  type: 'postgres',
  host: 'localhost',
  port: 5432,
  username: 'user',
  password: 'password',
  database: 'mydb',
  extra: {
    max: 10,           // Max connections
    min: 2,            // Min connections
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000
  }
});

Production Problems I Encountered

1. Debugging Connection Leaks

One day, "Connection pool exhausted" errors suddenly appeared. Monitoring showed connections continuously increasing until hitting the limit.

The problem was this code:

async function getUserWithPosts(userId) {
  const connection = await pool.getConnection();
  
  const [users] = await connection.execute('SELECT * FROM users WHERE id = ?', [userId]);
  
  if (users.length === 0) {
    throw new Error('User not found'); // connection.release() not called!
  }
  
  const [posts] = await connection.execute('SELECT * FROM posts WHERE user_id = ?', [userId]);
  
  connection.release();
  return { ...users[0], posts };
}

When errors occurred, release() wasn't executed, leaking connections.

Solution:

async function getUserWithPosts(userId) {
  const connection = await pool.getConnection();
  
  try {
    const [users] = await connection.execute('SELECT * FROM users WHERE id = ?', [userId]);
    
    if (users.length === 0) {
      throw new Error('User not found');
    }
    
    const [posts] = await connection.execute('SELECT * FROM posts WHERE user_id = ?', [userId]);
    
    return { ...users[0], posts };
  } finally {
    connection.release(); // Always executes
  }
}

2. Transactions and Connection Pooling

When using transactions, you must maintain the same connection:

async function transferMoney(fromId, toId, amount) {
  const connection = await pool.getConnection();
  
  try {
    await connection.beginTransaction();
    
    await connection.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId]);
    await connection.execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, toId]);
    
    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

Between beginTransaction() and commit(), you must use the same connection, so get a connection from the pool and maintain it until the transaction ends.

3. Connection Pooling in Serverless Environments

In serverless environments like AWS Lambda, connection pooling is tricky. When functions terminate, connections don't disconnect and remain, allowing reuse on next execution, but when multiple instances run simultaneously, connection counts explode.

Solution 1: Use RDS Proxy

AWS RDS Proxy is a connection pool management service for serverless:

// Lambda function
const mysql = require('mysql2/promise');

let pool;

exports.handler = async (event) => {
  if (!pool) {
    pool = mysql.createPool({
      host: process.env.RDS_PROXY_ENDPOINT, // RDS Proxy endpoint
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
      connectionLimit: 1 // Only 1 per Lambda
    });
  }
  
  const [rows] = await pool.execute('SELECT * FROM users');
  return { statusCode: 200, body: JSON.stringify(rows) };
};

Solution 2: Prisma Data Proxy

Prisma provides Data Proxy for serverless:

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  directUrl = env("DIRECT_URL") // Data Proxy URL
}

Monitoring Connection Pools

Proper connection pool usage requires monitoring.

Key Metrics

1. Active Connections: Currently used connections
2. Idle Connections: Idle state connections
3. Waiting Requests: Requests waiting for connections
4. Connection Errors: Connection acquisition failures

Monitoring Code

const pool = mysql.createPool({
  connectionLimit: 10
});

// Periodically log pool status
setInterval(() => {
  console.log({
    totalConnections: pool._allConnections.length,
    freeConnections: pool._freeConnections.length,
    queuedRequests: pool._connectionQueue.length
  });
}, 10000); // Every 10 seconds

Alert Setup

Set alerts when pool usage exceeds 80%:

function checkPoolHealth() {
  const usage = (pool._allConnections.length - pool._freeConnections.length) / pool.config.connectionLimit;
  
  if (usage > 0.8) {
    console.warn('Connection pool usage high:', usage * 100 + '%');
    // Send alert (Slack, PagerDuty, etc.)
  }
}

setInterval(checkPoolHealth, 5000);

How I Applied This to My Service

1. API Server

Globally manage connection pool in Express app:

// db.ts
import mysql from 'mysql2/promise';

export const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 20,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 10000
});

// routes/users.ts
import { pool } from '../db';

router.get('/users/:id', async (req, res) => {
  const connection = await pool.getConnection();
  try {
    const [rows] = await connection.execute('SELECT * FROM users WHERE id = ?', [req.params.id]);
    res.json(rows[0]);
  } finally {
    connection.release();
  }
});

2. Batch Jobs

Dynamically adjust pool size for bulk data processing:

// Normal: small pool
const normalPool = mysql.createPool({
  connectionLimit: 10
});

// Batch job: large pool
const batchPool = mysql.createPool({
  connectionLimit: 50
});

async function runBatchJob() {
  const connection = await batchPool.getConnection();
  try {
    // Process bulk data
    for (let i = 0; i < 100000; i++) {
      await connection.execute('INSERT INTO logs VALUES (?)', [i]);
    }
  } finally {
    connection.release();
  }
}

3. Microservices

Independent pool management per service:

// user-service
const userPool = mysql.createPool({
  connectionLimit: 10
});

// order-service
const orderPool = mysql.createPool({
  connectionLimit: 20 // Order service needs more connections
});

One-Line Summary

Connection pooling is a mechanism that pre-creates and reuses DB connections, reducing connection creation/teardown costs, limiting DB server load, and preventing connection leaks. Proper pool size configuration, safe connection management through try-finally, and continuous monitoring are key. Every application using databases in production must use connection pooling.