[pgpool-hackers: 1592] Re: Load balancing with synchronous replication

[Thomas Munro]

On Thu, May 26, 2016 at 9:10 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> If you use synchronous_commit = remote_apply in PostgreSQL 9.6, you
>> can get a certain sort of guarantee about being able to see
>> transactions that you know have committed.  Do you think there should
>> be some way for pgpool to do load balancing of reads on nodes that are
>> currently listed as 'sync' in pg_stat_replication.sync_state on the
>> master?
>>
>> Maybe it is difficult to match up the standbys pgpool knows about to
>> the rows in pg_stat_replication, and unfortunately the standbys don't
>> know themselves whether the master considers them to be 'sync' (ie
>> whether it waits for them for sync rep).  Maybe it is not necessary to
>> make any changes at all, and users who want to do this should simply
>> know which standbys should be 'sync' based on the configuration of
>> PostgreSQL and pgpool.
>>
>> There is also of course a small possibility that a standby is listed
>> as 'sync' now even though it wasn't a sync standby when the
>> transaction you care about was committed, around the time of network
>> topology changes: some higher priority server has been dropped, and
>> this standby has been promoted to 'sync', but that doesn't guarantee
>> that it has applied any particular transaction yet.  But that case
>> should be rare.  (I am planning to (re)submit a patch for 10.0 that
>> makes it possible for the *standby* to know if it can guarantee
>> visibility of all earlier committed transactions, and for the
>> guarantee to work even during transitions when standbys are added and
>> dropped etc, which I hope will be more interesting for load balancing
>> tools.)
>
> I think problem with your idea is the overhead for pgpool-II to ask
> status to PostgreSQL. This inquery will happen with every single query
> sent from clients to pgpool-II, and that could cause visible performance
> degration. What do you think?

Yeah, I didn't mean for every query.  I suppose something periodic
like sr_check_period.

-- 
Thomas Munro
http://www.enterprisedb.com