Date: Wed, 20 Aug 2025 21:45:37 +0900 (JST)
Message-Id: <20250820.214537.1156323467943836247.ishii@postgresql.org>
To: nadav@tailorbrands.com
Cc: pgpool-hackers@lists.postgresql.org
Subject: Re: Proposal: recent access based routing for primary-replica
 setups
From: Tatsuo Ishii <ishii@postgresql.org>
In-Reply-To: 
 <CACeKOO1-qront3LzcxOwjJBJz_jGYE9av4SrBa90SpTydPvY=Q@mail.gmail.com>
References: 
 <CACeKOO2E6cuCOQGFzq8i0+pFwi=JG4deiapHGkShjMjbn_-6tw@mail.gmail.com>
	<20250818.215106.1325564662459771705.ishii@postgresql.org>
	<CACeKOO1-qront3LzcxOwjJBJz_jGYE9av4SrBa90SpTydPvY=Q@mail.gmail.com>
Mime-Version: 1.0
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/20250820.214537.1156323467943836247.ishii%40postgresql.org>
Precedence: bulk

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