MIME-Version: 1.0
References: <CAKfDxxwF03mouH7GqdZ0AqpGKz2wTj4T3F6ei_ubEQnNtErfGA@mail.gmail.com>
 <CAFgz3ksSB1OvN1ONDOO2qvhhF-FS7PXQTQtkDQYP_Y_pcvf+6g@mail.gmail.com>
 <CAKfDxxxqWnf2Wb8dg2Ytx497aPpb-eFugxYVJfC=Pi8DaciXyg@mail.gmail.com> <CAKAnmm+jtytmNAYDjBkiQvCHQVwtskUzvp3J_=5mUsFvaOkRVA@mail.gmail.com>
In-Reply-To: <CAKAnmm+jtytmNAYDjBkiQvCHQVwtskUzvp3J_=5mUsFvaOkRVA@mail.gmail.com>
From: Jon Zeppieri <zeppieri@gmail.com>
Date: Sat, 26 Jul 2025 11:43:09 -0400
Message-ID: <CAKfDxxx0Bo3yvqAFqTX_3k9nxaX903byCwKhvj=63pAZsEZyQA@mail.gmail.com>
Subject: Re: Possible causes of high_replay lag, given replication settings?
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: Nick Cleaton <nick@cleaton.net>, pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAKfDxxx0Bo3yvqAFqTX_3k9nxaX903byCwKhvj%3D63pAZsEZyQA%40mail.gmail.com>
Precedence: bulk

On Fri, Jul 25, 2025 at 7:13=E2=80=AFPM Greg Sabino Mullane <htamfids@gmail=
.com> wrote:
>
> On Fri, Jul 25, 2025 at 9:57=E2=80=AFAM Jon Zeppieri <zeppieri@gmail.com>=
 wrote:
>>
>> Thanks for the response, Nick. I'm curious why the situation you describ=
e wouldn't also lead to the write_lag and flush_lag also being
>> high. If the problem is simply keeping up with the primary, wouldn't you=
 expect all three lag times to be elevated?
>
>
> No - write and flush are pretty quick and simple, it's just putting the W=
AL onto the local disk. Replay involves a lot more work as we have to parse=
 the WAL and apply the changes, which means doing a lot of I/O across many =
files. Still, *hours* to me indicates more than just a lot of extra traffic=
. Check that recovery_min_apply_delay is still 0, then log onto the replica=
 and see what's going on with regards to open transactions and locks.

Thanks Greg. `recovery_min_apply_delay` is 0, just checked. Also, I
didn't mention in my initial post that it seemed the cause of the
delay was long-running queries on the replica, rather than the
primary. It's possible, of course, that I'm wrong, but I was able to
get the replica moving again when I killed off old queries on the
replica. If those were the problem, though, then I don't understand
why the max_standby_streaming_delay didn't prevent that situation.

- Jon