PostgreSQL Connection Pooling: How to Choose the Right Pool Size

Database connections are a limited resource. In a busy application, opening a new PostgreSQL connection for every request can create unnecessary overhead and put pressure on the database.

Connection pooling solves this by keeping a controlled number of database connections available for application requests.

The difficult part is choosing the right pool size. Too few connections can make requests wait. Too many can overload PostgreSQL.

How Connection Pooling Works

Without pooling, the application may repeatedly create and close connections:

Request
   |
   v
Open Connection
   |
   v
Execute Query
   |
   v
Close Connection

With pooling:

Application
     |
     v
Connection Pool
  |  |  |  |
  v  v  v  v
 PostgreSQL

The application borrows an available connection, executes its query, and returns the connection to the pool.

Why More Connections Are Not Always Better

Suppose an application has 10 instances and each instance opens 100 database connections:

10 instances × 100 connections
= 1,000 connections

That does not mean PostgreSQL can process 1,000 queries efficiently at the same time.

Every active connection consumes resources. High concurrency can increase CPU usage, memory pressure, locking, context switching, and query contention.

The goal is to find a pool size that keeps the database busy without overwhelming it.

Start With Application Concurrency

Consider an application that normally has 20 concurrent database operations per instance.

A pool of 5 connections might cause requests to wait:

20 requests
     |
     v
5 database connections
     |
     +-- Requests wait for available connections

Increasing the pool may reduce that waiting, but only until the database becomes the bottleneck.

There is no universal formula such as "one connection per user."

Consider the Number of Application Instances

Pool configuration must be considered across the entire deployment.

For example:

20 application instances
×
10 connections per instance
=
200 possible connections

If the database can safely support fewer active connections, the application pool needs to be adjusted.

This is one of the most common mistakes in horizontally scaled applications.

Example Configuration

A Node.js application using a PostgreSQL client might configure a pool like this:

import pg from "pg";

const { Pool } = pg;

const pool = new Pool({
  max: 10,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000
});

The important settings include:

max: maximum connections in the pool
idleTimeoutMillis: how long an unused connection can remain available
connectionTimeoutMillis: how long the application waits when acquiring a connection

The exact values should be based on workload testing.

Do Not Hold Connections While Doing Unrelated Work

A connection should be acquired as late as practical and released as soon as the database operation finishes.

Avoid:

const client = await pool.connect();

await performExternalApiCall();

await client.query("SELECT * FROM orders");

client.release();

The connection remains occupied while the application waits for another service.

Prefer:

const response = await performExternalApiCall();

const result = await pool.query(
  "SELECT * FROM orders WHERE id = $1",
  [response.orderId]
);

This keeps database connections available for actual database work.

Monitor Pool Behavior

Useful metrics include:

Active connections
Idle connections
Waiting requests
Query latency
Database CPU
Connection errors

A useful signal is a growing pool wait time.

If application requests frequently wait for an available connection, investigate whether the pool is too small or whether queries are taking too long.

Increasing the pool is not always the correct fix.

Pool Size vs Query Performance

Suppose a query takes 500 ms.

Adding more connections may allow more queries to run concurrently, but eventually PostgreSQL resources become saturated.

If the query itself is inefficient, the better solution may be:

Slow query
   |
   +--> EXPLAIN
   |
   +--> Check indexes
   |
   +--> Reduce unnecessary data
   |
   +--> Optimize query

Connection pooling should not be used to hide inefficient database queries.

Common Mistakes

Setting a Very Large Pool

A large pool can create unnecessary database contention.

Ignoring Multiple Instances

A pool of 20 connections per application instance can become hundreds of connections after scaling.

Holding Connections Too Long

External API calls, file operations, or business logic should not unnecessarily occupy database connections.

Using One Global Connection

A single connection can become a bottleneck and reduce concurrency.

Guessing Pool Size

Use measurements from realistic workloads instead of copying a configuration from another application.

Best Practices

Start with a conservative pool size.
Calculate total possible connections across all application instances.
Monitor connection wait time.
Keep transactions short.
Release connections promptly.
Optimize slow queries before increasing the pool.
Test pool settings under expected peak traffic.
Leave database capacity for administrative and background operations.

Summary

The right PostgreSQL connection pool size depends on the application's concurrency, query duration, number of application instances, and database capacity.

A larger pool is not automatically faster. The database has a finite amount of CPU, memory, storage, and other resources.

Start with a reasonable pool, measure connection wait time and database utilization, then adjust it using production-like workloads. The goal is to keep connections efficiently utilized without turning the database into the bottleneck.