The analytics team ran their big nightly rollup against a PostgreSQL read replica, deliberately, because it took 40 minutes and nobody wanted it near the primary. For months it worked. Then one Tuesday it failed at 02:07 with an error they had never seen: canceling statement due to conflict with recovery. It failed again Wednesday, again Thursday, always a few minutes past two. The error was new; nothing in the query had changed; and the on-call rotation was about to learn what a hot standby conflict is, the hard way, at the worst hour.
What had changed was on the primary. A cleanup job had been tuned that week to delete old event rows more aggressively, and autovacuum had followed close behind, reclaiming space. Every one of those WAL records, arriving at the standby, said "these rows are gone," while the 40-minute query's snapshot still needed them. PostgreSQL resolved that conflict the only way it can: it killed the query. MySQL, for the same underlying situation, makes the opposite choice: it never cancels your read; instead the replication applier waits, and lag grows until the read finishes. Neither is a bug. They are two defensible answers to an impossible question, and if you run read replicas on either engine you need to know which answer you bought.
Why must a physical standby conflict with long queries at all?
Because a PostgreSQL standby is not a logical copy; it is a byte-level replay of the primary's WAL. The standby does not know what your query intends; it only knows what the WAL commands, and the WAL says a page changed or a row version became reclaimable. Your query's snapshot, meanwhile, predates those changes and still needs the old row versions. On the primary this is no conflict at all: vacuum checks which backends hold snapshots old enough to need the rows, and defers cleanup. On the standby there is no such negotiation by default, because the standby cannot tell the primary to stop replaying, and replay is not optional; it is how the standby stays a standby. When WAL replay needs to do something your snapshot forbids, one of you must yield. max_standby_streaming_delay decides how long replay waits before the query loses, and the default is thirty seconds.
You can watch the conflicts accumulate instead of discovering them by pager:
-- on the standby
SELECT datname,
confl_tablespace,
confl_lock,
confl_snapshot,
confl_bufferpin,
confl_deadlock
FROM pg_stat_database_conflicts
WHERE datname = 'analytics';The confl_snapshot counter is the one from our incident: replay wanted to remove row versions a running query could still see. confl_lock is replay needing a lock, typically an ACCESS EXCLUSIVE from a DDL replay, that your session holds; confl_bufferpin is rarer and nastier, replay needing a buffer your session has pinned. Every one of these is a case where the standby chose replication currency over your query, and the counters are per-database, cumulative, and free.
How do you stop PostgreSQL from killing the query, and what does it cost?
There are three levers, and each moves the cost somewhere specific. First, max_standby_streaming_delay: raise it and replay tolerates conflicting queries longer. Set it to -1 and replay waits indefinitely, which protects every query and converts the problem into replication lag that grows without bound until the query finishes. For a nightly 40-minute job that might be fine; for a standby you promote in a failover, it is a recovery-time bomb, because promotion has to finish replay first.
Second, hot_standby_feedback: the standby periodically reports its oldest snapshot back to the primary, and the primary's vacuum then refuses to clean up row versions the standby still needs. This eliminates the snapshot-conflict class almost entirely, and it is the setting most teams reach for first. The cost lands on the primary: dead tuples accumulate for as long as the standby's oldest query runs, so a 40-minute nightly query means 40 minutes of un-reclaimable bloat on every table that query touches, every night. We enabled it, the rollup survived, and two months later the primary's events table had a measurable bloat problem whose root cause took a week to trace back to that one GUC. The trade-off deserves its own careful reading, and the hot standby feedback article goes deep on exactly this mechanism.
Third, design: vacuum_defer_cleanup_age on the primary defers cleanup by a fixed row-age window regardless of standby feedback, which is blunter but predictable. And the operational answer that actually ended our incident series: run the 40-minute rollup at a different hour than the aggressive cleanup, and split the replica fleet, so the analytics standby is allowed to lag and the failover standby is not. Sometimes the right knob is a calendar.
Why doesn't MySQL cancel replica queries, and what does it do instead?
MySQL replication is logical at the row level: the applier applies row events as ordinary changes through the storage engine, not as physical page overwrites. A long SELECT on the replica holds its InnoDB read view; row events touching those rows simply apply as new versions, and the read view keeps the old ones visible through the undo log, exactly the way MVCC works between two local sessions. There is no physical replay forcing the issue, so there is nothing to conflict. The query always survives.
The cost arrives through two other doors. The first is applier blocking: the replication applier is a client of the same lock manager, and when it needs a lock your query holds, it waits. The classic case is DDL: a replicated ALTER TABLE needs a metadata lock on the table, your long SELECT holds one, and every subsequent replication event piles up behind the ALTER while lag climbs by the minute. We have watched a replica go from zero lag to forty minutes of lag behind a single long read and a small replicated ALTER, and the read never knew it was the cause. The metadata locks field notes cover how to see that pile-up forming. The second door is undo and purge: a long read view on the replica pins history the way it would on any InnoDB instance, so the replica's purge thread falls behind and its history list grows, trading PostgreSQL's bloat-on-the-primary for bloat-on-the-replica.
-- the replica is fine, the query survives, and lag is the price
SHOW REPLICA STATUS\G
-- Seconds_Behind_Source climbing while a long SELECT runs
-- and a replicated DDL waits on the metadata lock behind it
SELECT * FROM performance_schema.metadata_locks
WHERE object_name = 'events'
AND lock_status = 'PENDING';So the MySQL contract is: reads on replicas are sacred, replication currency is not, and you find out what that cost when Seconds_Behind_Source pages you. The monitoring instincts for that side live in the replication lag diagnosis runbook.
Which contract should you choose for a given replica's job?
Match the contract to the replica's purpose, because both engines let you have one replica of each temperament. A failover candidate must be current above all: on PostgreSQL that means modest max_standby_streaming_delay, no hot_standby_feedback pinning the primary for hours, and short queries by policy; on MySQL it means no long reads at all, because a lagging failover candidate is a data-loss budget you have not approved. An analytics offload replica can be allowed to lag: on PostgreSQL, generous or unlimited streaming delay with feedback enabled and bloat monitoring on the primary; on MySQL, long reads are naturally safe, but watch applier blocking around DDL windows and pin the deploy calendar so replicated ALTERs never meet the long queries. The mistake to avoid is the mixed-use replica, where failover readiness and 40-minute rollups coexist and whichever property you need first is the one the other one just destroyed.
Whichever split you choose, make the conflict class visible. On PostgreSQL, graph pg_stat_database_conflicts and standby replay lag together; a rising conflict counter next to flat lag means queries are dying to keep the replica current, which is at least a conscious trade. The read replica lag piece covers the production lag side. On MySQL, graph lag against long-running replica reads and any pending metadata locks. In both cases the question you want answered in advance is: when this replica falls behind or kills a query tonight, will I know which of the two contracts fired?
How MonPG watches the PostgreSQL side of this
Hot standby conflicts are a monitoring problem wearing a query error's clothes: the evidence lives on two servers at once, the standby's conflict counters and the primary's vacuum and bloat state, and the incident only makes sense when you see both timelines together. MonPG's PostgreSQL monitoring tracks replication lag, conflict counters, vacuum behavior, and long-query history across primary and standbys in one workflow, so the Tuesday the rollup started dying has a shape: conflicts up at 02:00, cleanup job timing changed the week before, feedback decision documented next to the graph.
MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the MySQL side of this article is what it will surface: applier lag broken down by what the applier is waiting on, metadata lock pile-ups behind long replica reads, and the history-list growth a long read view causes. Until then, the MySQL monitoring page tracks that work. And the core lesson needs no tooling at all: every read replica in the world is making one of these two trades, query survival or replication currency, and the only failure is not knowing which one yours is making.