Re: Disallow quorum uncommitted (with synchronous standbys) txns in logical replication subscribers

Hi,

On 2022-01-07 14:36:46 -0800, Jeff Davis wrote:
> On Fri, 2022-01-07 at 12:22 -0800, Andres Freund wrote:
> > I don't see how it can *not* cause a major performance / latency
> > gotcha. You're deliberately delaying replication after all?
>
> Are there use cases where someone wants sync rep, and also wants their
> read replicas to get ahead of the sync rep quorum?

Yes. Not in the sense of being ahead of the sync replicas, but in the sense of
being as cought up as possible, and to keep the lost WAL in case of crashes as
low as possible.

> > another sync replica would still not be guaranteed to be able to
> > follow the
> > newly promoted primary.
>
> If you only promote the furthest-ahead sync replica (which is what you
> should be doing if you have quorum commit), why wouldn't that work?

Remove "sync" from the above sentence, and the sentence holds true for
combinations of sync/async replicas as well.

> > To me this just sounds like trying to shoehorn something into syncrep
> > that
> > it's not made for.
>
> What *is* sync rep made for?
>
> The only justification in the docs is around durability:
>
> "[sync rep] extends that standard level of durability offered by a
> transaction commit... [sync rep] can provide a much higher level of
> durability..."

What is being proposed here doesn't increase durability. It *reduces* it -
it's less likely that WAL is replicated before a crash.

This is a especially relevant in cases where synchronous_commit=on vs local is
used selectively - after this change the durability of local changes is very
substantially *reduced* because they have to wait for the sync replicas before
also replicated to async replicas, but the COMMIT doesn't wait for
replication. So this "feature" just reduces the durability of such commits.

The performance overhead of syncrep is high enough that plenty real-world
usages cannot afford to use it for all transactions. And that's normally fine
from a business logic POV - often the majority of changes aren't that
important. It's non-trivial from an application implementation POV though, but
that's imo a separate concern.

> If we take that at face value, then it seems logical to say that async
> read replicas should not get ahead of sync replicas.

I don't see that. This presumes that WAL replicated to async replicas is
somehow bad. But pg_rewind exist, async replicas can be promoted and WAL from
the async replicas can be transferred to the synchronous replicas if only
those should be promoted.

Greetings,

Andres Freund