The problem is related to large tables (billions of rows) on the
database with tps about 1500 transactions per second. I will continue
investigate. Thank you for your tips.
On Tue, Nov 13, 2018 at 5:04 PM Andrew Gierth
<andrew@tao11.riddles.org.uk> wrote:
>
> >>>>> "Andrey" == Andrey Zhidenkov <andrey.zhidenkov@gmail.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)
--
-
With best regards, Andrey Zhidenkov
