Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: Connection pooler / LDAP auth / Load Balancing on read-only
 queries
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: <5b9df232-3c9e-ad5c-ffde-7d6b07452041@cloud.gatewaynet.com>
Date: Thu, 4 Jul 2024 06:47:47 -0600
Cc: pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <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>
To: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
Archived-At: 
 <https://www.postgresql.org/message-id/07B2F39D-E2A2-45C2-939D-85EF980176B1%40elevated-dev.com>
Precedence: bulk


> Hello
>=20
> 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.
>=20
> 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.)

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