Re: Let's make PostgreSQL multi-threaded

On 10/06/2023 21:01, Hannu Krosing wrote:
> On Mon, Jun 5, 2023 at 4:52 PM Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
>>
>> If there are no major objections, I'm going to update the developer FAQ,
>> removing the excuses there for why we don't use threads [1].
>
> I think it is not wise to start the wholesale removal of the objections there.
>
> But I think it is worthwhile to revisit the section about threads and
> maybe split out the historic part which is no more true, and provide
> both pros and cons for these.

> I started with this short summary from the discussion in this thread,
> feel free to expand, argue, fix :)
> * is current excuse
> -- is counterargument or ack

Thanks, that's a good idea.

> * Speed improvements using threads are small compared to the remaining
> backend startup time.
> -- we now have some measurements that show significant performance
> improvements not related to startup time

Also, I don't expect much performance gain directly from switching to
threads. The point is that switching to a multi-threaded model makes
possible, or at least greatly simplifies, a lot of other development.
Which can then help with the backend startup time, among other things.
For example, a shared catalog cache.

> * The backend code would be more complex.
> -- this is still the case

I don't quite buy that. A multi-threaded model isn't inherently more
complex than a multi-process model. Just different. Sure, the transition
period will be more complex, when we need to support both models. But in
the long run, if we can remove the multi-process mode, we can make a lot
of things *simpler*.

> -- even more worrisome is that all extensions also need to be rewritten

"rewritten" is an exaggeration. Yes, extensions will need adapt, similar
to the core code. But I hope it will be pretty mechanical work, marking
global variables as thread-local and such. Many extensions will work
with little to no changes.

> -- and many incompatibilities will be silent and take potentially years to find

IMO this is the most scary part of all this. I'm optimistic that we can
have enough compiler support and tooling to catch most issues. But we
don't know for sure at this point.

> * Terminating backend processes allows the OS to cleanly and quickly
> free all resources, protecting against memory and file descriptor
> leaks and making backend shutdown cheaper and faster
> -- still true

Yep. I'm not too worried about PostgreSQL code, our memory contexts and
resource owners are very good at stopping leaks. But 3rd party libraries
could pose hard problems. IIRC we still have a leak with the LLVM JIT
code, for example. We should fix that anyway, of course, but the
multi-process model is more forgiving with leaks like that.

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