On 16 November 2015 at 18:44, Simon Riggs <si...@2ndquadrant.com> 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.
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