[SigmundA]

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.

[CraigJPerry]

>> Each PG connection being a whole process does not scale like MSSQL that uses a thread per connection

There's no free lunch id think, the PG model is more robust. Unsafe extensions can take down the whole instance in the threaded model, processes contain the blast radius to that connection (also typically easier to debug since this type of issue is thankfully rare, it's also gnarly to get on top of).

Further, on linux (not on windows) a lot of the lines between a thread and a process get blurry (copy on write, shared memory mappings etc). They're both handled very similarly in the kernel, theyre both scheduled using similar machinery.

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

Is that true? I'm thinking the buffer cache and locks and WAL coordination are just as fast - it's just mmap'd SHM into each process. It's not like every access needs IPC?

[SigmundA]

>Unsafe extensions can take down the whole instance in the threaded model, processes contain the blast radius to that connection.

MSSQL has its own task scheduler with the ability to kill etc, its like its own OS internally in that respect but yes there is no memory protection there for unmanaged code.

It doesn't really have unsafe extensions other than it is possible to create extended stored procedures which could do bad things, very frowned upon. If you were concerned about that you would run that database in its own instance (process).

https://learn.microsoft.com/en-us/sql/relational-databases/s...

https://learn.microsoft.com/en-us/sql/t-sql/language-element...

https://learn.microsoft.com/en-us/sql/relational-databases/e...

>I'm thinking the buffer cache and locks and WAL coordination are just as fast - it's just mmap'd SHM into each process.

Plan caches contain pointers to executable code and sometimes jitted code, this cannot be just shared to another process with a different address space and work, this is code not data, to transport between processes it needs to be serialized in a way that is not tied to process memory including things like ASR, certainly doable but since it wasn't designed that way much needs to be changed vs just sharing between threads.

https://en.wikipedia.org/wiki/Position-independent_code