Query Performance Analysis on Read Replicas

A query that runs in 3 ms on the primary can take 300 ms on a replica serving the identical statement against identical data — and the difference is rarely a bug in your SQL. Replicas run a different cache, occasionally a different plan, and a continuous WAL replay process that periodically cancels running queries outright. Analyzing query performance on a read replica is therefore a distinct discipline from tuning the primary: the same tools apply, but the failure modes, the baselines, and the signals you alert on are specific to the replica’s role. This guide sits inside the broader work of monitoring and observability for read replicas, and focuses narrowly on the read path — why replica latency diverges, how to measure it per node, and how to keep analytical and transactional reads from poisoning each other.

Concept Definition and Scope

Query performance analysis on a read replica means answering three questions with data, not intuition: which statements are slow on this replica, why they are slower here than on the primary or on a peer replica, and whether the slowness is intrinsic to the query or imposed by the replica’s recovery process. The scope covers PostgreSQL hot standby replicas most directly — because their query-cancellation behavior has no equivalent on the primary — but the cache-warmth and plan-drift concerns apply equally to MySQL replicas.

What this guide does not cover is replica health at the fleet level (pool saturation, node availability, lag alerting) — those live in the sibling sections on Prometheus metrics for replica health and lag and alerting on replication lag and pool saturation. Here the unit of analysis is a single normalized query on a single node. The distinction matters because a replica can be perfectly healthy by lag and availability metrics while serving a specific query ten times slower than its peers, and only per-query telemetry will surface it.

Mechanism Deep-Dive

Five mechanisms drive the divergence between primary and replica query latency. Understanding them tells you which measurement to reach for.

Cold cache. A replica’s shared_buffers and OS page cache are warmed only by the queries it actually serves and by the WAL replay stream touching pages. A read-mostly replica that receives a query pattern the primary never runs — a nightly report, an ad-hoc analytical scan — starts cold and pays disk I/O for every page until the working set becomes resident. The same query is fast on the primary purely because the primary’s write workload keeps those pages hot.

Divergent plans. The planner on a replica uses the same table statistics that were replayed from the primary, but planner-relevant GUCs (work_mem, effective_cache_size, random_page_cost, jit) are per-node settings. If a replica is provisioned with less RAM or a different work_mem, the planner can choose a nested loop where the primary chose a hash join, or spill a sort to disk that the primary kept in memory. Plan drift is invisible until you compare EXPLAIN output from both nodes side by side.

Hot standby query cancellation. This is the mechanism with no primary equivalent. WAL replay on a standby sometimes needs to apply a change — a VACUUM cleanup, an ACCESS EXCLUSIVE lock, a row removal — that conflicts with a snapshot a running read query still depends on. When replay would have to wait longer than max_standby_streaming_delay, PostgreSQL cancels the query with ERROR: canceling statement due to conflict with recovery. To the application this looks like an intermittent, query-specific failure; the detailed guide to detecting hot standby query cancellations covers the full diagnosis and remediation path.

Vacuum and replay contention. Even when replay does not cancel a query, it competes with it. The startup (recovery) process consumes CPU decoding and applying WAL, and I/O bandwidth flushing dirty pages. Under a heavy write stream on the primary, that replay pressure alone raises replica query latency, and it does so unevenly — a burst of VACUUM activity on the primary translates into a replay spike on the replica that shows up as a latency bump with no corresponding change in query volume.

The max_standby_streaming_delay tradeoff. This one setting mediates the tension between the previous two mechanisms. Set it low (or zero) and replay never stalls, keeping lag minimal — but queries get cancelled aggressively. Set it high (or -1 for unlimited) and long queries survive — but replay pauses behind them, and replication lag grows for the duration of the query. There is no globally correct value; it is a per-replica policy decision driven by what that replica serves.

The lifecycle of a query on a replica

The diagram traces a single read from arrival through the branch that makes replicas distinct — the point where WAL replay can cancel a query that has otherwise executed correctly.

Annotated configuration for observability

Before any analysis, the replica must be instrumented. These settings load the two extensions that carry the analysis, on the replica’s own postgresql.conf:

# postgresql.conf on the REPLICA (each node is instrumented independently)
shared_preload_libraries = 'pg_stat_statements,auto_explain'

# pg_stat_statements: track normalized statement stats on this node
pg_stat_statements.max = 10000        # distinct normalized queries retained
pg_stat_statements.track = top        # track top-level statements only
pg_stat_statements.track_utility = off

# auto_explain: log a plan for anything slower than the threshold
auto_explain.log_min_duration = '250ms'
auto_explain.log_analyze = on         # real timings, not just estimates
auto_explain.log_buffers = on         # shared-buffer hit/read counts per node
auto_explain.log_nested_statements = on

# The lever that governs cancellation vs lag on THIS replica
max_standby_streaming_delay = 30s     # OLTP replica: cancel long queries early

shared_preload_libraries requires a restart, not a reload — plan the change into a maintenance window per replica. Because auto_explain.log_buffers records shared-buffer hits versus disk reads, it is the single most useful setting for diagnosing cold-cache slowness: a plan that reads thousands of buffers from disk on the replica but hits them all in cache on the primary is a cache-warmth problem, not a plan problem.

Trade-Off Comparison Table

The measurement tools overlap; each answers a different question and costs something different to run.

The practical workflow chains them: pg_stat_statements narrows the field to a handful of expensive normalized queries, auto_explain logs (or an on-demand EXPLAIN (ANALYZE, BUFFERS)) explains why each is expensive on this node, and pg_stat_database_conflicts confirms whether the tail you see in the p99 is genuine execution cost or replay cancellation masquerading as latency. For turning these into fleet-wide dashboards, the finding slow queries with pg_stat_statements guide shows the exporter and Prometheus wiring.

Configuration Runbook

PostgreSQL: baseline the top-N slow reads per replica

Run this against each replica to rank normalized queries by total time, with the mean-versus-p99 spread that reveals unstable latency:

-- On the replica: top time-consuming normalized queries with tail spread
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 + 2 * stddev_exec_time)::numeric, 2) AS approx_p95_ms,
  round(total_exec_time::numeric, 0)                      AS total_ms,
  round(100.0 * shared_blks_hit
        / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct,
  left(query, 80)                                         AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

The cache_hit_pct column is doing the heavy lifting: a low ratio on a query that is near-100% on the primary is the cold-cache signature. A large stddev_ms relative to mean_ms points at either plan instability or intermittent replay contention rather than a uniformly expensive query.

PostgreSQL: compare the real plan against the primary

For any query the baseline flags, capture the plan on both nodes and diff the buffer counts:

-- Run on the replica AND on the primary; compare Buffers and node types
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '7 days'
ORDER BY o.created_at DESC
LIMIT 100;

If the plan node types match but Buffers: shared read= is high on the replica and hit= on the primary, it is a cache problem — warm it or accept the first-run cost. If the node types differ (nested loop vs hash join, or a Sort spilling to disk), it is plan drift — reconcile work_mem, effective_cache_size, and random_page_cost with the primary.

PostgreSQL: watch recovery conflicts alongside latency

-- On the replica: cumulative recovery conflicts by cause
SELECT datname,
       confl_tablespace,
       confl_lock,
       confl_snapshot,
       confl_bufferpin,
       confl_deadlock
FROM pg_stat_database_conflicts
WHERE datname = current_database();

A rising confl_snapshot or confl_lock count correlated in time with a latency spike tells you the p99 is cancellations, not slow execution — a completely different fix. This is the entry point to the dedicated cancellation guide.

MySQL: replica-side statement digest

MySQL replicas expose an analogous per-node view through Performance Schema. There is no hot-standby cancellation on MySQL replicas, so the analysis reduces to cache warmth and plan drift:

-- On the MySQL replica: slowest digests by total latency
SELECT DIGEST_TEXT,
       COUNT_STAR                                AS calls,
       ROUND(AVG_TIMER_WAIT/1e9, 2)              AS avg_ms,
       ROUND(MAX_TIMER_WAIT/1e9, 2)              AS max_ms,
       SUM_ROWS_EXAMINED,
       SUM_NO_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

SUM_NO_INDEX_USED climbing on a replica but not the primary usually means the replica’s optimizer chose a table scan because its buffer pool warmth or index statistics diverged after a restart.

Monitoring and Alerting Signals

Per-query latency only becomes actionable when it is tracked per replica over time. The signals worth exporting and alerting on:

• Per-replica p99 execution time by query class. Export pg_stat_statements mean and derived p99 per queryid, labelled by node. Alert when one replica’s p99 for a class drifts above its fleet peers by more than a set multiple — that isolates a single sick node rather than a systemic regression.
• Cache hit ratio per replica. shared_blks_hit / (shared_blks_hit + shared_blks_read) from pg_stat_statements, or pg_statio_user_tables. A ratio that stays low after a node has been serving traffic for an hour indicates an undersized shared_buffers or a working set that does not fit.
• Recovery conflict rate. The delta of pg_stat_database_conflicts counters per scrape interval. Any non-zero confl_snapshot rate on an OLTP replica warrants investigation.
• Query cancellation error rate at the application. Count 40001 / canceling statement due to conflict with recovery in application logs; it is the user-visible face of the previous signal.
• Replay lag versus query duration correlation. When p99 latency and replication lag rise together, max_standby_streaming_delay is likely letting long queries pause replay.

These per-query signals complement, rather than replace, the fleet lag and saturation alerting described in alerting on replication lag and pool saturation, and they feed naturally into the Grafana dashboards for read replica fleets.

Failure Modes and Recovery Steps

1. A single replica is slow for one query class

Symptom: p99 for a specific normalized query is many times higher on one replica than its peers, while lag and availability look fine.

Recovery:

1. Confirm it is per-node by running the top-N pg_stat_statements query on each replica and comparing the mean_ms and cache_hit_pct for that queryid.
2. Capture EXPLAIN (ANALYZE, BUFFERS) on the slow node and a healthy node. If buffers differ, warm the cache or resize shared_buffers; if plans differ, reconcile planner GUCs.
3. If neither differs, check pg_stat_activity on the slow node for a long-running analytical query saturating I/O — see failure mode 3.

2. Intermittent query failures under primary write bursts

Symptom: Queries that usually succeed fail with canceling statement due to conflict with recovery, clustered in time with heavy write or VACUUM activity on the primary.

Recovery:

1. Correlate the failures against pg_stat_database_conflicts deltas to confirm they are cancellations.
2. Decide the per-replica policy: raise max_standby_streaming_delay to protect queries at the cost of lag, or enable hot_standby_feedback = on to protect against snapshot conflicts at the cost of primary bloat.
3. Add bounded application-side retry for the 40001 SQLSTATE. The full tradeoff analysis is in the hot standby cancellation guide.

3. Analytical query starves OLTP reads on the same replica

Symptom: OLTP read latency on a replica spikes whenever a reporting job runs, even though neither query is individually pathological.

Recovery:

1. Identify the offender in pg_stat_activity by query_start age and application_name.
2. Move analytical traffic to a dedicated replica with its own connection pool and a high max_standby_streaming_delay, keeping OLTP replicas tuned for low delay.
3. Enforce the split at the routing layer so the two classes never share a node — the pooling side of this is covered in connection routing and pooling strategies.

4. Post-restart cold-cache latency cliff

Symptom: Immediately after a replica restart or failover, every query is slow for minutes, then latency normalizes.

Recovery:

1. Expect it — a restarted replica has an empty buffer cache. Do not route latency-sensitive traffic to a freshly promoted or restarted node until its cache warms.
2. Consider pg_prewarm to preload the hot working set on startup.
3. Gate the node’s return to the active pool on its cache hit ratio recovering, not merely on it accepting connections.

Guides in This Section

Two focused guides drill into the mechanisms above with runnable detail:

Finding Slow Queries on Read Replicas with pg_stat_statements Enabling the extension on a replica, why its statistics are per-node and independent of the primary, the top-N slow-query SQL, reading mean versus p99 through stddev_exec_time, when and how to reset the counters, correlating slowness with buffer cache hit ratios, and exporting the whole thing to Prometheus for per-replica dashboards.

Detecting Hot Standby Query Cancellations on Postgres Replicas The canceling statement due to conflict with recovery symptom, every column of pg_stat_database_conflicts and what it means, the root-cause split between snapshot/lock/buffer-pin conflicts and raw WAL replay pressure, and the remediation menu — hot_standby_feedback, max_standby_streaming_delay, and application retry — with each option’s cost in bloat or lag.

FAQ

Related

← Back to Monitoring & Observability for Read Replicas

• Finding Slow Queries on Read Replicas with pg_stat_statements — the per-node top-N workflow, mean-vs-p99 interpretation, and Prometheus export.
• Detecting Hot Standby Query Cancellations on Postgres Replicas — diagnosing recovery conflicts and choosing between feedback, delay, and retry.
• Prometheus Metrics for Replica Health and Lag — the fleet-level health signals that sit alongside per-query latency.
• Grafana Dashboards for Read Replica Fleets — visualizing per-replica latency and cache-hit ratios across the fleet.
• Replication Lag & Consistency Management — how max_standby_streaming_delay and query duration trade off against replica lag.