Set Up Connection Pooling for Databases and HTTP Clients

Overview

Opening a new database or HTTP connection for every request is expensive. The TCP handshake, TLS negotiation, and database authentication add latency and burn CPU on both sides. Connection pooling keeps a reusable set of already established connections so your code borrows one, runs a query or API call, and returns it. You get lower latency, higher throughput, and fewer “too many connections” errors at peak load.

What follows are working examples for PostgreSQL, MySQL, and Redis, plus HTTP client pooling in Python, JavaScript, and Java. Every example includes the pool setup, a real query or request, and the return path so connections don’t leak.

When to Use

Use connection pooling when your application opens a new connection per request and throughput is lagging, when you’re hitting “too many connections” errors under load, when you make frequent outbound HTTP calls and want to reuse TCP, or when you need to cap concurrency to protect a database or remote service.

When to Avoid

Skip it when a short-lived script runs one query and exits, because the pool overhead isn’t worth it. Also skip it if your driver or HTTP client already manages persistent connections transparently, or if you’re on a serverless platform with strict connection lifetime limits.

Solution

Python

import psycopg2
from psycopg2 import pool
import requests
from requests.adapters import HTTPAdapter

# PostgreSQL connection pool
pg_pool = psycopg2.pool.ThreadedConnectionPool(
    minconn=5,
    maxconn=20,
    host="localhost",
    database="app",
    user="app",
    password="secret"
)

def get_user(user_id: int):
    conn = pg_pool.getconn()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))
            return cur.fetchone()
    finally:
        pg_pool.putconn(conn)

# HTTP client connection pooling
session = requests.Session()
adapter = HTTPAdapter(pool_connections=10, pool_maxsize=20)
session.mount("https://", adapter)
session.mount("http://", adapter)

resp = session.get("https://api.example.com/data")

JavaScript

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

// PostgreSQL connection pool
const pgPool = new Pool({
  host: 'localhost',
  database: 'app',
  user: 'app',
  password: 'secret',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

async function getUser(userId) {
  const client = await pgPool.connect();
  try {
    const result = await client.query('SELECT * FROM users WHERE id = $1', [userId]);
    return result.rows[0];
  } finally {
    client.release();
  }
}

// HTTP client with keep-alive
const http = require('http');
const https = require('https');

const httpAgent = new http.Agent({ keepAlive: true, maxSockets: 20 });
const httpsAgent = new https.Agent({ keepAlive: true, maxSockets: 20 });

const api = axios.create({ httpAgent, httpsAgent });

api.get('https://api.example.com/data')
  .then(res => console.log(res.data));

Java

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;
import okhttp3.ConnectionPool;
import okhttp3.OkHttpClient;
import okhttp3.Request;
import java.util.concurrent.TimeUnit;

// HikariCP — the gold standard for JVM connection pooling
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost/app");
config.setUsername("app");
config.setPassword("secret");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(2000);
config.setIdleTimeout(30000);
config.addDataSourceProperty("cachePrepStmts", "true");

HikariDataSource ds = new HikariDataSource(config);

int userId = 1;
try (Connection conn = ds.getConnection();
     PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?")) {
    ps.setInt(1, userId);
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        System.out.println(rs.getString("name"));
    }
}

// HTTP client with a bounded connection pool
OkHttpClient httpClient = new OkHttpClient.Builder()
    .connectionPool(new ConnectionPool(20, 5, TimeUnit.MINUTES))
    .connectTimeout(5, TimeUnit.SECONDS)
    .build();

Request request = new Request.Builder()
    .url("https://api.example.com/data")
    .build();

httpClient.newCall(request).execute();

Explanation

A connection pool is a bounded queue of established TCP connections. When your code asks for one, the pool hands over an idle connection instead of opening a new socket. When the work is done, the connection goes back to the pool instead of being closed, so most operations skip the handshake and authentication cost.

I once debugged a service where p99 latency jumped from 5ms to 80ms every morning at 9am. The cause was a connection pool with minconn=0: the first users of the day paid the full TCP+TLS+auth cost on every request until the pool warmed up. Setting minconn=5 fixed it instantly.

A few levers matter. The minimum size keeps connections warm so the first request isn’t slow. The maximum size is the ceiling that stops the pool from overwhelming the database or remote server. The connection timeout decides how long a caller waits before giving up. The idle timeout closes connections that have been idle too long. The max lifetime forces the pool to replace old connections before they go stale, which also helps when you rotate database credentials.

For HTTP clients, keep-alive reuses the underlying TCP connection across requests to the same host, saving the TLS handshake on every call. If the database is still the bottleneck after tuning the pool, the next steps are usually query and schema work. See database indexing and query optimization.

Variants

For PostgreSQL, the common choices are psycopg2.pool in Python, pg in Node.js, and HikariCP on the JVM. All three offer threaded or async pools, and the key is matching the pool size to your actual concurrency rather than copying defaults.

MySQL uses mysql-connector-python, mysql2, or HikariCP, depending on your stack. The two settings to watch are wait_timeout on the server side and max_connections, because a pool that opens too many connections will hit the server limit and start failing.

Redis is fast enough that people often skip pooling, but redis-py, ioredis, and Lettuce all ship connection pools for good reason. At high concurrency, a single shared connection becomes a bottleneck, and a small pool (5 to 10 connections) keeps throughput stable.

HTTP clients need pooling too, and each language handles it differently. In Python, requests.Session paired with HTTPAdapter exposes pool_maxsize per host. In Node.js, axios combined with http.Agent gives you maxSockets to control parallel connections. In Java, OkHttp’s ConnectionPool and Apache HttpClient both ship with per-route limits built in.

Best Practices

Size the pool to your actual concurrency, not the number of CPU cores. Start with the number of concurrent workers plus a small buffer and adjust from there. Always release connections in a finally block so they go back to the pool even when an exception is thrown. Keep connectionTimeout lower than your application’s end-to-end request timeout so a stalled pool fails fast instead of hanging. Monitor active, idle, waiting, and total connections; queueing usually means the pool is too small or the database is saturated. Enable prepared statement caching at the pool level when available, such as HikariCP cachePrepStmts. Use TLS for database and service-to-service HTTP connections, but keep a reasonable idle timeout to close stale sockets. For a broader performance strategy, see the performance optimization guide.

Monitoring pool health

Most pool libraries expose metrics you can scrape and alert on. HikariCP exposes active, idle, and waiting counts via JMX or its HikariPoolMXBean. In Python, psycopg2.pool doesn’t expose metrics directly, so track borrows and returns in a wrapper. In Node.js, pg.Pool exposes pool.totalCount, pool.idleCount, and pool.waitingCount. Set alerts on waiting threads growing over time, because that means the pool is undersized or the database is slow. Track connection leaks by comparing active connections before and after a request batch: if active keeps climbing, some code path forgot to return the connection.

Sizing in cloud and serverless

In containerized environments, size the pool to the container’s concurrency limit, not the host’s CPU count. A Kubernetes pod with 2 CPU cores and 4 concurrent requests needs a pool of 5 or 6, not 20. I learned this the hard way after a deployment where every pod opened 20 connections to a database that could only handle 100 total. On serverless platforms like AWS Lambda or Cloud Run, connection pools are tricky because each instance is short-lived and shares the database with many other instances. Consider a proxy like PgBouncer or RDS Proxy in front of the database to pool and share connections across many instances.

Common Mistakes

Not releasing connections is the biggest one: a leaked connection eventually empties the pool and blocks every request. A pool size of 1 serializes all database access and kills throughput. A pool that’s too large can overwhelm the database max_connections limit and waste memory. Ignoring idle timeouts leads to stale connections and silent failures. Disabling HTTP keep-alive wastes milliseconds and CPU by reopening TLS for every outbound request. Sharing one pool across unrelated databases couples traffic and makes tuning impossible; use one pool per database and per application instance.

Summary

Connection pooling keeps a reusable set of TCP connections so your code borrows one, runs a query or API call, and returns it. The pool size, connection timeout, idle timeout, and max lifetime are the four levers that matter. Size the pool to actual concurrency, not CPU cores. Always release connections in a finally block. Keep connectionTimeout shorter than your request timeout so you fail fast. For HTTP, enable keep-alive and bound maxSockets to your concurrency. When the database is still the bottleneck after tuning the pool, move on to database indexing and query optimization.

See Also

For the JVM side, the HikariCP documentation is the reference I keep coming back to. If you’re on Python, the psycopg2 pool module explains the threaded and async pool options. Node.js developers should read the node-postgres Pool API, which exposes totalCount, idleCount, and waitingCount. For HTTP pooling in Java, OkHttp’s ConnectionPool is well documented. On the Node.js HTTP side, the axios docs cover agent-based keep-alive in detail.