Architecture Weekly #189 - Mastering Database Connection Pooling

Welcome to the new week!

This edition is the first one in a new newsletter format! What does that mean?

• each edition will focus on a specific topic. This time, it’s a database connection pool, but there may be more reviews of reports, articles, etc. They may also be in different formulas: video, podcast, or guest appearance.

• From this week, Architecture Weekly will only be available to paid subscribers.

• I’ll publish one release each month for free subscribers. I might also open some older releases for free.

• All currently public content will remain as such.

I know this can be a big change for you; I understand if you won’t like it and unsubscribe, but I genuinely hope you’ll stay with me and appreciate the new way.

As I wrote in the previous release, I want to make this newsletter engaging, interesting, and filled with unique content. That takes time. I hope changing the format will help me free up more space to provide you with deep dives into Software Architecture. I think this will benefit you and make working on Architecture Weekly more sustainable for me.

A paid subscription also gives you access to exclusive Discord community monthly webinars with access to recordings of all past ones (currently 20 with over 30 hours of content). Check them out here.

I want to be fair, so I'm offering you a free month's trial. Here’s the link: https://www.architecture-weekly.com/b3b7d64d. You can check it out and decide if you like it and want to stay. I hope that you will!

Feel invited to respond or comment!

Still, off we go to Software Architecture!

---

Efficiently managing database connections is crucial for any application that handles multiple requests. Without proper connection management, your application can quickly become slow, unreliable, and costly to maintain. Database connection pooling offers a smarter way to handle database connections.

However, connection pooling isn't always ideal, especially in serverless architectures. Let's explore the mechanics of connection pooling, understand its importance, discuss when it might not be suitable, and explore how modern serverless solutions redefine connection management.

Life without connection pooling

Imagine you're building a new e-commerce platform that helps small and medium-sized businesses create and manage their online stores. It provides features like product listings, inventory management, order processing, and customer analytics. Initially, the focus is on delivering basic functionalities like product management and checkout processes.

You start with a simple architecture. The backend is written in Node.js and PostgreSQL database. The naive version of your database access code could look as:

Each HTTP request opens a new database connection. This can work well for local development or small deployment, but setup quickly hits a wall as your user base grows. Users start complaining about slow load times, and your database server gets overwhelmed by too many open connections.

Why does that become a problem?

Opening a new database connection for every request involves a time-consuming process involving authentication, network setup, and session establishment. This adds significant latency, especially in high-traffic applications. Your database has a finite limit on how many concurrent connections it can handle. Without pooling, you risk overwhelming the database, leading to resource exhaustion and potential downtime.

What is Connection Pooling?

Database connection pooling is a technique that allows applications to manage database connections more efficiently by reusing a set of established connections. Instead of creating a new connection for each database request, the application maintains a pool of open connections that can be reused, significantly reducing the time and resources needed to establish connections repeatedly.

How Connection Pooling Works

Here's a breakdown of how connection pooling operates in a technical environment:

1. Connection Initialization: When the application starts, it initializes a fixed number of database connections and stores them in a pool.

2. Request Handling: Whenever a request needs to interact with the database, the application borrows an existing connection from the pool instead of opening a new one.

3. Connection Reuse: After executing the query, the connection is returned to the pool, making it available for future requests.

4. Dynamic Pool Management: The pool can dynamically resize, creating more connections if demand exceeds a pre-defined limit and reducing idle connections to save resources.

Do you prefer visuals? There you have it!

Connection pooling can significantly enhance performance and reduce latency by eliminating the latency added by setting up a connection each time.

Implementing Connection Pooling

Each mature development environment has a built-in connection pooling. For instance, for PostgreSQL:

• Java has HikariCP,

• .NET has the Npgsql connection pool,

• Python has the SQLAlchemy connection pool,

• Go has the pgxpool for PostgreSQL,

• Node.js has pg.Pool,

• etc.

We’ll use the last one to address our connection management-related performance bottlenecks. The code could look as follows: