Query Latency p99 (ms), PostgreSQL

​

At a glance

The 99th-percentile query execution time, in milliseconds, across all statements running on the monitored PostgreSQL instance. p99 is the tail: 99 out of every 100 queries finished faster than this number, and 1 in 100 finished slower. For a DBA or SRE this is the “worst realistic query a real user hits” line. p50 tells you the typical experience; p99 tells you what your slowest customers, your heaviest reports, and your most contended checkout writes actually feel. When p99 climbs while p50 stays flat, you have a tail problem (lock contention, a cold cache, a missing index on one hot path), not a system-wide slowdown.

​

Calculation

For the rolling 5-minute window:
  per_statement_mean_ms = (delta total_exec_time) / (delta calls)
  Build a distribution where each statement contributes `delta calls` samples
    at value `per_statement_mean_ms`.
  p99 = the value at the 99th percentile of that call-weighted distribution.

1. Mean-of-means is an approximation of the true tail. pg_stat_statements stores a mean per statement, not a full histogram, so the p99 is the 99th percentile of statement means, not the 99th percentile of every individual execution. On a workload dominated by a few statement shapes this is very close to the true p99; on a workload with one statement whose individual runs vary wildly, the true tail can be worse than reported. Where the managed-service provider exposes a real latency histogram (Aurora Performance Insights, Cloud SQL Query Insights), Vortex IQ prefers that source and notes it on the card.
2. Resets reset the baseline. If someone runs pg_stat_statements_reset() mid-window the delta goes briefly negative; the engine detects the counter rollback and skips that one sample rather than reporting a nonsense spike.

​

Worked example

Root-cause framing:
  - p50 flat            -> the database as a whole is fine (CPU, memory, cache healthy)
  - p99 spiked          -> a narrow set of statements is slow
  - one UPDATE to blame -> lock contention, not a missing index

Investigation: the team had deployed a batch job at 14:13 that updated
invoices for every account in a long transaction, holding row locks.
Concurrent single-row UPDATEs from the web tier queued behind those locks.
That lock wait shows up as execution time, not pool wait, which is why p99
moved and Connection Pool Saturation did not.

1. Read p99 against p50, never alone. p99 of 640ms is alarming on its own, but the diagnostic value is the gap to p50. A wide p50-to-p99 gap means tail contention; a narrow gap that is simply high everywhere means the instance is undersized or starved.
2. Lock waits count as execution time. A query blocked on a row lock is still “executing” from pg_stat_statements’ point of view. That is why a contention event surfaces here and not on the pool-saturation card. Pair with Idle-in-Transaction Backends when p99 spikes, because a forgotten open transaction is the classic cause.
3. A 500ms p99 is a budget, not a verdict. For an interactive OLTP front end, 500ms at the tail is roughly where users start to feel sluggishness. For an analytics or reporting database, a 500ms p99 may be entirely fine. Tune the sensitivity threshold to your workload in the Sensitivity tab rather than treating the default as gospel.

​

Sibling cards to read alongside

​

Reconciling against the source

pg_stat_statements is the authoritative source. Order by mean to see where the tail lives:

SELECT queryid, calls, mean_exec_time, max_exec_time,
       total_exec_time, query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

pg_stat_activity shows what is running right now and what each backend is waiting on (wait_event_type, wait_event), which tells you whether the tail is lock waits or I/O. EXPLAIN (ANALYZE, BUFFERS) on the suspect statement gives the real plan and the actual time.

Amazon RDS / Aurora: Performance Insights exposes per-statement latency and wait-event breakdowns directly, and is the closest native equivalent to this card’s percentile. Google Cloud SQL: Query Insights provides per-query latency and plan sampling. Azure Database for PostgreSQL: Query Store and the Intelligent Performance blade hold the equivalent statement-level timings.

​

Known limitations / FAQs

​

Tracked live in Vortex IQ Nerve Centre