Re: Let's make PostgreSQL multi-threaded

On 06/06/2023 09:40, Robert Haas wrote:
> On Mon, Jun 5, 2023 at 10:52 AM Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
>> I spoke with some folks at PGCon about making PostgreSQL multi-threaded,
>> so that the whole server runs in a single process, with multiple
>> threads. It has been discussed many times in the past, last thread on
>> pgsql-hackers was back in 2017 when Konstantin made some experiments [0].
>>
>> I feel that there is now pretty strong consensus that it would be a good
>> thing, more so than before. Lots of work to get there, and lots of
>> details to be hashed out, but no objections to the idea at a high level.
>
> I'm not sure that there's a strong consensus, but I do think it's a good idea.

The consensus is not as strong as I hoped for... To summarize:

Tom, Andrew, Joe are worried that it will break a lot of stuff. That's a
valid point. The transition needs to be done well and not break things,
I agree with that. But if we can make the transition smooth, that's not
an objection to the idea itself.

Many comments have been along the lines of "it's hard, not worth the
effort". That's fair, but also not an objection to the idea itself, if
someone decides to spend the time on it.

Bruce was worried about the loss of isolation that the separate address
spaces gives, and Jeremy shared an anecdote on that. That is an
objection to the idea itself, i.e. even if transition was smooth,
bug-free and effortless, that point remains. I personally think the
isolation we get from separate address spaces is overrated. Yes, it
gives you some protection, but given how much shared memory there is,
the blast radius is large even with separate backend processes.

So I think there's hope. I didn't hear any _strong_ objections to the
idea itself, assuming the transition can be done smoothly.

>> # Transition period
>>
>> The transition surely cannot be done fully in one release. Even if we
>> could pull it off in core, extensions will need more time to adapt.
>> There will be a transition period of at least one release, probably
>> more, where you can choose multi-process or multi-thread model using a
>> GUC. Depending on how it goes, we can document it as experimental at first.
>
> I think the transition period should probably be effectively infinite.
> There might be some distant future day when we'd remove the process
> support, if things go incredibly well with threads, but I don't think
> it would be any time soon.

I don't think this is worth it, unless we plan to eventually remove the
multi-process mode. We could e.g. make lock table expandable in threaded
mode, and fixed-size in process mode, but the big gains would come from
being able to share things between threads and have variable-length
shared data structures more easily. As long as you need to also support
processes, you need to code to the lowest common denominator and don't
really get the benefits.

I don't know how long a transition period we need. Maybe 1 release, maybe 5.

> If nothing else, considering that we don't want to force a hard
> compatibility break for extensions.
Extensions regularly need small tweaks to adapt to new major Postgres
versions, I don't think this would be too different.

--
Heikki Linnakangas
Neon (https://neon.tech)