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@xxxxxxxxxxxxxxxxxxxx> wrote: > > >>>>> "Andrey" == Andrey Zhidenkov <andrey.zhidenkov@xxxxxxxxx> 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
