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!
