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 ideally combined in one.
> AFAIK, pgpool still doesn't do what I'd consider true pooling, that is MxN multiplexing of connections. (Every client connection has 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 could potentially be using different snapshots of the data. (There is a safeguard there, involving looking at replication delay, 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.