From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Andrey Zhidenkov <andrey.zhidenkov@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: hot_standby_feedback parameter doesn't work
In-Reply-To: 
 <CAJw4d1Vnw+BFVuSnSne8APg+e1cD0p4RVOtdqpf3pEVaSgb3Cw@mail.gmail.com>
	(Andrey Zhidenkov's message of "Tue, 13 Nov 2018 10:28:20 +0700")
Message-ID: <87muqduxgt.fsf@news-spur.riddles.org.uk>
References: 
 <CAJw4d1Vnw+BFVuSnSne8APg+e1cD0p4RVOtdqpf3pEVaSgb3Cw@mail.gmail.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (berkeley-unix)
Date: Tue, 13 Nov 2018 10:04:48 +0000
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk

>>>>> "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)