Re: Let's make PostgreSQL multi-threaded

On Mon, Jun 12, 2023 at 5:17 PM Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:

> 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:
>
> <<<SNIP>>>

>
> > * 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*.
>

If I may weigh in here:
Making a previously unthreaded process able to handle multiple threads, is
a tedious process.

>
> > -- 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.
>

I can tell you from experience it isn't that easy. In my career I have
taken a few "old" technologies and made them multithreaded and it is really
a complex and laborious undertaking.
Many operations that you do just fine without threads will break in a
multithreaded system. You need to make sure every function in every library
that you use is "thread safe." Take a file handle, if you read, seek, or
write a file handle you are fine in a single process, but this breaks in a
multithreaded environment if the file handle is shared. That's a very
simple example. Openssl operations will almost certainly break and you will
need to rewrite your ssl stuff and protect some things with mutexes. When
you fork() a lot is essentially duplicated (COW) between the parent and
child that will ultimately be shared in a threaded model. Decades old
assumptions in the design and architecture will break and you will need to
rethink what you are doing and how it is done. You will need to change file
handling to get beyond the 1024 file limit in calls like "select." There is
a LOT of this kind of stuff, it is not mechanical. I even call into
question "Many extensions will work with little to no changes" as those too
will need to be audited for thread safety. Think about loading extensions,
extensions are typically not loaded until they are used. In a
multi-threaded model, a shared library will only be loaded once. Think
about memory management, you will have multiple threads fighting over the
global heap as they allocate memory. The list is virtually endless.

>
> > -- 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.
>

We absolutely do not know and it *is* very scary.

>
> > * 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.
>
> Again, we believe that this is true.

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