On 16 November 2015 at 18:44, Simon Riggs <[email protected]> wrote:
>
> The pooler knows which statements are reads and writes
>
I think that's an iffy assumption. It's one we tend to make because
otherwise read/write pooling won't work, but in PostgreSQL there's really
no way to know when calling a function.
What does
SELECT get_user_stats()
do? The pooler has _no_ _idea_ unless manually configured with knowledge
about particular user defined functions.
In the absence of such knowledge it can:
- send the work to a replica and report the ERROR to the user if it fails
due to an attempted write;
- send the work to a replica, capture an ERROR due to attempted write, and
retry on the master;
- send everything it's not sure about to the master
If a pooler had insight into the catalogs and if we had readonly /
readwrite attributes on functions, it could be smarter.
> I would like to see a load balancing pooler in Postgres.
>
Given the number of times I say "no, no, don't raise max_connections to
2000 to solve your performance problems, lower it to around 100 and put
pgbouncer in front if your application doesn't support connection pooling
internally" .... yes!
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
