[pgpool-hackers: 1588] Load balancing with synchronous replication

[Thomas Munro]

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.)

Thanks,

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