This seems somehow problematic: a DB trying to meet modern performance requirements by relying on how it was done over 12 years and 2 major kernel versions ago.
Even MySQL makes judicious use of threads for managing periodic tasks and parallel data parsing.
Per-connection processes does make some sense to me, but it seems wasteful when most connections to a DB are idle most of the time. Having to coordinate locks cross-process also seems wasteful; more syscalls and context switches than should be necessary.
> Per-connection processes does make some sense to me, but it seems wasteful when most connections to a DB are idle most of the time.
Per-process vs per-thread overhead isn't that different in e.g. linux. Some things are more expensive with multiple processes (more page tables/more wasted space/increased process switch cost), others are cheaper (e.g. memory allocation, although that's getting better over the last few years).
> Having to coordinate locks cross-process also seems wasteful; more syscalls and context switches than should be necessary.
I don't think there's a meaningful difference here. We use atomic operations for the non-sleeping lock paths (which'd not be different in threads) and for sleeping locks when we need to sleep, we use semaphores for directed wakeups - but you'd need something similar for threads as well.
Really, the majority of the cost of threading is when you explicitly want to share more state, after processes have initialized. It's e.g. a lot harder to dynamically scale the size of the buffer pool up/down. It's also one of the things that made intra-query parallelism harder.
> This seems somehow problematic: a DB trying to meet modern performance requirements by relying on how it was done over 12 years and 2 major kernel versions ago.
A newly developed product made today would use threads but we're not talking about a new product. Once the effort has been expended to make processes and shared memory work, especially for a product with a fairly constrained scope like a DB server, the argument becomes about whether it would be worthwhile re-writing today to target threads. So far for PG the answer is : no.
I think you are making assumptions on the basis that "threads == progress == better" that are not necessarily valid. For example do you have benchmark evidence that in-process locking would deliver significantly better performance for PG under workloads of interest vs the current XP locking scheme? It might, but I doubt it.
Here's a discussion on the topic that's only 16 years old:
> Back in the day
This seems somehow problematic: a DB trying to meet modern performance requirements by relying on how it was done over 12 years and 2 major kernel versions ago.
Even MySQL makes judicious use of threads for managing periodic tasks and parallel data parsing.
Per-connection processes does make some sense to me, but it seems wasteful when most connections to a DB are idle most of the time. Having to coordinate locks cross-process also seems wasteful; more syscalls and context switches than should be necessary.