Re: Proposal: recent access based routing for primary-replica setups

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 “file”
and add another config for file path. Or just if starts with “file:” it’ll
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 PM Tatsuo Ishii <ishii(at)postgresql(dot)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
>