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

From: James Addison <[email-masked]>
Date: Wed, 14 Jun 2023 23:23:45 +0100

On Tue, 13 Jun 2023 at 07:55, Konstantin Knizhnik <[email-masked]> wrote:
>
>
>
> On 12.06.2023 3:23 PM, Pavel Borisov wrote:
> > 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
> >
> >
> 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.

Hi Konstantin - do you have code/links that you can share for the
prototype and benchmarks used to gather those results?

---

From: Konstantin Knizhnik <[email-masked]>
Date: Thu, 15 Jun 2023 10:12:32 +0300

On 15.06.2023 1:23 AM, James Addison wrote:
> On Tue, 13 Jun 2023 at 07:55, Konstantin Knizhnik<[email-masked]>  wrote:
>>
>>

Sorry, I have already shared the link:
https://github.com/postgrespro/postgresql.pthreads/

As you can see last commit was 6 years ago when I stopped work on this 
project.
Why?  I already tried to explain it:
- benefits from switching to threads were not so large. May be I just 
failed to fid proper workload, but is was more or less expected result,
because most of the code was not changed - it uses the same sync 
primitives, the same local catalog/relation caches,..
To take all advantage of multithreadig model it is necessary to rewrite 
many components, especially related with interprocess communication.
But maintaining such fork of Postgres and synchronize it with mainstream 
requires too much efforts and I was not able to do it myself.

There are three different but related directions of improving current 
Postgres:
1. Replacing processes with threads
2. Builtin connection pooler
3. Lightweight backends (shared catalog/relation/prepared statements caches)

The motivation for such changes are also similar:
1. Increase Postgres scalability
2. Reduce memory consumption
3. Make Postgres better fir cloud and serverless requirements

I am not sure now which one should be addressed first or them can be 
done together.

Replacing static variables with thread-local is the first and may be the 
easiest step.
It requires more or less mechanical changes. More challenging thing is 
replacing private per-backend data structures
with shared ones (caches, file descriptors,...)

---

From: James Addison <[email-masked]>
Date: Thu, 15 Jun 2023 09:41:39 +0100

> https://github.com/postgrespro/postgresql.pthreads/

Nope, my mistake for not locating the existing link - thank you.

Is there a reason that parser-related files (flex/bison) are added as
part of the changeset?  (I'm trying to narrow it down to only the
changes necessary for the functionality.  so far it looks mostly
fairly minimal, which is good.  the adjustments to progname are
another thing that look a bit unusual/maybe unnecessary for the
feature)

> As you can see last commit was 6 years ago when I stopped work on this project.
> - benefits from switching to threads were not so large. May be I just failed to fid proper workload, but is was more or less expected result,
> because most of the code was not changed - it uses the same sync primitives, the same local catalog/relation caches,..
> To take all advantage of multithreadig model it is necessary to rewrite many components, especially related with interprocess communication.
> But maintaining such fork of Postgres and synchronize it with mainstream requires too much efforts and I was not able to do it myself.

I get the feeling that there are probably certain query types or
patterns where a significant, order-of-magnitude speedup is possible
with threads - but yep, I haven't seen those described in detail yet
on the mailing list (but as hinted by my not noticing the github link
previously, maybe I'm not following the list closely enough).

What workloads did you try with your version of the project?

> There are three different but related directions of improving current Postgres:
> I am not sure now which one should be addressed first or them can be done together.
>
> Replacing static variables with thread-local is the first and may be the easiest step.
> It requires more or less mechanical changes. More challenging thing is replacing private per-backend data structures

Thank you.  Personally I think that motivation two (reducing memory
consumption) -- as long as it can be done without detrimentally
affecting functionality or correctness, and without making the code
harder to develop/understand -- could provide benefits for all three
of the motivating cases (and, in fact, for non-cloud/serverful use
cases too).

This is making me wonder about other performance/scalability areas
that might not have been considered due to focus on the details of the
existing codebase, but I'll save that for another thread and will try
to learn more first.

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 15 Jun 2023 11:04:20 +0200

> existing codebase, but I'll save that for another thread and will try

A gradual move to more shared structures seems to be a way forward

It should get us all the benefits of threading minus the need for TLB
reloading and (in some cases) reduction of per-process virtual memory
mapping tables.

In any case we would need to implement all the locking and parallelism
management of these shared structures that are not there in the
current process architecture.

So a fair bit of work but also a clearly defined benefits of
1) reduced memory usage
2) no need to rebuild caches for each new connection
3) no need to track PREPARE statements inside connection poolers.

There can be extra complexity when different connections use the same
prepared statement name (say "PREP001") for different queries.
For this wel likely will need a good cooperation with connection
pooler where it passes some kind of client connection id along at the
transaction start

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 15 Jun 2023 11:07:30 +0200

One more unexpected benefit of having shared caches would be easing
access to other databases.

If the system caches are there for all databases anyway, then it
becomes much easier to make queries using objects from multiple
databases.

Note that this does not strictly need threads, just shared caches.

---

From: Konstantin Knizhnik <[email-masked]>
Date: Thu, 15 Jun 2023 22:36:30 +0300

> another thing that look a bit unusual/maybe unnecessary for the

Sorry, absolutely no reason - just my fault.

> What workloads did you try with your version of the project?

I do not remember now precisely (6 years passed).
But definitely I tried pgbench, especially read-only pgbench (to be more 
CPU rather than disk bounded)

---

From: Konstantin Knizhnik <[email-masked]>
Date: Thu, 15 Jun 2023 22:49:23 +0300

> 3) no need to track PREPARE statements inside connection poolers.

Shared plan cache (not only prepared statements cache) also opens way to 
more sophisticated query optimizations.
Right now we are not performing some optimization (like constant 
expression folding) just because them increase time of processing normal 
queries.
This is why queries generated by ORMs or wizards, which can contain a 
lot of dumb stuff, are not well simplified  by Postgres.
With MS-Sql it is quite frequent that query execution time is much 
smaller than query optimization time.
Having shared plan cache allows us to spend more time in optimization 
without risk to degrade performance.

---

From: Matthias van de Meent <[email-masked]>
Date: Fri, 28 Jul 2023 20:10:44 +0200

> becomes much easier to make queries using objects from multiple

We have several optimizations in our visibility code that allow us to
remove dead tuples from this database when another database still has
a connection that has an old snapshot in which the deleting
transaction of this database has not yet committed. This is allowed
because we can say with confidence that other database's connections
will never be able to see this database's tables. If we were to allow
cross-database data access, that would require cross-database snapshot
visibility checks, and that would severely hinder these optimizations.
As an example, it would increase the work we need to do for snapshots:
For the snapshot data of tables that aren't shared catalogs, we only
need to consider our own database's backends for visibility. With
cross-database visibility, we would need to consider all active
backends for all snapshots, and this can be significantly more work.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech/)