PG pre-dates (working) threads in Unix by a long time. Processes and threads are much the same thing as far as concurrency and parallelism are concerned. PG maintains a pool of pre-forked processes so there is typically no process startup delay. Shared memory regions and SysV cross-process locks facilitate IPC. Back in the day this is how threads were done in user-land:
> PG maintains a pool of pre-forked processes so there is typically no process startup delay.
Nope, we don't. You can use an external connection pooler like pgbouncer to achieve that however.
> Shared memory regions and SysV cross-process locks facilitate IPC. Back in the day this is how threads were done in user-land:
Well, that's how IPC is/was done in case of multiple processes. I'd not call that threading however, that'd imo require at least a single process space (i.e. shared virtual memory space).
The big advantage of processes is increased isolation. I.e. problems in one process are much likely to affect others, there's less locking required (e.g. in the memory allocator for local memory allocations, internally for mmaps and such). The big disadvantage is that dynamically scaling the amount of shared memory is quite ugly in multi-process models. It's hard to portably allocate shared memory after the fact and guarantee it's mapped at the same address in all processes, thereby making pointer usage hard/impossible.
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:
That is correct Linux treats Process & threads as identical tasks.
I was thinking about control/data location. We had this three tier achitecturr, which is becoming 2-tier. I am seeing more control co-located with data for more optimal use. It will be useful to have coroutine like supprt, if someone integrates the whole Middleware layer inside postgres.
I am more familar with MySQL as well, but I heard that Postgres uses forks, wheresas MySQL uses threads. So for heavy load (many users) like with public websites, threads or thread-pools scale better. Can someone tune in and answer how you handle such a workload with Postgres? Maybe some cache or worker-pool in front of the DB?
They're just too useful in helping to spread the load of all the IO required to return even one result.