Re: Let's make PostgreSQL multi-threaded

[snip]

I think we're starting to hit quite a few limits related to the process model,
particularly on bigger machines. The overhead of cross-process context
switches is inherently higher than switching between threads in the same
process - and my suspicion is that that overhead will continue to
increase. Once you have a significant number of connections we end up spending
a *lot* of time in TLB misses, and that's inherent to the process model,
because you can't share the TLB across processes.

IMHO, as one sysadmin who has previously played with Postgres on "quite large" machines, I'd propose what most would call a "hybrid model"....

* Threads are a very valuable addition for the "frontend" of the server. Most would call this a built-in session-aware connection pooler :)

    Heikki's (and others') efforts towards separating connection state into discrete structs is clearly a prerequisite for this; Implementation-wise, just toss the connState into a TLS[thread-local storage] variable and many problems just vanish.

    Postgres wouldn't be the first to adopt this approach, either...

* For "heavyweight" queries, the scalability of "almost independent" processes w.r.t. NUMA is just _impossible to achieve_ (locality of reference!) with a pure threaded system. When CPU+mem-bound (bandwidth-wise), threads add nothing IMO.

Indeed a separate postmaster is very much needed in order to control the processes / guard overall integrity.

Hence, my humble suggestion is to consider a hybrid architecture which benefits from each model's strengths. I am quite convinced that transition would be much safer and simpler (I do share most of Tom and other's concerns...)

Other projects to draw inspiration from:

 * Postfix -- multi-process, postfix's master guards processes and performs privileged operations; unprivileged "subsystems". Interesting IPC solutions
 * Apache -- MPMs provide flexibility and support for e.g. non-threaded workloads (PHP is the most popular; cfr. "prefork" multi-process MPM)
 * NginX is actually multi-process (one per CPU) + event-based (multiplexing) ...
 * PowerDNS is internally threaded, but has a "guardian" process. Seems to be evolving to a more hybrid model.

I would suggest something along the lines of :

* postmaster -- process supervision and (potentially privileged) operations; process coordination (i.e descriptor passing); mostly as-is
* frontend -- connection/session handling; possibly even event-driven
* backends -- process heavyweight queries as independently as possible. Can span worker threads AND processes when needed
* dispatcher -- takes care of cached/lightweight queries (cached catalog / full snapshot visibility+processing)
* utility processes can be left "as is" mostly, except to be made multi-threaded for heavy-sync ones (e.g. vacuum workers, stat workers)

For fixed-size buffers, i.e. pages / chunks, I'd say mmaped (anonymous) shared memory isn't that bad... but haven't read the actual code in years.

For message queues / invalidation messages, i guess that shmem-based sync is really a nuisance. My understanding is that Linux-specific (i.e. eventfd) mechanisms aren't quite considered .. or are they?

The amount of duplicated code we have to deal with due to to the process model
is quite substantial. We have local memory, statically allocated shared memory
and dynamically allocated shared memory variants for some things. And that's
just going to continue.

Code duplication is indeed a problem... but I wouldn't call "different approaches/solution for very similar problems depending on context/requirement" a duplicate. I might well be wrong / lack detail, though... (again: haven't read PG's code for some years already).

Just my two cents.

Thanks,

    J.L.

-- 
Parkinson's Law: Work expands to fill the time alloted to it.