RE: Streaming replication versus Logical replication

Thanks, Ninad, for the response.
So, am I to understand that when there is a long-running query on the subscriber, the vacuumed data updates from the publisher are held over on the subscriber until the query completes? If so, where and how are they held over, and what does it mean in terms of disk space (either on the publisher or on the subscriber)?

Regards.
Alanoly Andrews.

From: Ninad Shah [mailto:nshah(dot)postgres(at)gmail(dot)com]
Sent: Thursday, November 4, 2021 2:20 PM
To: Alanoly Andrews <alanolya(at)invera(dot)com>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Streaming replication versus Logical replication

[Email External/Externe] Caution opening links or attachments/attention lors de l'ouverture de liens ou de pièces jointes.
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<mailto: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.

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.