Understanding Connection Pool - What, Why and How?

Hello, everyone 👋

What is a Connection Pool?

Connection Pool is a cache of Database connections to reuse the connections made to database. Making a connection to database is costly and resource intensive, it consists of several time-consuming steps like a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, and so on, so it is a wise decision to cache the connections and reuse them.

Why is it important?

Connection Pool is maintained in Web/Application server. In an ideal scenario we normally open a connection to database, execute transaction and close the connection. Imagine there are 100 concurrent request to your server!! Yes, you will open and close connections 100 times, which is costly and affects performance of your service. With connection pooling, every new connection you open will get added to pool for future use. For 100 concurrent request the connection to database is reused from the pool and that saves you lot of resources and performance.

How do I configure?

All modern data access library/module have an option to configure connection pool. If you are using Sequelize in nodejs environment,

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

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql',
  pool: {
     max: 5, // Maximum number of connection in pool
     min: 0, // Minimum number of connection in pool
     idle: 10000 // The maximum time, in milliseconds, that a connection can be idle before being released from pool
  }
});

Thanks for reading! Stay tuned 🙂