a) You can't hold 5000 open connections out of which 4900 are idle without performance impact.
b) Opening a new connection takes way longer than connecting through pgBouncer running on a differnt(!) box.
It would be nice if memory was the only drawback.
Author here. I agree. In fact this blog post only exists because a blog post on analyzing the various bottleneck around connection scalability in postgres was getting too long, so I split it out... That post has numbers showing the slowdowns: https://techcommunity.microsoft.com/t5/azure-database-for-po...
There's a significant amount of improvements in Postgres' development branch addressing the - in my opinion - major source for the performance impact of lots of idle connections.
> b) Opening a new connection takes way longer than connecting through pgBouncer running on a differnt(!) box.
Hm. How long is way longer? And how high is shared buffers on that instance? In tests it's pretty easy to reach a few thousand connection establishments/second. That's not great, but also not terrible.
If the instance is otherwise very busy (e.g. due to the above issue), it can make connecting very slow, due to contention on very important locks.
A TCP connection is really lightweight, when stripped down enough. Thousands of connection establishments per second per core is not actually gonna break a sweat.
> while simply copying the shared buffers table into a new process represents a real overhead for postgres proper.
You mean the page table being large due to the large shared memory allocation? With huge pages that's not that large anymore (132KB for 16GB of s_b). My impression that with huge pages the problem moves to be primarily the locking around the huge pages when a lot of processes constantly fork and exit, than the actual amount of copying.
You’re definitely right that hugepages make the allocation pretty small, but it still has to walk the page table to copy it; I’ve seen that be a real bottleneck in apps. Then again, I might be misremembering; The problems could have been simply from having a huge number of 4k pages to walk. What I’m trying to say is: A fork() call can be relatively cheap, but is rarely as cheap as an accept().
The biggest limiting factor in my experience is actually Work Mem. You can reduce it quite a bit from the default (which I was remembering as 10MB/connection, though apparently the default is 4MB; it might be AWS RDS Defaults that I had in my head). Even halving that, to 2MB, it still presents a significant overhead. A thousand connections takes two gigs of memory - Obnoxious, but workable. Ten thousand? Now you’re biting significantly into the size of your working set.
Agreed, obviously. While I personally am not that concerned with the cost of connection establishment, and much more concerned with the context switches in a threaded / process model, the fix for the latter would also likely fix the former. I think there's a few higher priority issues in PG (some are prerequisites too), but it's somewhere in the top 5 issues
> The biggest limiting factor in my experience is actually Work Mem.
For me it hasn't been that big an issue in practice. It's transient memory usage, i.e. it's only used in the backends processing queries, not idle ones. And many - but not all! - cases where you have a huge number of connections most queries are simple, and use a good bit less than 2MB.
There are a fair number of issues with work_mem, don't get me wrong. But more around it being used several times in more complex queries, than the simple fact of using some memory for query execution. And it being hard to limit the number of concurrent queries rather than the number of connections.
I see no downside to doing this and you can probably get it done in <1000 lines of code, most pgBouncer features aren't needed.
Unfortunately there are some kernel tuneables (buffer sizes) which you have to set at compile time to support massive data transfers on sockets.
Obviously this only works if you are on the same machine and if you connect directly to Postgres this way, it will happily spawn a process for every query :(.
https://techcommunity.microsoft.com/t5/azure-database-for-po...