Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uozsw-00ENG5-M2 for pgpool-hackers@arkaria.postgresql.org; Thu, 21 Aug 2025 07:39:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uozsv-00EsZa-P9 for pgpool-hackers@arkaria.postgresql.org; Thu, 21 Aug 2025 07:38:58 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uozsv-00EsZS-ES for pgpool-hackers@lists.postgresql.org; Thu, 21 Aug 2025 07:38:58 +0000 Received: from mail-yw1-x1131.google.com ([2607:f8b0:4864:20::1131]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uozss-000ywe-0B for pgpool-hackers@lists.postgresql.org; Thu, 21 Aug 2025 07:38:56 +0000 Received: by mail-yw1-x1131.google.com with SMTP id 00721157ae682-71e6eb6494eso6022137b3.3 for ; Thu, 21 Aug 2025 00:38:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tailorbrands.com; s=google; t=1755761934; x=1756366734; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=RNVyQhi4g5Qu+qCix48KKeGthmg1q3jR/4RyPOYX7Is=; b=C+3RbnkXUfgBE00Bexo9BSuYTiFYw/DlS01U+RqDzrFFnwoQwVFm8yqo+g8PZT88f1 cJdLO2KHXcjd87wkI9m6OjJYOw4LeHPMO6Y8Eo8bhgeCbxuoRAlzteF/aDQjDcvD2j2L 2mdoDafA44oUUMwm1AaEf+BgRFMgsIin1SwPla1z4EDastllYN4v6qhtWY9puFzuO4Hd liQst8uPsfqob5P7bwAFT6ufLlHmDFWZXXvFgULqY3hdympexmpF3jv8v7HJ9sG1jTKC Y4LTOUiAXUSuwWabskbmzALLa56E5SSlRT86OYHKyM7M5/rF4IJSHfnFooPA6cCvOTK7 AloQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1755761934; x=1756366734; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=RNVyQhi4g5Qu+qCix48KKeGthmg1q3jR/4RyPOYX7Is=; b=dSIcE8/wmCv8KCr9a7PTcVPDARkXs8li6g0ZLvKBXIMZc4UJkFpr8FleYZpVaUy+IG EaLBt2gG9tRlC1Czko259ljXG1XilTspl6qxDKsdweHmBWN062nzYPpCF7xBtykMMXPg 0MtXUa0YY4sU3btPi7uF5sX48m5ifM8oAHQ3H04fGrI+837cKWS3KmSvkkg/k8Dhyk1V DrQN7mj0AANbp5cbvBPp7WIcV+HIGIzXm4/NvOIJpCKUf12lKyhOkVdnkBD4RMi5ipEe SupadSG8Gc73hZ322ZziBql4CWduRUxjrjrZDC1/GoTczFcOQXYjKg/y6qPB8oY0N8w+ GbrA== X-Gm-Message-State: AOJu0YzT3EkAoQn9BTDRwkeibJsxcabpuJVIazoj9skScC0cj7G2h+DO d55QepH4m3hD/3rC9eE6Hd72Vhm9+xUwbyJOoGbcHP/38rM3abdZtLh2HQoi3WrSZJPzNuP2sXQ JrVlZUvOAxk/DBydV1pyZkbeZy4SxudHA9r/OXAVwVA== X-Gm-Gg: ASbGncsDmozSx9dPOyXUmrBXs5pow6QAiJAoqASLjTTTOioAP4gBmKJ1xrp2N1SNHec oK+uzCHe2RAPWAGVCKZU7w9S3VNCgxcGvfsTVDS3pknBgnMUsyR6AsQBtqOn4qQ/PlIWG3UhEVy 3PwtUS/Fksl1jZhCV2n9U2atDWq/+O+wtVwy59RY/GAKej0laZ9fnop2REZutp5OSMydSd/M+lc 904CNMkQIYc5oTF X-Google-Smtp-Source: AGHT+IFtkTJ9o+x8+4uZoZZpXAh2oW9sqo9cfXv6yCdhr5OYSRLfIcdqrwMTG4gz0HKMfW00wRSXvzslDPOTJ1Dt1TA= X-Received: by 2002:a81:fa04:0:b0:71f:9370:f522 with SMTP id 00721157ae682-71fc883f0bamr16654257b3.2.1755761933793; Thu, 21 Aug 2025 00:38:53 -0700 (PDT) MIME-Version: 1.0 References: <20250820.214537.1156323467943836247.ishii@postgresql.org> <20250821.140434.533994102324088664.ishii@postgresql.org> In-Reply-To: <20250821.140434.533994102324088664.ishii@postgresql.org> From: Nadav Shatz Date: Thu, 21 Aug 2025 10:38:42 +0300 X-Gm-Features: Ac12FXwmoBG4_Hkj5FFNyru0dftD2TEiHQ-BTsa6vRxzUHp2yd4olpHHFJUifhI Message-ID: Subject: Re: Proposal: recent access based routing for primary-replica setups To: Tatsuo Ishii Cc: pgpool-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000005fe376063cdb2e13" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005fe376063cdb2e13 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Tatsuo, I'm fine with all of your comments and suggestions. I'll work on a patch and we can iterate over it. Hope that's okay. Best, On Thu, Aug 21, 2025 at 8:04=E2=80=AFAM Tatsuo Ishii = wrote: > Hi Nadav, > > > 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=9Cf= ile:=E2=80=9D > it=E2=80=99ll > > understand. > > My concern about the "file:" approach is, race condition. What if > pgpool reads the file while it is being updated by someone else? Also > I think the command approach is more flexible and generic. For > example, the "file approch" can be easily simulated by setting the > command "/usr/bin/cat path_to_the_file". > > > Then the internal polling can just read the file on schedule. The entir= e > > updating mechanism will be left to the external service. > > Internal polling is a little bit complicated and will not be easily > changed to just reading a file. The internal polling has two options: > one is checking WAL LSN difference, the other is replication delay in > time. The file approch would only replace the latter. I suggest to > leave the internal polling code as it is. > > > 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 agree that calling API could bring security concerns. > > BTW, in the command approch, the command should be executed as > sr_check_user. > > > I suggest I work on a patch for file support. > > > > What do you think? > > For the reason above I prefer the command approch, not the file > support. > > > Nadav Shatz > > Tailor Brands | CTO > > > > > > On Wed, Aug 20, 2025 at 3:45=E2=80=AFPM Tatsuo Ishii > 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 > >> > --=20 Nadav Shatz Tailor Brands | CTO --0000000000005fe376063cdb2e13 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0Tatsuo,

I'm fine with all o= f your comments and suggestions.

I'll work on = a patch and we can iterate over it.

Hope that'= s okay.

Best,

On Thu, A= ug 21, 2025 at 8:04=E2=80=AFAM Tatsuo Ishii <ishii@postgresql.org> wrote:
Hi Nadav,

> 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 comple= tely
> 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=9C= file:=E2=80=9D it=E2=80=99ll
> understand.

My concern about the "file:" approach is, race condition. What if=
pgpool reads the file while it is being updated by someone else?=C2=A0 Also=
I think the command approach is more flexible and generic. For
example, the "file approch" can be easily simulated by setting th= e
command "/usr/bin/cat path_to_the_file".

> Then the internal polling can just read the file on schedule. The enti= re
> updating mechanism will be left to the external service.

Internal polling is a little bit complicated and will not be easily
changed to just reading a file. The internal polling has two options:
one is checking WAL LSN difference, the other is replication delay in
time. The file approch would only replace the latter. I suggest to
leave the internal polling code as it is.

> 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 migh= t
> come with a lot more bulk and security concerns.

I agree that calling API could bring security concerns.

BTW, in the command approch, the command should be executed as
sr_check_user.

> I suggest I work on a patch for file support.
>
> What do you think?

For the reason above I prefer the command approch, not the file
support.

> Nadav Shatz
> Tailor Brands | CTO
>
>
> On Wed, Aug 20, 2025 at 3:45=E2=80=AFPM Tatsuo Ishii <ishii@postgresql.org> w= rote:
>
>> Hi Nadav,
>>
>> Thank you for the answer.
>>
>> I think your proposal actually includes two orthogonal proposals.<= br> >>
>> (1) "inject" replication delay value from external sourc= e (in your
>> case from Aurora).
>>
>> (2) per relation recent access based routing.
>>
>> I suggest to implement (1) first, then (2). This incremental appro= ach
>> 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 "builti= n", then
>> replication delay source is PostgreSQL as we already does today. I= f
>> it's set other than "builtin", then it's an exte= rnal command name (+
>> arguments) to be executed to import replication delay value. The >> command should return replication delay value represented in strin= gs
>> 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 qui= ck
>> idea.
>>
>> (2) would be probably much harder than (1). So we need more discus= sion
>> later on.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS K.K.
>> English: http://www.sraoss.co.jp/index_en/
>> Japanese:http://www.sraoss.co.jp
>>


--
Nadav Shatz
<= font color=3D"#000000">Tailor Brands=C2=A0| CTO
--0000000000005fe376063cdb2e13--