> The significance of the distinction depends entirely on the use case.

Indeed.

> Context switches are usually cheaper when you don’t need to throw out the old virtual address space (and invalidate the Translation Lookaside Buffer). Pthreads share virtual address space, so there is no need to flush the TLB.

I believe the cost of that has been reduced somewhat due to tagged TLBs on modern hardware.

> In a use case like Postgres, you don’t necessarily need to optimise for context switches. If you have a lot of concurrent connections, each of which has one process, then you’ll only hit limits with context switching overhead if very few of those connections are fighting over any locks or spending much time in IO at all. This is atypical, so usually those other factors hit you first.

Yea. There's a number of limitations in postgres due to the process model, but they're imo not TLB / context switch related. The biggest issue is that dynamically sharing memory between processes is harder, because there's no guarantee that it's possible for all post-fork memory allocations can portably be put at the same virtual addresses. Which then makes it more complicated to have shared datastructures, because you need to use relative pointers and such. That's not a problem for the main buffer pool etc, which is allocated when postgres is started, but it is problematic e.g. for memory shared between multiple processes working on the same query (say the memory for a shared hashtable in a hashjoin).