What is a connection pool?
Connection pooling is a design approach aiming to recycle the database connections for handling multiple requests instead of closing these connections directly upon executing a query.
Typically, a connection pooler is a software program between the client application and the database engine to control and manage the connections between these two parties.
Connection pooling is a background process and does not necessitate changing the application code.
Process
The following schema illustrates the connection pooling process:
Benefits
Using connection pooling has many advantages:
Alleviate connection management and reduce the cost of opening and closing connections.
The caching of connections improves the database response time.
Cut down the time required to establish a database connection.
Promote the reuse of connection objects.
Minimize the number of stale and zombie connections.
Examples
Let's look at the following examples demonstrating how the connection pooling operates:
Scenario 1
This scenario, developed using the Python programming language, aims to create a connection pool composed of 5 connections targeting an SQLite database. These connections will be cached in memory, and they can be reused when needed.
from sqlalchemy import create_enginefrom sqlalchemy.pool import QueuePoole = create_engine(name_or_url ="sqlite:///test",poolclass = QueuePool,pool_size = 5,max_overflow = 0,pool_timeout = 0)[e.connect() for con in range(5)]
Now, let's go through the above code:
Lines 1–2: Import the appropriate objects from the SQLAlchemy library, a standard database abstraction layer providing developers with many features for designing and managing databases.
Line 4–9: Establish database connectivity using the create_engine function. This function takes as arguments the following:
Parameter | Description |
| The data source connection string. |
| The QueuePool imposes a preset limit on the number of established connections. It is the default pooling mode when an engine is instantiated. |
| The number of connections to be maintained in the pool. |
| The pool's maximum overflow size. It represents the number of connections to allow beyond the pool_size setting. Setting this parameter to -1 means no overflow limit. |
| The time (number of seconds) to wait before ceasing to return a connection. |
Line 11: Fetch 5 connections from the pool which is equal to the pool size.
Based on the above configuration, you will not encounter any error when executing the above scenario since the number of requested connections equals the pool size.
Scenario 2
Considering the above scenario, we will try in this scenario to fetch a number of connections that exceed the pool size. You will notice that a timeout error is thrown.
from sqlalchemy import create_enginefrom sqlalchemy.pool import QueuePoole = create_engine(name_or_url ="sqlite:///test",poolclass = QueuePool,pool_size = 5,max_overflow = 0,pool_timeout = 0)[e.connect() for con in range(6)]
The code above is similar to the one previously elaborated upon, except for the number of connections we are trying to establish (Line 11) exceed the specified pool size.
Free Resources
