When a client needs to run SQL queries on a database server, it must first establish a connection with the server. However, this process involves multiple steps and can create additional overhead. Furthermore, maintaining an open connection requires system resources such as memory and CPU, which can negatively impact the performance of the database server.

Limitations of Directly Opening Connections

To complicate matters, most database servers have a limit on the number of connections that can be established at any given time. Simply increasing this limit is not a viable solution, as it can exacerbate the aforementioned issues. If a client tries to establish a connection when the maximum number of connections has already been reached, the request will be rejected. In this case, the client must implement connection management code to handle retries (preferably with exponential backoff) or scale the database by increasing the maximum connection limit.

Connection Pooling

One way to address these issues is through connection pooling. Connection pooling is a software component that manages database connections between the client and database server. By retaining open connections instead of repeatedly opening and closing them, connection pooling minimizes the overhead associated with these operations.

To use connection pooling, clients must specify a maximum connection limit, also known as the pool size. This is to ensure that the connection pooling software does not attempt to establish more connections than the maximum limit imposed by the database server. If there are no available connections, the client will wait until a connection becomes available or times out.

Internal and External Connection Pooling

There are two types of connection pooling: internal and external. Internal connection pooling is typically included as part of an ORM or database client, while external connection pooling software must be deployed separately.

Determining the optimal pool size for internal connection pooling can be challenging, especially when multiple client applications share a database server. For example, if the database server has a limit of 100 maximum connections and there are two client applications, each app should have a maximum pool size of 50. It’s also important to consider overflow, which some internal connection pooling software allows.

External connection pooling is a better solution when dealing with distributed systems that autoscale, such as serverless architectures like AWS Lambda. With external connection pooling, the configuration can be adjusted independently of the client applications.

Conclusion

By using connection pooling, clients can minimize the overhead associated with establishing and maintaining database connections. Internal and external connection pooling solutions offer different benefits and should be chosen based on the specific needs of the system.