MIME-Version: 1.0
References: 
 <CACeKOO2E6cuCOQGFzq8i0+pFwi=JG4deiapHGkShjMjbn_-6tw@mail.gmail.com>
 <20250818.215106.1325564662459771705.ishii@postgresql.org>
 <CACeKOO1-qront3LzcxOwjJBJz_jGYE9av4SrBa90SpTydPvY=Q@mail.gmail.com>
 <20250820.214537.1156323467943836247.ishii@postgresql.org>
In-Reply-To: <20250820.214537.1156323467943836247.ishii@postgresql.org>
From: Nadav Shatz <nadav@tailorbrands.com>
Date: Wed, 20 Aug 2025 17:27:06 +0300
Message-ID: 
 <CACeKOO2t9hhwr82ksziBApBGZtEUn9KuXqS-bJB-=N7KRs6Acg@mail.gmail.com>
Subject: Re: Proposal: recent access based routing for primary-replica setups
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: pgpool-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000019a710063cccc543"
Archived-At: 
 <https://www.postgresql.org/message-id/CACeKOO2t9hhwr82ksziBApBGZtEUn9KuXqS-bJB-%3DN7KRs6Acg%40mail.gmail.com>
Precedence: bulk

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

Hi Tatsuo,

Thank you for your reply, I agree with your approach. Better to get (1) out
of the way first.

As a simplest approach that we can implement that would support completely
offloading the responsibility of the lag checking we can set it to =E2=80=
=9Cfile=E2=80=9D
and add another config for file path. Or just if starts with =E2=80=9Cfile:=
=E2=80=9D it=E2=80=99ll
understand.
Then the internal polling can just read the file on schedule. The entire
updating mechanism will be left to the external service.

Having this as a first step also opens up the door for other
implementations.

Another classic option would be calling an API endpoint. But that might
come with a lot more bulk and security concerns.

I suggest I work on a patch for file support.

What do you think?

Nadav Shatz
Tailor Brands | CTO


On Wed, Aug 20, 2025 at 3:45=E2=80=AFPM Tatsuo Ishii <ishii@postgresql.org>=
 wrote:

> Hi Nadav,
>
> Thank you for the answer.
>
> I think your proposal actually includes two orthogonal proposals.
>
> (1) "inject" replication delay value from external source (in your
> case from Aurora).
>
> (2) per relation recent access based routing.
>
> I suggest to implement (1) first, then (2). This incremental approach
> would be easier than implementing (1)+(2) at once.
>
> For (1) we could add new pgpool.conf parameter, say
> "replication_delay_source". If it is set to "builtin", then
> replication delay source is PostgreSQL as we already does today. If
> it's set other than "builtin", then it's an external command name (+
> arguments) to be executed to import replication delay value. The
> command should return replication delay value represented in strings
> like "0 20 10", which means node 0, 1 and 2 replication delay values
> in millisecond (in this case since the node 0 is primary, its
> replication delay is 0). The command will be invoked every
> sr_check_period.
>
> I am not sure if this actually works in Aurora. This is just a quick
> idea.
>
> (2) would be probably much harder than (1). So we need more discussion
> later on.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
>

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

<div dir=3D"auto">Hi=C2=A0Tatsuo,</div><div dir=3D"auto"><br></div><div dir=
=3D"auto">Thank you for=C2=A0your reply, I agree with your approach. Better=
 to get (1) out of the way first.=C2=A0</div><div dir=3D"auto"><br></div><d=
iv dir=3D"auto">As a simplest approach that we can implement that would sup=
port completely offloading the responsibility of the lag checking we can se=
t it to =E2=80=9Cfile=E2=80=9D and add another config for file path. Or jus=
t if starts with =E2=80=9Cfile:=E2=80=9D it=E2=80=99ll understand.=C2=A0</d=
iv><div dir=3D"auto">Then the internal polling can just read the file on sc=
hedule. The entire updating mechanism will be left to the external service.=
=C2=A0</div><div dir=3D"auto"><br></div><div dir=3D"auto">Having this as a =
first step also opens up the door for other implementations.=C2=A0</div><di=
v dir=3D"auto"><br></div><div dir=3D"auto">Another classic option would be =
calling an API endpoint. But that might come with a lot more bulk and secur=
ity concerns.=C2=A0</div><div dir=3D"auto"><br></div><div dir=3D"auto">I su=
ggest I work on a patch for file support.</div><div dir=3D"auto"><br></div>=
<div dir=3D"auto">What do you think?<br clear=3D"all"><br clear=3D"all"><di=
v dir=3D"auto"><div dir=3D"rtl" class=3D"gmail_signature" data-smartmail=3D=
"gmail_signature"><div dir=3D"ltr"><div><div dir=3D"ltr"><div dir=3D"rtl"><=
div dir=3D"ltr"><font style=3D"color:rgb(0,0,0)">Nadav Shatz</font></div><d=
iv dir=3D"ltr"><font style=3D"color:rgb(0,0,0)">Tailor Brands=C2=A0| CTO</f=
ont></div></div></div></div></div></div></div></div><div><br></div><div><br=
><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D=
"gmail_attr">On Wed, Aug 20, 2025 at 3:45=E2=80=AFPM Tatsuo Ishii &lt;<a hr=
ef=3D"mailto:ishii@postgresql.org">ishii@postgresql.org</a>&gt; wrote:<br><=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left-width:1px;border-left-style:solid;padding-left:1ex;border-left-co=
lor:rgb(204,204,204)">Hi Nadav,<br>
<br>
Thank you for the answer.<br>
<br>
I think your proposal actually includes two orthogonal proposals.<br>
<br>
(1) &quot;inject&quot; replication delay value from external source (in you=
r<br>
case from Aurora).<br>
<br>
(2) per relation recent access based routing.<br>
<br>
I suggest to implement (1) first, then (2). This incremental approach<br>
would be easier than implementing (1)+(2) at once.<br>
<br>
For (1) we could add new pgpool.conf parameter, say<br>
&quot;replication_delay_source&quot;. If it is set to &quot;builtin&quot;, =
then<br>
replication delay source is PostgreSQL as we already does today. If<br>
it&#39;s set other than &quot;builtin&quot;, then it&#39;s an external comm=
and name (+<br>
arguments) to be executed to import replication delay value. The<br>
command should return replication delay value represented in strings<br>
like &quot;0 20 10&quot;, which means node 0, 1 and 2 replication delay val=
ues<br>
in millisecond (in this case since the node 0 is primary, its<br>
replication delay is 0). The command will be invoked every<br>
sr_check_period.<br>
<br>
I am not sure if this actually works in Aurora. This is just a quick<br>
idea.<br>
<br>
(2) would be probably much harder than (1). So we need more discussion<br>
later on.<br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS K.K.<br>
English: <a href=3D"http://www.sraoss.co.jp/index_en/" rel=3D"noreferrer" t=
arget=3D"_blank">http://www.sraoss.co.jp/index_en/</a><br>
Japanese:<a href=3D"http://www.sraoss.co.jp" rel=3D"noreferrer" target=3D"_=
blank">http://www.sraoss.co.jp</a><br>
</blockquote></div></div>

--00000000000019a710063cccc543--