Connection Pooling in Database

As a developer, you may not have spent a lot of time thinking about database connections. A single database connection is not expensive, but as things scale up, problems can arise. So let’s dive into the world of connection pooling, and take a look at how it can help us build more performant applications

A typical way of database connection

Before we get into pooling, let’s quickly review what happens when your application connects to the database to perform a database operation:

  1. The application uses a database driver to open a connection.
  2. A network socket is opened to connect the application and the database.
  3. The user is authenticated(means database user).
  4. The operation completes and the connection may be closed.

As we can see, the opening and closing of the connection and the network socket is a several step process that requires computing resources. However, not closing the connection and keeping it open all time also consumes resources.

Why pool database connections?

For a simple application typical way of connecting databases may suffice
but when your application gets some amount of users, a simple database operation might take a few seconds.
Also, the constant opening and closing of connections for each database operation
will consume too many resources.

Often, it makes sense to find a way of keeping connections open and passing them from operation to operation as they’re needed, rather than opening and closing a brand new connection for each operation.

What is database connection pooling?

Database connection pooling is a way to reduce the cost of opening and closing connections by maintaining a pool of open connections that can be passed from database operation to database operation as needed.

How to create and configure connection pools?

For an example, I’ll take PostgreSQL and Sequelize ORM to demonstrate.

But almost all kinds of database or ORM supports connection pooling.

Below code, snippet contains some default values

pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000,
}

max-> a maximum number of clients pool can extend concurrently.

min -> a minimum number of clients pool can extend concurrently.

acquire -> The maximum time (in milliseconds) that pool will try to get a connection before throwing an error.

idle -> The maximum time (in milliseconds) that a connection can be idle or wait for any database operation before being released.

These are the minimal configurations needed to get started quickly.

You always look back and add more configuration as per your requirement.

Thank You for reading.

Feel free to share your thoughts in the comments section.

Follow Prodip Kumar Paul for more such content like this.

--

--

--

A Self Taught Developer

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Connectivity Checker (Shellmates)— Write-up.

PPL2021 — Docker Orchestration — Tupperware For Your Code

Launching the Credmark Risk Library

Coding Standards in Java

Why it’s Necessary for Enterprises to Embrace the Hybrid Cloud System

Metacity Spotlight: Harry Dinh, Chief Technology Officer

Laboratory Elements

Improving KPI management

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Prodip Kumar Paul

Prodip Kumar Paul

A Self Taught Developer

More from Medium

Collaborative Drawing App: Using the Database

How To Store And Retrieve RedisJSON on Redis Cloud using Node.js

NoSQL Database | MongoDB

Fix React start command Connection Refused on Docker-Compose