Finding Slow Queries on Read Replicas with pg_stat_statements

Q: Do I need to enable pg_stat_statements on the primary for it to work on a replica?

No. pg_stat_statements is a per-node extension whose counters live in that node's shared memory. A replica collects statistics from the queries it actually executes, independent of the primary. You must add it to shared_preload_libraries and restart on each replica you want to observe; enabling it on the primary alone gives you nothing on the replicas.

Q: How do I approximate p99 latency when pg_stat_statements only stores mean and stddev?

pg_stat_statements stores mean_exec_time and stddev_exec_time but not true percentiles. A common approximation is mean + 2*stddev for a rough p95 and mean + 3*stddev for p99, which is only meaningful when the distribution is roughly normal. For accurate tail latency, pair it with auto_explain logging of slow plans or export histograms from your driver, since a high stddev relative to mean signals the approximation is unreliable.

Problem statement: Read latency on one of your replicas has crept up, but the primary looks fine and you have no per-query visibility on the replica — you need to know exactly which normalized statements are slow on that node, whether the slowness is cold cache or an expensive plan, and how to watch it over time rather than at a single moment.

Symptom Identification

These are the signals that send you to pg_stat_statements on a replica specifically, rather than on the primary:

A read-serving replica shows rising application-side p99 while the primary’s own pg_stat_statements and dashboards are flat — the regression exists only on the read path.
One replica in a fleet is slower than its peers for the same traffic mix, suggesting a node-local cause (cache, plan, or resource) rather than a shared query regression.
After a replica restart or failover, latency is elevated and you suspect cold cache but have no per-query buffer-hit numbers to confirm it.
A reporting job and an OLTP endpoint share a replica, and you need to attribute total execution time to each so you can decide whether to split them onto separate nodes.

pg_stat_statements answers all four, but only if it is running on the replica itself. This guide is the measurement half of the broader query performance analysis on read replicas topic.

Root Cause Analysis

The reason you cannot just look at the primary is architectural: pg_stat_statements keeps its counters in a fixed-size hash table in each node’s shared memory, keyed by a normalized query fingerprint (queryid). Those counters are incremented only when a statement executes on that node. Writes replay onto a replica through the WAL apply process, not through the executor’s statement path, so they never touch the replica’s pg_stat_statements table. The replica’s statistics are therefore a pure record of the read traffic it served.

This has two consequences that shape everything below. First, to see a replica’s slow reads you must connect to the replica and query its own extension — there is no aggregation from the primary. Second, each node resets, ages out, and fills its hash table independently, so comparing two replicas means comparing two separate populations that may have been collecting for different durations. Always read calls and, where you track it, the time since the last reset, before drawing conclusions from mean_exec_time.

The slowness itself, once located, almost always resolves to one of the mechanisms covered in the parent guide: a cold buffer cache (visible as a low shared_blks_hit ratio), a plan that diverged from the primary because of per-node planner settings, or contention from WAL replay. pg_stat_statements distinguishes the first from the others directly, and points you at EXPLAIN (ANALYZE, BUFFERS) for the rest.

Step-by-Step Resolution

Step 1: Load the extension on the replica

pg_stat_statements must be in shared_preload_libraries, which requires a restart. Set it on the replica’s own postgresql.conf:

ini

# postgresql.conf on the replica
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000     # normalized queries retained before eviction
pg_stat_statements.track = top     # count top-level statements, not nested
pg_stat_statements.track_utility = off
pg_stat_statements.save = on       # persist across restarts

Restart the replica, then create the extension in the database you serve reads from. On a hot standby you cannot run CREATE EXTENSION (it writes the catalog) — instead create it on the primary and let it replicate, or ensure it already exists before the replica was cloned:

sql

-- Run on the PRIMARY; the catalog entry replicates to the standby
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Inline verification: on the replica, SELECT count(*) FROM pg_stat_statements; returns a growing number as reads flow in. If it errors with relation "pg_stat_statements" does not exist, the catalog entry has not replicated yet.

Step 2: Confirm the statistics are per-node

Prove to yourself that what you are looking at is replica-local traffic, not the primary’s:

sql

-- On the replica
SELECT pg_is_in_recovery() AS is_replica;   -- must be t

-- The queries here should be reads only; no INSERT/UPDATE/DELETE fingerprints
SELECT left(query, 60) AS q, calls
FROM pg_stat_statements
WHERE query ~* '^(insert|update|delete)'
LIMIT 5;

Inline verification: the first query returns t; the second returns zero rows, because writes never execute through the replica’s statement path. Seeing write fingerprints would mean you are accidentally connected to the primary.

Step 3: Run the top-N slow-query query

Rank normalized statements by cumulative time, and surface the tail spread in the same result:

sql

-- On the replica: the 20 most time-consuming normalized reads
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2)   AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  round(min_exec_time::numeric, 2)    AS min_ms,
  round(max_exec_time::numeric, 2)    AS max_ms,
  round(total_exec_time::numeric, 0)  AS total_ms,
  left(query, 90)                     AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Rank by total_exec_time, not mean_exec_time: a query averaging 4 ms but called ten million times costs the replica more than one averaging 900 ms called twice. The former is where tuning pays off.

Inline verification: the top rows should be your known read-heavy endpoints. A surprise entry near the top — an unexpected sequential scan, a report you thought ran elsewhere — is itself a finding.

Step 4: Read mean versus p99 through stddev

pg_stat_statements does not store true percentiles, only mean_exec_time, stddev_exec_time, min_exec_time, and max_exec_time. Use the spread to classify each query’s stability:

sql

-- Flag queries whose latency is unstable (tail far from mean)
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2)                          AS mean_ms,
  round(stddev_exec_time::numeric, 2)                        AS stddev_ms,
  round((mean_exec_time + 3 * stddev_exec_time)::numeric, 2) AS approx_p99_ms,
  round((stddev_exec_time / nullif(mean_exec_time, 0))::numeric, 2) AS cov,
  left(query, 70) AS query_sample
FROM pg_stat_statements
WHERE calls > 100
ORDER BY stddev_exec_time DESC
LIMIT 20;

The coefficient of variation (cov = stddev / mean) is the key column. A cov near or above 1 means the query’s latency swings wildly — often a sign of intermittent WAL replay contention or occasional cancellations rather than a uniformly slow plan. In that case the mean + 3*stddev p99 estimate is unreliable and you should reach for auto_explain or a driver histogram. A low cov with a high mean is a genuinely, consistently expensive query worth optimizing.

Inline verification: cross-check one flagged query’s max_ms against your application’s observed worst-case latency for that endpoint; they should be in the same order of magnitude.

Step 5: Correlate with the buffer cache

The single most useful discriminator on a replica is the shared-buffer hit ratio per query, because a cold cache is the most common replica-specific slowness:

sql

-- Per-query cache-hit ratio on the replica
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  shared_blks_hit,
  shared_blks_read,
  round(100.0 * shared_blks_hit
        / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct
FROM pg_stat_statements
WHERE calls > 50
ORDER BY shared_blks_read DESC
LIMIT 20;

A query with cache_hit_pct well below the ~99% you would expect on a warm node, and a large shared_blks_read, is paying disk I/O — the cold-cache signature. If the same query is near-100% on the primary, the fix is cache warmth (let it warm, size shared_buffers up, or pg_prewarm), not query rewriting.

Inline verification: re-run the query a minute later; on a warming node cache_hit_pct should climb and shared_blks_read per call should fall as pages become resident.

Step 6: Export to Prometheus

To watch these numbers over time and across the fleet, scrape the extension with postgres_exporter. Define a custom query so each normalized statement becomes a labelled time series per replica:

yaml

# queries.yaml for postgres_exporter, running against the replica
pg_stat_statements:
  query: |
    SELECT queryid::text AS queryid,
           calls,
           mean_exec_time,
           stddev_exec_time,
           total_exec_time,
           shared_blks_hit,
           shared_blks_read
    FROM pg_stat_statements
    ORDER BY total_exec_time DESC
    LIMIT 100
  metrics:
    - queryid:   {usage: "LABEL",   description: "normalized query id"}
    - calls:     {usage: "COUNTER", description: "number of executions"}
    - mean_exec_time:   {usage: "GAUGE", description: "mean exec ms"}
    - stddev_exec_time: {usage: "GAUGE", description: "stddev exec ms"}
    - total_exec_time:  {usage: "COUNTER", description: "cumulative exec ms"}
    - shared_blks_hit:  {usage: "COUNTER", description: "buffer hits"}
    - shared_blks_read: {usage: "COUNTER", description: "buffer disk reads"}

Run one exporter per replica so the instance label distinguishes nodes, then a recording rule turns cumulative counters into a per-scrape mean latency:

yaml

groups:
  - name: replica_query_perf
    rules:
      - record: replica:query_mean_ms:rate5m
        expr: |
          rate(pg_stat_statements_total_exec_time[5m])
          / clamp_min(rate(pg_stat_statements_calls[5m]), 1)

Inline verification: in Prometheus, replica:query_mean_ms:rate5m should return one series per queryid per instance. Grafana can then chart a single query’s latency across every replica — the export path feeding the Grafana dashboards for read replica fleets.

Configuration Snippet

Minimal replica-side configuration to make this repeatable:

ini

# postgresql.conf on every read replica you want to profile
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.track_utility = off
pg_stat_statements.save = on
compute_query_id = on          # ensures a stable queryid for cross-node matching
track_io_timing = on           # populates blk_read_time for I/O attribution

compute_query_id = on matters when you want the same queryid on the primary and the replica so a Grafana panel can overlay both — without it the ids will not line up across nodes.

Verification and Rollback

Confirm the extension is collecting and that a reset behaves as expected:

sql

-- Collecting?
SELECT count(*) AS tracked_statements,
       (SELECT stats_reset FROM pg_stat_statements_info) AS last_reset
FROM pg_stat_statements;

-- Establish a clean baseline before a load test (safe on a replica)
SELECT pg_stat_statements_reset();

pg_stat_statements_reset() mutates only the in-memory hash table, so it is safe on a read-only standby and affects only that node. To roll the whole feature back, remove it from shared_preload_libraries and restart:

ini

# revert
shared_preload_libraries = ''

The extension carries measurable but small shared-memory and per-execution overhead; if you must remove it under load, do so on one replica at a time and confirm latency is unaffected before proceeding to the next.

Edge Cases and Gotchas

The hash table evicts your slow query before you see it

pg_stat_statements.max bounds the number of distinct normalized statements. On a replica serving highly varied ad-hoc queries (a common analytics pattern), low-frequency slow queries can be evicted before you inspect them, and the extension reports the eviction in pg_stat_statements_info.dealloc. If dealloc is climbing, raise pg_stat_statements.max or move ad-hoc analytical traffic to a dedicated replica so the OLTP node’s table stays stable.

mean_exec_time is meaningless without knowing the window

Because each replica resets independently and you may have restarted a node, a mean_exec_time of 20 ms could summarize two calls or two million. Always read calls alongside the mean, and record stats_reset from pg_stat_statements_info so you know the observation window. Comparing means across two replicas is only valid when both have accumulated comparable call counts over comparable durations.

Cancellations inflate the tail but not the mean cleanly

A query cancelled by a recovery conflict may or may not be recorded depending on how far it executed before cancellation, and the surrounding retries show up as extra calls with their own timings. If a query’s cov is high and you suspect cancellations rather than slow execution, confirm with pg_stat_database_conflicts and the hot standby cancellation guide before treating it as a tuning problem.

FAQ

Do I need to enable pg_stat_statements on the primary for it to work on a replica?

No. pg_stat_statements is a per-node extension whose counters live in that node’s shared memory. A replica collects statistics from the queries it actually executes, independent of the primary. You must add it to shared_preload_libraries and restart on each replica you want to observe; enabling it on the primary alone gives you nothing on the replicas.

Can I run pg_stat_statements_reset() on a read replica?

Yes. Resetting the counters mutates only the extension’s in-memory hash table, not any replicated data, so it works on a hot standby despite the replica being read-only. It affects only that node’s statistics and does not touch the primary or any other replica. Use it to establish a clean baseline before a load test or after tuning.

How do I approximate p99 latency when pg_stat_statements only stores mean and stddev?

pg_stat_statements stores mean_exec_time and stddev_exec_time but not true percentiles. A common approximation is mean + 2*stddev for a rough p95 and mean + 3*stddev for p99, which is only meaningful when the distribution is roughly normal. For accurate tail latency, pair it with auto_explain logging of slow plans or export histograms from your driver, since a high stddev relative to mean signals the approximation is unreliable.

← Back to Query Performance Analysis on Read Replicas

Detecting Hot Standby Query Cancellations on Postgres Replicas — when a high-variance query in pg_stat_statements is actually being cancelled by WAL replay.
Prometheus Metrics for Replica Health and Lag — the exporter and scrape setup that this guide’s per-query metrics plug into.
Grafana Dashboards for Read Replica Fleets — turning per-replica query latency series into fleet-wide panels.
Monitoring & Observability for Read Replicas — the parent section covering fleet health, lag, and alerting alongside query analysis.