Re: Connection pooler / LDAP auth / Load Balancing on read-only queries

On 7/4/24 15:47, Scott Ribe wrote:
>> Hello
>>
>> pgpool does a great job at that. pgpool does load balancing to the first statement that is considered a write
statement,for that point on, the rest of the transaction is routed to the primary.
 
>>
>> But the question here is how to achieve all of the aforementioned features, of all those great tools combined, or
ideallycombined in one.
 
> AFAIK, pgpool still doesn't do what I'd consider true pooling, that is MxN multiplexing of connections. (Every client
connectionhas a connection from pgpool -> server, but the server connections become available for resuse when clients
disconnect.)
Yes, unfortunately, pgbouncer shines in this department.
>
> And to me, the mechanism for routing queries in a transaction is highly suspect, as the initial reads vs later writes
couldpotentially be using different snapshots of the data. (There is a safeguard there, involving looking at
replicationdelay, but that's not a transactional guarantee, just "here's how out of date the replica can be to get read
queries".)

It seems pgpool supports snapshot_isolation mode, which is similar to 
streaming_replication, + it adds visibility consistency, but at the 
expense of running with default_transaction_isolation = 'repeatable 
read' : 
https://www.pgpool.net/docs/latest/en/html/runtime-config-running-mode.html#GUC-SNAPSHOT-ISOLATION-MODE

Read latency is an issue with asynchronous physical replication, but 
then again, the problem is there no matter the HA/pooling solution.