How to Fix Database Connection Pool Exhaustion

DodaTech 2 min read

In this tutorial, you'll learn about How to Fix Database Connection Pool Exhaustion. We cover key concepts, practical examples, and best practices.

The Problem

Your application throws Connection pool exhausted or Timeout: Pool has been closed or Cannot acquire connection from pool — all connections are in use. The database connection pool is depleted because connections are not returned to the pool after use. Each connection that isn't released is permanently lost from the pool until the application restarts or the connection times out.

Quick Fix

1. Identify the pool exhaustion in Node.js (pg-pool)

const { Pool } = require('pg');
const pool = new Pool({ max: 20 });

// Check pool stats
console.log('Total:', pool.totalCount);
console.log('Idle:', pool.idleCount);
console.log('Waiting:', pool.waitingCount);

2. Fix: always release connections

// Bad: forgetting to release — pool leak!
app.get('/data', async (req, res) => {
  const client = await pool.connect();
  const result = await client.query('SELECT * FROM items');
  res.json(result.rows);
  // client.release() is never called!
});

// Good: release in a finally block
app.get('/data', async (req, res) => {
  const client = await pool.connect();
  try {
    const result = await client.query('SELECT * FROM items');
    res.json(result.rows);
  } finally {
    client.release();
  }
});

3. Use a context manager in Python

# Bad: manual management
conn = psycopg2.connect(dsn)
cur = conn.cursor()
cur.execute("SELECT * FROM items")
conn.close()  # Easy to forget on error paths

# Good: context manager
from contextlib import closing
with closing(psycopg2.connect(dsn)) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM items")
        # Auto-closes on exit, even on exceptions

4. Configure pool size correctly

// Node.js (pg-pool)
const pool = new Pool({
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

# Python (SQLAlchemy)
from sqlalchemy import create_engine
engine = create_engine(
    'postgresql://user:pass@host/db',
    pool_size=10,
    max_overflow=20,
    pool_timeout=30,
    pool_pre_ping=True,
)

5. Set database-side timeouts

-- PostgreSQL
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60000';
ALTER SYSTEM SET statement_timeout = '30000';
SELECT pg_reload_conf();

6. Add a health check endpoint

app.get('/health/pool', (req, res) => {
  res.json({
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount,
    usagePercent: (pool.totalCount / pool.max) * 100,
  });
});

Common Causes

Cause	Why It Happens	Fix
Connections not released	`conn.release()` never called in error path	Use `try/finally` or context managers
Pool size too small	`max: 5` when app needs 20	Increase pool `max` size
Stuck long-running queries	Transaction takes minutes, blocking the connection	Set `statement_timeout` on the database
Connection leak after exception	Exception handler doesn't release	Catch exceptions and release in `finally`
Too many app instances	10 replicas * 10 pool size = 100 connections	Reduce per-instance pool or use PgBouncer
No idle timeout	Connections stay open forever	Set `idleTimeoutMillis` to 30 seconds

Prevention

Always use try/finally or context managers to release connections
Set pool_pre_ping=true (SQLAlchemy) or equivalent to test connections before use
Set idleTimeoutMillis and connectionTimeoutMillis to reasonable values (30-60 seconds)
Monitor pool metrics and set alerts when usage exceeds 80% of the pool size

← Previous Cum să faci backup și restore la o bază de date — pg_dump, mysqldump Next → Cum să folosești chei străine în SQL — ON DELETE CASCADE, RESTRICT

Built by the developers of DodaTech

Doda Browser, DodaZIP & Durga Antivirus Pro

Home Browse Quick Fix