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

[Tatsuo Ishii]

> Hi pgpool hackers,
> 
> 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?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp