Database Connection Pooling

I used to think database performance was all about writing efficient SQL queries. Then I discovered that most of my API's response time was spent just opening and closing database connections. That was the time I learned about connection pooling. Let me consolidate them into 3 points.

1. How a database connection is established and why connection pool is required:

const connection = await database.connect();        

This seems simple, but behind the scenes below things happen:

• Your app and database establish a TCP connection.
• If using encryption, they negotiate secure communication.
• Database authenticates username and password.
• Database allocates memory and prepares the session.

All of these steps take time. To reduce this time with each requests, we need to create a pool of connections which will establish connection once and carry the requests one after another.

Real Code Example: Before vs After

app.get(/api/users/id, async (req, res) => {
  // Opening a new connection - takes 400ms
  const connection = await mysql.createConnection({
    host: localhost,
    user: root,
    password: password,
    database: myapp
  });
  
  // Running the query - takes 5ms
  const [rows] = await connection.execute(
    SELECT * FROM users WHERE id = ?,
    [req.params.id]
  );
  
  // Closing the connection - takes 50ms
  await connection.end();
  
  res.json(rows[0]);
});        

After (With Connection Pooling)

// Create a pool once at startup
const pool = mysql.createPool({
  host: localhost,
  user: root,
  password: password,
  database: myapp,
  connectionLimit: 10  // Keep 10 connections ready
});

app.get(/api/users/id, async (req, res) => {
  // Grab a connection from the pool - takes <1ms
  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. How to Size Your Connection Pool

This is an interesting area. If you think more is better, then think again. Too many connections in a connection pool will bottleneck the server because each connection uses RAM. Also, more connections means thrashing (the database spends more time switching between connections than doing work). The thumb rule for defining pool size is as follows.

POOL SIZE = (Number of CPU cores X 2) + 1

So if the database server has 4 CPU cores, the optimal pool size is 9.

3. Common Pitfalls and How to Avoid Them

• Forgetting to release connections. Always add a finally block to release the connections.

const conn = await pool.getConnection();
try {
  const [rows] = await conn.execute(...);
  return rows;
} finally {
  conn.release(); // Always release!
}        

• No queue limit. During a traffic spike, requests pile up. If queue grows continuously, app gets stuck.

In the end connection pooling makes your life easier, makes your apps run smoother. It is a powerful but simple concept in app building. Do let me know your thoughts.