Considering the huge engineering teams SqlServer and Oracle have, I'm always amazed how well Postgres works - despite the tiny number of full time developers.
To get around the inefficiencies of spawning many processes, I put pgbouncer in front of PostgreSQL.
isn't this still processes, just with shared memory?
One pro of non-threaded is simplicity and no need for thread safety everywhere.
This introduces lot's of overhead across the board.
* Cross process communication is a lot more expensive (done via shared memory or as here via the file system)
* Switching between processes is a lot more expensive because each process has its own memory space, hence a switch flushes the TLB. Also more bookkeeping for the OS.
This is especially bad for a DB, which will usually spend most of its time waiting for IO, so can switch execution context all the time.
* Each process also has a distinct set of file descriptors, so those need to be cloned as well
* A dB needs lots of locks. Cross process locks are more expensive.
* ...
These things add up.