Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 14, 2023 at 3:16 PM James Addison <jay@jp-hosting.net> wrote:
> I think that they're practical performance-related questions about the
> benefits of performing a technical migration that could involve
> significant development time, take years to complete, and uncover
> problems that cause reliability issues for a stable, proven database
> management system.

I don't. I think they're reflecting confusion about what the actual,
practical path forward is.

For a first cut at this, all of our global variables become
thread-local. Every single last one of them. So there's no savings of
the type described in that email. We do each and every thing just as
we do it today, except that it's all in different parts of a single
address space instead of different address spaces with a chunk of
shared memory mapped into each one. Syscaches don't change, catcaches
don't change, memory copying is not reduced, literally nothing
changes. The coding model is just as it is today. Except for
decorating global variables, virtually no backend code needs to notice
or care about the transition. There are a few exceptions. For
instance, TopMemoryContext would need to be deleted explicitly, and
the FD caching stuff would have to be revised, because it uses up all
the FDs that the process can open, and having many threads doing that
in a single process isn't going to work. There's probably some other
things that I'm forgetting, but the typical effect on the average bit
of backend code should be very, very low. If it isn't, we're doing it
wrong.

So, I think saying "oh, this is going to destabliize PostgreSQL for
years" is just fear-mongering. If someone proposes a patch that we
think is going to have that effect, we should (and certainly will)
reject it. But I see no reason why we can't have a good patch for this
where most code changes only in mechanical ways that are easy to
validate.

> This reads like a code quality argument: that's worthwhile, but I
> don't see how it supports your 'False' assertions.  Do two queries
> running in separate processes spend much time allocating and waiting
> on resources that could be shared within a single thread?

I don't have any idea what this has to do with what Andres was talking
about, honestly. However, there certainly are cases of the thing
you're talking about here. Having many backends separately open the
same file means we've got a whole bunch of different file descriptors
accessing the same file instead of just one. That does have a
meaningful cost on some workloads. Passing tuples between cooperating
processes that are jointly executing a parallel query is costly in the
current scheme, too. There might be ways to improve on that somewhat
even without threads, but if you don't think that the process model
made getting parallel query working harder and less efficient, I'm
here as the guy who wrote a lot of that code to tell you that it very
much did.

> That seems valid.  Even so, I would expect that for many queries, I/O
> access and row processing time is the bulk of the work, and that
> context-switches to/from other query processes is relatively
> negligible.

That's completely true, but there are ALSO many OTHER situations in
which the overhead of frequent context switching is absolutely
crushing. You might as well argue that umbrellas don't need to exist
because there are lots of sunny days.

--
Robert Haas
EDB: http://www.enterprisedb.com