Message-ID: <1e2678b7-8ab5-c2d4-89e1-bcfe7ea8ddcb@cloud.gatewaynet.com>
Date: Thu, 4 Jul 2024 17:28:29 +0300
MIME-Version: 1.0
Subject: Re: Connection pooler / LDAP auth / Load Balancing on read-only
 queries
Content-Language: en-US
To: Scott Ribe <scott_ribe@elevated-dev.com>
Cc: pgsql-admin@lists.postgresql.org
References: <1745e084-a408-445f-97e3-44e29977ccc5@cloud.gatewaynet.com>
 <D20C9519-9AEB-411E-A49A-584CFB4678D5@elevated-dev.com>
 <5b9df232-3c9e-ad5c-ffde-7d6b07452041@cloud.gatewaynet.com>
 <07B2F39D-E2A2-45C2-939D-85EF980176B1@elevated-dev.com>
From: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
In-Reply-To: <07B2F39D-E2A2-45C2-939D-85EF980176B1@elevated-dev.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/1e2678b7-8ab5-c2d4-89e1-bcfe7ea8ddcb%40cloud.gatewaynet.com>
Precedence: bulk


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.