PostgreSQL Connection Pool Errors on Linux: Diagnosis and Resolution

Introduction

Few errors are as frustrating as FATAL: too many connections for role when your application is under load. PostgreSQL connection pools are supposed to prevent this, yet I have seen production systems go down because of misconfigured pooling, leaked connections, and incorrect role limits. This post covers the most common connection pool failures on Linux and how to resolve them systematically.

Environment

The setup in question was a PostgreSQL 16 cluster running on Ubuntu 22.04 LTS with PgBouncer as the connection pooler. The application server ran Node.js with the pg package, connecting through PgBouncer. The PostgreSQL instance had max_connections set to 200, and PgBouncer was configured with a pool size of 25 per user.

psql --version
# psql (PostgreSQL) 16.1

pgbouncer --version
# pgbouncer version 1.21.0

Problem

Under moderate traffic spikes, the application started throwing connection errors:

# Application logs
Error: connection refused: FATAL: too many connections for role "app_user"
    at /app/node_modules/pg/lib/client.js:327:11

Meanwhile, PostgreSQL logs showed:

2025-06-15 14:23:45.123 UTC [8901] FATAL:  too many connections for role "app_user"
2025-06-15 14:23:45.124 UTC [8901] DETAIL:  Role "app_user" has 25 connections already, user-limit is 25.

The PgBouncer pool was exhausting its allocation and passing overflow connections directly to PostgreSQL, which then rejected them.

Analysis

Connection pool issues on Linux typically stem from a combination of factors:

1. PgBouncer pool size vs PostgreSQL role limits

If PgBouncer's pool_size exceeds the PostgreSQL role's connection limit, overflow connections will fail:

# Check role limits in PostgreSQL
psql -U admin -d postgres -c "SELECT rolname, rolconnlimit FROM pg_roles WHERE rolname = 'app_user';"
#  rolname   | rolconnlimit
# -----------+--------------
#  app_user  |           25

2. Connection leaks

Applications that do not properly release connections cause the pool to gradually fill:

-- Check for idle connections
SELECT pid, state, state_change, query
FROM pg_stat_activity
WHERE usename = 'app_user'
ORDER BY state_change;

3. Transaction-level pooling vs session-level pooling

PgBouncer's pooling mode affects how connections are managed:

# /etc/pgbouncer/pgbouncer.ini
[pgbouncer]
pool_mode = transaction    # Best for most web apps
server_reset_query = DISCARD ALL

Transaction mode is more efficient but requires careful handling of prepared statements and session state.

4. File descriptor limits

Linux limits the number of open file descriptors per process, which directly affects connection capacity:

cat /proc/$(pgrep pgbouncer)/limits | grep "Max open files"
# Max open files            1024                 1048576              files

Solution

1. Increase PostgreSQL role limits appropriately

ALTER ROLE app_user WITH CONNECTION LIMIT 50;

2. Configure PgBouncer correctly

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 10
reserve_pool_timeout = 3

server_lifetime = 3600
server_idle_timeout = 600
client_idle_timeout = 0

max_client_conn = 1000
max_db_connections = 50

3. Increase file descriptor limits

# In /etc/security/limits.conf
pgbouncer soft nofile 65536
pgbouncer hard nofile 65536

# Or via systemd
[Service]
LimitNOFILE=65536

4. Fix connection leaks in the application

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

const pool = new Pool({
  max: 20,          // Match or stay below PgBouncer pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

// Always release connections
async function query(text, params) {
  const client = await pool.connect();
  try {
    return await client.query(text, params);
  } finally {
    client.release();  // Critical: always release
  }
}

5. Monitor connection usage

Set up alerts based on connection count:

#!/bin/bash
CURRENT=$(psql -U admin -t -c "SELECT count(*) FROM pg_stat_activity WHERE usename='app_user'" | tr -d ' ')
LIMIT=$(psql -U admin -t -c "SELECT rolconnlimit FROM pg_roles WHERE rolname='app_user'" | tr -d ' ')
PERCENT=$((CURRENT * 100 / LIMIT))
if [ "$PERCENT" -gt 80 ]; then
  echo "WARNING: Connection usage at ${PERCENT}%" | logger -t pg-monitor
fi

After implementing these changes, the connection pool errors stopped completely, and the system handled traffic spikes without dropping connections.

Lessons Learned

Connection pooling is not a set-it-and-forget-it configuration. The pool size, connection limits, and application behavior all interact. Always set PgBouncer's default_pool_size to match or be less than PostgreSQL's role connection limit. Monitor idle connections regularly to detect leaks early. And remember that transaction-mode pooling, while more efficient, requires applications to avoid session-state dependencies.

This blog does not accept any external sponsorships, affiliate marketing, or ad revenue.