Interactive Troubleshooter

Published: Sep 4, 2026

PostgreSQL High Connections: Pooling and Tuning

Time: 15 mins Level: Beginner

By StepDiag Engineering Team

Reviewed by Ibraheem Taofeeq Opeyemi

An interactive root-cause diagnostic tree to fix PostgreSQL high connection errors and client limit exhaustion. Follow structured troubleshooting paths to configure PgBouncer pooling, tune max_connections, resolve idle transactions, and clean up leaked sockets.

Full Diagnostic Tree & Step-by-Step Overview

What specific connection error or performance bottleneck is your PostgreSQL instance experiencing?

Clients receiving 'FATAL: sorry, too many clients already' error when connecting.
High memory consumption, slow query response, or high latency due to hundreds of backends.
PgBouncer pooler errors (e.g., 'no more connections allowed', auth failures, or pooling mode conflicts).
Application backend connection leaks or unclosed sessions accumulating in 'idle' state.

What is the primary factor driving the connection limit exhaustion in PostgreSQL?

PostgreSQL max_connections parameter limit reached due to direct, unpooled application connections.
Superuser reserved connections are exhausted, blocking administrative DBA logins.
Connection spikes during traffic bursts or microservice scaling without client-side pooling limits.

Deploy PgBouncer Connection Pooler & Tune max_connections

Solution:

Root Cause: Excessive Direct Client Connections Spawning Dedicated OS Processes

PostgreSQL uses a process-per-connection architecture. Each incoming client connection forks a dedicated backend process, consuming memory (work_mem, shared_buffers context, stack size) and incurring heavy CPU context switching overhead. When application instances scale horizontally or launch unpooled microservices, the connection count rapidly hits the configured max_connections limit, resulting in FATAL: sorry, too many clients already.

Step-by-Step Fix:

1Audit Current Connection Counts via SQL:

Connect to PostgreSQL as superuser and execute:

sql
     SELECT count(*), state, usename, client_addr 
     FROM pg_stat_activity 
     GROUP BY state, usename, client_addr 
     ORDER BY count(*) DESC;

2Install and Configure PgBouncer:

Install PgBouncer package on Linux:

bash
     sudo apt-get install pgbouncer  # Ubuntu/Debian
     sudo yum install pgbouncer      # RHEL/CentOS

Edit /etc/pgbouncer/pgbouncer.ini to enable transaction pooling:

ini
     [databases]
     mydb = host=127.0.0.1 port=5432 dbname=mydb

     [pgbouncer]
     listen_port = 6432
     listen_addr = *
     auth_type = md5
     auth_file = /etc/pgbouncer/userlist.txt
     pool_mode = transaction
     max_client_conn = 5000
     default_pool_size = 20
     min_pool_size = 10
     reserve_pool_size = 5

3Tune postgresql.conf Settings:

Set max_connections to a safe limit based on available hardware (e.g., 100–200 max connections) in /etc/postgresql/16/main/postgresql.conf:

ini
     max_connections = 100

4Restart Services and Redirect Application Traffic:

Reload PostgreSQL: sudo systemctl reload postgresql

Start PgBouncer: sudo systemctl restart pgbouncer

Point application connection strings to PgBouncer port 6432.

Increase Superuser Reserved Connections & Terminate Stale Sessions

Solution:

Root Cause: Exhaustion of Reserved Connection Slots for Administrative Access

By default, PostgreSQL reserves a specified number of connection slots (superuser_reserved_connections, default 3) exclusively for roles with SUPERUSER privileges to perform administrative troubleshooting when connection limits are reached. If application roles log in with superuser privileges or if all reserved slots are consumed, DBAs are locked out from inspecting or killing rogue queries.

Step-by-Step Fix:

1Connect via Local Unix Domain Socket:

SSH directly into the database server host and access PostgreSQL via local socket (which bypasses TCP network limits and uses administrative reserves):

bash
     sudo -u postgres psql -d postgres

2Identify and Kill Rogue Application Sessions:

Terminate all idle or long-running queries from non-superuser roles:

sql
     SELECT pg_terminate_backend(pid)
     FROM pg_stat_activity
     WHERE usename != 'postgres'
       AND pid <> pg_backend_pid();

3Increase Reserved Administrative Slots:

Edit postgresql.conf to allocate safety slots for DBAs:

ini
     superuser_reserved_connections = 10

4Enforce Non-Superuser Role Separation:

Audit application roles and revoke SUPERUSER privilege from application connection strings:

sql
     ALTER ROLE app_user NOSUPERUSER;

Configure Client Application Connection Pools & Throttling

Solution:

Root Cause: Unbounded Application-Side Connection Pool Spikes

Modern web frameworks and ORMs (e.g., Prisma, TypeORM, SQLAlchemy, or HikariCP) initialize client-side connection pools per application process or serverless function execution (e.g., AWS Lambda, Vercel). During traffic bursts or concurrent cron jobs, each worker process spawns full connection pools (e.g., 10–20 connections per pod across 50 Kubernetes pods), instantly overwhelming PostgreSQL.

Step-by-Step Fix:

1Restrict Client ORM/Driver Pool Max Size:

Lower max connection limits in application configuration files (e.g., set HikariCP maximumPoolSize=5 or Prisma connection_limit=5).

2Implement Serverless Proxy / Data API:

For serverless architectures, route database calls through dedicated proxies like AWS RDS Proxy, Prisma Data Proxy, or PgBouncer running in transaction mode.

3Set Statement & Idle Timeout Safeguards:

Configure timeout limits in postgresql.conf to automatically cancel runaway queries and clean up hung connection handshakes:

ini
     statement_timeout = 30000                  # 30 seconds
     idle_in_transaction_session_timeout = 60000 # 60 seconds

4Apply Changes:

Execute SELECT pg_reload_conf(); in SQL to apply non-restart parameters.

What specific operational degradation is caused by high connection counts?

Connections stuck in 'idle in transaction' state holding locks and causing table bloat.
High RAM consumption and OOM (Out Of Memory) kernel kills due to work_mem allocation.
High CPU context switching and high load averages with low query throughput.

Configure idle_in_transaction_session_timeout & Fix Application Locks

Solution:

Root Cause: Orphaned Transactions Holding Locks and Preventing Autovacuum

Connections remaining in the idle in transaction state occur when an application opens a database transaction (BEGIN), executes a query, and fails to issue a COMMIT or ROLLBACK due to unhandled exceptions or blocking network calls. These orphaned transactions hold table/row locks, prevent autovacuum from cleaning up dead tuples (causing table bloat), and consume backend connection slots.

Step-by-Step Fix:

1Locate Idle Transactions in SQL:

Identify long-running idle transactions and their active duration:

sql
     SELECT pid, usename, state, age(clock_timestamp(), xact_start), query
     FROM pg_stat_activity
     WHERE state = 'idle in transaction'
     ORDER BY xact_start ASC;

2Enforce Automatic Idle Transaction Termination:

Set global or role-level timeouts in postgresql.conf or via SQL:

sql
     ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
     SELECT pg_reload_conf();

3Implement Application-Level Error Handling:

Wrap database calls in try...finally or using blocks to ensure connections return to the pool and issue explicit rollbacks on error.

Optimize work_mem & Memory Allocation Per Backend Connection

Solution:

Root Cause: OOM Killer Terminating Postgres Due to Uncapped Memory Overhead

PostgreSQL allocates dynamic memory for complex sorting, hash joins, and temporary operations per connection based on the work_mem parameter. If work_mem is set too high (e.g., 64MB) and 200 backend processes perform complex queries simultaneously, memory consumption can exceed host RAM, prompting the Linux OOM killer to terminate the PostgreSQL primary process. Refer to the PostgreSQL Official Documentation on Resource Consumption for detailed memory configuration rules.

Step-by-Step Fix:

1Calculate Safe Memory Budget:

Formula for total max memory potential:

$$\text{Total RAM Required} = \text{shared\_buffers} + (\text{max\_connections} \times \text{work\_mem} \times \text{avg\_sorts\_per\_query})$$

2Tune work_mem and shared_buffers in postgresql.conf:

Reduce work_mem globally (e.g., 4MB to 8MB) and increase it only for specific complex reporting sessions:

ini
     shared_buffers = 4GB              # 25% of total system RAM
     work_mem = 8MB                    # Safe for ~100 connections
     maintenance_work_mem = 512MB

3Set Per-Session work_mem Overrides for Analytics:

For heavy reporting jobs, set work_mem dynamically at session scope without risking global OOM:

sql
     SET LOCAL work_mem = '256MB';
     -- Execute heavy query --

4Reload PostgreSQL Configuration:

Run sudo systemctl reload postgresql to apply memory changes without restarting the server.

Reduce CPU Context Switching via Connection Scaling Laws

Solution:

Root Cause: CPU Thrashing and Cache Invalidation from High Active Process Counts

Increasing max_connections beyond the physical CPU core count does not increase database throughput. Active database backends compete for CPU time slices and disk I/O, generating excessive context switching and cache invalidations. As active connections exceed ~2-4x physical CPU cores, throughput drops precipitously while latency spikes.

Step-by-Step Fix:

1Determine Optimal Connection Pool Ceiling:

Use the standard PostgreSQL connection sizing formula:

$$\text{Optimal Connections} = (\text{CPU Cores} \times 2) + \text{Effective Spindle Count}$$

For an 8-vCPU cloud instance with SSD storage, optimal active connection count is between 17 and 32.

2Enforce Strict Connection Pooling Thresholds:

Configure PgBouncer default_pool_size = 25 to keep active PostgreSQL backend count low, queueing brief spikes at the PgBouncer pool layer rather than stressing PostgreSQL backends.

3Monitor System CPU & Context Switches:

Check Linux OS context switching rate using vmstat 1 (watch the cs column).

4Verify Performance Improvement:

Measure query latency drop under load testing using pgbench before and after capping active connections.

What specific PgBouncer error or session behavior are you encountering?

PgBouncer returning 'ERROR: pgbouncer cannot connect to server' or authentication failed.
Prepared statements, LISTEN/NOTIFY, or temporary tables breaking in Transaction Pooling mode.
PgBouncer error 'no more connections allowed' or reaching max_client_conn limit.

Fix PgBouncer User Authentication & userlist.txt Configuration

Solution:

Root Cause: Auth File Mismatch or Incorrect Password Hashing

PgBouncer sits between clients and PostgreSQL, requiring its own authentication mechanism (userlist.txt or auth_query) to verify client credentials before opening or reusing a server backend connection. If client passwords in userlist.txt do not match PostgreSQL pg_shadow hashes, or if MD5/SCRAM-SHA-256 auth protocols conflict, PgBouncer rejects connections.

Step-by-Step Fix:

1Generate Auth User List from PostgreSQL:

Query user credentials directly from PostgreSQL:

sql
     SELECT concat('"', usename, '" "', passwd, '"') 
     FROM pg_shadow;

2Populate /etc/pgbouncer/userlist.txt:

Write output to the userlist file:

text
     "app_user" "SCRAM-SHA-256$4096:..."
     "postgres" "md5..."

Ensure correct file permissions: sudo chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt && sudo chmod 600 /etc/pgbouncer/userlist.txt

3Enable Automated Auth Query (Alternative to Manual File):

Configure PgBouncer to query PostgreSQL directly for user hashes in pgbouncer.ini:

ini
     auth_type = scram-sha-256
     auth_query = SELECT usename, passwd FROM pgbouncer.get_auth($1)

4Reload PgBouncer:

Issue reload command: sudo systemctl reload pgbouncer.

Configure PgBouncer Statement Pooling / Protocol Prep-Statement Support

Solution:

Root Cause: Incompatibility Between Transaction Pooling and Session-State Features

In pool_mode = transaction, PgBouncer assigns a server connection to a client only for the duration of a single transaction. Session-level features—such as prepared statements (PREPARE), SET session variables, temporary tables, advisory locks, and LISTEN/NOTIFY—are tied to a specific backend process. Reassigning the backend mid-session causes prepared statement non-existence errors or state leakage between clients.

Step-by-Step Fix:

1Option A: Enable Prepared Statement Support in PgBouncer (v1.21+):

Enable protocol-level prepared statement tracking in pgbouncer.ini:

ini
     max_prepared_statements = 100

2Option B: Use Client-Side Prepared Statement Disabling:

Configure application ORM/driver to disable server-side prepared statements (e.g., in JDBC prepareThreshold=0, or in Pgx prefer_simple_protocol=true).

3Option C: Switch Specific Databases to Session Pooling:

For applications relying on session state or LISTEN/NOTIFY, set pool_mode = session for that specific database block in pgbouncer.ini:

ini
     [databases]
     events_db = host=127.0.0.1 port=5432 dbname=events_db pool_mode=session

Scale PgBouncer max_client_conn & File Descriptors

Solution:

Root Cause: Exceeding Client Connection Limits or Linux OS File Descriptor Limits

When max_client_conn in pgbouncer.ini is reached, PgBouncer refuses new incoming client sockets with ERROR: no more connections allowed. Additionally, if the operating system process limit for open file descriptors (nofile) is lower than max_client_conn, the Linux kernel denies socket creation.

Step-by-Step Fix:

1Increase max_client_conn in pgbouncer.ini:

Edit /etc/pgbouncer/pgbouncer.ini:

ini
     max_client_conn = 10000
     default_pool_size = 30

2Increase Linux systemd Service Limits for PgBouncer:

Override systemd file limits via sudo systemctl edit pgbouncer:

ini
     [Service]
     LimitNOFILE=65536

3Reload Systemd and PgBouncer:

Apply systemd changes and reload:

bash
     sudo systemctl daemon-reload
     sudo systemctl restart pgbouncer

What type of connection leak or unclosed session accumulation is occurring?

Application clients opening connections without closing them ('idle' state accumulating indefinitely).
Network dropouts or dead TCP sockets leaving ghost connections open on PostgreSQL server.

Enforce Client Session Timeouts & Connection Leak Auditing

Solution:

Root Cause: Missing Connection Closure Logic in Application Code

Connection leaks occur when application code acquires a database connection from a driver pool or directly, but fails to invoke .close() or return the connection to the pool upon completion or during exception handling. The backend stays in idle state indefinitely, holding open socket descriptors.

Step-by-Step Fix:

1Audit Idle Connection Duration in PostgreSQL:

Find connections that have been idle for long periods:

sql
     SELECT pid, usename, client_addr, state_change, age(clock_timestamp(), state_change) AS idle_duration
     FROM pg_stat_activity
     WHERE state = 'idle'
     ORDER BY idle_duration DESC;

2Set idle_session_timeout in PostgreSQL (v14+):

Terminate non-transactional idle connections automatically after a set duration in postgresql.conf:

ini
     idle_session_timeout = 300000 # 5 minutes (in milliseconds)

3Enable Connection Leak Detection in Application Pools:

In HikariCP, set leakDetectionThreshold = 20000 (logs a warning if a connection is held outside the pool for > 20s).

4Reload PostgreSQL:

Run SELECT pg_reload_conf(); to activate the timeout.

Enable TCP Keepalives to Purge Dead Ghost Connections

Solution:

Root Cause: Unclosed TCP Sockets Caused by Network Disconnections or Firewalls

When client applications terminate abruptly (e.g., power loss, container restart, cloud security group drops) without sending a TCP FIN or RST packet, the PostgreSQL server kernel considers the TCP connection active. Without aggressive TCP keepalives, these ghost connections remain in pg_stat_activity indefinitely.

Step-by-Step Fix:

1Configure OS-Level TCP Keepalives (/etc/sysctl.conf):

Reduce TCP keepalive idle time and probing frequency on the database host:

ini
     net.ipv4.tcp_keepalive_time = 120    # Wait 2 minutes before sending keepalive probes
     net.ipv4.tcp_keepalive_intvl = 15    # Send probes every 15 seconds
     net.ipv4.tcp_keepalive_probes = 5    # Drop connection after 5 failed probes

Apply sysctl settings: sudo sysctl -p

2Configure PostgreSQL Specific Keepalive Parameters (postgresql.conf):

Override kernel defaults directly in PostgreSQL configuration:

ini
     tcp_keepalives_idle = 120
     tcp_keepalives_interval = 15
     tcp_keepalives_count = 5

3Reload PostgreSQL Configuration:

Run sudo systemctl reload postgresql to purge dead ghost connections automatically.

Related Diagnostic Guides

Windows Update Error 0x80070002 Missing Files Diagnostic Guide

System Restore Failed 0x80070005 Antivirus Conflict Tree

Windows Photo Viewer Not Showing Thumbnails: Codec Diagnostic

Safe Mode Password Not Working With Microsoft Account Fix

Clipboard History Not Working Windows 11: Sync Service Tree

Subscribe to StepDiag

Embed Guide on Your Website