How Database Connection Pool Works

In this article, I'll explain how database connections work, specifically focusing on PostgreSQL. However, you can apply this knowledge to other databases such as MySQL and MS SQL, as they follow similar patterns. Let's start by understanding what a database connection is.

What is a Database Connection?

A database connection allows communication between an application (such as your backend, pgAdmin, DBeaver, etc.) and a database server (PostgreSQL, MySQL, MS SQL). It enables the application, which we'll refer to as the DB Client, to execute queries against the database server.

Typically, establishing a database connection requires:

• Username
• Password
• Host URL
• Port
• Database name

These details are usually combined into a "connection string". Here's an example for PostgreSQL:

jsxCopy
postgresql://myuser:mypassword@localhost:5432/mydatabase

You might wonder why the database name is mandatory. Can't we just connect directly to the server and then access multiple databases?

The answer lies in how PostgreSQL manages connections.

Understanding PostgreSQL Architecture

PostgreSQL is structured using multiple processes. The main server process listens on port 5432, and it will manage different processes how handle different things that happen in PostgresQL, for example Background writer, checkpointer...

When a client connects, the server spawns a separate backend process dedicated to handling that connection:

Client Request → PostgreSQL Server (Main Process) → Backend Process (dedicated per connection)

Each connection gets its own backend process, which terminates when the connection closes.

Because each process consumes resources, PostgreSQL limits the maximum number of simultaneous connections (default: 100). Creating and destroying connections frequently can significantly impact server performance.

So, how do we handle this efficiently? The answer is to use a connection pool.

What is a Connection Pool?

A connection pool acts as a cache for database connections on the client side (PostgreSQL itself does not provide this functionality). Instead of creating and closing connections for each query, the connection pool maintains a set of reusable connections. This significantly improves performance by reducing the overhead of establishing new connections.

Let's illustrate this with examples:

Example without Connection Pool

jsxCopy
const { Client } = require('pg');

async function fetchUsers() {
  const client = new Client({
    user: 'myuser',
    host: 'localhost',
    database: 'mydatabase',
    password: 'mypassword',
    port: 5432,
  });

  try {
    await client.connect();
    console.log('🔌 Connected for fetchUsers');
    const result = await client.query('SELECT * FROM users');
    console.log('Users:', result.rows);
  } catch (err) {
    console.error('Error fetching users:', err);
  } finally {
    await client.end();
    console.log('❌ Connection closed for fetchUsers');
  }
}

async function fetchOrders() {
  const client = new Client({
    user: 'myuser',
    host: 'localhost',
    database: 'mydatabase',
    password: 'mypassword',
    port: 5432,
  });

  try {
    await client.connect();
    console.log('🔌 Connected for fetchOrders');
    const result = await client.query('SELECT * FROM orders');
    console.log('Orders:', result.rows);
  } catch (err) {
    console.error('Error fetching orders:', err);
  } finally {
    await client.end();
    console.log('❌ Connection closed for fetchOrders');
  }
}

async function main() {
  await fetchUsers();  // Opens/closes connection #1
  await fetchOrders(); // Opens/closes connection #2
}

main();

The above code creates and closes two separate connections, reducing performance.

Example with Connection Pool

Now, let's refactor using a connection pool:

jsxCopy
const { Pool } = require('pg');

const pool = new Pool({
  user: 'myuser',
  host: 'localhost',
  database: 'mydatabase',
  password: 'mypassword',
  port: 5432,
  idleTimeoutMillis: 30000,  // Disconnect idle clients after 30 seconds
});

async function fetchUsers() {
  try {
    const result = await pool.query('SELECT * FROM users');
    console.log('Users:', result.rows);
  } catch (err) {
    console.error('Error fetching users:', err);
  }
}

async function fetchOrders() {
  try {
    const result = await pool.query('SELECT * FROM orders');
    console.log('Orders:', result.rows);
  } catch (err) {
    console.error('Error fetching orders:', err);
  }
}

async function main() {
  await fetchUsers();
  await fetchOrders();
}

main();

What happens here?

• The first query (fetchUsers) creates a connection.
• After completion, the connection returns to the pool.
• The second query (fetchOrders) reuses this connection, significantly reducing overhead.
• Connections idle for over 30 seconds are closed automatically.

Best Practices

Avoid Manual Connection Handling

Let the connection pool manage connections automatically. Avoid this pattern:

jsxCopy
await pool.connect();
await pool.close();

Why?

Manually managing connections risks forgetting to close them, eventually exhausting available connections and potentially bringing down the entire application.

Tune the Pool Size

• Set the pool size based on:

  • Application concurrency
  • Query duration
  • PostgreSQL’s max_connections setting

• A good rule of thumb:

  jsxCopy
  pool_size = max_connections / number_of_app_instances - safety_margin

Example:

If PostgreSQL allows 100 connections and you have 5 app instances, try a pool size of 15–18 per instance.

Avoid Long Queries

Having multiple long-running queries simultaneously can exhaust connections and degrade performance. Use monitoring tools like pg_stat_activity, Grafana or Datadog to:

• Identify slow endpoints and queries.
• Improve query performance through indexing or pre-calculating values.
• Pool exhaustion
• Idle connections

Handling Multiple Instances with PgBouncer

If your application runs multiple instances, each instance maintains its own connection pool. Consider using a tool like PgBouncer, a connection pooler for PostgreSQL, that efficiently manages connections across multiple application instances:

PgBouncer acts as a central pool, significantly reducing the number of connections opened directly against PostgreSQL.

---

By now, you should have a solid understanding of database connection pooling, why it’s beneficial, and best practices for implementing it effectively in your applications.