ERROR database replication_error ai_generated true

ERROR: canceling statement due to conflict with recovery

ID: database/pg-recovery-conflict

Also available as: JSON · Markdown
88%Fix Rate
91%Confidence
65Evidence
2020-01-01First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
16 active — — —

Root Cause

Queries on a hot standby replica are canceled because WAL replay needs to apply changes (typically a VACUUM removing dead tuples or an ACCESS EXCLUSIVE lock) that conflict with an in-progress read query. The standby must replay WAL within max_standby_streaming_delay or it kills conflicting queries. This is inherent to PostgreSQL streaming replication when long-running analytics queries run on read replicas.

generic

Workarounds

  1. 85% success Enable hot_standby_feedback on the standby to prevent vacuum conflicts 
    On the standby, set:
  ALTER SYSTEM SET hot_standby_feedback = on;
  SELECT pg_reload_conf();

This tells the primary about the standby's oldest running transaction so VACUUM will not remove tuples the standby still needs. Combine with a reasonable max_standby_streaming_delay (e.g., 300s):
  ALTER SYSTEM SET max_standby_streaming_delay = '300s';

    Sources: https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK

  2. 92% success Use a dedicated logical replication subscriber or separate read replica for analytics 
    Create a publication on the primary for the tables analytics queries need:
  CREATE PUBLICATION analytics_pub FOR TABLE orders, events, users;

On a separate server, create a subscription:
  CREATE SUBSCRIPTION analytics_sub
    CONNECTION 'host=primary dbname=mydb'
    PUBLICATION analytics_pub;

Logical replication does not have WAL replay conflicts because changes are applied as regular SQL, not through physical WAL replay.

    Sources: https://www.postgresql.org/docs/current/logical-replication.html

  3. 78% success Optimize long-running standby queries to complete within max_standby_streaming_delay 
    Profile slow queries with EXPLAIN (ANALYZE, BUFFERS):
  EXPLAIN (ANALYZE, BUFFERS) SELECT ... FROM large_table WHERE ...;

Add appropriate indexes, use materialized views for complex aggregations:
  CREATE MATERIALIZED VIEW daily_stats AS
    SELECT date_trunc('day', created_at) AS day, count(*)
    FROM events GROUP BY 1;
  REFRESH MATERIALIZED VIEW CONCURRENTLY daily_stats;

Break large scans into smaller time-windowed queries to stay under the delay threshold.

    Sources: https://www.postgresql.org/docs/current/hot-standby.html

Dead Ends

Common approaches that don't work:

  1. Set max_standby_streaming_delay to -1 (infinite) to never cancel queries 65% fail

    Setting max_standby_streaming_delay to -1 prevents query cancellation but causes the standby to fall arbitrarily far behind the primary. During failover, you lose recent committed transactions. WAL accumulates on the primary causing disk pressure, and replication lag becomes unbounded. This trades query stability for data recency and operational safety.

  2. Disable hot_standby_feedback thinking it reduces conflicts 85% fail

    hot_standby_feedback actually helps prevent conflicts by informing the primary about the standby's oldest active query, so the primary's VACUUM does not remove tuples the standby still needs. Disabling it increases conflict frequency because the primary vacuums aggressively without awareness of standby queries.

  3. Retry the exact same long-running query immediately after cancellation 70% fail

    If WAL replay is ongoing (e.g., during a large VACUUM on the primary), the same conflict will recur. The query will be canceled again within max_standby_streaming_delay. Without addressing the root cause, retries enter a cancel-retry loop that wastes resources and never completes.

Error Chain

Leads to:
Preceded by:
Frequently confused with: