Fixing PostgreSQL Connection Pool Exhaustion

Problem

Application throws FATAL: remaining connection slots are reserved errors under load.

Diagnosis

Check current connections:

SELECT count(*) FROM pg_stat_activity;
SELECT max_connections FROM pg_settings WHERE name = 'max_connections';

Solution

  1. Increase max_connections in postgresql.conf:
max_connections = 200
  1. Configure connection pooling with PgBouncer:
[databases]
mydb = host=localhost dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
  1. Fix application connection leaks - ensure connections are properly closed:
# Bad
conn = psycopg2.connect(...)
# do work
# forgot to close

# Good
with psycopg2.connect(...) as conn:
    # do work
    pass  # automatically closed

Verification

Monitor connection usage:

SELECT pid, state, query FROM pg_stat_activity;
Advertisement