Comment by zbentley
3 hours ago
> lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurrent connection.
Pg connections are definitely heavy, but usually on resources other than memory in my experience. If you configure reasonable dirty reclamation and recycling, the memory numbers are often overstated due to Linux tools’ deceptive fork accounting and shared buffers. Ofc, if you’re averaging lots of heavy queries per connection it’ll be truly heavy, but many times the numbers overstate the impact.
Each PG connection being a whole process does not scale like MSSQL that uses a thread per connection which has a max of 32k per instance.
There are no need for connection poolers in front of MSSQL although it is normal to pool connections in the client application which may hold hundreds open typically in a web server.
This also allows MSSQL to more easily share cached query plans between connections since its just sharing executable code between threads.
For PG to do plan caching it would need to serialize the plan between processes and that would require some significant work since it was never designed that way.
PG has it obvious unix roots using processes instead of threads, MSSQL coming from Windows where new process are expensive and there was no real fork, but threads are cheap uses that approach instead.