There is Proxy/Connector approaches - ProxySQL, pgPool etc solve this problem in a different way and often is best approach but I still think it is very interesting to see how much the database itself can handle.
Meanwhile, session pooling mode provides almost no benefit unless you completely disable connection pooling in your application, which then means you incur the cost of setting up a new connection on each request.
MySQL's ability to handle many thousands of idle connections without breaking a sweat actually solves this problem, and does so in a very convenient way, particularly in a micro-services architecture, and I really wish Postgres supported it.
There's several PG developers aware of this, and hope to work on this. Getting the resources to work on it isn't trivial, but looks like it might happen. Obviously that's not going to help you short term...
My understanding was that the difficulty here was Postgres's currently very simple forked-process-per-connection model. In order for idle connections to take minimal resource, connections can no longer be mapped 1:1 with processes. Instead, active connections will have to be balanced across a process or thread pool, whilst idle connections' states will have to be stored in a master process.
Not only that, but it must be possible to transmit a full connection state between processes, and also to queue-up idle connections transitioning to the active state if there is a sudden burst exceeding the size of the process pool.
Right, but the problem is that the cost to compute them essentially scales with the number of active connections (and to some degree with the number of allowed connections). Which means if your system actually does things, all those idle connections cost. We can, and should, make the constant factor for computing this considerably smaller. But obviously larger architectural issues need to be addressed too, at some point..
Edit: expand.
Named prepared statements via PREPARE also work, but the client needs to use deterministic names and rescue if the query isn’t prepared: https://github.com/diesel-rs/diesel/issues/1028#issuecomment...
In any case, DBMS can usually scale vertically relatively well (lots of CPU, Ram, NVME etc).