PostgreSQL Production Troubleshooting Guide - AI-Optimized Knowledge Base

Executive Summary

PostgreSQL fails in four primary patterns that account for 90% of production issues. Understanding these patterns prevents hours of random troubleshooting and reduces mean time to resolution from hours to minutes.

Critical Failure Patterns

1. Connection Refused Errors

Failure Impact: Complete service unavailability, user-facing outages
Resolution Time: 2-15 minutes if following systematic approach
Common Root Causes:

• Service not running (40% of cases)
• Network/firewall blocking (25% of cases)
• Configuration errors (35% of cases)

Diagnostic Sequence:

1. Service status: sudo systemctl status postgresql
2. Port connectivity: nc -zv hostname 5432
3. Configuration check: grep -n "listen_addresses\|port" /path/to/postgresql.conf

Critical Context: "Connection refused" provides no useful diagnostic information. Always work through systematic checklist rather than guessing.

2. Authentication Type 10 Not Supported (SCRAM-SHA-256)

Failure Impact: Applications unable to authenticate, complete service disruption
Frequency: Affects all PostgreSQL 13+ upgrades with JDBC drivers older than 42.2.0
Business Impact: Production-killing error with 268k+ Stack Overflow views

Root Cause: PostgreSQL 13+ defaults to SCRAM-SHA-256 authentication; older drivers don't support it
Solution Priority: Upgrade JDBC driver to 42.2.0+ (secure approach)
Avoid: Downgrading PostgreSQL security to MD5 (creates security vulnerabilities)

Driver Compatibility Matrix:

• Java: PostgreSQL JDBC 42.2.0+
• Python: psycopg2 2.8.0+
• Node.js: pg 7.8.0+
• .NET: Npgsql 4.0.0+

3. Performance Degradation

Failure Impact: User complaints, timeout errors, revenue loss
Primary Indicators:

• Queries taking minutes instead of seconds
• CPU usage constantly at 100%
• Memory usage climbing without recovery

Diagnostic Tool: EXPLAIN (ANALYZE, BUFFERS) - shows exact bottleneck location
Critical Red Flags:

• Seq Scan on tables >10k rows = missing index
• actual time >> cost = bad statistics
• Buffers: shared read=50000 = excessive disk I/O

Index Creation Best Practice: Use CREATE INDEX CONCURRENTLY in production to avoid table locking

4. Memory Exhaustion (OOM Killer)

Failure Impact: Database process termination, data corruption risk
Critical Threshold: PostgreSQL processes are primary OOM killer targets due to size

PostgreSQL 15+ Memory Trap: work_mem multiplies by parallel workers

• Example: work_mem=200MB × 4 workers × 5 operations = 4GB instant consumption
• hash_mem_multiplier defaults to 2.0, further multiplying memory usage

Prevention Strategy:

• shared_buffers = 25% of total RAM
• work_mem = (Available RAM - shared_buffers) / max_connections / 4
• Monitor with SELECT count(*) FROM pg_stat_activity

Configuration Intelligence

Connection Management

Critical Limit: Never set max_connections > 200 without connection pooling
Operational Reality: Each connection consumes ~2.5MB RAM minimum
Solution: PgBouncer with transaction pooling (only reliable connection pooler)

PgBouncer Configuration (Production-Tested):

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5

Authentication Security

pg_hba.conf Processing: Top-to-bottom, first match wins, case-sensitive
Production Failure Mode: Single character error breaks all authentication
Reload Command: sudo systemctl reload postgresql (avoid restart)

Memory Tuning Thresholds

Safe Starting Points:

• shared_buffers: 25% of RAM (conservative)
• work_mem: 32MB (dangerous above 64MB without careful analysis)
• maintenance_work_mem: 256MB (higher improves VACUUM performance)
• effective_cache_size: OS cache + shared_buffers estimate

Operational Intelligence

Diagnostic Commands (Production-Critical)

# Service verification
sudo systemctl status postgresql

# Connection testing
nc -zv hostname 5432

# Memory monitoring
free -h && cat /proc/meminfo | grep -E "(MemAvailable|SwapTotal)"

# OOM killer detection  
sudo dmesg | grep -i "killed process"

# Connection usage monitoring
SELECT count(*) as current, setting::int as max 
FROM pg_stat_activity, pg_settings 
WHERE name = 'max_connections';

Performance Monitoring (Essential Extensions)

pg_stat_statements: Mandatory for production - identifies actual slow queries
Installation: Add to shared_preload_libraries, requires restart

Critical Performance Queries:

-- Top time consumers
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements 
ORDER BY total_exec_time DESC LIMIT 10;

-- Tables needing indexes
SELECT schemaname, tablename, seq_scan, seq_tup_read
FROM pg_stat_user_tables 
WHERE seq_scan > 1000 AND seq_tup_read / seq_scan > 10000;

Resource Requirements & Time Investment

Troubleshooting Time Estimates

• Connection issues: 2-15 minutes (if following systematic approach)
• Authentication problems: 5-30 minutes (driver upgrade required)
• Performance issues: 30 minutes - 4 hours (depends on index creation time)
• Memory problems: 15 minutes - 2 hours (may require PostgreSQL restart)

Expertise Requirements

Beginner Level: Service management, basic configuration
Intermediate Level: Authentication troubleshooting, basic performance tuning
Expert Level: Memory tuning, complex performance optimization

Infrastructure Costs

Connection Pooling: PgBouncer - minimal resource overhead, high reliability
Monitoring: pg_stat_statements - 5-10% performance overhead, essential for diagnostics
Memory: Rule of thumb - 2.5MB per connection + shared_buffers

Critical Warnings & Breaking Points

Production Killers

1. Never run PostgreSQL as root - breaks file permissions, security nightmare
2. Test backups by restoring them - untested backups are useless
3. Monitor disk space at 80% - PostgreSQL fails hard at 100% disk usage
4. Connection limit alerts at 80% - approaching max_connections kills performance

Breaking Points

• UI becomes unusable: >1000 spans in distributed tracing
• Performance cliff: work_mem × parallel_workers × concurrent_queries
• Connection exhaustion: max_connections - superuser_reserved_connections
• Disk space: PostgreSQL cannot create files at 100% disk usage

Hidden Failure Modes

• Log file growth: Can fill disk faster than data files
• VACUUM blocking: Long-running transactions prevent cleanup
• WAL file accumulation: Broken archiving causes disk space consumption
• Index bloat: Unused indexes slow writes and waste space

Recovery Procedures

Nuclear Option (When Everything Fails)

sudo systemctl stop postgresql
sudo rm -f /var/lib/postgresql/*/main/postmaster.pid
sudo systemctl start postgresql

WARNING: Terminates all connections, potential data loss, use only when already down

Connection Recovery

-- Kill idle connections
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle' 
AND backend_start < now() - interval '1 hour';

Performance Recovery

• Identify slow queries with pg_stat_statements
• Create missing indexes with CONCURRENTLY option
• Update table statistics with ANALYZE
• VACUUM tables with high dead tuple ratios

Monitoring Thresholds (Production Alerts)

Critical Alerts (Immediate Response Required)

• Connection usage > 95% of max_connections
• Disk space > 95% on data directory
• Memory usage triggering OOM killer
• Query execution time > 30 seconds

Warning Alerts (Investigation Required)

• Connection usage > 80% of max_connections
• Disk space > 80% on data directory
• Average query time increasing trend
• Lock wait time > 5 seconds

Success Metrics

• Connection pool efficiency > 90%
• Cache hit ratio > 95%
• Average query time < 100ms
• Zero OOM killer activations

Tool Quality Assessment

Reliable Tools

• PgBouncer: Only consistently working connection pooler
• pg_stat_statements: Essential performance monitoring
• EXPLAIN ANALYZE: Accurate bottleneck identification

Avoid These Tools

• PgPool: Complex configuration, debugging nightmare
• Generic connection increase: Creates memory problems
• MD5 authentication: Security vulnerability, avoid in production

Community Support Quality

• Stack Overflow PostgreSQL tag: 4th answer usually contains real fix
• PostgreSQL mailing lists: Good for bugs, terrible for basic issues
• Official documentation: Comprehensive but useless during emergencies