Re: Switching XLog source from archive to streaming when primary available

Hi,

On Wed, Jan 8, 2025 at 5:17 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Thu, Jan 02, 2025 at 11:12:37PM +0500, Andrey M. Borodin wrote:
> > In my observation restore from archive is many orders of magnitude
> > faster than streaming replication. Advanced archive tools employ
> > compression (x6 to speed), download parallelism (x4), are not
> > constrained be primary's network limits (x3) and disk limits, do not
> > depend on complicated FEBE protocol, etc.
>
> This is a fair argument in terms of flexibility of what can be
> achieved on a file-basis, yes, because you are not bottlenecked by
> the existing replication protocol and can request them ahead of time
> if necessary and can decide what you want within a single
> restore_command or archive_command (or module for the latter).
>
> It may be relevant to think in terms of what could be done at protocol
> level to retrieve batches of WAL segments so as the backend has a
> better control on how each segment is handled in a batch, or provide
> better in-core tools to achieve that with the existing two command
> GUCs for restore and archiving? Nathan has also proposed a couple of
> months ago restore modules, because relying on commands can be very
> fancy in terms of error handling. And we already have the archive
> module part.

Reviving this thread - the discussion so far has mostly centered on
whether streaming or archive recovery is faster, and understandably
stalled there since it depends heavily on the environment (storage
type, network, archive tooling). I'd like to set that question aside
and make the case that this patch solves a correctness/availability
problem independent of performance.

The core issue: if a standby remains in archive recovery for an
extended period, its corresponding physical replication slot on the
primary stops advancing - the slot's restart_lsn only moves forward
via streaming feedback, which isn't happening while the standby is
reading from the archive. If the primary is generating WAL at a
meaningful rate during this window, the primary has to retain all of
it for that stalled slot, which can exhaust the primary's data disk
and take it down entirely.

This gets worse with the slot sync feature (PG17). When
synchronized_standby_slots is configured, the primary holds back
logical decoding until every listed physical standby slot has
confirmed receipt of that WAL. The logical slots are tightly coupled
to the physical slot representing the failover-candidate standby. So
if that physical slot stalls because the standby is stuck in archive
mode, logical replication stalls too, not just physical.

In short: the disk-exhaustion risk on the primary, and the
logical-replication stall under slot sync, both stem from the standby
staying in archive mode longer than necessary regardless of whether
streaming would technically be faster in a given environment. I would
suggest we evaluate this patch primarily against that concern, and
treat the performance angle as a secondary benefit rather than the
main justification.

I will rebase the patch (last posted as v24) and follow up with an
updated version if there's interest in continuing this.

--
With Regards,
Ashutosh Sharma.