Top 10 PostgreSQL Production Outages & SRE Incident Troubleshooting Runbooks

Introduction

PostgreSQL is one of the most widely adopted open-source relational database systems, powering applications ranging from startups and SaaS platforms to large-scale enterprise infrastructure.

However, running PostgreSQL in production introduces challenges that go beyond writing SQL queries and designing database schemas.

A sudden database outage can cause application downtime, failed transactions, delayed background jobs, broken APIs, and even data loss. Sometimes, the database remains technically available but becomes so slow that applications experience timeouts and users cannot complete their operations.

For Site Reliability Engineers (SREs), Database Administrators (DBAs), and backend engineers, understanding how to identify, troubleshoot, and recover from PostgreSQL incidents is essential.

🚀 Keep Learning with DevsCampus

Technology evolves continuously, and staying updated with practical engineering knowledge is essential for every developer.

At DevsCampus, we publish technical articles, engineering tutorials, and learning resources covering PostgreSQL, Database Engineering, DevOps, Cloud Computing, System Design, Artificial Intelligence, and emerging technologies.

Whether you are a student preparing for a software engineering career or an experienced developer looking to strengthen your technical skills, DevsCampus helps you explore concepts, understand real-world engineering challenges, and keep up with the latest technology trends.

Explore these resources to continue learning:

• PostgreSQL Engineering Articles – Explore PostgreSQL architecture, implementation, and production best practices.
• SQL Learning Hub – Learn SQL, database design, indexing, query optimization, and production database engineering.

Visit DevsCampus.com to read more technical articles, explore practical tutorials, and stay updated with the latest developments in technology.

Now, let's explore the 10 most common PostgreSQL production outages, their root causes, and practical SRE troubleshooting runbooks that engineers can use to investigate and resolve real-world incidents.

Whether you manage PostgreSQL on Kubernetes, AWS RDS, Azure Database for PostgreSQL, or self-hosted Linux servers, these runbooks will help you respond to production incidents systematically.

1. PostgreSQL Connection Pool Exhaustion

Incident Scenario

Your application suddenly starts returning errors:

FATAL: remaining connection slots are reserved for non-replication superuser connections 

Your API response times increase, background workers stop processing jobs, and new application connections fail.

Root Cause

PostgreSQL has a configured maximum number of concurrent connections.

The following configuration controls the connection limit:

SHOW max_connections; 

Common causes include:

• Application connection leaks.
• Too many application instances.
• Incorrect connection pool configuration.
• Long-running transactions.
• Background workers opening excessive connections.
• Missing connection pooling.

SRE Troubleshooting Runbook

Step 1: Check active connections

SELECT state, COUNT(*) FROM pg_stat_activity GROUP BY state; 

Step 2: Identify applications consuming connections

SELECT application_name, client_addr, state, COUNT(*) AS connections FROM pg_stat_activity GROUP BY application_name, client_addr, state ORDER BY connections DESC; 

Step 3: Identify long-running connections

SELECT pid, usename, application_name, state, now() - query_start AS duration, query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY duration DESC; 

Step 4: Terminate confirmed problematic sessions

SELECT pg_terminate_backend(12345); 

Replace 12345 with the actual process ID after verifying that terminating the session is safe.

Recovery Strategy

• Configure connection pooling using PgBouncer.
• Set appropriate application pool limits.
• Implement connection timeouts.
• Close unused connections.
• Investigate connection leaks.
• Monitor active connections against max_connections.

Production Prevention: Establish connection budgets across all application instances rather than allowing every service to independently consume the entire database connection limit.

Related Reading: Explore PostgreSQL engineering resources on DevsCampus to learn more about PostgreSQL architecture, connection management, and production database operations.

2. PostgreSQL High CPU Utilization

Incident Scenario

The PostgreSQL server suddenly reaches 95–100% CPU utilization.

Applications become slow, API requests time out, and database queries take significantly longer than usual.

Root Cause

High CPU utilization can result from:

• Missing database indexes.
• Expensive JOIN operations.
• Inefficient SQL queries.
• Large sequential scans.
• Excessive concurrent queries.
• Poor query execution plans.
• Incorrect database statistics.

SRE Troubleshooting Runbook

Step 1: Identify expensive queries

SELECT pid, now() - query_start AS duration, state, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC; 

Step 2: Identify frequently executed expensive queries

If pg_stat_statements is enabled:

SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; 

Step 3: Analyze the execution plan

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 1001; 

Remember that EXPLAIN ANALYZE actually executes the query. Exercise caution with expensive queries and statements that modify data.

Step 4: Check server CPU usage

top 

Or:

htop 

Recovery Strategy

• Identify and optimize expensive queries.
• Add appropriate indexes.
• Update database statistics using ANALYZE.
• Reduce unnecessary query concurrency.
• Review execution plans.
• Consider read replicas for read-heavy workloads.

Production Prevention: Monitor query execution time, CPU utilization, and database workload continuously.

Related Reading: Learn how production database schemas influence query performance and scalability in Production Database Design on DevsCampus.

3. PostgreSQL Disk Space Exhaustion

Incident Scenario

Your application starts reporting database write failures:

ERROR: could not extend file No space left on device 

Insert and update operations begin failing. PostgreSQL may also experience problems writing WAL files or temporary query files.

Root Cause

Common causes include:

• Rapid database growth.
• Unbounded application logs.
• Large temporary files.
• WAL accumulation.
• Failed replication slots retaining WAL.
• Missing WAL archiving cleanup.
• Large database indexes.
• Long-running transactions preventing cleanup.

SRE Troubleshooting Runbook

Step 1: Check available disk space

df -h 

Step 2: Identify large directories

sudo du -sh /var/lib/postgresql/* 

Adjust the PostgreSQL data directory according to your installation.

Step 3: Check database sizes

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database ORDER BY pg_database_size(datname) DESC; 

Step 4: Check WAL directory size

sudo du -sh /var/lib/postgresql/*/pg_wal 

Step 5: Check replication slots

SELECT slot_name, active, restart_lsn, pg_wal_lsn_diff( pg_current_wal_lsn(), restart_lsn ) AS retained_wal_bytes FROM pg_replication_slots; 

Recovery Strategy

• Expand the underlying storage volume.
• Investigate inactive replication slots.
• Correct WAL archiving failures.
• Review temporary file generation.
• Remove only confirmed obsolete files through supported PostgreSQL maintenance procedures.
• Implement database growth alerts.

Critical Warning: Never manually delete files from PostgreSQL's pg_wal directory. Doing so can cause database corruption or prevent PostgreSQL from starting.

Production Prevention: Configure disk usage alerts at 70%, 80%, and 90% capacity.

4. PostgreSQL Replication Lag

Incident Scenario

Your production application uses PostgreSQL primary-replica architecture.

The primary database processes writes successfully, but read replicas return outdated data.

Users update their profiles but continue seeing old information.

Root Cause

Replication lag can occur because of:

• Network latency.
• High write throughput.
• Slow replica storage.
• Long-running queries on replicas.
• Replication conflicts.
• Insufficient replica resources.
• WAL receiver or replay delays.

SRE Troubleshooting Runbook

Step 1: Check replication status on the primary

SELECT application_name, client_addr, state, sync_state, write_lag, flush_lag, replay_lag FROM pg_stat_replication; 

Step 2: Check replication status on the replica

SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(); 

Step 3: Calculate replay delay

SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay; 

This timestamp-based calculation is most meaningful when the primary is actively committing transactions.

Recovery Strategy

• Investigate network connectivity.
• Increase replica compute and storage capacity where necessary.
• Optimize queries running on replicas.
• Investigate WAL receiver and replay activity.
• Review replication configuration.
• Temporarily route consistency-sensitive reads to the primary.

Production Prevention: Monitor replication lag and establish thresholds that trigger alerts before application consistency is affected.

5. PostgreSQL Deadlocks

Incident Scenario

Your application starts reporting transaction failures:

ERROR: deadlock detected DETAIL: Process 1234 waits for ShareLock on transaction 5678 

Multiple transactions are waiting for resources held by each other.

Root Cause

A deadlock occurs when two or more transactions wait indefinitely for locks held by one another.

Example:

Transaction A:

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; 

Transaction B:

BEGIN; UPDATE accounts SET balance = balance - 50 WHERE id = 2; UPDATE accounts SET balance = balance + 50 WHERE id = 1; COMMIT; 

Both transactions acquire locks in different orders, potentially creating a deadlock.

SRE Troubleshooting Runbook

Step 1: Inspect database locks

SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks ORDER BY pid; 

Step 2: Identify blocked queries

SELECT pid, pg_blocking_pids(pid) AS blocking_pids, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0; 

Step 3: Inspect PostgreSQL logs

Look for:

ERROR: deadlock detected 

Recovery Strategy

• Acquire database locks in a consistent order.
• Keep transactions short.
• Avoid unnecessary operations inside transactions.
• Implement application-level transaction retries.
• Review transaction isolation requirements.

Production Prevention: Enable appropriate deadlock logging and monitor deadlock frequency.

6. PostgreSQL Slow Queries and Query Timeouts

Incident Scenario

Your application starts experiencing intermittent API failures:

ERROR: canceling statement due to statement timeout 

Some database queries take several seconds or even minutes to complete.

Root Cause

Common reasons include:

• Missing indexes.
• Inefficient joins.
• Large table scans.
• Outdated database statistics.
• Lock contention.
• Excessive sorting.
• Poor pagination strategies.

SRE Troubleshooting Runbook

Step 1: Find long-running queries

SELECT pid, now() - query_start AS execution_time, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state = 'active' ORDER BY execution_time DESC; 

Step 2: Analyze query execution

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'PENDING'; 

Step 3: Check table statistics

SELECT relname, n_live_tup, n_dead_tup, last_analyze, last_autoanalyze FROM pg_stat_user_tables; 

Step 4: Review timeout configuration

SHOW statement_timeout; SHOW lock_timeout; SHOW idle_in_transaction_session_timeout; 

Recovery Strategy

• Optimize problematic SQL.
• Add appropriate indexes.
• Review JOIN conditions.
• Update statistics.
• Investigate lock waits.
• Apply sensible timeout limits.

Production Prevention: Use pg_stat_statements to identify queries with high total execution time and monitor query latency percentiles.

Related Reading: Learn more about production schema design, indexing considerations, and query scalability in Production Database Design – DevsCampus.

7. PostgreSQL Database Crash or Unexpected Restart

Incident Scenario

Your production application suddenly loses database connectivity.

Monitoring reports:

Connection refused 

The PostgreSQL service is no longer accepting connections.

Root Cause

Possible causes include:

• Operating system out-of-memory termination.
• Hardware failures.
• Storage problems.
• PostgreSQL process crashes.
• Invalid configuration changes.
• Container or Kubernetes restarts.
• Host-level resource exhaustion.

SRE Troubleshooting Runbook

Step 1: Check PostgreSQL service status

sudo systemctl status postgresql 

Step 2: Inspect recent PostgreSQL logs

sudo journalctl -u postgresql \ --since "30 minutes ago" 

Depending on the distribution, PostgreSQL logs may also be available under:

/var/log/postgresql/ 

Step 3: Check memory utilization

free -h 

Step 4: Check kernel OOM events

dmesg -T | grep -i -E 'oom|killed process' 

Step 5: Check storage health

df -h 

Recovery Strategy

• Identify the actual crash reason before restarting repeatedly.
• Verify storage availability.
• Check memory and CPU pressure.
• Review PostgreSQL recovery logs.
• Restore service after confirming the underlying issue is resolved.
• Validate application connectivity and transaction processing.

Production Prevention: Implement database health checks, infrastructure monitoring, automated backups, and tested recovery procedures.

8. PostgreSQL VACUUM Problems and Table Bloat

Incident Scenario

Your database size keeps increasing even though application data is not growing significantly.

Queries become slower, indexes grow larger, and disk utilization increases.

Root Cause

PostgreSQL uses MVCC (Multi-Version Concurrency Control) to manage concurrent transactions.

Updates and deletes create obsolete row versions that must eventually be cleaned up by VACUUM.

Table bloat can increase when:

• Autovacuum is disabled or incorrectly configured.
• Long-running transactions prevent cleanup.
• High update/delete workloads generate dead tuples.
• Autovacuum cannot keep up with table modifications.
• Replication slots or standby feedback retain old row versions.

SRE Troubleshooting Runbook

Step 1: Identify tables with dead tuples

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10; 

Step 2: Inspect autovacuum configuration

SHOW autovacuum; SHOW autovacuum_vacuum_scale_factor; SHOW autovacuum_analyze_scale_factor; 

Step 3: Identify long-running transactions

SELECT pid, usename, state, now() - xact_start AS transaction_duration, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY transaction_duration DESC; 

Recovery Strategy

• Investigate long-running transactions.
• Ensure autovacuum is enabled.
• Tune autovacuum for large and frequently updated tables.
• Run targeted VACUUM operations where appropriate.

VACUUM (ANALYZE) orders; 

For severe table bloat, evaluate specialized maintenance approaches such as pg_repack, accounting for locking, disk-space, and operational requirements.

Production Prevention: Monitor dead tuples, autovacuum activity, transaction age, and table growth.

Related Reading: PostgreSQL maintenance is essential for preventing table bloat and keeping query performance stable. Explore Database Maintenance on DevsCampus to understand VACUUM, ANALYZE, index maintenance, and database statistics in greater detail.

9. PostgreSQL WAL Growth and Checkpoint Problems

Incident Scenario

Your database experiences increased disk utilization and write latency.

PostgreSQL logs contain messages related to checkpoints:

checkpoint starting: time checkpoint complete 

The WAL directory continues growing, and storage consumption becomes a concern.

Root Cause

Write-Ahead Logging (WAL) is essential for PostgreSQL durability and crash recovery.

WAL growth can result from:

• High transaction volume.
• Inactive replication slots.
• WAL archiving failures.
• Replication lag.
• Long-running backups.
• Incorrect WAL retention configuration.

Frequent checkpoints can also increase I/O pressure.

SRE Troubleshooting Runbook

Step 1: Check WAL configuration

SHOW wal_level; SHOW max_wal_size; SHOW min_wal_size; SHOW checkpoint_timeout; SHOW checkpoint_completion_target; 

Step 2: Check replication slot retention

SELECT slot_name, active, restart_lsn, wal_status FROM pg_replication_slots; 

Step 3: Check archiver statistics

SELECT archived_count, failed_count, last_archived_wal, last_failed_wal, last_failed_time FROM pg_stat_archiver; 

Step 4: Review PostgreSQL logs

Search for:

checkpoints are occurring too frequently 

Recovery Strategy

• Fix WAL archiving failures.
• Investigate inactive replication slots.
• Tune checkpoint settings based on workload.
• Ensure storage capacity is sufficient.
• Review replication and backup configurations.

Production Prevention: Monitor WAL generation rate, retained WAL, checkpoint frequency, and archiving failures.

Important: Never delete WAL files manually to free disk space.

10. PostgreSQL Backup and Recovery Failure

Incident Scenario

A production database becomes corrupted or unavailable.

The SRE team attempts to restore the latest backup but discovers that the backup is incomplete, outdated, or unusable.

This is one of the most serious production incidents because it directly affects business continuity and data recovery.

Root Cause

Common reasons include:

• Backup jobs silently failing.
• Incorrect backup retention policies.
• Missing WAL archives.
• Unverified backup integrity.
• Incomplete restore procedures.
• Missing encryption keys.
• Backups stored in the same failure domain as the database.

SRE Troubleshooting Runbook

Step 1: Check backup availability

For pg_dump backups:

ls -lh /backup/postgres/ 

Step 2: Create a logical backup

pg_dump -Fc -U postgres \ -d production_db \ -f production_db.dump 

Step 3: Validate the dump archive

pg_restore -l production_db.dump 

This checks whether the archive can be listed, but it does not prove that a complete restore will succeed.

Step 4: Restore into a separate database

createdb -U postgres recovery_test 

pg_restore -U postgres \ -d recovery_test \ production_db.dump 

Step 5: Validate restored data

SELECT COUNT(*) FROM important_table; 

Compare the results against expected records and application-level consistency checks.

Recovery Strategy

• Maintain automated database backups.
• Use continuous WAL archiving for point-in-time recovery (PITR).
• Store backups in a separate failure domain.
• Define Recovery Point Objective (RPO) and Recovery Time Objective (RTO).
• Perform regular restore drills.
• Document disaster recovery procedures.

Production Prevention: A backup should never be considered reliable until a successful restore has been tested.

PostgreSQL SRE Incident Response: A Practical Framework

Knowing individual PostgreSQL failures is important, but production reliability requires a consistent incident response process.

SRE teams should follow a structured approach whenever a database outage occurs.

Step 1: Detect

Use monitoring and alerting tools to identify incidents before customers report them.

Monitor:

• Database availability.
• Active connections.
• CPU utilization.
• Memory utilization.
• Disk utilization.
• Query latency.
• Replication lag.
• WAL growth.
• Deadlocks.
• Database errors.

Step 2: Triage

Determine the impact and severity.

Ask:

• Is the database completely unavailable?
• Are writes failing or only reads?
• Are all applications affected?
• Is the problem isolated to one database or replica?
• Is data integrity at risk?

Avoid making configuration changes before understanding the incident.

Step 3: Mitigate

Prioritize restoring critical application functionality.

Examples:

• Terminate confirmed problematic sessions.
• Route consistency-sensitive reads to the primary.
• Scale infrastructure resources.
• Reduce non-critical database workloads.
• Restore available service capacity.

Always evaluate the impact of each mitigation action.

Step 4: Recover

After resolving the immediate issue:

• Verify database health.
• Validate application connectivity.
• Test read and write operations.
• Confirm replication health.
• Check for data consistency issues.
• Monitor the system for recurring failures.

Step 5: Conduct a Blameless Postmortem

Every major production incident should produce a postmortem.

A useful postmortem includes:

The goal is not to blame an engineer. The goal is to improve the system so the same failure is less likely to happen again.

Essential PostgreSQL Production Monitoring Metrics

A production-ready PostgreSQL environment should continuously monitor the following metrics.

Recommended PostgreSQL Monitoring Tools

1. Prometheus and Grafana

Use Prometheus with PostgreSQL Exporter to collect database metrics and Grafana to build operational dashboards.

2. pg_stat_statements

A PostgreSQL extension that provides statistics about SQL query execution.

3. PgBouncer

A lightweight connection pooler that helps manage database connection overhead.

4. pgBadger

A PostgreSQL log analyzer that helps identify slow queries and database workload patterns.

5. Patroni

A high-availability solution for managing PostgreSQL clusters with automated failover capabilities.

Learn More: Production Database Monitoring

Monitoring is one of the most important responsibilities of an SRE team. Identifying performance degradation before it becomes a production outage requires more than checking whether a database is online.

Explore Production Database Monitoring on DevsCampus to understand:

• Database latency and p99 query performance.
• Active and available database connections.
• Replication lag monitoring.
• Cache and buffer hit ratios.
• Lock waits and deadlock detection.
• Table and index bloat monitoring.
• Production database alerting strategies.

This learning resource complements the troubleshooting runbooks covered in this article.

PostgreSQL Production Readiness Checklist

Before deploying PostgreSQL into production, validate the following:

Availability

• [ ] Configure appropriate connection pooling.
• [ ] Implement database health checks.
• [ ] Configure replication where required.
• [ ] Define failover procedures.

Performance

• [ ] Enable pg_stat_statements.
• [ ] Monitor slow queries.
• [ ] Review index usage.
• [ ] Configure autovacuum appropriately.

Storage

• [ ] Configure disk utilization alerts.
• [ ] Monitor WAL growth.
• [ ] Monitor replication slots.
• [ ] Review backup retention policies.

Security

• [ ] Use strong authentication.
• [ ] Apply least-privilege database permissions.
• [ ] Enable encrypted connections.
• [ ] Store database credentials securely.
• [ ] Audit privileged database access.

Disaster Recovery

• [ ] Automate backups.
• [ ] Configure point-in-time recovery where required.
• [ ] Define RPO and RTO.
• [ ] Perform regular restore tests.
• [ ] Document disaster recovery runbooks.

Conclusion

PostgreSQL production outages are not always caused by database crashes. Many incidents originate from connection exhaustion, inefficient queries, storage pressure, replication lag, transaction conflicts, or inadequate backup strategies.

For SREs and database engineers, the ability to identify symptoms, investigate root causes, and execute safe recovery procedures is critical.

The ten incident scenarios covered in this guide provide a practical starting point for building reliable PostgreSQL operations.

However, production reliability is not achieved by troubleshooting incidents alone. It requires continuous monitoring, capacity planning, automation, tested disaster recovery procedures, and a culture of learning from failures.

Remember: A production-ready database is not simply one that runs without errors. It is one that can detect failures, recover safely, and maintain data integrity when things go wrong.

Learn More with DevsCampus

Want to strengthen your skills in PostgreSQL, Database Engineering, DevOps, Site Reliability Engineering, and Cloud Infrastructure?

DevsCampus is a learning platform designed for students, developers, and technology professionals who want to build practical, industry-relevant engineering skills.

Explore these resources to continue your learning journey:

Recommended Learning Resources

1. PostgreSQL Engineering Articles – Explore PostgreSQL architecture, production implementation, and best practices.

2. Production Database Design – Understand database schema design, scalability, and production-ready architecture.

3. Database Maintenance – Learn about VACUUM, ANALYZE, index maintenance, and database performance.

4. Source：https://dev.to/devscampus_a497dc3309edea/top-10-postgresql-production-outages-sre-incident-troubleshooting-runbooks-783

   My favorites