The PostgreSQL administration field guideField notes · Runbooks · Free certification
pgdba.dev
Menu

Operational runbook · Quick reference

Queries suddenly became slow

Separate broad resource pressure, blocking, and a query-plan regression before changing configuration or indexes.

slow querieslatency spiketimeoutshigh CPU

Before you begin

Prerequisites

  • Confirm the target cluster, database, PostgreSQL major version, and your read-only access to the required statistics views.
  • Record the current user impact, incident owner, UTC timestamp, and a baseline before running diagnostics.

Control the blast radius

Safety boundary

Start with read-only observations. Do not restart, terminate, delete, fail over, or change configuration until ownership, blast radius, and an approved recovery path are explicit.

Triage

Establish impact

  1. Confirm which applications, endpoints, databases, and query fingerprints are affected.
  2. Record whether latency is broad or isolated and whether error or timeout rates are rising.
  3. Check CPU, storage latency, memory pressure, and connection demand at the same timestamp.

Observe

Gather evidence

Diagnostic query

Find the longest currently active queries

Identify active work and its current wait so you can separate execution from resource waiting.

SELECT
  pid,
  usename AS user_name,
  application_name,
  now() - query_start AS running_for,
  wait_event_type,
  wait_event,
  left(query, 180) AS query_preview
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY running_for DESC;
low observation cost

Interpret

Interpret the evidence

  • If many unrelated statements slow together, prioritise shared resources and blocking over single-query tuning.
  • If one fingerprint regresses, compare its current plan, row estimates, data distribution, and recent deploy history.

Act

Take the safest useful action

  1. Protect users with an application-level timeout or traffic reduction only after identifying the affected workload.
  2. Cancel a runaway statement before terminating its session when cancellation can release enough pressure safely.
  3. Preserve the plan, query identifier, statistics window, and resource evidence for follow-up analysis.

Prove the outcome

Verification

  1. Repeat the baseline observation over a known interval and confirm that the measured queue or risk is moving in the intended direction.
  2. Verify the user-facing objective and every affected replica or dependency before closing or handing over the incident.

Keep recovery close

Rollback

Record the original state and rollback owner before acting. If verification worsens or the objective is missed, reverse only the bounded change, confirm the baseline is restored, and escalate with the before-and-after evidence.

Escalate

Escalate when

  • Escalate when latency continues to grow, transactions are timing out, or resource queues do not converge after load is reduced.

Prevent recurrence

Study the underlying system

observability query workload indexes locks

Certification competency

Refresh the assessed operating model

Index design and access paths Query plans and performance diagnosis Monitoring, capacity, and operational health Incident diagnosis and response

Reference assurance

Version and review

Compatible versions
PostgreSQL 16–18
Content version
2026.08
Reviewed
2026-08-04

Catalog columns, wait events, and operational controls can vary by PostgreSQL major version, extensions, and orchestration layer. Verify commands against the deployed version.

Verify independently

Sources