| From: | shihao zhong <zhong950419(at)gmail(dot)com> |
|---|---|
| To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
| Cc: | Troels Arvin <troels(at)arvin(dot)dk>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: max_standby_streaming_delay or hot_standby_feedback |
| Date: | 2026-10-29 14:06:25 |
| Message-ID: | CAGRkXqRqA11SYXFqx9zPJ+vgLXsksbH4rgatSSk4eZu-ac_byQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Hi Troels,
You are hitting something called a recovery conflict. This means a read
query on the replica conflicts with incoming WAL changes from the primary
on certain objects.
There are 3 main types of conflicts:
Snapshot conflict: The primary's VACUUM removes tuple versions that a
transaction on the replica still needs to see. If the replica replays that
VACUUM-related WAL record, the read transaction would no longer see the
correct data. Postgres must either pause WAL replay or cancel the read
query. Enabling hot_standby_feedback mitigates most of these cases because
the primary delays vacuuming tuple versions visible to the standby.
Lock conflict: The primary requires an AccessExclusiveLock on an object
(usually a table), typically during DDL operations or the table-truncation
phase of a VACUUM. If a read query is actively accessing that table, replay
cannot proceed.
Buffer pin conflict: An operation on the primary needs an exclusive cleanup
lock (sometimes called a super-exclusive lock) on a specific buffer
page—most often during b-tree or heap index vacuuming. while a query on the
replica holds a pin on that buffer (common during heavy nested loop joins).
In all of these cases, Postgres tries to balance replication lag against
read query execution. This is where max_standby_streaming_delay comes into
play. If set too low, your queries will frequently be canceled. If set too
high, replication lag will increase. If set to -1 (wait indefinitely), you
run the risk of the primary accumulating excess WAL until it exceeds
wal_keep_size or invalidates the replication slot via
max_slot_wal_keep_size, which would break replication entirely.
This is a trade off to consider.
Thanks,
Shihao
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Paul A Jungwirth | 2026-10-30 22:34:40 | Patch Review Workshop: November 2026 |
| Previous Message | Laurenz Albe | 2026-10-29 12:25:09 | Re: max_standby_streaming_delay or hot_standby_feedback |