Why is Connection Pooling better than Single Connection

Database connections are costly since they are heavy to create and maintain by the database server. So picking up a connection that suits your application is an important step of your implementation.

There are two ways to establish a connection with a database: Single Connection and Connection Pool. But before you decide which connection to use for your application, you should define your needs and requirements such as the number of users at the same time and the queries’s complexity level. This will help you pick up the right one for your application.

Before we take a depth look at What Connection Pool is and How a Connection Pool works, we will explain first What is a single connection and What is the difference between single connection and connection pooling?. So at the end of this article you will have a clear idea on: Why and when a connection pooling is better than a single connection and how it works.

Related Articles:

This article will cover:

What is Single Connection?
What is Connection Pooling?
How Connection Pooling works
Connection Pooling vs Single Connection
Summary
You might also like:

What is Single Connection?

Single database connection means that you are using only one shared connection to execute all the queries triggered by all your application users. So, we use single connection only when the number of an application users at the same time is limited and theirs queries are simple.

With this type of connection, when a user makes a query, the application creates a connection and sends the user query to the database for execution. After that, the application gets the results and displays the data to the user and closes the connection which means disconnect the database. So the application will create a new connection for the next new query. This process of closing and creating connection may slow down your application and reduce its performance.

What is Connection Pooling?

When using a single connection, the queries have to be simple. Otherwise, their execution will slow down the application and decrease its performance. Hence the limited number of users at the same time. Also, a new connection is created for each new query and we all know that opening and maintaining a database connection is costly and heavy task. The biggest issue of a single connection is when the connection is closed suddenly and you did not take this into account in your implementation.

Connection pools are used to improve the performance of the queries’s execution on a database without exception, prevent opening and closing connections frequently and reduce the number of new connections.

In connection pooling, a connection can be an active connection or an available connection. When a new connection is created, it is placed in the pool and used to execute the query has just arrived. After execution, the application closes the connection, so pool is notified that the connection is available and ready for reuse. Having connections created in advance reduce the user waiting time needed to create new connection to execute new queries.

How Connection Pooling works

When an application requests a connection from the connection pool, the Pool assigns an available connection. If an unused connection exists, the pool return it. Otherwise, if there is no available connection, the Pool will create a new connection and assigns it to the application as an active connection.

When a connection is created, it is added to the pool for multiple uses.

After connection, the application sends a query for execution, obtains the results and display it to the user.

Then the application closes the connection and returns the connection to the pool as an available connection.

If a connection is corrupted, it will be removed from the pool.

When the maximum number of connections the pool can hold is exceeded, the newly arrived requests that require a connection will be halted until a connection from the pool becomes available. If that does not happen within the time specified in the connection time-out, the requests will be rejected.

Connection Pooling vs Single Connection

The table below summarizes the comparison between single connection and connection pool:

Summary

To answer the question in title, Why is Connection Pooling better than Single Connection?, we can say that it really depends on:

The type of your application.
The number of connected users at the same time.
The complexity level of the queries.

If your application has a few users at the same time and the requested queries are simple, then a well implemented and configured single connection will do the job.

but if your application has a significant number of users and a sizeable amount of queries, in this case, Connection Pooling is better than a single connection.

To see an example of simple connection pool implementation you can check here: How to connect node.js API to MySQL database?.