Fixing PostgreSQL Connection Pool Exhaustion and High Query Latency

Incident & Problem Summary

PostgreSQL connection pool exhaustion occurs when an application exceeds the maximum number of concurrent connections allowed by the database, leading to “Too many open files” errors or “Connection refused” exceptions. High query latency results from inefficient query execution, poor indexing, or insufficient hardware resources. This issue typically manifests in production environments with high traffic, causing application slowdowns, failed requests, and degraded user experiences.

Symptoms include:

• Application logs reporting connection errors (e.g., FATAL: remaining connection slots are reserved for superusers).
• Increased query execution times, as observed in pg_stat_statements or monitoring tools.
• High CPU or memory utilization on the PostgreSQL server.

Symptoms & Diagnostic Checklist

Verify if your system is affected by checking the following:

1. Connection Pool Limits:

Run SELECT * FROM pg_stat_activity; to count active connections. If the number exceeds max_connections or max_pool_size, the pool is exhausted.

1. Query Performance Metrics:

Use pg_stat_statements to identify slow queries. Look for entries with query_start timestamps close to query_end and high total_time values.

1. Resource Utilization:

Monitor CPU, memory, and disk I/O on the PostgreSQL server. High utilization can indirectly cause latency and connection issues.

1. Log Analysis:

Check PostgreSQL logs for entries like LOG: connection authorized: user=... or LOG: duration: ... ms.

Technical Root Cause Analysis

Connection pool exhaustion is typically caused by:

• Misconfigured Connection Pooling: Applications using connection pools (e.g., PgBouncer) may exceed max_pool limits if not properly tuned.
• Leaked Connections: Unreleased connections from idle or abandoned sessions can deplete the pool.
• High Query Load: Poorly optimized queries, especially full table scans or complex joins, increase execution time and resource consumption.

High query latency often results from:

• Missing Indexes: Queries lacking appropriate indexes force full scans, increasing execution time.
• Inefficient Query Patterns: Subselects, nested loops, or excessive use of SELECT * without filtering.
• Resource Constraints: Insufficient RAM for shared buffers or disk I/O bottlenecks.

Step-by-Step Resolution Procedures

1. Adjust Connection Pool Settings:

 # Modify PostgreSQL configuration 
 sudo nano /etc/postgresql/14/main/postgresql.conf 

Update:

 max_connections = 200 
 max_pool = 100 

Restart PostgreSQL:

 sudo systemctl restart postgresql 

1. Optimize Queries Using pg_stat_statements:

 -- Install and enable extension 
 CREATE EXTENSION pg_stat_statements; 

Analyze slow queries:

 SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10; 

Add indexes to frequently queried columns:

 CREATE INDEX idx_table_column ON table_name(column_name); 

1. Tune PostgreSQL Configuration Parameters:

 # Adjust shared_buffers and work_mem 
 sudo nano /etc/postgresql/14/main/postgresql.conf 

Update:

 shared_buffers = 2GB 
 work_mem = 64MB 

Reload configuration:

 sudo systemctl reload postgresql 

Temporary Workarounds

• Increase Connection Limit Temporarily:

 sudo -u postgres psql -c "ALTER SYSTEM SET max_connections = 300;" 
 sudo systemctl reload postgresql 

• Use a Connection Pooler:

Deploy PgBouncer to manage connection pooling:

 sudo apt install pg-bouncer 

Configure pgbouncer.ini with max_pool and min_pool settings.

What NOT to Do

• Avoid Increasing max_connections Without Monitoring:

Overprovisioning can lead to resource contention and further latency.

• Do Not Disable Logging:

Logs are critical for diagnosing root causes.

• Avoid Using SELECT *:

Unfiltered queries increase data transfer and processing overhead.

Long-Term Prevention & Alerting

• Set Up Monitoring:

Use tools like Prometheus + Grafana to track pg_stat_activity and pg_stat_statements.

• Implement Automated Index Analysis:

Schedule regular ANALYZE commands for large tables.

• Enable Query Plan Logging:

 SET log_min_duration_statement = '1s'; 

Review slow query plans to identify optimization opportunities.

Verified Technical Author

Written by Techniq World

Technology specialist and technical writer at Techniq World, covering modern software, operating systems, and developer tools.