How to diagnose a PostgreSQL incident in under 5 minutes (real case)

Objective

In an environment where every second counts, a PostgreSQL slowdown can be costly: lost performance, CPU saturation, blocked queries, frustrated users, cascading incidents. Yet diagnosing the root cause of a PostgreSQL problem should never take hours.

This article presents a real PostgreSQL performance diagnosis case carried out using a lightweight snapshot system. The goal: identify the root cause of a major slowdown in under a minute, thanks to a clear, structured and immediately actionable report.

Capturing two snapshots

A PostgreSQL snapshot is an instant photo of the database's activity at a given moment. Each snapshot captures: query statistics, CPU / IO metrics, active sessions, locks, wait events and overall server load. It is a frozen image of PostgreSQL's state at time T.

Several snapshots of the database are captured at different times. When an incident occurs, the two snapshots around the problem are selected (here 108 and 109). By comparing them, a PWR report is automatically generated that reveals what changed and identifies the root cause of the slowdown.

Concrete case: a slowdown appears

To understand what happened, two snapshots are selected: Snapshot A, captured just before the problem appeared, and Snapshot B, captured as the load increases.

These two snapshots represent two distinct states of the system. By comparing them, you can immediately see which queries increased in cost, which users generated the load, whether CPU or IO was saturated, whether locks or wait events appeared, whether sessions were blocked, and which objects or indexes were abnormally hit.

The system then generates a PWR report from these two snapshots, making it possible to identify the root cause of the slowdown.

Interpreting the metrics

After comparing the two snapshots, several indicators show that the slowdown does not come from a connection issue, but from one or more very costly queries.

Sessions: no saturation. Active sessions 22/100, no mass session blocking -> the number of connections is not the cause of the problem.

CPU: overload confirmed. Observed CPU load: 32% of maximum capacity -> too high for PostgreSQL over a short period -> a clear sign of a CPU-hungry query.

DB Time > Elapsed Time. DB Time: 31.40 min, Elapsed Time: 11.42 min -> the database spends more time executing queries than waiting -> this confirms a CPU overload, not a session or lock issue.

Blocked sessions and intermediate conclusion

Blocked sessions: only 1 blocked session -> no significant contention -> the problem lies elsewhere.

Intermediate conclusion: the metrics all converge on the same conclusion — the slowdown comes from one or more very costly queries. To confirm this, we analyze the "Top Queries by average execution time (CPU)", "Top CPU Queries" and "Top Cache Memory Queries" sections of the report. In all three categories, the same query appears at the top.

Faulty query identified

The query SELECT * FROM pgbench_accounts WHERE aid <= $1 ORDER BY abalance ... dominates every metric: Top CPU, Top Mean Exec Time, Top Cache Misses, Top IO (rows), and Top in the deltas between snapshots. This is the query responsible for the slowdown.

Root cause

This query combines a filter on aid with a sort on abalance. Without a composite index, PostgreSQL has to scan a large part of the table, sort a large volume of rows, consume a huge amount of CPU, saturate the cache, and slow down every other query.

Result: problem resolved in under 5 minutes

After deploying a suitable composite index: CPU dropped from 32% to 8%, DB Time came back below Elapsed Time, sessions stable, no more cache saturation, query optimized, incident fully resolved.

Diagnosis + fix in under 5 minutes.

Conclusion

This real case shows that a PostgreSQL diagnosis doesn't need to be long, complex or uncertain. With two lightweight snapshots and a root-cause-oriented PWR report, it becomes possible to immediately identify the faulty query, understand what changed, and apply an effective fix in just a few minutes.

A simple, fast and repeatable approach — ideal for DBA, DevOps and SRE teams who need to act quickly and precisely.