Re: Streaming replication versus Logical replication

Yes, it is going to resolve the issue because streaming is completely a
slave(with few exceptions). Even the VACUUM operation gets replicated
through the master, which is not a case with logical replication. In
logical replication, only data from a few tables gets replicated. In terms
of database administration, they are different entities.

In case the subscriber has long-running queries, unlike streaming
replication, it does not affect synchronisation operations.

Regards,
Ninad Shah

On Thu, 4 Nov 2021 at 21:16, Alanoly Andrews <alanolya(at)invera(dot)com> wrote:

> We are currently running some long-running SELECT queries on the
> replication database in a streaming replication pair. Some of these queries
> can run for 1 hour or more. To avoid errors related to "data no more being
> available" on the replication due to vacuuming of old data on the primary
> database, we have set the following parameters: max_standby_archive_delay
> = -1, max_standby_streaming_delay = -1, hot_standby_feedback = on. With
> these set, the long queries are able to run to completion, but there is
> table bloat on both the primary and the replicated databases, leading to
> throughput delay on the primary production database.
>
> Will this issue exist if we use "logical replication" instead? With the
> above three parameters set back to normal, will the replicated database get
> overwritten when vacuuming runs on the primary and removes old data? If it
> does not, will there be table bloat on the primary database? What is the
> mechanism by which data changes on the "publisher" are propagated to the
> "subscriber"? What happens when the subscriber database has an long-running
> query?
>
> Thanks.
>
> Alanoly Andrews.
>
>
> This e-mail may be privileged and/or confidential, and the sender does not
> waive any related rights and obligations. Any distribution, use or copying
> of this e-mail or the information it contains by other than an intended
> recipient is unauthorized. If you received this e-mail in error, please
> advise me (by return e-mail or otherwise) immediately.
>
> Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux
> droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou
> copie de ce message ou des renseignements qu'il contient par une personne
> autre que le (les) destinataire(s) désigné(s) est interdite. Si vous
> recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par
> retour de courriel ou par un autre moyen.'. If the disclaimer can't be
> applied, attach the message to a new disclaimer message.
>