Connection Pool

On dealing with databases we always face that matter of having a connection limitation on each database server.

Solving this matter, the "singleton" design pattern was evolved to ensure a one connection instance per application.

But looking to high traffic situations and multiple queries , we face long time issues as a one connection instance keeps all queries inside a queue where they run one after another. So if one query needs 3 minutes to get executed and you have 50 queries requested at the same time , the last query will finish execution after 150 minutes!! TOO LONG.

Moving to another option , what about making a new connection for each query and close it after execution ?

Drawbacks for this option :

1) Making a new connection instance takes time

2)If traffic was higher than database server connections limit , your server gets down.

Here's when connection pool came to solve

Connection pool opens a number of connections that you decide according to your limitation , and it divides queries on those connections. so when a new query comes it looks to a free connection inside the pool otherwise it waits inside the queue.

So instead of using only one connection , you can open 100 and serve 100 requests parallel at the same time without worrying about the amount of traffic hitting your server.

Here is the link for a Nodejs script simulating the difference between running queries using a single instance VS using connection pool.

https://github.com/NourhanMS/ConnectionPool

More resources :

https://riptutorial.com/node-js/example/4587/using-a-connection-pool

Acknowledgement :

Special thanks goes to Alaa Mohamed Hayba for introducing this concept to me .