Connection Pooling in Databases?

Connection Pooling in Databases?

Situation

Imagine a scenario: You have a web application where requests pour in from users at a rapid pace.

These requests, whether it's to view a product catalog, retrieve user information, or perform any other database-related task, all share a common challenge:

They require a connection to the database.

Each time a request comes in, a new connection must be created, and once the database operation is completed, that connection is promptly torn down.

But that's how it is supposed to work right?

How come is this a problem?

The thing is with thousands of requests hitting your backend every second, the constant creation and destruction of connections become an heavy bottleneck in your system's performance.

As backend developers, we know that improving backend performance is a necessity.

And this is where connection pooling steps in as a crucial mechanism to solve this issue.


What is a Connection Pool ?

It is a pool or a cache of active database connections that can be reused for different incoming requests.

And connection pooling is a mechanism to manage and reuse active database connections.

But how does the connection pooling work?

Let's understand with MongoDB and Mongoose.

When you establish a connection to your MongoDB database using Mongoose, either through mongoose.connect() or mongoose.createConnection(), Mongoose initializes a connection pool by default.

This connection pool creates and maintains a set of connections to the MongoDB server.

When your application needs to perform a database operation, Mongoose checks if there is an available connection in the pool. If an idle connection is available, it is reused for the operation.

This reuse of connections significantly reduces the overhead of creating and tearing down connections for every operation.

Not only that but the connection pool allows you to set limits on the number of connections it maintains. These limits are controlled through options like minPoolSize and maxPoolSize and are defined when establishing the initial connection using mongoose.connect().

const express = require('express');
const mongoose = require('mongoose');
const Test = require('./test.mode');

const app = express();
const port = 3000;

const dbURI = 'mongodb://localhost:27017/db_name';

// setting the max and min size of Connection Pool
mongoose.connect(dbURI, {
    maxPoolSize: 10,
    minPoolSize: 5,
})
    .then(() => console.log('Connected to MongoDB'))
    .catch((err) => console.log(err));


app.get('/', async (req, res) => {
    const result = await Test.findOne({}); // Simulate a database query
    res.json(result);
});


/**
Routes to handle requests
*/


app.listen(port, () => {
    console.log(`Server is listening on port ${port}`);
});

You can specify how many connections can be kept in the pool simultaneously.

minPoolSize and maxPoolSize set the min and max no. of active database connections required for use.

Mongoose often includes mechanisms to validate the health of connections in the pool.

If a connection becomes invalid due to a network issue or other problems, Mongoose can automatically replace it with a new one.

Mongoose also manages idle connections in the pool, closing connections that have been idle for an extended period to ensure that your application doesn't exhaust the database's connection limits.

Because re-using a connection from the pool is much more efficient than creating a new connection for each request because it reduces the overhead of connection setup.

After a request - response cycle is completed, the database connection is not closed but rather returned to the connection pool, where it can be reused for the next incoming requests.

Database connections are closed when the server is shut down or maybe due to any error that can leads to server crash :) .

Multiple Request Handling

Connection Pooling also helps to manage multiple incoming requests simultaneously.

As per the documentation in mongoose version 8.0.0, it says for both maxPoolSize and minPoolSize : "MongoDB only allows one operation per socket at a time, so you may want to increase this if you find you have a few slow queries that are blocking faster queries from proceeding."

The link is here.

Each socket represents a single, established network connection between your Node.js application (using Mongoose) and the MongoDB server.

Now this is possible that certain requests, especially write operations, can be a slow query blocking other faster queries since one connection or socket allows one operation a time and block other incoming request until the current operation is finished.

Therefore a pool of active connection can be used to handle other requests that are faster queries, therefore reducing the load over one connection between the node.js server and mongodb.

Conclusion

Libraries like Mongoose automatically handles connection pooling so that you don't have to worry about it. But I guess it's still nice to learn about these concepts :D .

If you like this article, follow me for more such content.

Would really appreciate your feedback if possible.

Also,

You can follow me on twitter :)

Did you find this article valuable?

Support Mayukh Bhowmick by becoming a sponsor. Any amount is appreciated!