Software EngineeringOct 30, 2026·10 min

PostgreSQL Under Pressure: A Database Reliability Playbook for Production Incidents

TV
TechVRS Research & Engineering Lab

Production systems architecture, technical SEO, and cloud-native security research.

#PostgreSQL#Database#Reliability#Incident Response
Executive Summary & Key Findings

A repeatable playbook for PostgreSQL incidents: classify the bottleneck, stabilise without destructive moves, and fix the root cause.

A PostgreSQL incident rarely announces itself cleanly. One minute dashboards look normal, the next minute application latency climbs, connection pools saturate, and a queue of queries that normally finish in milliseconds starts stacking up behind a lock nobody can name. The instinct in that moment is to restart something, add connections, or scale the instance. Sometimes one of those moves helps. Often it makes the problem worse, because the database was not out of capacity at all — it was waiting, and waiting does not get fixed by adding more waiters.

Production database reliability is less about heroic tuning than about having a repeatable way to tell which kind of trouble you are in: too much work, blocked work, an exhausted resource such as connections, or a plan change that made a cheap query expensive. Each has a different first move, and confusing them wastes the most valuable minutes of an incident.

This playbook covers the sequence that works in practice: confirm the symptom from the application's point of view, identify the bottleneck class in under five minutes, apply the least destructive mitigation first, and only then dig into root cause. It assumes ordinary observability — pg_stat_statements, pg_stat_activity, system metrics, and slow query logging.

#Quick Answer: How Do You Respond to a PostgreSQL Performance Incident?

Start by checking whether the application is actually failing or merely slow, then look at pg_stat_activity to classify the wait: many sessions in active state point to CPU or I/O saturation, many in idle in transaction point to a connection or application leak, and sessions stuck on Lock or LWLock point to blocking. Kill nothing until you know which session is the blocker, because terminating victims while the blocker survives only restarts the queue. The safest first mitigations are usually read-only: stop new deploys, shed non-essential background jobs, and route read traffic away if a replica is available. Restarting PostgreSQL or raising max_connections mid-incident is a last resort, not a first step.

#Recognise the Four Bottleneck Classes

Most PostgreSQL incidents fall into four classes, and the diagnostic path diverges immediately once you know which one you face. Treating them as one generic "slow database" problem is how teams spend an hour tuning shared_buffers while a single idle-in-transaction session holds a lock on the busiest table.

The first class is saturation: CPU, disk I/O, or memory pressure from genuine workload. You will see high active session counts, rising I/O wait, checkpoint pressure, or buffer cache churn. The second class is blocking: sessions waiting on locks, advisory locks, or lightweight locks, often with one long-running transaction at the head of the chain. The third class is connection exhaustion: the pool is full, new connections queue or fail, and most sessions are idle or idle in transaction rather than doing work. The fourth class is plan regression: a query that was fast yesterday is slow today because statistics changed, a table grew past a threshold, or an ANALYZE ran after a bulk load and the planner chose a different path.

A fast triage query gives you the shape of the problem in seconds:

sql
SELECT state, wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY state, wait_event_type, wait_event
ORDER BY count(*) DESC;

If the top rows are active with IO or CPU wait events, you are saturated. If they are Lock waits, find the blocker before anything else. If most rows are idle or idle in transaction, the database is not the bottleneck — the application or pool is holding connections without using them.

Finding the Blocker, Not Just the Victims

When lock waits dominate, the blocker query is the one every other session is queued behind. PostgreSQL exposes this through pg_locks joined to pg_stat_activity:

sql
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query,
       blocking.state AS blocking_state
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.granted
 AND blocked_locks.granted = false
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE blocked.pid <> blocking.pid;

The usual culprits are uncommitted transactions left open by an application error path, long-running reporting queries, and DDL such as CREATE INDEX without CONCURRENTLY. Cancel a runaway report with pg_cancel_backend, and terminate a stuck session with pg_terminate_backend only after confirming it is not a critical writer. A large aborted transaction can take longer to roll back than it took to run, and during rollback it still holds its locks.

#Stabilise Before You Optimise

The pressure during an incident pushes teams toward permanent-looking fixes applied under stress: raising max_connections, increasing work_mem globally, or restarting the instance. Each has a downside that is easy to overlook. Raising max_connections without raising pool limits just moves the queue and increases memory and context-switch overhead. Raising work_mem globally can turn a memory problem into an out-of-memory kill, because work_mem is per operation, not per query, and a single query can allocate it many times over. Restarting clears caches, forces a checkpoint recovery, and loses the diagnostic state you need to understand what happened.

Safer stabilisation steps, in order, are: freeze changes, shed load, and isolate. Freeze deploys and schema changes so the incident does not get a second cause. Shed load by pausing batch jobs and non-critical writes, and enabling any degraded mode in the application. Isolate by moving analytical queries to a replica if one exists, and confirm backups and replication are still healthy before you touch anything else.

If connection exhaustion is the class, check the pooler first. PgBouncer in transaction pooling mode multiplexes many application connections onto fewer server connections, but too few server connections, or direct connections bypassing the pooler, will still exhaust the server. Compare application, pooler client, and server connection counts; the gap tells you where the leak lives. Watch retries too: aggressive retry on timeout can multiply load several times over during exactly the period the database can least afford it.

What to Check in the First Five Minutes

A short, written checklist beats memory under stress. Confirm error rates and latency from the application side, so you know whether users are affected or only an internal job is slow. Check disk space and throughput, replication lag, and whether a recent deploy, schema change, bulk load, or ANALYZE coincides with the onset. Check autovacuum too: an unvacuumed table accumulates dead tuples, bloats indexes, and can eventually trigger anti-wraparound autovacuum, which is far more disruptive than routine vacuuming.

sql
SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

#Diagnose Slow Queries Properly

Once the system is stable, pg_stat_statements finds which queries actually cost time. Enable it in shared_preload_libraries, and rank by total_exec_time rather than mean time alone, because a moderately slow query executed a million times costs more than one spectacularly slow query executed twice:

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

For a specific slow query, EXPLAIN (ANALYZE, BUFFERS) shows the plan actually used, including estimated versus returned rows and buffers hit versus read. A large gap usually means stale statistics, and a targeted ANALYZE is a cheap first fix. A sequential scan is not automatically a problem — on a small table it is the right plan — but one on a large table inside a nested loop, repeated thousands of times, is a classic regression. Look for hash joins spilling to disk and sorts exceeding work_mem, each pointing to a different remedy.

Adding an index during an incident should use CREATE INDEX CONCURRENTLY, which avoids locking writes, but it still consumes I/O and can take a long time on a large table. Before adding anything, check whether an existing index is simply not being used because of a type mismatch or a function applied to the column — for example, wrapping an indexed column in LOWER() without a matching expression index. These boring causes are good news during an incident because they have cheap fixes.

Configuration Changes That Actually Help

Most tuning advice online is aimed at a fresh installation, not a running incident. The settings worth understanding beforehand are shared_buffers, effective_cache_size, work_mem, checkpoint_timeout and max_wal_size, and autovacuum thresholds. Small shared_buffers cause excess disk reads; a low effective_cache_size makes the planner avoid index scans it should use. On write-heavy tables, lowering autovacuum_vacuum_scale_factor or setting per-table thresholds prevents bloat from accumulating.

Change one setting at a time, record the before and after, and reload rather than restart where the parameter allows it — many are reloadable with pg_reload_conf. Keep a runbook note of what changed during each incident, because untangling a stack of mid-incident changes afterwards is a second incident of its own.

#Prevent the Next Incident

Reliability work after the incident matters more than the response itself, because the same bottleneck class will recur unless something structural changes. The review should answer three questions: what was the bottleneck class, what signal would have caught it earlier, and what guardrail prevents recurrence. A lock incident suggests statement, lock, and idle_in_transaction_session_timeout values. A saturation incident suggests capacity headroom and a replica for analytics. A connection incident suggests pooler enforcement and per-service limits. A plan regression suggests testing queries against production-like data volumes before deploy.

Alerting should be built around the same classes. Alert on active session count and wait event distribution, not just CPU, because CPU can look fine while sessions pile up on locks. Alert on replication lag, disk space trends, autovacuum age on the largest tables, and long-running or idle-in-transaction sessions, with thresholds in minutes, not hours. And test the runbook: a playbook nobody has rehearsed is a document, not a capability. A game day simulating a blocking session, even on staging, teaches the team where the queries live and which commands need privileges they may not have in production.

Finally, keep backups and recovery tested independently of performance work. Verify a restore end to end on a schedule, not just that a backup job reported success. A team that can restore quickly makes better decisions during an incident, because the catastrophic option is covered.

#Frequently Asked Questions

Should I restart PostgreSQL when queries get slow?

Usually no. A restart clears shared buffers and the operating system cache, forces recovery, and destroys the pg_stat_activity state you need for diagnosis. Restart only when the instance is unresponsive, when a parameter change genuinely requires it, or when you have already identified a condition that only a restart clears. Stabilise first, diagnose, and treat restart as a deliberate decision with a rollback plan.

How do I find which query is blocking everything else?

Query pg_locks joined to pg_stat_activity to find granted locks held by one session that ungranted sessions are waiting on. The blocking session is often idle in transaction, meaning the application opened a transaction, did some work, and never committed or rolled back. Cancel or terminate that session after confirming what it was doing, and fix the application code path that left the transaction open, or the same block will recur.

What is a safe value for statement_timeout?

There is no universal value, because reporting queries legitimately run longer than transactional queries. A common pattern is a short timeout, such as 30 seconds, for the application connection pool, and a separate longer timeout for explicitly approved reporting or migration connections. Set lock_timeout shorter than statement_timeout so a query waiting on a lock fails fast instead of queueing indefinitely. Apply timeouts per role or per connection string, not as a single global value.

Why did a query get slower without any code change?

Data volume, statistics, and plan choice change even when code does not. A table crossing a size threshold can flip a plan from index scan to sequential scan, a bulk load followed by ANALYZE can change row estimates, and bloat from insufficient vacuuming can make every access path slower. Compare EXPLAIN output from before and after, check pg_stat_user_tables for dead tuples and last vacuum, and check whether a deploy, migration, or bulk job coincided with the regression.

Is PgBouncer necessary, or can I just raise max_connections?

Raising max_connections works up to a point, but each backend consumes memory and adds scheduling overhead, and beyond a few hundred active connections PostgreSQL throughput typically degrades rather than improves. A pooler in transaction mode lets thousands of application connections share a smaller number of server connections, which is the standard answer for connection-heavy workloads. If you raise max_connections, raise it deliberately with memory and pool limits in mind, not as an incident reflex.

#Sources and Further Reading

  • PostgreSQL Documentation, "Monitoring Database Activity" and "The Statistics Collector" — https://www.postgresql.org/docs/current/monitoring.html
  • PostgreSQL Documentation, "pg_stat_statements" — https://www.postgresql.org/docs/current/pgstatstatements.html
  • PostgreSQL Documentation, "Routine Vacuuming" — https://www.postgresql.org/docs/current/routine-vacuuming.html
  • PostgreSQL Wiki, "Lock Monitoring" — https://wiki.postgresql.org/wiki/Lock_Monitoring
  • "PostgreSQL 16 Administration Cookbook" by Simon Riggs and Gianni Ciolli (Packt) — practical guidance on monitoring, locking, and performance tuning
  • PgBouncer documentation, "Configuration" — https://www.pgbouncer.github.io/config.html
Engineering Advisory

Implementing this architecture in your production stack?

TechVRS engineers design, audit, and scale high-performance web applications, technical SEO pipelines, and autonomous AI systems for global businesses.