Database Connection Pool Exhausted Fix

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

Your application fails with ERROR: remaining connection slots are reserved for non-replication superuser connections or Connection pool exhausted — all database connections in the pool are in use and new requests cannot acquire a connection.

Step-by-Step Fix

1. Check current active connections

-- PostgreSQL: check active connections
SELECT count(*) AS total_connections,
       count(*) FILTER (WHERE state = 'active') AS active,
       count(*) FILTER (WHERE state = 'idle') AS idle
FROM pg_stat_activity
WHERE datname = 'mydb';

Expected output:

total_connections | active | idle
100               | 98     | 2

2. Kill idle connections

-- Terminate idle connections older than 5 minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < now() - INTERVAL '5 minutes'
  AND datname = 'mydb';

3. Fix connection pool configuration

# Wrong — too small pool size for the workload
from psycopg2 import pool

connection_pool = pool.SimpleConnectionPool(
    1, 10,  # min, max connections
    host="localhost",
    database="mydb",
    user="myuser",
    password="mypass",
)
# 10 connections is too few for 50 concurrent requests

# Right — size the pool for the workload
connection_pool = pool.SimpleConnectionPool(
    5, 50,  # min, max connections
    host="localhost",
    database="mydb",
    user="myuser",
    password="mypass",
)

4. Fix connection leaks (always return connections)

# Wrong — never returning connection to pool
def get_user(user_id):
    conn = connection_pool.getconn()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    user = cursor.fetchone()
    # Connection is never returned to the pool!

# Right — always return connection in finally block
def get_user(user_id):
    conn = None
    try:
        conn = connection_pool.getconn()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
        return cursor.fetchone()
    finally:
        if conn:
            connection_pool.putconn(conn)

Common Mistakes

Prevention

• Set connection pool size to handle peak concurrent requests (monitor with APM tools).
• Use connection pool libraries for all database access (psycopg2 pool, HikariCP, etc.).
• Set statement_timeout and idle_in_Transaction_session_timeout.
• Monitor connection utilization with pg_stat_activity.
• Implement circuit breakers for database access to fail fast when the pool is exhausted.

DodaTech Tools

Doda Browser's database connection monitor tracks pool utilization and alerts on exhaustion risks. DodaZIP archives connection pool configuration snapshots for capacity planning. Durga Antivirus Pro maintains a lean connection pool with circuit breakers to handle database failures gracefully.

Common Mistakes with pool exhausted

1. Misunderstanding that String is [Char] with poor performance for large text operations
2. Using foldl instead of foldl' causing stack overflow on large lists
3. Forgetting deriving (Show, Eq) on custom data types needed for debugging

These mistakes appear frequently in real-world CONNECTION code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ