MIME-Version: 1.0
References: <514f6f2f-6833-4539-39f1-96cd1e011f23@enterprisedb.com>
 <18dbc929-a281-8552-4f1d-7e4d0e4eedba@enterprisedb.com> <646acd8d-9896-ed8d-2ab4-0bf11b3285d4@enterprisedb.com>
 <20220205195944.drwc3psrb5udt2cw@alap3.anarazel.de> <CAE9k0PnGmQ97ikLy2oBDoom72FsV_x+H9JuptJ4ysx=CFM-+kQ@mail.gmail.com>
 <20220207203222.22aktwxrt3fcllru@alap3.anarazel.de> <CAE9k0Pn80FMhkUZAPyPCLWDYnOcTmxKN3Q3TNNv1m4V99qG-Ew@mail.gmail.com>
 <YgWH4/4tK1AMIOYG@momjian.us> <ac40e8f8-46f5-7bb4-c08b-df3c9e6f1599@enterprisedb.com>
 <aa626ffb-2cb1-8683-5188-dad6031a4623@gmail.com> <b5cc7fd2-ebbc-512f-40fc-5c7ce93b8bdf@gmail.com>
 <aeab9f12-93a6-743d-4ac6-5f2c73f9bfea@gmail.com> <CAA4eK1LrZuDLGu6saZ8RDs7gKLM0dmafq+9oqRPKKN1cEEao_Q@mail.gmail.com>
 <CAJpy0uCMNz3XERP-Vzp-7rHFztJgc6d+xsmUVCqsxWPkZvQz0Q@mail.gmail.com>
 <CALj2ACUV6QzAvzjDDbu2oyhrVgAP98ypobY25b-dQGFom7Gjtw@mail.gmail.com>
 <CAJpy0uCdENWu1eTAcDStBPQeomXgbR1LS7OzDVmsg8wt51uCZg@mail.gmail.com> <CALj2ACV+VX9McnogGNyFCjZW+qnPvdmjnBjttotygs8+7D5JuA@mail.gmail.com>
In-Reply-To: <CALj2ACV+VX9McnogGNyFCjZW+qnPvdmjnBjttotygs8+7D5JuA@mail.gmail.com>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Mon, 24 Jul 2023 09:00:15 +0530
Message-ID: <CAA4eK1J2h_KhaJNG-Q1W38pgHHpOV+aaC9VN-GuLPTyRfUtgDg@mail.gmail.com>
Subject: Re: Synchronizing slots from primary to standby
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>, 
	"Drouvot, Bertrand" <bertranddrouvot.pg@gmail.com>, 
	Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Bruce Momjian <bruce@momjian.us>, 
	Ashutosh Sharma <ashu.coek88@gmail.com>, Andres Freund <andres@anarazel.de>, 
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAA4eK1J2h_KhaJNG-Q1W38pgHHpOV%2BaaC9VN-GuLPTyRfUtgDg%40mail.gmail.com>
Precedence: bulk

On Mon, Jul 24, 2023 at 8:03=E2=80=AFAM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> On Fri, Jul 21, 2023 at 5:16=E2=80=AFPM shveta malik <shveta.malik@gmail.=
com> wrote:
> >
> > Thanks Bharat for letting us know. It is okay to split the patch, it
> > may definitely help to understand the modules better but shall we take
> > a step back and try to reevaluate the design first before moving to
> > other tasks?
>
> Agree that design comes first. FWIW, I'm attaching the v9 patch set
> that I have with me. It can't be a perfect patch set unless the design
> is finalized.
>
> > I analyzed more on the issues stated in [1] for replacing LIST_SLOTS
> > with SELECT query. On rethinking, it might not be a good idea to
> > replace this cmd with SELECT in Launcher code-path
>
> I think there are open fundamental design aspects, before optimizing
> LIST_SLOTS, see below. I'm sure we can come back to this later.
>
> > Secondly, I was thinking if the design proposed in the patch is the
> > best one. No doubt, it is the most simplistic design and thus may
> > .......... Any feedback is appreciated.
>
> Here are my thoughts about this feature:
>
> Current design:
>
> 1. On primary, never allow walsenders associated with logical
> replication slots to go ahead of physical standbys that are candidates
> for future primary after failover. This enables subscribers to connect
> to new primary after failover.
> 2. On all candidate standbys, periodically sync logical slots from
> primary (creating the slots if necessary) with one slot sync worker
> per logical slot.
>
> Important considerations:
>
> 1. Does this design guarantee the row versions required by subscribers
> aren't removed on candidate standbys as raised here -
> https://www.postgresql.org/message-id/20220218222319.yozkbhren7vkjbi5%40a=
lap3.anarazel.de?
>
> It seems safe with logical decoding on standbys feature. Also, a
> test-case from upthread is already in patch sets (in v9 too)
> https://www.postgresql.org/message-id/CAAaqYe9FdKODa1a9n%3Dqj%2Bw3NiB9gkw=
vhRHhcJNginuYYRCnLrg%40mail.gmail.com.
> However, we need to verify the use cases extensively.
>

Agreed.

> 2. All candidate standbys will start one slot sync worker per logical
> slot which might not be scalable.
>

Yeah, that doesn't sound like a good idea but IIRC, the proposed patch
is using one worker per database (for all slots corresponding to a
database).

> Is having one (or a few more - not
> necessarily one for each logical slot) worker for all logical slots
> enough?
>

I guess for a large number of slots the is a possibility of a large
gap in syncing the slots which probably means we need to retain
corresponding WAL for a much longer time on the primary. If we can
prove that the gap won't be large enough to matter then this would be
probably worth considering otherwise, I think we should find a way to
scale the number of workers to avoid the large gap.

--=20
With Regards,
Amit Kapila.