Postgres' inability to handle multiple concurrent queries on a single connection makes it a pain when using it for multi-tenant services. With all the SAAS companies out there I'm surprised this issue doesn't get brought up more often
We have tens of thousands of connections and no problems. We also forked pgbouncer to use multicore [0] which allows us properly utilize servers.
It's just an external connection pooler when your native language driver doesn't have a good one
Not sure what solution are you imagining would be the right one.
Most distributed systems have eventual consistency. Very few can achieve strong consistency. Postgres sacrifices a lot for a strong consistency and predictable performance.
I'm not saying that PG has the best model, but for the wast majority of users, out of connections is just not an issue. Most clients have now direct pooling and they are very easy to setup. There are a few who need more connections, but there are poolers for it.
What you gain but separating connections is much easier monitoring and debugging process. You can examine each connection, its impact on resources, state, what locks they need to acquired, ...
Additionally, because each connection opens a file descriptor, you gain a lot of operating security from the underlying OS and its kernel.
PG contributors spent almost 3 decades building on this system. I'm pretty sure the gain would be so minuscule in comparison to the effort that would have to be put in to rebuild the connection model.
I for one would appreciate server polling built in, better HA including simple discoverability, handoffs, ...
Fundamentally just needs isolation to be done at a different layer.
You definitely need to think about pooling fro the beginning of the design of your application.
Connection pools are a necessary work around for a specific design decision with the system software. They can be helpful other problems as well, but they aren't necessarily a requirement.