From: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
To: Scott Ribe <scott_ribe@elevated-dev.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Connection pooler / LDAP auth / Load Balancing on read-only queries
Date: Thu, 4 Jul 2024 17:28:29 +0300
Message-ID: <1e2678b7-8ab5-c2d4-89e1-bcfe7ea8ddcb@cloud.gatewaynet.com> (raw)
In-Reply-To: <07B2F39D-E2A2-45C2-939D-85EF980176B1@elevated-dev.com>
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>
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-M...
Read latency is an issue with asynchronous physical replication, but
then again, the problem is there no matter the HA/pooling solution.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: a.mantzios@cloud.gatewaynet.com, scott_ribe@elevated-dev.com, pgsql-admin@lists.postgresql.org
Subject: Re: Connection pooler / LDAP auth / Load Balancing on read-only queries
In-Reply-To: <1e2678b7-8ab5-c2d4-89e1-bcfe7ea8ddcb@cloud.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox