PostgreSQL

PostgreSQL — ERROR: canceling statement due to conflict with recovery

Written and reviewed by Sahil Srivastav

PostgreSQLReplicationRead replicas
ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.

What this error actually means

You ran a query on a hot standby. While it was running, WAL replay from the primary needed to remove row versions your query’s snapshot still depended on. The standby cannot both apply replication and preserve your snapshot, so it cancelled your query.

This is a genuine, unavoidable trade-off rather than a misconfiguration. A standby has two jobs that conflict: stay current with the primary, and serve consistent reads. `max_standby_streaming_delay` decides who wins — it is the amount of replication lag the standby will tolerate before it stops waiting and starts cancelling queries. Set it low and long queries get killed; set it high and the replica falls behind.

The `DETAIL` line tells you which flavour you hit. "Row versions that must be removed" means vacuum cleanup on the primary conflicted with your snapshot, which is the common case. Conflicts can also arise from exclusive locks taken by DDL replayed from the primary, or from dropped relations and tablespaces.

Causes, most common first

  1. 1Long query on the standby versus vacuum on the primary. The standard case. Your query holds a snapshot for minutes; vacuum on the primary removes dead tuples; replay of that cleanup cannot proceed without breaking your snapshot. Longer queries are exponentially more likely to be cancelled.
  2. 2max_standby_streaming_delay set aggressively low. A low value prioritises freshness, which is correct for a replica serving user traffic and wrong for one serving analytics. Many setups inherit a default tuned for the opposite workload.
  3. 3hot_standby_feedback disabled. Without feedback, the primary does not know what snapshots the standby needs and vacuums freely. With it enabled, the primary defers cleanup of rows the standby still requires — which removes most conflicts at the cost of bloat on the primary.
  4. 4DDL replayed from the primary. An `ALTER TABLE` or `DROP` on the primary replays as an exclusive lock on the standby, conflicting with any query touching that relation. Migrations therefore cause a burst of cancellations on replicas.
  5. 5Bulk deletes or updates on the primary. Large churn creates large amounts of cleanup, which is exactly what conflicts with standby snapshots. A nightly purge job is a frequent culprit for a pattern of nightly reporting failures.

When you see it

  • Only analytical or long-running queries fail; short queries on the same replica are fine
  • Failures cluster after vacuum activity or a bulk delete on the primary
  • Retrying immediately often succeeds, exactly as the `HINT` suggests
  • Reporting dashboards fail intermittently while the application is healthy
  • Raising the delay stops the cancellations and pushes replica lag up instead

How to diagnose it

Step 1

Confirm which conflict type is occurring

`pg_stat_database_conflicts` breaks conflicts down by cause on the standby, which tells you whether you are fighting vacuum, locks, or dropped objects.

SELECT * FROM pg_stat_database_conflicts;

Step 2

Check the current settings on the standby

These three values define the entire trade-off. Read them before changing anything, because the right answer depends on what this replica is for.

SELECT name, setting FROM pg_settings
WHERE name IN ('max_standby_streaming_delay', 'hot_standby_feedback',
               'max_standby_archive_delay', 'vacuum_defer_cleanup_age');

Step 3

Measure replication lag while queries run

This shows you the cost side of raising the delay. If lag is already near your tolerance, raising the delay is not available to you.

SELECT now() - pg_last_xact_replay_timestamp() AS replay_lag,
       pg_is_in_recovery() AS is_standby;

Step 4

Correlate with primary-side activity

Match the cancellation timestamps against vacuum, purge jobs, and migrations on the primary. A clear correlation usually points at a schedule change rather than a settings change.

The fix

Decide what the replica is for, then tune accordingly — this is a policy choice, not a tuning exercise. A replica serving user-facing reads should stay fresh: keep `max_standby_streaming_delay` low and accept that long queries are not welcome there. A replica serving analytics should tolerate lag: raise the delay to minutes and let it fall behind, since nobody cares whether a report is thirty seconds stale.

Enable `hot_standby_feedback` if the standby serves long queries. The primary then defers vacuuming rows the standby still needs, which removes most cleanup conflicts. Understand the cost: bloat accumulates on the primary while a long standby query runs, so a runaway query on the replica becomes a primary-side problem.

Separate the workloads physically. One replica tuned for freshness serving the application, another tuned for lag serving reporting, is far more robust than one replica compromised between the two. This is the fix that actually removes the conflict.

Make clients retry. The `HINT` is accurate — these cancellations are transient and a retry usually succeeds. Any job querying a standby should treat this error as retryable rather than fatal.

Shorten the queries. Conflict probability scales with query duration, so the same indexing and pagination work that makes a query fast also makes it far less likely to be cancelled.

How to stop it coming back

  • Route analytical and user-facing reads to different replicas with different delay settings
  • Treat this error as retryable in every client that reads from a standby
  • Enable `hot_standby_feedback` where long queries are legitimate, and monitor primary bloat as the trade
  • Schedule bulk purges and migrations away from reporting windows
  • Alarm on `pg_stat_database_conflicts` so a schedule change shows up as a metric, not as a user complaint

Practise this failure in a real repository

Gronex ships the application-side half of replica reads as a runnable repository: a service that writes to the primary and reads from a lagging standby, breaking read-your-own-writes. The tests assert correctness under lag, so routing reads elsewhere is not enough.

FAQ

Why does the same query work on the primary?

The primary has no replay to apply, so nothing forces it to discard your snapshot — it simply keeps the row versions your query needs. The conflict is specific to a standby having to reconcile replay with active reads.

Should I just enable hot_standby_feedback everywhere?

It removes most cancellations but moves the cost to the primary, which now retains dead tuples for as long as the longest standby query runs. One pathological report on the replica can bloat primary tables badly. Enable it where long queries are expected, and monitor bloat.

How high can I set max_standby_streaming_delay?

As high as your staleness tolerance for that replica. Minutes is entirely reasonable for analytics; `-1` means wait forever, which guarantees no cancellations and unbounded lag — acceptable only for a replica nobody reads fresh data from.

Is retrying safe?

Yes. The query was cancelled before returning results, so nothing partial was consumed, and the `HINT` explicitly recommends it. Retry with a short backoff, since the conflicting replay usually completes quickly.

Related

Read replica consistency failureDatabase engineering interview questions

Other errors engineers hit next to this one

Full error and symptom index →