Diagnosing PostgreSQL Bottlenecks: From p95 Latency to pg_stat_statementsNote · digest for October 30
Dramatic chiaroscuro still life of a database engineer's workstation breaking down PostgreSQL latency between lock waits and CPU execution time.
Note2 sections≈ 4 min

Diagnosing PostgreSQL Bottlenecks: From p95 Latency to pg_stat_statements

It's a scene many backend engineers know all too well: monitoring shows p95 and p99 database latencies climbing, the HTTP request queue starts backing up, yet CPU utilization on the database server sits at a relaxed 15–20%. Management's knee-jerk reaction is an immediate vertical scale-up ("let's move the DB from 16 cores to 64"). You eat the downtime, pay a much bigger cloud bill, and... nothing changes. Latency stays right where it was.

The reason is simple: most database bottlenecks aren't caused by a shortage of CPU cores. They come from architectural traffic jams—hidden transaction locks, bad sequential scan plans, and buffer cache misses.

A guide from the SQL Portal community ("Finding Database Bottlenecks") lays out a crisp PostgreSQL diagnostic workflow that isolates the root cause in minutes without taking production offline.

Splitting Latency: Compute vs. Wait Time

The first step in any proper investigation is separating total latency into two distinct buckets: pure CPU execution time (execution time) and time spent stuck waiting (wait time).

To see which bucket is hurting you, query pg_stat_activity. If a query hangs for seconds while CPU sits idle, it's almost certainly waiting on a resource. The wait_event_type column tells you what's blocking it:

  • Lock: the session is waiting for a row or table lock held by another transaction;
  • IO: the query hit a disk bottleneck (reading data pages or waiting on WAL flushes);
  • Client: the server is waiting on the application client (the classic idle in transaction state).

Here is a battle-tested SQL query that exposes the lock tree and pinpoints the blocking session right away:

SELECT 
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocked.wait_event_type,
    blocked.wait_event,
    left(blocked.query, 60) AS blocked_query,
    left(blocking.query, 60) AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking 
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE NOT blocked.pid = pg_backend_pid();

If you spot an idle in transaction session holding an exclusive lock, you can terminate just that session with SELECT pg_terminate_backend(blocking_pid); and bring the cluster back to normal immediately.

Finding the Culprits with pg_stat_statements

When there are no locks in sight but slow queries are dragging the DB down, turn to pg_stat_statements. It normalizes SQL queries (replacing literal IDs with placeholders) and tracks detailed execution stats across the cluster.

In our hands-on guide to hunting heavy queries with pg_stat_statements when CPU is idle, we dug into buffer metrics. Eight times out of ten, database degradation traces back to just two or three queries doing sequential scans (Seq Scan) over multi-million-row tables and thrashing shared_buffers.

This query surfaces your top offenders by total execution time alongside their cache hit ratio:

SELECT 
    queryid,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((shared_blks_hit::numeric / nullif(shared_blks_hit + shared_blks_read, 0) * 100)::numeric, 1) AS hit_percent,
    left(query, 80) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Whenever hit_percent drops below 95%, that query is forcing the kernel to hit physical disk.

In our piece on diagnosing high-cardinality workloads in pg_stat_statements, we also showed why parameterized queries matter so the extension's own buffer doesn't overflow.

Safe EXPLAIN in Production

Never run EXPLAIN ANALYZE on heavy write queries (UPDATE/DELETE) against a live database without wrapping them in a rolled-back transaction (BEGIN ... ROLLBACK). For a safe read-only check, use EXPLAIN (BUFFERS, COSTS) to inspect estimated page reads without mutating data.

Before throwing bigger hardware at the problem, spend 15 minutes X-raying locks and buffer stats—chances are your database will start running like clockwork.

Want a deep diveVotes help us choose what to break down in detail. This material has 0 votes.
Sources · 3