Replication conflicts despite hot_standby_feedback = on?

On Wed, Jun 3, 2020 at 1:07 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

I'm seeing the following at a customer site:

SELECT confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock
FROM pg_stat_database_conflicts
WHERE datname = 'something' \gx

-[ RECORD 1 ]----+------
confl_tablespace | 0
confl_lock | 0
confl_snapshot | 84990
confl_bufferpin | 0
confl_deadlock | 0

SHOW hot_standby_feedback;

hot_standby_feedback
----------------------
on
(1 row)

This is PostgreSQL 11.7, the standby didn't disconnect from the primary, and
the number of replication conflicts is growing.

I had thought that "hot_standby_feedback = on" would get rid of such
conflicts.

In the code I see a lot of call sites for ResolveRecoveryConflictWithSnapshot,
so it is hard for me to track this down. Does anybody know what could cause
these replication conflicts?

One of the frequent causes is the lock acquired by (auto)vacuum when
truncating the trailing empty blocks, maybe you can correlate your
conflicts with autovacuum activity?

On Wed, 2020-06-03 at 13:41 +0200, Julien Rouhaud wrote:

I'm seeing the following at a customer site:

SELECT confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock
FROM pg_stat_database_conflicts
WHERE datname = 'something' \gx

-[ RECORD 1 ]----+------
confl_tablespace | 0
confl_lock | 0
confl_snapshot | 84990
confl_bufferpin | 0
confl_deadlock | 0

SHOW hot_standby_feedback;

hot_standby_feedback
----------------------
on
(1 row)

One of the frequent causes is the lock acquired by (auto)vacuum when
truncating the trailing empty blocks, maybe you can correlate your
conflicts with autovacuum activity?

Yes, but that would show up as a lock conflict, not a snapshot conflict,
right?

Yours,
Laurenz Albe

On Wed, Jun 3, 2020 at 2:05 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Wed, 2020-06-03 at 13:41 +0200, Julien Rouhaud wrote:

I'm seeing the following at a customer site:

SELECT confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock
FROM pg_stat_database_conflicts
WHERE datname = 'something' \gx

-[ RECORD 1 ]----+------
confl_tablespace | 0
confl_lock | 0
confl_snapshot | 84990
confl_bufferpin | 0
confl_deadlock | 0

SHOW hot_standby_feedback;

hot_standby_feedback
----------------------
on
(1 row)

One of the frequent causes is the lock acquired by (auto)vacuum when
truncating the trailing empty blocks, maybe you can correlate your
conflicts with autovacuum activity?

Yes, but that would show up as a lock conflict, not a snapshot conflict,
right?

Oh yes indeed, sorry for the noise.