Re: synchronous streaming replication

pon., 24 paź 2022, 06:08 użytkownik Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
napisał:

> On Sat, 2022-10-22 at 17:28 +0200, Joseph Kennedy wrote:
> > Hi,I have a question. Its stays at the intersection of software
> engineering and PostgreSQL.
> >
> > I have configured streaming synchronous replication and whit setting
> "synchronous_commit=remote_apply"
> > to make sure that the slave will always respond the same as the MASTER
> (this is a developers'
> > requirement that the MASTER always responds the same as SLAVE). I set
> "hot_standby_feedback=on"
> > and "max_standby_streaming_delay=-1",
> > max_standby_streaming_delay set to -1 to make MASTER wait indefinitely
> before SELECT conflicts on the SLAVE will end.
> >
> > Here's where the problem arises, because not long after the replication
> has been started some serious
> > delays occur in the form of "replay_lag" - which rather indicates the
> appearance of conflicts;
> > the replication stops working properly.
> >
> > From the server logs it appears that UPDATE (select for update) has
> occurred on the MASTER, and SELECT
> > queries are in progress on SLAVE causing replication conflicts, with
> setting "max_standby_streaming_delay=-1"
> > they never ends and there are huge lags.
> >
> > From the findings with the developers it emerged that they do not want
> me to set max_standby_streaming_delay
> > to a value after which the queries conflicted with replication will be
> canceled.
> > * So I'm wondering if, in this configuration, it can work properly at
> all without setting, for example,
> > "max_standby_streaming_delay=30" ?
> > * On the other hand I wonder if the application should not be developed
> in such a way to support replication
> > of PostgreSQL configured as a streaming synchronous replication
> cluster with "synchronous_commit=remote_apply" ?
> > * Or perhaps "synchronous streaming replication" is a bad choice, maybe
> logical replication would be better ?
> > * What are the best practices?
> > * Perhaps you just need to force/teach applications to work with
> synchronous replication in such a way that
> > when the SELECT causes conflicts with replication such queries are
> canceled and the application should resend/repeat query ?
> > * I also think that after setting, for example,
> "max_standby_streaming_delay=30" queries (addressed) to the
> > database should be very well optimized, so that too long queries are
> not canceled too frequently?
> > * Do you know any books focused on applications adapted to work in
> postgresql synchronous streaming
> > replication environment i.e. High Availability?
>
> This can never work properly. If you have synchronous replication with
> "synchronous_commit = remote_apply",
> COMMIT on the primary will wait until the information has been replayed on
> the standby. If you set
> "max_standby_streaming_delay = -1", replication can be delayed
> indefinitely long in the event of a replication
> conflict, so COMMIT can take arbitrarily long.
>
> You can reduce replication conflicts (by setting "hot_standby_feedback =
> on" and by altering all tables to
> set "vacuum_truncate = off"), but you will never get rid of them
> completely.
>
> You will either have to accept stale ready on the standby (by setting
> "synchronous_commit" to something lower)
> or you have to accept canceled queries on the standby (by lowering
> "max_standby_streaming_delay").
>
> Yours,
> Laurenz Albe

Bloated tables are a serious matter then, after setting
vacuum_truncate=off auto vacuum will be turned off , then how to reduce
size of tables ?
Use pg_repack to reduce locks ?