MIME-Version: 1.0
References: <126eb1e4-d98e-4647-b629-517adbcad28e@uni-muenster.de>
In-Reply-To: <126eb1e4-d98e-4647-b629-517adbcad28e@uni-muenster.de>
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Sun, 29 Mar 2026 11:31:49 -0700
Message-ID: 
 <CAHg+QDeO4cbOKJSx0+yzD00a_1xv_z9dLrE4nKkxb1A+KEoKeg@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="0000000000002fea6b064e2df36e"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDeO4cbOKJSx0%2ByzD00a_1xv_z9dLrE4nKkxb1A%2BKEoKeg%40mail.gmail.com>
Precedence: bulk

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

Hi Jim,

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

> Hi,
>
> When connecting with target_session_attrs=3Dstandby (or prefer-standby,
> read-only, any) and multiple standbys are available, libpq currently
> selects the first acceptable candidate without regard for how "current"
> its data is. A standby configured with recovery_min_apply_delay,
> experiencing slow I/O, or otherwise lagging is treated the same as one
> that is fully caught up.
>
> I would like to propose a new libpq connection parameter,
> max_wal_replay_size, that allows clients to skip standby servers whose
> WAL replay backlog exceeds a given threshold.
>
> Example:
>
>   psql "host=3Dhost1,host2,host3 port=3D5111,5222,5333 \
>         target_session_attrs=3Dstandby max_wal_replay_size=3D16MB"
>
> When this parameter is set, libpq executes a small query during
> connection establishment to evaluate:
>
>   pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())
>
> on the standby. If the result exceeds the specified threshold, the
> server is skipped and the next host in the list is tried. The check is
> skipped entirely when target_session_attrs is set to primary or
> read-write, since those modes already exclude standbys.
>

What if none of them meets the criteria? You fail the connection? Wouldn't
it cause an availability issue?


>
> 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 the
> threshold and is skipped.
>

When a standby is replaying archiving log, it can still be caught up. This
doesn't seem right to me.


>
> 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 replay 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.

Thanks,
Satya

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

<div dir=3D"ltr"><div dir=3D"ltr">Hi=C2=A0Jim,</div><br><div class=3D"gmail=
_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Sun,=
 Mar 29, 2026 at 10:56=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><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex">Hi,<br>
<br>
When connecting with target_session_attrs=3Dstandby (or prefer-standby,<br>
read-only, any) and multiple standbys are available, libpq currently<br>
selects the first acceptable candidate without regard for how &quot;current=
&quot;<br>
its data is. A standby configured with recovery_min_apply_delay,<br>
experiencing slow I/O, or otherwise lagging is treated the same as one<br>
that is fully caught up.<br>
<br>
I would like to propose a new libpq connection parameter,<br>
max_wal_replay_size, that allows clients to skip standby servers whose<br>
WAL replay backlog exceeds a given threshold.<br>
<br>
Example:<br>
<br>
=C2=A0 psql &quot;host=3Dhost1,host2,host3 port=3D5111,5222,5333 \<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 target_session_attrs=3Dstandby max_wal_replay_s=
ize=3D16MB&quot;<br>
<br>
When this parameter is set, libpq executes a small query during<br>
connection establishment to evaluate:<br>
<br>
=C2=A0 pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())=
<br>
<br>
on the standby. If the result exceeds the specified threshold, the<br>
server is skipped and the next host in the list is tried. The check is<br>
skipped entirely when target_session_attrs is set to primary or<br>
read-write, since those modes already exclude standbys.<br></blockquote><di=
v><br></div><div>What if none of them meets the criteria? You fail the conn=
ection? Wouldn&#39;t it cause an availability issue?</div><div>=C2=A0</div>=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
If pg_last_wal_receive_lsn() is NULL (e.g. no active WAL receiver due to<br=
>
missing primary_conninfo or a disconnected upstream), the backlog cannot<br=
>
be determined. In that case, the standby is treated as exceeding the<br>
threshold and is skipped.<br></blockquote><div><br></div><div>When a standb=
y is replaying archiving log, it can still be caught up. This doesn&#39;t s=
eem right to me.</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex">
<br>
This parameter measures only the apply lag on the standby itself, i.e.,<br>
how much already-received WAL remains to be replayed. It does not<br>
attempt to measure how far the standby is behind the primary. In<br>
particular, a standby that is slow to receive WAL but fast to replay it<br>
may report a small backlog here while still being significantly behind.<br>=
</blockquote><div><br></div><div>IMHO, this change appears to not meet the =
objective of routing connections/queries to the most up-to-date standby.</d=
iv><div>=C2=A0</div><div>Thanks,</div><div>Satya</div></div></div>

--0000000000002fea6b064e2df36e--