Hot Standby and Recovery Conflicts

The one thing to understand first

Hot standby lets a streaming standby serve read-only queries while it continuously replays WAL from the primary. This is how PostgreSQL scales reads: route SELECT traffic to one or more standbys. But it creates a tension that does not exist on the primary — the standby is doing two things at once: replaying changes and answering queries against the data those changes are modifying.

A recovery conflict is fundamentally unsolvable in the moment: when replayed WAL would remove rows a standby query still needs, PostgreSQL can keep replay current or keep the query alive, never both. Every hot-standby knob is just a choice about which side loses.

Why recovery conflicts happen

The standby replays the primary's WAL, including operations that remove data a running query still needs to see. The classic case: vacuum on the primary removes dead tuples; that removal arrives as a WAL record; replaying it would delete row versions that a long-running query on the standby is still reading under its MVCC snapshot. The replay and the query conflict over the same rows — a recovery conflict.

Other conflict sources: an ACCESS EXCLUSIVE lock replayed for DDL, a dropped tablespace/database, or buffer pin conflicts.

The forced choice

When a conflict occurs the standby must choose: pause replay (letting the query finish, but increasing replication lag) or cancel the query (keeping replay current, but the user sees an error). PostgreSQL cannot do both — this is the core trade-off of hot standby. The canceled query reports:

ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions
        that must be removed.

Layer 3 — Watch it happen on your own database

One catalog on the standby counts exactly which kind of conflict is hurting you:

-- Per-database conflict counters on the standby
SELECT datname, confl_snapshot, confl_lock,
       confl_bufferpin, confl_deadlock, confl_tablespace
FROM   pg_stat_database_conflicts;

High confl_snapshot points to vacuum-vs-query conflicts — the case hot_standby_feedback addresses. confl_lock means replayed DDL locks are killing queries; confl_bufferpin and confl_tablespace point to the rarer pin/drop cases. Read this table first: it tells you which lever to reach for.

Layer 4 — The levers this hands you

Two GUCs resolve conflicts from opposite ends — one arbitrates after a conflict, the other prevents it:

• **max_standby_streaming_delay* sets how long replay may be delayed* to let conflicting queries finish before they are canceled. 30s means "pause WAL replay up to 30 seconds waiting for a query; after that, cancel the query and proceed." High favours long read queries (at the cost of lag); low keeps the standby fresh (at the cost of canceled queries); -1 waits indefinitely — never cancels, but lag can grow without bound.
• **hot_standby_feedback = on* is the cleaner fix for read-heavy standbys. The standby tells the primary the oldest snapshot its queries still need; the primary's vacuum then refrains from removing rows still visible to standby queries, so the conflict never arises. The trade-off moves to the primary: it keeps more dead tuples, risking bloat* if a standby runs very long queries (it extends the primary's vacuum horizon, just like a long local transaction).

Choosing a strategy:

• Read-heavy, long analytic queries on the standby: turn on hot_standby_feedback and accept some primary bloat; monitor it.
• Standby primarily for failover, freshness critical: keep max_standby_streaming_delay low and let long queries be canceled.
• Mixed: a dedicated reporting standby with feedback on, plus a separate failover standby kept fresh.

Layer 5 — What an Oracle DBA should expect vs what they get

An Oracle DBA reading from an Active Data Guard standby will find the trade-offs unfamiliar:

• Hot standby ≈ Active Data Guard real-time query, but free and built in — no separate option to license to read from the standby.
• Recovery conflicts have no real Active Data Guard analogue. Oracle's read-consistency on a standby is served from UNDO, so a long query there does not get killed by redo apply the way a Postgres query is canceled by WAL replay. The "canceling statement due to conflict with recovery" error surprises Oracle DBAs.
• **hot_standby_feedback ≈ deliberately holding back the primary's undo/cleanup,** trading standby query survival for primary-side bloat — a Postgres-specific tuning dial with no exact Oracle counterpart.
• **max_standby_streaming_delay ≈ a bounded apply lag,** conceptually like deciding how far real-time apply may fall behind to protect queries — but here it is an explicit per-standby timeout that ends in query cancellation.

Key takeaway

A hot standby both replays WAL and answers queries, so when replayed cleanup would remove rows a query still needs, PostgreSQL must either pause replay (more lag) or cancel the query — it cannot do both. Use pg_stat_database_conflicts to see which conflict dominates, then choose your lever: max_standby_streaming_delay to arbitrate after the fact, or hot_standby_feedback to prevent snapshot conflicts at the cost of extra bloat on the primary.