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

Hi,

On Tue, Aug 25, 2026 at 12:28 AM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
>
> > Although fetching from archive could be much faster (with parallel
> > restoring of a batch of WAL files without going through the
> > replication protocol), as rightly mentioned upthread, a transiently
> > failed archive network or storage could lead to the primary's disk
> > filling up or blocking logical replication, and perhaps vacuum too.
> >
> > Can we start by having some tests where the archive is in S3 or a
> > similar remote store and measure the latencies for fetching say 1000
> > or more WAL files from the archive vs the primary via streaming with a
> > simple restore command, and also the impact on the primary while the
> > standby stays in archive mode? This helps set some context for the use
> > case.
>
> Thanks for the suggestion. I'd like to hold off on measuring archive
> recovery vs. streaming speed.
>
> The main motivation for reviving this thread for me is to set the
> performance question of archive recovery vs. streaming replication
> aside entirely...

Fair enough. I would not stress on this experiment now, especially
since I agree with your described high write workload cases below.

> and focus instead on the impact of a standby remaining
> in archive recovery mode for a sustained period specifically, how that
> affects the primary (via a stalled replication slot and unbounded WAL
> retention) and, where slot sync is in use, the logical replicas
> downstream as well.

+1.

> To put this in concrete terms: consider a primary under a high write
> workload generating WAL at 100 MB/s, or roughly 360 GB per hour. When
> a new or existing standby starts recovery with a substantial archive
> backlog, it continues restoring archived WAL while suitable WAL
> remains available. Because WAL redo is largely sequential and
> single-process, it may/does not keep pace with WAL generation under
> such a workload, even on well provisioned hardware. The standby
> therefore remains in archive recovery for an extended period.
>
> As a result, the primary could retain more than 500 GB of WAL within a
> couple of hours and several terabytes within four or five hours. This
> can exhaust the primary’s storage and cause an outage, forcing users
> either to increase storage substantially or accept prolonged
> unavailability.
>
> The effect can also extend to logical replicas when slot
> synchronization is enabled. When synchronized_standby_slots includes
> the stalled physical slot, logical WAL senders cannot advance beyond
> that physical standby’s confirmed position. The resulting delay
> therefore affects not only the primary and standby, but also logical
> replicas downstream.

Agreed. This effect can be even worse in practice under such a high
write workload.

> We have observed this behavior on several occasions. Although WAL
> prefetching implemented at our end substantially improves archive
> retrieval, redo remains largely single-process and still falls behind
> WAL generation on heavily loaded systems generating 100 MB/s of WAL or
> more, keeping the standby in archive recovery for an extended period,
> allowing the associated replication slot and retained WAL on the
> primary to continue growing.

Looking forward to the new patch here. Thanks again for picking this up.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com