Re: POC: enable logical decoding when wal_level = 'replica' without a server restart

On Thu, Apr 24, 2025 at 5:30 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Wed, Apr 23, 2025 at 9:35 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > On Wed, Apr 23, 2025 at 5:46 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > >
> > > BTW, did we consider the idea to automatically transition to 'logical'
> > > when the first logical slot is created and transition back to
> > > 'replica' when last logical slot gets dropped? I see some ideas around
> > > this last time we discussed this topic.
> >
> > Yes. Bertrand pointed out that a drawback is that the primary server
> > needs to create a logical slot in order to execute logical decoding on
> > the standbys[1].
> >
>
> True, but if we want to avoid that, we can still keep 'logical' as
> wal_level for the ease of users.

I think we'd like to cover the use case like where users start with
'replica' on the primary and execute logical decoding on the standby
without neither creating a logical slot on the primary nor restarting
the primary.

> We can also have another API like the
> one you originally proposed (pg_activate_logical_decoding) for the
> ease of users. But the minimum requirement would be that one creates a
> logical slot to enable logical decoding/replication.

I think we want to avoid the runtime WAL level automatically decreased
to 'replica' once all logical slots are removed, if users still want
to execute logical decoding on only the standby. One idea is that if
users enable logical decoding using pg_activate_logical_decoding(),
the runtime WAL level doesn't decrease to 'replica' even if all
logical slots are removed. But it would require for us to remember how
the logical decoding has been enabled in a permanent way. Also, I'm
concerned that having three ways to enable logical decoding could
confuse users: wal_level GUC parameter, creating at least one logical
slot, and pg_activate_logical_decoding().

> Additionally, shall we do some benchmarking, if not done already, to
> show the cases where the performance and WAL volume can hurt users if
> we make wal_level as 'logical'?

I believe it would be significant especially for REPLICA IDENTITY FULL
tables. I agree it's worth benchmarking it but I guess the result
would not convince us to make 'logical' default.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com