A PostgreSQL latency incident cannot be classified from CPU alone: an active backend may be executing, waiting for storage, blocked by a lock, stalled behind shared-memory contention, or waiting on a client.

Technology and product capabilities in this series are evaluated as of June 30, 2026.

Situation

PostgreSQL on EC2 exposes the full performance boundary: engine sessions, waits, locks, checkpoints, WAL, and I/O; Linux scheduler, memory, filesystem, process, and device pressure; and CloudWatch instance and volume limits.

That control is useful only on one timeline. A five-minute CPU average, current activity snapshot, and counters accumulated since another reset cannot prove what happened together. Retain timestamps, periods, reset times, versions, resource IDs, and change events.

After collection, the LLM reconciles layers, ranks testable hypotheses, and identifies missing evidence. It cannot turn one snapshot into causality.

The Problem

Several common shortcuts misclassify PostgreSQL incidents:

  • high EC2 CPU means one expensive query;
  • low CPU means the database is healthy;
  • state = 'active' means a backend is consuming CPU;
  • wait_event_type = 'IO' proves EBS is saturated;
  • many connections mean connection pressure;
  • a high cache-hit ratio means storage is irrelevant;
  • cumulative pg_stat_io values describe the incident window.

PostgreSQL makes state and wait_event independent. An active backend with a wait is executing but blocked. A null wait means no reported wait at that instant—not continuous CPU use.

The engine cannot distinguish a kernel-cache read from a storage read. Default EC2 metrics also omit the complete in-guest memory, swap, filesystem, and device story.

The core question is: is latency caused by runnable PostgreSQL work, heavyweight locks, internal contention, I/O demand, checkpoint or WAL pressure, client behavior, host memory or scheduler pressure, an EBS limit, or a system outside the database?

Build a Time-Aligned PostgreSQL Triage Pack

flowchart TD
    A[application latency throughput and errors] --> H[time-aligned incident window]
    B[EC2 CPU network status and credits] --> H
    C[Linux scheduler memory swap filesystem and devices] --> H
    D[PostgreSQL sessions states and waits] --> H
    E[locks and blocking relationships] --> H
    F[PostgreSQL I/O checkpoints and WAL deltas] --> H
    G[EBS latency queue throughput and limit checks] --> H
    H --> I[deterministic classification and anomaly features]
    I --> J[LLM evidence correlation]
    J --> K[observations with artifact references]
    J --> L[ranked hypotheses and contradictions]
    J --> M[missing evidence and next bounded collector]
    K --> N[DBA verification]
    L --> N
    M --> N

Preserve baseline, transition, incident, and recovery. Retain distributions for gauges; retain start, end, elapsed time, and stats_reset for counters. Compare deltas, not unrelated totals.

Symptoms

Evidence patternLeading classificationWhat it does not prove
High CPU, run queue, active backends without reported waitsRunnable work or excessive concurrencyOne SQL statement is responsible
Many Lock waits with named blockersTransaction or DDL contentionStorage or CPU needs resizing
IO waits plus rising PostgreSQL read time and EBS latencyStorage path is involvedEBS provisioned performance is exceeded
Low available memory, swap and reclaim activityHost memory pressureshared_buffers alone is too large
ClientRead or ClientWrite dominatesClient, network, or application flow controlPostgreSQL is necessarily slow
Requested checkpoints and write pressure riseWAL or checkpoint path needs investigationmax_wal_size is automatically the fix
CPU is low and waits are absentSampling or another layer may be missingNo incident exists

First Five Checks

1. Freeze identity, time, configuration, and counter age

Record version, primary or standby role, boot and postmaster times, instance type, volumes, mounts, timeline, configuration hash, and recent changes. Confirm the application window first.

Capture stats_reset with cumulative views. PostgreSQL preserves statistics after clean shutdown but resets them after crashes, starting from a base backup, and point-in-time recovery. A reset can resemble workload collapse.

2. Classify Linux and EC2 pressure before blaming SQL

Use bounded samples rather than one command at one instant:

uptime
vmstat 1 5
iostat -xz 1 5
pidstat -dur -C postgres 1 5
free -m
df -h
df -i
ss -s

Interpret them together. Load average includes uninterruptible waits as well as runnable work. Low free memory can be normal; available memory, reclaim, major faults, swap activity, and OOM events are stronger evidence. Pair device utilization with latency, queue, request size, and throughput. Check bytes and inodes separately.

Built-in EC2 metrics add CPU, network, status, and applicable credit evidence. In-guest memory, swap, filesystem, and device detail require another collector. Preserve metric period and statistic.

3. Count backend state and waits without starting from query text

Take repeated pg_stat_activity samples grouped by backend, database, application, state, and wait:

SELECT clock_timestamp() AS observed_at,
       backend_type,
       datname,
       application_name,
       state,
       wait_event_type,
       wait_event,
       count(*) AS backends
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY backend_type, datname, application_name,
         state, wait_event_type, wait_event;

Label null waits no reported PostgreSQL wait, never “CPU.” Separate client backends from autovacuum, checkpointer, WAL, replication, parallel, and extension workers. Retain transaction and state-change age plus query_id. An idle in transaction session can hold locks or an old snapshot while using little CPU; ordinary idle connections are different from queued active work.

4. Prove blocking before terminating anything

For Lock waits, record pg_blocking_pids() and sanitized blocker metadata: transaction age, state, application, user pseudonym, wait, and query ID. Lock, LWLock, and BufferPin represent different mechanisms.

Do not infer the root blocker from age or let the LLM terminate it. The DBA must verify ownership, business impact, rollback cost, and permitted action.

5. Correlate PostgreSQL I/O deltas with Linux and EBS

For PostgreSQL 18, snapshot pg_stat_io, pg_stat_checkpointer, pg_stat_wal, and pg_stat_database at both window boundaries. pg_stat_io groups cluster-wide activity by backend type, object, and context; version 18 includes byte counters and WAL rows. Timing columns require track_io_timing or track_wal_io_timing and are misleading after partial-window enablement.

Align engine deltas with Linux device evidence and EBS latency, queue, volume exceeded checks, and instance-level EBS checks. DataFileRead proves a backend waited for a read; surrounding evidence distinguishes cache churn, competing I/O, and limits.

Decision Tree

flowchart TD
    A[application latency incident] --> B{host status filesystem or OOM failure}
    B -->|yes| C[infrastructure recovery path]
    B -->|no| D{Lock waits and blockers present}
    D -->|yes| E[verify blocking chain and transaction owner]
    D -->|no| F{IO waits elevated}
    F -->|yes| G[correlate PostgreSQL Linux and EBS deltas]
    F -->|no| H{Client waits dominate active work}
    H -->|yes| I[inspect application network and result consumption]
    H -->|no| J{LWLock or BufferPin elevated}
    J -->|yes| K[collect event-specific contention evidence]
    J -->|no| L{CPU run queue and active work elevated}
    L -->|yes| M[move to workload and SQL diagnosis]
    L -->|no| N[verify sampling gaps and upstream dependencies]

LLM Correlation Contract

Require four distinct outputs:

OBSERVATIONS
- Active backends increased during the incident window [activity-07].
- Lock waits remained at baseline [waits-03].
- EBS read latency and PostgreSQL read-time deltas rose together [ebs-04, pgio-09].

HYPOTHESES
1. Read-path pressure is contributing to latency.
2. CPU saturation is less consistent with the evidence.

MISSING EVIDENCE
- Per-device Linux latency for the same minute.
- Workload source of the additional reads.

ACTIONS
- Run the approved device snapshot collector.
- Continue to SQL attribution only after the storage boundary is classified.

References are mandatory. A named wait is an observation, not a complete cause.

In Practice

The PostgreSQL 18 monitoring documentation defines pg_stat_activity as current activity and warns that state and wait_event are independent. It also documents that cumulative statistics can lag, may remain cached within a transaction, and can be refreshed with pg_stat_clear_snapshot(). The documented pattern is to timestamp and version snapshots rather than ask an LLM to reconcile counters with unknown age.

The same documentation states that PostgreSQL I/O statistics cannot distinguish storage reads from data already in the kernel page cache and recommends combining them with operating-system utilities. It also notes that pg_stat_io timing columns require the corresponding timing configuration. This is why an IO wait, a pg_stat_io delta, and an EBS metric answer different parts of the same question.

AWS documents that EBS volumes publish one-minute metrics, including latency and queue evidence on supported configurations, and that exceeded-check metrics indicate attempts to drive beyond provisioned volume performance. AWS also documents separate instance-level EBS exceeded checks. A healthy volume limit does not prove the EC2 instance’s aggregate EBS limit is healthy, and neither proves PostgreSQL generated the competing I/O.

The CloudWatch agent’s Linux metrics include memory availability, swap, filesystem, device, and CPU-mode evidence beyond default EC2 monitoring. The operational conclusion is derived from named system behavior: self-managed PostgreSQL triage requires engine, guest, instance, and volume evidence on one clock.

Remediation Options

Proven boundaryImmediate containmentDurable directionValidate with
Heavyweight blockingResolve owner; cancel or terminate only with approvalShorter transactions, safer DDL, lock timeout policyBlocked sessions, transaction age, application errors
Excess runnable workShed or queue load; protect connection headroomPool sizing, concurrency control, SQL and plan remediationRun queue, active backends, throughput, latency
Host memory pressureStop unbounded work; preserve recovery headroomMemory model across connections, parallelism, maintenance, OSAvailable memory, swap, OOM, query concurrency
EBS volume or instance limitReduce competing I/O; defer maintenanceProvisioned IOPS and throughput matched to measured demandExceeded checks, latency, queue, PostgreSQL I/O rates
Checkpoint pressureAvoid speculative parameter changes during peakTune only after WAL and checkpoint-rate analysisRequested checkpoints, write and sync time, latency
Client or network backpressureBound results or isolate unhealthy clientsApplication consumption, timeout, network, payload designClient waits, network, result size, end-to-end latency

Rollback Plan

Triage should be read-only. If a collector adds pressure, stop its schedule, close its pool, and return sampling frequency and CloudWatch-agent configuration to the prior version. Preserve the incident window and record the resulting evidence gap.

For containment, define rollback before action: restore traffic limits gradually, revert pool settings, resume paused maintenance, or return storage configuration only when the previous capacity remains valid. A terminated transaction cannot be “unterminated”; PostgreSQL rolls it back, so recovery means verifying rollback completion and application retry semantics. Do not reset statistics during an incident—doing so destroys the baseline needed to validate the change.

Where It Breaks

Failure modeMisleading conclusionCorrection
One pg_stat_activity snapshotA transient sample represents the incidentSample repeatedly and preserve distributions
Null wait is labeled CPUEvery active backend consumes CPUCorrelate with process and scheduler evidence
Cumulative totals are compared directlyLargest lifetime counter caused the spikeCalculate deltas with elapsed time and reset age
IO wait is equated with EBS saturationStorage must be resizedCheck Linux, volume, instance, cache, and competing I/O
High cache-hit ratio closes storage analysisPhysical reads are negligibleInspect rates, working-set transition, OS cache, temp and WAL I/O
Connection total becomes the diagnosisMore sessions caused the incidentSeparate idle, transactional, active, waiting, and backend types
LLM sees raw query text firstSensitive detail overwhelms classificationBegin with aggregates and stable query IDs
Remediation precedes proofA parameter change masks the incidentRequire observation, hypothesis, missing evidence, validation, rollback

What the LLM Cannot Do

Explicit operational boundaries govern the LLM:

  • No Raw Customer Data: The LLM must not receive bind values, EXPLAIN ANALYZE output containing literal filter constants, pg_stat_activity.query text with embedded parameters, or table contents. Send normalized pg_stat_statements queryid and digest text, plan shapes, and timings.
  • No Autonomous Production Execution: The LLM cannot run VACUUM FULL, REINDEX, ALTER SYSTEM, pg_terminate_backend, index DDL, or parameter changes. It proposes; a human authorizes, applies, validates, and holds the rollback condition.
  • No Causal Conclusion Without Contradicting Evidence: Every hypothesis must cite both the telemetry that supports it and the telemetry that would falsify it. A ranked hypothesis with no disconfirming test attached is an assertion, not a diagnosis.
  • No Silent Gap-Filling: Where the evidence pack lacks a required signal, the LLM must name it explicitly under missing evidence rather than inferring a plausible value. “Not collected” and “collected and normal” are different findings and must never be merged.
  • No Change Without a Human Gate: Production changes require human authorization, a stated validation signal, and a defined rollback condition agreed before the change is applied.

What to Do Next

  • Problem: PostgreSQL, Linux, EC2, and EBS each expose only part of a performance incident, while snapshots and cumulative counters operate on different time semantics.
  • Solution: Build a time-aligned pack of backend states, waits, blockers, engine I/O deltas, host pressure, and cloud limits; let the LLM correlate those artifacts without treating any single signal as root cause.
  • Proof: Replay a known lock, CPU, and storage test. The classifier should route each to a different next diagnostic, retain contradictory evidence, and avoid proposing a parameter change before the boundary is proven.
  • Action: Implement the five bounded collectors and reset-aware delta logic, then test the decision tree under load before connecting the output to an LLM.

The next article moves from a classified workload incident to SQL attribution: using pg_stat_statements, execution plans, buffer evidence, and actual-versus-estimated rows without turning EXPLAIN ANALYZE into a production hazard.

Sources