RE: Streaming replication versus Logical replication

From: Ninad Shah [mailto:nshah.postgres@gmail.com]
Sent: Thursday, November 4, 2021 2:20 PM
To: Alanoly Andrews <alanolya@invera.com>
Cc: pgsql-general@lists.postgresql.org
Subject: 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.

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.