SproutStackSproutStack home
SeniorPostgreSQL

Long reports keep getting cancelled on the replica

Analysts run 20-minute queries on a hot standby, and PostgreSQL cancels them.

The setup today

The primary database serves the product. One streaming replica (a hot standby) serves read traffic for the app and also the analysts' reports, which scan large tables for 10 to 20 minutes.

Current configuration

replica: hot_standby = on
max_standby_streaming_delay = 30s   # default
hot_standby_feedback = off           # default

What you know

  • Reports often fail with "canceling statement due to conflict with recovery".
  • The app's reads on the same replica need data that is at most a second or two old.

The new requirement

Analysts need their reports to finish reliably, and the app's reads must stay fresh. Neither may harm the primary.

Make the call

2 questions · first-try answers earn the most XP

1. Why are the reports cancelled?
2. What is the best change for both groups?
What a strong answer also covers
  • If you enable hot_standby_feedback anywhere, alert on primary bloat and on the oldest transaction horizon.
  • Long-term, move analytics to a warehouse fed by change data capture, so reports never touch production databases.
  • Set statement timeouts per role so a runaway report cannot run for hours.
  • Measure replica lag per replica, and route app reads only to replicas within the freshness budget.
Variations to think through

Change one fact and decide again. There is no single right answer here.

  • Budget allows only one replica. Which setting would you choose, and how would you limit its harm?
  • A few reports must use data that is fresh to the minute. Where do those run?
  • Analysts want to run ad-hoc queries any time. How do you protect the system from one bad query?

The background

More drills