However, if pooling isn’t used, there’s always an overhead (tens of milliseconds or more) when creating a new connection because Postgres needs to fork a process. And yes, applications can be written without pooling, which isn’t ideal, but happens quite a lot.
Application frameworks have also changed. Serverless architectures can generate tens of thousands of connections, which is where Postgres starts to run into issues. I’m personally not a big fan of using more than a few hundred connections, but it is very realistic in this era.
Why Postgres should be doing this?
Not every client creates a lot of connections and spinning up PgBouncer is easy.
On the other hand, debugging async multithreaded complex code is hard.
Postres project also once lacked replication, calling it unnecessary to the core effort. Now it has two means of replication in core and I'd argue is better for it, especially after suffering through both Pgpool and Slony.
I hope they do develop a native, threaded pooling, even if it were incompatible with some libraries or extensions.
You were coming into the field just as companies were fielding their first reasonable answers to the Threading Model that Java put forward, which was sort of Windows' but with extra features. Even Solaris choked on Java. HP UX did worse and I can't recall if SGI was worse or better than HP. But getting compatible with Java shook a lot of companies up, in how they handled concurrency.
Postgres and SQLite were being designed at that same time but by industry veterans. People who had been deploying high load systems before any of this threading nonsense was around. And they were supporting people running on old hardware.
Yeah, I remember writing epoll libraries for Perl (https://metacpan.org/pod/Sys::Syscall, first out 2005-08-01) doing raw system calls because libc on the distros of the time (at least Debian) didn't have epoll support yet.
So in 2005 I didn't expect Postgres to do super well here, but it's 21 years later and we're still pgbouncin'. It's just kinda sad.
However, if pooling isn’t used, there’s always an overhead (tens of milliseconds or more) when creating a new connection because Postgres needs to fork a process. And yes, applications can be written without pooling, which isn’t ideal, but happens quite a lot.
Application frameworks have also changed. Serverless architectures can generate tens of thousands of connections, which is where Postgres starts to run into issues. I’m personally not a big fan of using more than a few hundred connections, but it is very realistic in this era.