I'm not sure it's that critical. You can use connection pooling on client and/or you can outsource it to a proxy/bouncer that will do this for you.

We have tens of thousands of connections and no problems. We also forked pgbouncer to use multicore [0] which allows us properly utilize servers.

[0] https://github.com/Pexeso/pgbouncer-smp

PGBouncer doesn't do anything to magically prevent connections to postgres from being locked and idle for a pending query.

It's just an external connection pooler when your native language driver doesn't have a good one

pgb can timeout connections and close them if they are idle for too long, which is usually the way to deal with stale connections.

Not sure what solution are you imagining would be the right one.

Examine any other distributed system that implements pipelining. Allows for multiple pending requests on a connection (simply by having IDs on every request) which makes for efficient use of connections.

I do understand where are you coming from, but there is price to everything.

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, ...

That's really cool! But why don't you contribute back this code to pgbouncer? :)

Oh, we tried. Believe me, we tried many times. Original pgb creator didn't bother to respond to any of my emails.

That's because connections (sessions, really, but in practice most things map those 1:1) are the primitives on which Postgres and most other RDBMSes divide their units of transactional consistency--one of their key guarantees. Connections are expensive to create and maintain largely (but not entirely) because of those properties.

This isn't a required design to achieve transactional consistency. See for example how this would be done in Erlang - single connection dispatching to isolated Erlang processes.

Fundamentally just needs isolation to be done at a different layer.

Why not just open another connection?

Each open postgres connection requires about 10mb of memory overhead IIRC

That's why you need a connection pool. That's true for all database systems.

Not all databases systems require a connection pool, though it is often a good idea.

I disagree. I've faced incidents on oracle/sql server/postgres/sybase instances when several hundreds of connections where spawned.

You definitely need to think about pooling fro the beginning of the design of your application.

Yes, all of those databases allocate query memory for queries at connection time, and consequently don't scale efficiently for large number of idle clients. That's not all databases. Some don't do that. Some don't even do connections period.

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.