|
|
|
|
|
|
by CraigJPerry
28 days ago
|
|
|
>> 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? |
|
|
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