What is connection pooling?

Connection pooling is a technique used in computer programming to improve the performance of applications that communicate with a database. It involves creating a pool or a cache of connections that can be reused by multiple requests, instead of creating a new connection every time a request is made.

Connection pooling term is generally used with databases, but in general it can be added on top of any application that needs to establish and maintain a large number of connections to external resources can benefit from using a connection pool. Connection pooling helps reduce the overhead of establishing and tearing down connections, which can improve performance and scalability of the application. In ruby there is a gem connection_pool to help with this.

Let's talk about how a connection to database would works if there is no connection pooling. Every time an application needs to interact with the database, it needs to establish a new connection to the database. This involves several steps, such as creating a new connection object, authenticating with the database server, and negotiating the connection parameters. Once the connection is established, the application can perform the necessary database operations.

The problem with this approach is that establishing a new connection to the database is a time-consuming.

This is where connection pooling comes in. Connection pooling is a technique where a pool of pre-established connections is created and maintained by the application. When a new request comes in, instead of creating a new connection, the application picks up an available connection from the pool, reuses it for the request, and returns it to the pool when the request is complete.

As we already know number of max connections available in the pool, this allows the application to control the number of connections to the database, preventing overload of the database server.

The connection pool setup should be done in a very informative manner, to much connections can cause DB to over load and slow response times, to few connections can become the bottleneck for application to process the request in timely manner.