Re: hot_standby_feedback parameter doesn't work

>>>>> "Andrey" == Andrey Zhidenkov <andrey(dot)zhidenkov(at)gmail(dot)com> writes:

Andrey> We have a few database clusters (1 master, 2 hot standbys) with
Andrey> hot_standby_feedback setting on. But I am constantly seeing
Andrey> query conflicts on standbys because dead rows on the masters
Andrey> are deleted by VACUUM. Usually I notice it shortly after VACUUM
Andrey> ends, because the replication lag on replicas is starting to
Andrey> increase.

Feedback can only try and avoid one of the approximately five possible
causes of conflicts (albeit the most common one).

Next most likely is a pin conflict, especially if you have any tables
involved which are both small and with a relatively high update
frequency.

(Unfortunately with a max delay of 0 the standby queries won't be getting
cancelled which in turn means that there are no statistics about the
causes of conflicts.)

The most likely scenario for a pin conflict is if you have queries which
are (a) long-running, and (b) contain a sequential scan of a small (one
or a few blocks) table that is _not_ underneath a Hash node or similar;
for example, if the Seq Scan appears as the outer path of a Nestloop
join at or near the top of the plan. In this case, vacuum of the small
table may block because the query is holding pin for an extended period
of time on the block that vacuum wants to clean up. (It can happen with
index scans too, but is less likely.)

--
Andrew (irc:RhodiumToad)