MIME-Version: 1.0
References: <126eb1e4-d98e-4647-b629-517adbcad28e@uni-muenster.de>
 <CAHg+QDeO4cbOKJSx0+yzD00a_1xv_z9dLrE4nKkxb1A+KEoKeg@mail.gmail.com>
 <dc1280a7-2033-4790-b147-babf55ae52f2@uni-muenster.de>
In-Reply-To: <dc1280a7-2033-4790-b147-babf55ae52f2@uni-muenster.de>
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Sun, 29 Mar 2026 16:51:27 -0700
Message-ID: 
 <CAHg+QDffk2NSTTvobAqqBmpN+DTZDJ3cdwsi5XxOUmY-MdKgwA@mail.gmail.com>
Subject: Re: Add max_wal_replay_size connection parameter to libpq
To: Jim Jones <jim.jones@uni-muenster.de>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000496c40064e326aa9"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDffk2NSTTvobAqqBmpN%2BDTZDJ3cdwsi5XxOUmY-MdKgwA%40mail.gmail.com>
Precedence: bulk

--000000000000496c40064e326aa9
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi,

On Sun, Mar 29, 2026 at 11:53=E2=80=AFAM Jim Jones <jim.jones@uni-muenster.=
de>
wrote:

>
>
> On 29/03/2026 20:31, SATYANARAYANA NARLAPURAM wrote:
> > What if none of them meets the criteria? You fail the connection?
> > Wouldn't it cause an availability issue?
>
>
> Yes, the connection fails if no host meets the threshold. This is
> intentional, and it is consistent with the existing behaviour of
> target_session_attrs: if you set target_session_attrs=3Dstandby and no
> standby is reachable, the connection fails too.
>
>
> >     If pg_last_wal_receive_lsn() is NULL (e.g. no active WAL receiver
> due to
> >     missing primary_conninfo or a disconnected upstream), the backlog
> cannot
> >     be determined. In that case, the standby is treated as exceeding th=
e
> >     threshold and is skipped.
> >
> >
> > When a standby is replaying archiving log, it can still be caught up.
> > This doesn't seem right to me.
>
>
> I totally see your point here. The issue is that
> pg_last_wal_receive_lsn() returns NULL when there is no WAL receiver
> process -- regardless of how current the data actually is. Without a
> receive LSN, the metric this parameter is based on (receive_lsn -
> replay_lsn) is simply undefined for that standby.
>
> Please let me know if I am missing something here.
>
>
> >
> >     This parameter measures only the apply lag on the standby itself,
> i.e.,
> >     how much already-received WAL remains to be replayed. It does not
> >     attempt to measure how far the standby is behind the primary. In
> >     particular, a standby that is slow to receive WAL but fast to repla=
y
> it
> >     may report a small backlog here while still being significantly
> behind.
> >
> >
> > IMHO, this change appears to not meet the objective of routing
> > connections/queries to the most up-to-date standby.
>
>
> The parameter's objective is not to route to the most up-to-date
> standby; it is to skip standbys whose apply lag exceeds a given threshold=
.
>

What is the expectation from such a routing? Is it for freshness of data
for the client or
freeing up the standby  from user connections so that it can catch up with
primary?
The paragraph described originally was talking about the freshness.

Thanks,
Satya

--000000000000496c40064e326aa9
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi,</div><br><div class=3D"gmail_quote gmail_quote_co=
ntainer"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, Mar 29, 2026 at 11:5=
3=E2=80=AFAM Jim Jones &lt;<a href=3D"mailto:jim.jones@uni-muenster.de">jim=
.jones@uni-muenster.de</a>&gt; wrote:<br></div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex"><br>
<br>
On 29/03/2026 20:31, SATYANARAYANA NARLAPURAM wrote:<br>
&gt; What if none of them meets the criteria? You fail the connection?<br>
&gt; Wouldn&#39;t it cause an availability issue?<br>
<br>
<br>
Yes, the connection fails if no host meets the threshold. This is<br>
intentional, and it is consistent with the existing behaviour of<br>
target_session_attrs: if you set target_session_attrs=3Dstandby and no<br>
standby is reachable, the connection fails too.<br>
<br>
<br>
&gt;=C2=A0 =C2=A0 =C2=A0If pg_last_wal_receive_lsn() is NULL (e.g. no activ=
e WAL receiver due to<br>
&gt;=C2=A0 =C2=A0 =C2=A0missing primary_conninfo or a disconnected upstream=
), the backlog cannot<br>
&gt;=C2=A0 =C2=A0 =C2=A0be determined. In that case, the standby is treated=
 as exceeding the<br>
&gt;=C2=A0 =C2=A0 =C2=A0threshold and is skipped.<br>
&gt; <br>
&gt; <br>
&gt; When a standby is replaying archiving log, it can still be caught up.<=
br>
&gt; This doesn&#39;t seem right to me.<br>
<br>
<br>
I totally see your point here. The issue is that<br>
pg_last_wal_receive_lsn() returns NULL when there is no WAL receiver<br>
process -- regardless of how current the data actually is. Without a<br>
receive LSN, the metric this parameter is based on (receive_lsn -<br>
replay_lsn) is simply undefined for that standby.<br>
<br>
Please let me know if I am missing something here.<br>
<br>
<br>
&gt; <br>
&gt;=C2=A0 =C2=A0 =C2=A0This parameter measures only the apply lag on the s=
tandby itself, i.e.,<br>
&gt;=C2=A0 =C2=A0 =C2=A0how much already-received WAL remains to be replaye=
d. It does not<br>
&gt;=C2=A0 =C2=A0 =C2=A0attempt to measure how far the standby is behind th=
e primary. In<br>
&gt;=C2=A0 =C2=A0 =C2=A0particular, a standby that is slow to receive WAL b=
ut fast to replay it<br>
&gt;=C2=A0 =C2=A0 =C2=A0may report a small backlog here while still being s=
ignificantly behind.<br>
&gt; <br>
&gt; <br>
&gt; IMHO, this change appears to not meet the objective of routing<br>
&gt; connections/queries to the most up-to-date standby.<br>
<br>
<br>
The parameter&#39;s objective is not to route to the most up-to-date<br>
standby; it is to skip standbys whose apply lag exceeds a given threshold.<=
br></blockquote><div><br></div><div>What is the expectation from such a rou=
ting? Is it for freshness of data for the client or=C2=A0</div><div>freeing=
 up the standby=C2=A0 from user connections so that it can catch up with pr=
imary?</div><div>The paragraph described originally was talking about the f=
reshness.</div><div>=C2=A0</div><div>Thanks,</div><div>Satya</div></div></d=
iv>

--000000000000496c40064e326aa9--