Subject: Re: Let's make PostgreSQL multi-threaded

From: Dilip Kumar <[email-masked]>
Date: Mon, 12 Jun 2023 09:31:17 +0530

On Sat, Jun 10, 2023 at 11:32 PM Hannu Krosing <[email-masked]> wrote:
>
> On Mon, Jun 5, 2023 at 4:52 PM Heikki Linnakangas <[email-masked]> 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
> ----------------
> As an example, threads are not yet used instead of multiple processes
> for backends because:
> * Historically, threads were poorly supported and buggy.
> -- yes they were, not relevant now when threads are well-supported and non-buggy
>
> * An error in one backend can corrupt other backends if they're
> threads within a single process
> -- still valid for silent corruption
> -- for detected crash - yes, but we are restarting all backends in
> case of crash anyway.
>
> * 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
>
> * The backend code would be more complex.
> -- this is still the case
> -- even more worrisome is that all extensions also need to be rewritten
> -- and many incompatibilities will be silent and take potentially years to find
>
> * 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
>
> * Debugging threaded programs is much harder than debugging worker
> processes, and core dumps are much less useful
> -- this was countered by claiming that
>   -- by now we have reasonable debugger support for threads
>   -- there is no direct debugger support for debugging the exact
> system set up like PostgreSQL processes + shared memory
>
> * Sharing of read-only executable mappings and the use of
> shared_buffers means processes, like threads, are very memory
> efficient
> -- this seems to say that the current process model is as good as threads ?
> -- there were a few counterarguments
>   -- per-backend virtual memory mapping can add up to significant
> amount of extra RAM usage
>   -- the discussion did not yet touch various per-backend caches
> (pg_catalog cache, statement cache) which are arguably easier to
> implement in threaded model
>   -- TLB reload at each process switch is expensive and would be
> mostly avoided in case of threads

I think it is worth mentioning that parallel worker infrastructure
will be simplified with threaded models e.g. 'parallel query', and
'parallel vacuum'.

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

---

From: Pavel Borisov <[email-masked]>
Date: Mon, 12 Jun 2023 16:23:14 +0400

Is the following true or not?

1. If we switch processes to threads but leave the amount of session
local variables unchanged, there would be hardly any performance gain.
2. If we move some backend's local variables into shared memory then
the performance gain would be very near to what we get with threads
having equal amount of session-local variables.

In other words, the overall goal in principle is to gain from less
memory copying wherever it doesn't add the burden of locks for
concurrent variables access?

Regards,
Pavel Borisov,
Supabase

---

From: Andres Freund <[email-masked]>
Date: Mon, 12 Jun 2023 12:24:30 -0700

Hi,

> local variables unchanged, there would be hardly any performance gain.

False.

> having equal amount of session-local variables.

False.

> concurrent variables access?

False.

Those points seems pretty much unrelated to the potential gains from switching
to a threading model. The main advantages are:

1) We'd gain from being able to share state more efficiently (using normal
   pointers) and more dynamically (not needing to pre-allocate). That'd remove
   a good amount of complexity. As an example, consider the work we need to do
   to ferry tuples from one process to another. Even if we just continue to
   use shm_mq, in a threading world we could just put a pointer in the queue,
   but have the tuple data be shared between the processes etc.

   Eventually this could include removing the 1:1 connection<->process/thread
   model. That's possible to do with processes as well, but considerably
   harder.

2) Making context switches cheaper / sharing more resources at the OS and
   hardware level.

Greetings,

Andres Freund

---

From: Konstantin Knizhnik <[email-masked]>
Date: Tue, 13 Jun 2023 09:55:36 +0300

> concurrent variables access?
IMHO both statements are not true.
Switching to threads will cause less context switch overhead (because 
all threads are sharing the same memory space and so preserve TLB.
How big will be this advantage? In my prototype I got ~10%. But may be 
it is possible to fin workloads when it is larger.

Postgres backend is "thick" not because of large number of local variables.
It is because of local caches: catalog cache, relation cache, prepared 
statements cache,...
If they are not rewritten, then backend still may consume a lot of 
memory even if it will be thread rather then process.
But threads simplify development of global caches, although it can be 
done with DSM.

---

From: James Addison <[email-masked]>
Date: Wed, 14 Jun 2023 20:15:37 +0100

> to a threading model. The main advantages are:

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.

>    model. That's possible to do with processes as well, but considerably

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?

> 2) Making context switches cheaper / sharing more resources at the OS and

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.