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

From: Tom Lane <[email-masked]>
Date: Mon, 05 Jun 2023 11:18:27 -0400

Heikki Linnakangas <[email-masked]> writes:
> 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.

> The purpose of this email is to make that silent consensus explicit. If 
> you have objections to switching from the current multi-process 
> architecture to a single-process, multi-threaded architecture, please 
> speak up.

For the record, I think this will be a disaster.  There is far too much
code that will get broken, largely silently, and much of it is not
under our control.

			regards, tom lane

---

From: Heikki Linnakangas <[email-masked]>
Date: Mon, 5 Jun 2023 18:33:57 +0300

> code that will get broken, largely silently, and much of it is not

Noted. Other large projects have gone through this transition. It's not 
easy, but it's a lot easier now than it was 10 years ago. The platform 
and compiler support is there now, all libraries have thread-safe 
interfaces, etc.

I don't expect you or others to buy into any particular code change at 
this point, or to contribute time into it. Just to accept that it's a 
worthwhile goal. If the implementation turns out to be a disaster, then 
it won't be accepted, of course. But I'm optimistic.

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

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Mon, 5 Jun 2023 13:40:13 -0400

> it won't be accepted, of course. But I'm optimistic.

I don't have enough expertise in this area to comment on if it'd be a 
"disaster" or not. My zoomed out observations are two-fold:

1. It seems like there's a lack of consensus on which of processes vs. 
threads yield the best performance benefit, and from talking to folks 
with greater expertise than me, this can vary between workloads. I 
believe one DB even gives uses a choice if they want to run in processes 
vs. threads.

2. While I wouldn't want to necessarily discourage a moonshot effort, I 
would ask if developer time could be better spent on tackling some of 
the other problems around vertical scalability? Per some PGCon 
discussions, there's still room for improvement in how PostgreSQL can 
best utilize resources available very large "commodity" machines (a 
448-core / 24TB RAM instance comes to mind).

I'm purposely giving a nonanswer on whether it's a worthwhile goal, but 
rather I'd be curious where it could stack up against some other efforts 
to continue to help PostgreSQL improve performance and handle very large 
workloads.

Thanks,

Jonathan

---

From: Andrew Dunstan <[email-masked]>
Date: Mon, 5 Jun 2023 14:51:50 -0400

On 2023-06-05 Mo 11:18, Tom Lane wrote:
> Heikki Linnakangas<[email-masked]>  writes:

If we were starting out today we would probably choose a threaded 
implementation. But moving to threaded now seems to me like a 
multi-year-multi-person project with the prospect of years to come 
chasing bugs and the prospect of fairly modest advantages. The risk to 
reward doesn't look great.

That's my initial reaction. I could be convinced otherwise.

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

---

From: Joe Conway <[email-masked]>
Date: Mon, 5 Jun 2023 16:08:24 -0400

> That's my initial reaction. I could be convinced otherwise.

I read through the thread thus far, and Andrew's response is the one 
that best aligns with my reaction.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

---

From: "Jonah H. Harris" <[email-masked]>
Date: Mon, 5 Jun 2023 14:07:52 -0700

> code that will get broken, largely silently, and much of it is not

While I've long been in favor of a multi-threaded implementation, now in my
old age, I tend to agree with Tom. I'd be interested in Konstantin's
thoughts (and PostgresPro's experience) of multi-threaded vs. internal
pooling with the current process-based model. I recall looking at and
playing with Konstantin's implementations of both, which were impressive.
Yes, the latter doesn't solve the same issues, but many real-world ones
where multi-threaded is argued. Personally, I think there would be not only
a significant amount of time spent dealing with in-the-field stability
regressions before a multi-threaded implementation matures, but it would
also increase the learning curve for anyone trying to start with internals
development.

-- 
Jonah H. Harris

---

From: Jeremy Schneider <[email-masked]>
Date: Mon, 5 Jun 2023 17:27:11 -0700

On 6/5/23 2:07 PM, Jonah H. Harris wrote:
> On Mon, Jun 5, 2023 at 8:18 AM Tom Lane <[email-masked]
> <mailto:[email-masked]>> wrote:
> 
> While I've long been in favor of a multi-threaded implementation, now in
> my old age, I tend to agree with Tom. I'd be interested in Konstantin's
> playing with Konstantin's implementations of both, which were
> impressive. Yes, the latter doesn't solve the same issues, but many
> real-world ones where multi-threaded is argued. Personally, I think
> there would be not only a significant amount of time spent dealing with
> in-the-field stability regressions before a multi-threaded
> implementation matures, but it would also increase the learning curve
> for anyone trying to start with internals development.

To me, processes feel just a little easier to observe and inspect, a
little easier to debug, and a little easier to reason about. Tooling
does exist for threads - but operating systems track more things at a
process level and I like having the full arsenal of unix process-based
tooling at my disposal.

Even simple things, like being able to see at a glance from "ps" or
"top" output which process is the bgwriter or the checkpointer, and
being able to attach gdb only on that process without pausing the whole
system. Or to a single backend.

A thread model certainly has advantages but I do feel that some useful
things might be lost here.

And for the record, just within the past few weeks I saw a small mistake
in some C code which smashed the stack of another thread in the same
process space. It manifested as unpredictable periodic random SIGSEGV
and SIGBUS with core dumps that were useless gibberish, and it was
rather difficult to root cause.

But one interesting outcome of that incident was learning from my
colleague Josh that apparently SUSv2 and C99 contradict each other: when
snprintf() is called with size=0 then SUSv2 stipulates an unspecified
return value less than 1, while C99 allows str to be NULL in this case,
and gives the return value (as always) as the number of characters that
would have been written in case the output string has been large enough.

So long story short... I think the robustness angle on the process model
shouldn't be underestimated either.

-Jeremy

-- 
http://about.me/jeremy_schneider

---

From: Konstantin Knizhnik <[email-masked]>
Date: Tue, 6 Jun 2023 15:06:19 +0300

On 06.06.2023 12:07 AM, Jonah H. Harris wrote:
> While I've long been in favor of a multi-threaded implementation, now 
> in my old age, I tend to agree with Tom. I'd be interested in 
> Konstantin's thoughts (and PostgresPro's experience) of multi-threaded 
> vs. internal pooling with the current process-based model. I recall 
> looking at and playing with Konstantin's implementations of both, 
> which were impressive. Yes, the latter doesn't solve the same issues, 
> but many real-world ones where multi-threaded is argued. Personally, I 
> think there would be not only a significant amount of time spent 
> dealing with in-the-field stability regressions before a 
> multi-threaded implementation matures, but it would also increase the 
> learning curve for anyone trying to start with internals development.
>
> -- 
> Jonah H. Harris
>

Let me share my experience with porting Postgres to threads (by the way 
- repository is still alive - 
https://github.com/postgrespro/postgresql.pthreads 
<https://github.com/postgrespro/postgresql.pthreads>
but I have not keep it in sync with recent versions of Postgres).

1. Solving the problem with static variables was not so difficult as I 
expected - thanks to TLS and its support in modern compilers.
So the only thing we should do is to add some special modified to 
variable declaration:

-static int     MyLockNo = 0;
-static bool holdingAllLocks = false;
+static session_local int       MyLockNo = 0;
+static session_local bool holdingAllLocks = false;

But there are about 2k such variables which storage class has to be changed.
This is one of the reasons why I do not agree with the proposal to 
define some session context, place all session specific variables in 
such context and pass it everywhere. It will be very inconvenient to 
maintain structure with 2k fields and adding new field to this struct 
each time you need some non-local variable. Even i it can be hide in 
some macros like DEF_SESSION_VAR(type, name).
Also it requires changing of all Postgres code working with this 
variables, not just declarations.
So patch will be 100x times more and almost any line of Postgres code 
has to be changed.
And I do not see any reasons for it except portability and avoid 
dependecy on compiler.
Implementation of TLS is quite efficient (at least at x86) - there is 
special register pointing to TLS area, so access TLS variable is not 
more expensive than static variable.

2. Performance improvement from switching to threads was not so large 
(~10%). But please notice that I have not changed ny Postgres sync 
primitives.
(But still not sure that using for example pthead_rwlock instead of our 
own LWLock will cause some gains in performance)

3. Multithreading significantly simplify concurrent query execution and 
interaction between workers.
Right now with dynamic shared memory stuff  we can support work with 
varying size data in shared memory but
in mutithreaded program it can be done much easier.

4. Multuthreaded model  opens a way for fixing many existed Postgres 
problems: lack of shared catalog and prepared statements cache, changing 
page pool size (shared buffers) in runtime, ...

5. During this porting I had most of troubles with the following 
components: GUCs,  signals, handling errors and file descriptor cache. 
File descriptor cache really becomes bottleneck because now all backends 
and competing for file descriptors which number is usually limited by 
1024 (without editing system configuration). Protecting it with mutex 
cause significant degrade of performance. So I have to maintain 
thread-local cache.

6. It is not clear how to support external extensions.

7. It will be hard to support non-multithreaded PL languages (like 
python), but for example support of Java will be more natural and efficient.

I do not think that development of multithreaded application is more  
complex or requires large "learning curve".
When you deal with parallel execution you should be careful in any case.
The advantage of process model is that there is much clear distinction 
between shared and private variables.
Concerning debugging and profiling - it is more convenient with 
multithreading in some cases and less convenient in other.
But programmers are living with threads for more than 30 years so now 
most tools are supporting threads at least not worse than processes.
And for many developers now working with threads is more natural and 
convenient.

OOM and local backend memory consumption seems to be one of the main 
challenges for multithreadig model:
right now some queries can cause high consumption of memory. work_mem is 
just a hint and real memory consumption can be much higher.
Even if it doesn't cause OOM, still not all of the allocated memory is 
returned to OS after query completion and increase memory fragmentation.
Right now restart of single backend suffering from memory fragmentation 
eliminates this problem. But if will be impossible for multhreaded Postgres.

So? as I see from this thread, most of authoritative members of Postgres 
community are still very pessimistic (or conservative:)
about making  Postgres  multi-threaded. And it is really huge work which 
will cause significant code discrepancy. It significantly complicates
backpatching and support of external extension. It can not be done 
without support and approval by most of committers. This is why this 
work was stalled in PgPro.

My personal opinion is that Postgres almost reaches its "limit of 
evolution" or is close to it.
Making some major changes such as multithreading, undo log, columnar 
store with vector executor
requires so much changes and cause so many conflicts with existed code 
that it will be easier to develop new system from scratch
rather than trying to plugin new approach in old architecture. May be I 
wrong. It can be my personal fault that I was not able to bring 
multithread Postgres, builtin connection pooler, vectorized executor, 
libpq compression and other my PRs to commit.
I have a filling that it is not possible to merge in mainstream 
something non-trivial, affecting Postgres core without interest and help 
of several
committers. Fro the other hand  presence of such Postgres forks as 
TimescaleDB, OrioleDB, GreenPlum demonstrates that Postgres still has 
high potential for extension.

---

From: [email-masked]
Date: Tue, 06 Jun 2023 11:48:23 -0400

> python), but for example support of Java will be more natural and 

To this I say ...

Hmm.

Surely, the current situation with a JVM in each backend process
(that calls for one) has been often seen as heavier than desirable.

At the same time, I am not sure how manageable one giant process
with one giant JVM instance would prove to be, either.

It is somewhat nice to be able to tweak JVM settings in a session
and see what happens, without disrupting other sessions. There may
also exist cases for different JVM settings in per-user or per-
database GUCs.

Like Python with the GIL, it is documented for JNI_CreateJavaVM
that "Creation of multiple VMs in a single process is not
supported."[1]

And the devs of Java, in their immeasurable wisdom, have announced
a "JDK Enhancement Proposal" (that's just what these things are
called, don't blame Orwell), JEP 411[2][3], in which all of the
Security Manager features that PL/Java relies on for bounds on
'trusted' behavior are deprecated for eventual removal with no
functional replacement. I'd be even more leery of using one big
shared JVM for everybody's work after that happens.

Might the work toward allowing a run-time choice between a
process or threaded model also make possible some
intermediate models as well? A backend process for
connections to a particular database, or with particular
authentication credentials? Go through the authentication
handshake and then sendfd the connected socket to the
appropriate process. (Has every supported platform got
something like sendfd?)

That way, there could be some flexibility to arrange how many
distinct backends (and, for Java purposes, how many JVMs) get
fired up, and have each sharing sessions that have something in
common.

Or, would that just require all the complexity of both
approaches to synchronization, with no sufficient benefit?

Regards,
-Chap

[1] 
https://docs.oracle.com/en/java/javase/17/docs/specs/jni/invocation.html#jni_createjavavm
[2] 
https://blogs.apache.org/netbeans/entry/jep-411-deprecate-the-security1
[3] https://github.com/tada/pljava/wiki/JEP-411

---

From: Heikki Linnakangas <[email-masked]>
Date: Tue, 6 Jun 2023 19:24:11 +0300

> shared JVM for everybody's work after that happens.

Ouch.

> appropriate process. (Has every supported platform got

I'm afraid having multiple processes and JVMs doesn't help that. If you 
can escape the one JVM in one backend process, it's game over. Backend 
processes are not a security barrier, and you have the same problems 
with the current multi-process architecture, too.

https://github.com/greenplum-db/plcontainer is one approach. It launches 
a separate process for the PL, separate from the backend process, and 
sandboxes that.

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

---

From: [email-masked]
Date: Tue, 06 Jun 2023 13:00:11 -0400

On 2023-06-06 12:24, Heikki Linnakangas wrote:
> I'm afraid having multiple processes and JVMs doesn't help that.
> If you can escape the one JVM in one backend process, it's game over.

So there's escape and there's escape, right? Java still prioritizes
(and has, in fact, strengthened) barriers against breaking module
encapsulation, or getting access to arbitrary native memory or code.

The features that have been deprecated, to eventually go away, are
the ones that offer fine-grained control over operations that there
are Java APIs for. Eventually it won't be as easy as it is now to say
"ok, your function gets to open these files or these sockets but
not those ones."

Even for those things, there may yet be solutions. There are Java
APIs for virtualizing the view of the file system, for example. It's
yet to be seen how things will shake out. Configuration may get
trickier, and there may be some incentive to to include, say,
sepgsql in the picture.

Sure, even access to a file API can be game over, depending on
what file you open, but that's already the risk for every PL
with an 'untrusted' flavor.

Regards,
-Chap

---

From: Thomas Munro <[email-masked]>
Date: Wed, 7 Jun 2023 10:26:07 +1200

On Tue, Jun 6, 2023 at 6:52 AM Andrew Dunstan <[email-masked]> wrote:
> If we were starting out today we would probably choose a threaded implementation. But moving to threaded now seems to me like a multi-year-multi-person project with the prospect of years to come chasing bugs and the prospect of fairly modest advantages. The risk to reward doesn't look great.
>

Here is one thing I often think about when contemplating threads.
Take a look at dsa.c.  It calls itself a shared memory allocator, but
really it has two jobs, the second being to provide software emulation
of virtual memory.  That’s behind dshash.c and now the stats system,
and various parts of the parallel executor code.  It’s slow and
complicated, and far from the state of the art.  I wrote that code
(building on allocator code from Robert) with the expectation that it
was a transitional solution to unblock a bunch of projects.  I always
expected that we'd eventually be deleting it.  When I explain that
subsystem to people who are not steeped in the lore of PostgreSQL, it
sounds completely absurd.  I mean, ... it is, right?    My point is
that we’re doing pretty unreasonable and inefficient contortions to
develop new features -- we're not just happily chugging along without
threads at no cost.

---

From: Tomas Vondra <[email-masked]>
Date: Wed, 7 Jun 2023 21:20:15 +0200

> and compiler support is there now, all libraries have thread-safe

Is the platform support really there for all platforms we want/intend to
support? I have no problem believing that for modern Linux/BSD systems,
but what about the older stuff we currently support.

Also, which other projects did this transition? Is there something we
could learn from them? Were they restricted to much smaller list of
platforms?

> it won't be accepted, of course. But I'm optimistic.

I personally am not opposed to the effort in principle, but how do you
even evaluate cost and benefits for a transition like this? I have no
idea how to quantify the costs/benefits for this as a single change.

I've seen some benchmarks in the past, but it's hard to say which of
these improvements are possible only with threads, and what would be
doable with less invasive changes with the process model.

IMHO the only way to move this forward is to divide this into smaller
changes, each of which gives us some benefit we'd want anyway. For
example, this thread already mentioned improving handling of many
connections. AFAICS that requires isolating "session state", which seems
useful even without a full switch to threads as it makes connection
pooling simpler. It should be easier to get a buy-in for these changes,
while introducing abstractions simplifying the switch to threads.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Andres Freund <[email-masked]>
Date: Wed, 7 Jun 2023 14:37:21 -0700

Hi,

On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote:
> would ask if developer time could be better spent on tackling some of the
> other problems around vertical scalability? Per some PGCon discussions,
> there's still room for improvement in how PostgreSQL can best utilize
> resources available very large "commodity" machines (a 448-core / 24TB RAM
> instance comes to mind).

I think we're starting to hit quite a few limits related to the process model,
particularly on bigger machines. The overhead of cross-process context
switches is inherently higher than switching between threads in the same
process - and my suspicion is that that overhead will continue to
increase. Once you have a significant number of connections we end up spending
a *lot* of time in TLB misses, and that's inherent to the process model,
because you can't share the TLB across processes.

The amount of duplicated code we have to deal with due to to the process model
is quite substantial. We have local memory, statically allocated shared memory
and dynamically allocated shared memory variants for some things. And that's
just going to continue.

> rather I'd be curious where it could stack up against some other efforts to
> continue to help PostgreSQL improve performance and handle very large
> workloads.

There's plenty of things we can do before, but in the end I think tackling the
issues you mention and moving to threads are quite tightly linked.

Greetings,

Andres Freund

---

From: Thomas Kellerer <[email-masked]>
Date: Wed, 7 Jun 2023 23:39:54 +0200

> could learn from them? Were they restricted to much smaller list of

Firebird did this a while ago if I'm not mistaken.

Not open source, but Oracle was historically multi-threaded on Windows and multi-process on all other platforms.
I _think_ starting with 19c you can optionally run it multi-threaded on Linux as well.

But I doubt, they are willing to share any insights ;)

---

From: Jeremy Schneider <[email-masked]>
Date: Wed, 7 Jun 2023 15:37:31 -0700

On 6/7/23 2:39 PM, Thomas Kellerer wrote:
> Not open source, but Oracle was historically multi-threaded on Windows
> and multi-process on all other platforms.
Looks like it actually became publicly available in 12c. AFAICT Oracle
supports both modes today, with a config parameter to switch between them.

This is a very interesting case study.

Concepts Manual:

https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/process-architecture.html#GUID-4B460E97-18A0-4F5A-A62F-9608FFD43664

Reference:

https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/THREADED_EXECUTION.html#GUID-7A668A49-9FC5-4245-AD27-10D90E5AE8A8

List of Oracle process types, which ones can run as threads and which
ones always run as processes:

https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/background-processes.html#GUID-86184690-5531-405F-AA05-BB935F57B76D

Looks like they have four processes that will never run in threads:
* dbwriter (writes dirty blocks in background)
* process monitor (cleanup after process crash to avoid full server
restarts) <jealous>
* process spawner (like postmaster)
* time keeper process

Per Tim Hall's oracle-base, it seems that plenty of people are sticking
with the process model, and that one use case for threads was:
"consolidating lots of instances onto a single server without using the
multitennant option. Without the multithreaded model, the number of OS
processes could get very high."

https://oracle-base.com/articles/12c/multithreaded-model-using-threaded_execution_12cr1

I did google search for "oracle threaded_execution" and browsed a bit;
didn't see anything that seems earth shattering so far.

Ludovico Caldara and Martin Bach published blogs when it was first
released, which just introduced but didn't test or hammer on it. The
feature has existed for 10 years now and I don't see any blog posts
saying that "everyone should use this because it doubles your
performance" or anything like that. I think if there were really
significant performance gains then there would be many interesting blog
posts on the internet by now from the independent Oracle professional
community - I know many of these people.

In fact, there's an interesting blog by Kamil Stawiarski from 2015 where
he actually observed one case of /slower/ performance with threads. That
blog post ends with: "So I raise the question: why and when use threaded
execution? If ever?"

https://blog.ora-600.pl/2015/12/17/oracle-12c-internals-of-threaded-execution/

I'm not sure if he ever got an answer

-Jeremy

---

From: Thomas Munro <[email-masked]>
Date: Thu, 8 Jun 2023 11:37:00 +1200

> supports both modes today, with a config parameter to switch between them.

It's old, but this describes the 4 main models and which well known
RDBMSes use them in section 2.3:

https://dsf.berkeley.edu/papers/fntdb07-architecture.pdf

TL;DR DB2 is the winner, it can do process-per-connection,
thread-per-connection, process-pool or thread-pool.

I understand this thread to be about thread-per-connection (= backend,
session, socket) for now.

---

From: "Drouvot, Bertrand" <[email-masked]>
Date: Thu, 8 Jun 2023 07:55:34 +0200

Hi,

> didn't see anything that seems earth shattering so far.

FWIW, I recall Karl Arao's wiki page: https://karlarao.github.io/karlaraowiki/#%2212c%20threaded_execution%22
where some performance and memory consumption studies have been done.

Regards,

-- 
Bertrand Drouvot

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 8 Jun 2023 11:54:17 +0200

> because you can't share the TLB across processes.

This part was touched in the "AMA with a Linux Kernale Hacker"
Unconference session where he mentioned that the had proposed a
'mshare' syscall for this.

So maybe a more fruitful way to fixing the perceived issues with
process model is to push for small changes in Linux to overcome these
avoiding a wholesale rewrite ?

> and dynamically allocated shared memory variants for some things. And that's

Maybe we can already remove the distinction between static and dynamic
shared memory ?

Though I already heard some complaints at the conference discussions
that having the dynamic version available has made some developers
sloppy in using it resulting in wastefulness.

> issues you mention and moving to threads are quite tightly linked.

Still we should be focusing our attention at solving the issues and
not at "moving to threads" and hoping this will fix the issues by
itself.

Cheers
Hannu

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 8 Jun 2023 11:56:37 +0200

I think I remember that in the early days of development somebody did
send a patch-set for making PostgreSQL threaded on Solaris.

I don't remember why this did not catch on.

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 8 Jun 2023 12:15:58 +0200

> 'mshare' syscall for this.

Also, the *static* huge pages already let you solve this problem now
by sharing the page tables

Cheers
Hannu

---

From: Tomas Vondra <[email-masked]>
Date: Thu, 8 Jun 2023 12:37:37 +0200

> thread-per-connection, process-pool or thread-pool.

I think the basic architectures are known, especially from the user
perspective. I'm more interested in challenges the projects faced while
moving from one architecture to the other, or how / why they support
more than just one, etc.

In [1] Heikki argued that:

    I don't think this is worth it, unless we plan to eventually remove
    the multi-process mode. ... 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.

But these projects clearly support multiple architectures, and have no
intention to ditch some of them. So how did they do that? Surely they
think there are benefits.

One option would be to just have separate code paths for processes and
threads, but the effort required to maintain and improve that would be
deadly. So the only feasible option seems to be they managed to abstract
the subsystems enough for the "regular" code to not care about model.

[1]
https://www.postgresql.org/message-id/[email-masked]

> session, socket) for now.

Maybe, although people also proposed to switch the parallel query to
threads (so that'd be multiple threads per session). But I don't think
it really matters, the concerns are mostly about moving from one
architecture to another and/or supporting both.

regards

---

From: Matthias van de Meent <[email-masked]>
Date: Thu, 8 Jun 2023 14:15:33 +0200

> avoiding a wholesale rewrite ?

We support not just Linux, but also Windows and several (?) BSDs. I'm
not against pushing Linux to make things easier for us, but Linux is
an open source project, too, where someone need to put in time to get
the shiny things that you want. And I'd rather see our time spent in
PostgreSQL, as Linux is only used by a part of our user base.

> Maybe we can already remove the distinction between static and dynamic

That sounds like a bad idea, dynamic shared memory is more expensive
to maintain than our static shared memory systems, not in the least
because DSM is not guaranteed to share the same addresses in each
process' address space.

> sloppy in using it resulting in wastefulness.

Do you know any examples of this wastefulness?

> not at "moving to threads" and hoping this will fix the issues by

I suspect that it is much easier to solve some of the issues when
working in a shared address space.
E.g. resizing shared_buffers is difficult right now due to the use of
a static allocation of shared memory, but if we had access to a single
shared address space, it'd be easier to do any cleanup necessary for
dynamically increasing/decreasing its size.
Same with parallel workers - if we have a shared address space, the
workers can pass any sized objects around without being required to
move the tuples through DSM and waiting for the leader process to
empty that buffer when it gets full.

Sure, most of that is probably possible with DSM as well, it's just
that I see a lot more issues that you need to take care of when you
don't have a shared address space (such as the pointer translation we
do in dsa_get_address).

Kind regards,

Matthias van de Meent
Neon, Inc.

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 8 Jun 2023 14:44:11 +0200

> PostgreSQL, as Linux is only used by a part of our user base.

Do we have any statistics for the distribution of our user base ?

My gut feeling says that for performance-critical use the non-Linux is
in low single digits at best.

My fascination for OpenSource started with realisation that instead of
workarounds you can actually fix the problem at source. So if the
specific problem is that TLB is not shared then the proper fix is
making it shared instead of rewriting everything else to get around
it. None of us is limited to writing code in PostgreSQL only. If the
easiest and more generix fix can be done in Linux then so be it.

It is also possible that Windows and *BSD already have a similar feature.

> because DSM is not guaranteed to share the same addresses in each

Then this too needs to be fixed

> Do you know any examples of this wastefulness?

No. Just somebody mentioned it in a hallway conversation and the rest
of the developers present mumbled approvingly :)

> working in a shared address space.

Probably. But it would come at the cost of needing to change a lot of
other parts of PostgreSQL.

I am not against making code cleaner for potential threaded model
support. I am just a bit sceptical about the actual switch being easy,
or doable in the next 10-15 years.

> dynamically increasing/decreasing its size.

This again could be done with shared memory mapping + dynamic shared memory.

> empty that buffer when it gets full.

Larger shared memory :)

Same for shared plan cache and shared schema cache.

> don't have a shared address space (such as the pointer translation we

All of the above seem to point to the need of a single thing - having
an option for shared memory mappings .

So let's focus on fixing things with minimal required change.

And this would not have an adverse affect on systems that can not
share mapping, they just won't become faster. And thay are all welcome
to add the option for shared mappings too if they see enough value in
it.

It could sound like the same thing as threaded model, but should need
much less changes and likely no changes for most out-of-tree
extensions

---
Cheers
Hannu

---

From: Robert Haas <[email-masked]>
Date: Thu, 8 Jun 2023 10:08:57 -0400

> because you can't share the TLB across processes.

This is a very good point.

Our default posture on this mailing list is to try to maximize use of
OS facilities rather than reimplementing things - well and good. But
if a user writes a query with FOO JOIN BAR ON FOO.X = BAR.X OR FOO.Y =
BAR.Y and then complains that the resulting query plan sucks, we don't
slink off in embarrassment: we tell the user that there's not really
any fast plan for that query and that if they write queries like that
they have to live with the consequences. But the same thing applies
here. To the extent that context switching between more processes is
more expensive than context switching between threads for
hardware-related reasons, that's not something that the OS can fix for
us. If we choose to do the expensive thing then we pay the overhead.

--
Robert Haas
EDB: http://www.enterprisedb.com

---

From: Robert Haas <[email-masked]>
Date: Thu, 8 Jun 2023 10:56:32 -0400

> Then this too needs to be fixed

Honestly, I'm struggling to respond to this non-sarcastically. I mean,
I was the one who implemented DSM. Do you think it works the way that
it works because I considered doing something smart and decided to do
something dumb instead?

Suppose you have two PostgreSQL backends A and B. If we're not running
on Windows, each of these was forked from the postmaster, so things
like the text and data segments and the main shared memory segment are
going to be mapped at the same address in both processes, because they
inherit those mappings from the postmaster. However, additional things
can get mapped into the address space of either process later. This
can happen in a variety of ways. For instance, a shared library can
get loaded into one process and not the other. Or it can get loaded
into both processes but at different addresses - keep in mind that
it's the OS, not PostgreSQL, that decides what address to use when
loading a shared library. Or, if one process allocates a bunch of
memory, then new address space will have to be mapped into that
process to handle those memory allocations and, again, it is the OS
that decides where to put those mappings. So over time the memory
mappings of these two processes can diverge arbitrarily. That means
that if the same DSM has to be mapped into both processes, there is no
guarantee that it can be placed at the same address in both processes.
The address that gets used in one process might not be available in
the other process.

It's worth pointing out here that there are no portable primitives
available for a process to examine what memory segments are mapped
into its address space. I think it's probably possible on every OS,
but it works differently on different ones. Linux exposes such details
through /proc, for example, but macOS doesn't have /proc. So if we're
using standard, portable primitives, we can't even TRY to put the DSM
at the same address in every process that maps it. But even if we used
non-portable primitives to examine what's mapped into the address
space of every process, it wouldn't solve the problem. Suppose 4
processes want to share a DSM, so they all run around and use
non-portable OS-specific interfaces to figure out where there's a free
chunk of address space large enough to accommodate that DSM and they
all map it there. Hooray! But then say a fifth process comes along and
it ALSO wants to map that DSM, but in that fifth process the address
space that was available in the other four processes has already been
used by something else. Well, now we're screwed.

The fact that DSM is expensive and awkward to use isn't a defect in
the implementation of DSM. It's a consequence of the fact that the
address space mappings in one PostgreSQL backend can be almost
arbitrarily different from the address space mappings in another
PostgreSQL backend. If only there were some kind of OS feature
available that would allow us to set things up so that all of the
PostgreSQL backends shared the same address space mappings!

Oh, right, there is: THREADS.

The fact that we don't use threads is the reason why DSM sucks and has
to suck. In fact it's the reason why DSM has to exist at all. Saying
"fix DSM instead of using threads" is roughly in the same category as
saying "if the peasants are revolting because they have no bread, then
let them eat cake." Both statements evince a complete failure to
understand the actual source of the problem.

With apologies for my grumpiness,

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 8 Jun 2023 17:02:08 +0200

> it works because I considered doing something smart and decided to do

No, I meant that this needs to be fixed at OS level, by being able to
use the same mapping.

We should not shy away from asking the OS people for adding the useful
features still missing.

It was mentioned in the Unconference Kernel Hacker AMA talk and said
kernel hacker works for Oracle, andf they also seemed to be needing
this :)

---

From: Matthias van de Meent <[email-masked]>
Date: Thu, 8 Jun 2023 17:08:16 +0200

> easiest and more generix fix can be done in Linux then so be it.

TLB is a CPU hardware facility, not something that the OS can decide
to share between processes. While sharing (some) OS memory management
facilities across threads might be possible (as you mention, that
mshare syscall would be an example), that doesn't solve the issue of
the hardware not supporting sharing TLB entries across processes. We'd
use less kernel memory for memory management, but the CPU would still
stall on TLB misses every time we switch processes on the CPU (unless
we somehow were able to use non-process-namespaced TLB entries, which
would make our processes not meaningfully different from threads
w.r.t. address space).

> Then this too needs to be fixed

That needs kernel facilities in all (most?) supported OSes, and I
think that's much more work than moving to threads:
Allocations from the kernel are arbitrarily random across the
available address space, so a DSM segment that is allocated in one
backend might overlap with unshared allocations of a different
backend, making those backends have conflicting memory address spaces.
The only way to make that work is to have a shared memory addressing
space, but some backends just not having the allocation mapped into
their local address space; which seems only slightly more isolated
than threads and much more effort to maintain.

> of the developers present mumbled approvingly :)

The only "wastefulness" that I know of in our use of DSM is the queue,
and that's by design: We need to move data from a backend's private
memory to memory that's accessible to other backends; i.e. shared
memory. You can't do that without copying or exposing your private
memory.

> or doable in the next 10-15 years.

PostgreSQL only has a support cycle of 5 years. 5 years after the last
release of un-threaded PostgreSQL we could drop support for "legacy"
extension models that don't support threading.

> This again could be done with shared memory mapping + dynamic shared memory.

Yes, but as I said, that's much more difficult than lock and/or atomic
operations on shared-between-backends static variables, because if
these variables aren't in shared memory you need to pass the messages
to update the variables to all backends.

> Same for shared plan cache and shared schema cache.

Shared memory in processes is not free, if only because the TLB gets
saturated much faster.

> So let's focus on fixing things with minimal required change.

That seems logical, but not all kernels support dynamic shared memory
mappings. And, as for your suggested solution, I couldn't find much
info on this mshare syscall (or its successor mmap/VM_SHARED_PT), nor
on whether it would actually fix the TLB issue.

> much less changes and likely no changes for most out-of-tree

We can't expect the kernel to fix everything for us - that's what we
build PostgreSQL for. Where possible, we do want to rely on OS
primitives, but I'm not sure that it would be easy to share memory
address mappings across backends, for reasons including the above
("That needs kernel facilities in all [...] more effort to maintain").

Kind regards,

Matthias van de Meent
Neon, Inc.

---

From: Robert Haas <[email-masked]>
Date: Thu, 8 Jun 2023 11:56:13 -0400

> kernel hacker works for Oracle, andf they also seemed to be needing

Fair enough, but we aspire to work on a bunch of different operating
systems. To make use of an OS facility, we need something that works
on at least Linux, Windows, macOS, and a few different BSD flavors.
It's not as if when the PostgreSQL project asks for a new operating
system facility everyone springs into action to provide it
immediately. And even if they did, and even if they all released an
implementation of whatever we requested next year, it would still be
at least five, more realistically ten, years before systems with those
facilities were ubiquitous. And unless we have truly obscene amounts
of clout in the OS community, it's likely that all of those different
operating systems would implement different things to meet the stated
need, and then we'd have to have a complex bunch of platform-dependent
code in order to keep working on all of those systems.

To me, this is a road to nowhere. I have no problem at all with us
expressing our needs to the OS community, but realistically, any
PostgreSQL feature that depends on an OS feature less than twenty
years old is going to have to be optional, which means that if we want
to do anything about sharing address space mappings in the next few
years, it's going to need to be based on threads.

---

From: Andres Freund <[email-masked]>
Date: Thu, 8 Jun 2023 09:00:02 -0700

Hi,

> > 'mshare' syscall for this.

As-is that'd just lead to sharing page table, not the TLB. I don't think you
currently do sharing of the TLB for parts of your address space on x86
hardware. It's possible that something like that gets added to future
hardware, but ...

> by sharing the page tables

You don't share the page tables with huge pages on linux.

- Andres

---

From: Greg Sabino Mullane <[email-masked]>
Date: Thu, 8 Jun 2023 12:05:21 -0400

> in low single digits at best.

Stats are probably not possible, but based on years of consulting, as well
as watching places like SO, Slack, IRC, etc. over the years, IMO that's a
very accurate gut feeling. I'd hazard 1% or less for non-Linux systems.

Cheers,
Greg

---

From: Ilya Anfimov <[email-masked]>
Date: Thu, 8 Jun 2023 20:02:46 +0300

On Wed, Jun 07, 2023 at 10:26:07AM +1200, Thomas Munro wrote:
> On Tue, Jun 6, 2023 at 6:52???AM Andrew Dunstan <[email-masked]> wrote:
> of virtual memory.  That???s behind dshash.c and now the stats system,
> and various parts of the parallel executor code.  It???s slow and

 Isn't  all  the  memory operations would require nearly the same
shared memory allocators if someone switches to a threaded imple-
mentation?

> that we???re doing pretty unreasonable and inefficient contortions to

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 8 Jun 2023 19:07:48 +0200

I discovered this thread from a Twitter post "PostgreSQL will finally
be rewritten in Rust"  :)

---

From: Andres Freund <[email-masked]>
Date: Thu, 8 Jun 2023 11:41:00 -0700

Hi,

> We should not shy away from asking the OS people for adding the useful

There's a large part of this that is about hardware, not software. And
honestly, for most of the problems the answer is to just use threads. Adding
complexity to operating systems to make odd architectures like postgres'
better is a pretty dubious proposition.

I don't think we have even remotely enough influence on CPU design to make
e.g. *partial* TLB sharing across processes a thing.

> kernel hacker works for Oracle, andf they also seemed to be needing

The proposals around that don't really help us all that much. Sharing the page
table will be a bit more efficient, but it won't really change anything
dramatically.  From what I understand they are primarily interested in
changing properties of a memory mapping across multiple processes, e.g. making
some memory executable and have that reflected in all processes. I don't think
this will help us much.

Greetings,

Andres Freund

---

From: Andres Freund <[email-masked]>
Date: Thu, 8 Jun 2023 11:54:44 -0700

Hi,

On 2023-06-08 11:56:13 -0400, Robert Haas wrote:
> facilities were ubiquitous.

I'm less concerned about this aspect - most won't have upgraded to a version
of postgres that benefit from threaded postgres in a similar timeframe. And if
the benefits are large enough, people will move.  But:

> And unless we have truly obscene amounts of clout in the OS community, it's
> likely that all of those different operating systems would implement
> different things to meet the stated need, and then we'd have to have a
> complex bunch of platform-dependent code in order to keep working on all of
> those systems.

And even more likely, they just won't do anything, because it's a model that
large parts of the industry have decided isn't going anywhere. It'd be one
thing if we had 5 kernel devs that we could deploy to work on this, but we
don't. So we have to convince kernel devs employed by others that somehow this
is an urgent enough thing that they should work on it. The likely, imo
justified, answer is just going to be: Fix your architecture, then we can
talk.

Greetings,

Andres Freund

---

From: Thomas Munro <[email-masked]>
Date: Fri, 9 Jun 2023 07:10:35 +1200

> shared memory allocators if someone switches to a threaded imple-

It's true that we'd need concurrency-aware MemoryContext
implementations (details can be debated), but we wouldn't need that
address translation layer, which adds a measurable cost at every
access.

---

From: Thomas Munro <[email-masked]>
Date: Fri, 9 Jun 2023 07:34:49 +1200

> hardware. It's possible that something like that gets added to future

I wasn't in Mathew Wilcox's unconference in Ottawa but I found an
older article on LWN:

https://lwn.net/Articles/895217/

For what it's worth, FreeBSD hackers have studied this topic too (and
it's been done in Android and no doubt other systems before):

https://www.cs.rochester.edu/u/sandhya/papers/ispass19.pdf

I've shared that paper on this list before in the context of
super/huge pages and their benefits (to executable code, and to the
buffer pool), but a second topic in that paper is the idea of a shared
page table: "We find that sharing PTPs across different processes can
reduce execution cycles by as much as 6.9%. Moreover, the combined
effects of using superpages to map the main executable and sharing
PTPs for the small shared libraries can reduce execution cycles up to
18.2%."  And that's just part of it, because those guys are more
interested in shared code/libraries and such so that's probably not
even getting to the stuff like buffer pool and DSMs that we might tend
to think of first.

I'm pretty sure PostgreSQL (along with another fork-based RDBMSs
mentioned in this thread) must be one of the worst offenders for page
table bloat, simply because we can have a lot of processes and touch a
lot of memory.

I'm no expert in this stuff, but it seems to be that with shared page
table schemes you can avoid wasting huge amounts of RAM on duplicated
page table entries (pages * processes), and with huge/super pages you
can reduce the number of pages, but AFAIK you still can't escape the
TLB shootdown cost, which is all-or-nothing (PCID level at best).  The
only way to avoid TLB shootdowns on context switches is to have
*exactly the same memory map*.  Or, as Robert succinctly shouted,
"THREADS".

---

From: Dave Cramer <[email-masked]>
Date: Thu, 8 Jun 2023 15:59:29 -0400

> I discovered this thread from a Twitter post "PostgreSQL will finally

By the time we got around to finishing this, there would be a better
language to write it in.

Dave

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 9 Jun 2023 20:23:08 -0400

> session, socket) for now.

I am quite confused that few people seem to care about which model,
processes or threads, is better for Oracle, and how having both methods
available can be a reasonable solution to maintain.  Someone suggested
they abstracted the differences so the maintenance burden was minor, but
that seems very hard to me.

Did these vendors start with processes, add threads, and then find that
threads had downsides so they had to keep both?

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

---

From: David Geier <[email-masked]>
Date: Thu, 27 Jul 2023 15:27:57 +0200

Hi,

> because you can't share the TLB across processes.

Another problem I haven't seen mentioned yet is the excessive kernel 
memory usage because every process has its own set of page table entries 
(PTEs). Without huge pages the amount of wasted memory can be huge if 
shared buffers are big.

For example with 256 GiB of used shared buffers a single process needs 
about 256 MiB for the PTEs (for simplicity I ignored the tree structure 
of the page tables and just took the number of 4k pages times 4 bytes 
per PTE). With 512 connections, which is not uncommon for machines with 
many cores, a total of 128 GiB of memory is just spent on page tables.

We used non-transparent huge pages to work around this limitation but 
they come with plenty of provisioning challenges, especially in cloud 
infrastructures where different services run next to each other on the 
same server. Transparent huge pages have unpredictable performance 
disadvantages. Also if some backends only use shared buffers sparsely, 
memory is wasted for the remaining, unused range inside the huge page.

-- 
David Geier
(ServiceNow)

---

From: Merlin Moncure <[email-masked]>
Date: Fri, 11 Aug 2023 07:05:17 -0500

> (PTEs). Without huge pages the amount of wasted memory can be huge if

Hm, noted this upthread, but asking again, does this
help/benefit interactions with the operating system make oom kill
situations less likely?   These things are the bane of my existence, and
I'm having a hard time finding a solution that prevents them other than
running pgbouncer and lowering max_connections, which adds complexity.  I
suspect I'm not the only one dealing with this.   What's really scary about
these situations is they come without warning.  Here's a pretty typical
example per sar -r.

             kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit
%commit  kbactive   kbinact   kbdirty
 14:20:02       461612  15803476     97.16         0  11120280  12346980
  60.35  10017820   4806356       220
 14:30:01       378244  15886844     97.67         0  11239012  12296276
  60.10  10003540   4909180       240
 14:40:01       308632  15956456     98.10         0  11329516  12295892
  60.10  10015044   4981784       200
 14:50:01       458956  15806132     97.18         0  11383484  12101652
  59.15   9853612   5019916       112
 15:00:01     10592736   5672352     34.87         0   4446852   8378324
  40.95   1602532   3473020       264   <-- reboot!
 15:10:01      9151160   7113928     43.74         0   5298184   8968316
  43.83   2714936   3725092       124
 15:20:01      8629464   7635624     46.94         0   6016936   8777028
  42.90   2881044   4102888       148
 15:30:01      8467884   7797204     47.94         0   6285856   8653908
  42.30   2830572   4323292       436
 15:40:02      8077480   8187608     50.34         0   6828240   8482972
  41.46   2885416   4671620       320
 15:50:01      7683504   8581584     52.76         0   7226132   8511932
  41.60   2998752   4958880       308
 16:00:01      7239068   9026020     55.49         0   7649948   8496764
  41.53   3032140   5358388       232
 16:10:01      7030208   9234880     56.78         0   7899512   8461588
  41.36   3108692   5492296       216

Triggering query was heavy (maybe even runaway), server load was minimal
otherwise:

                 CPU     %user     %nice   %system   %iowait    %steal
%idle
 14:30:01        all      9.55      0.00      0.63      0.02      0.00
89.81

 14:40:01        all      9.95      0.00      0.69      0.02      0.00
89.33

 14:50:01        all     10.22      0.00      0.83      0.02      0.00
88.93

 15:00:01        all     10.62      0.00      1.63      0.76      0.00
86.99

 15:10:01        all      8.55      0.00      0.72      0.12      0.00
90.61

The conjecture here is that lots of idle connections make the server appear
to have less memory available than it looks, and sudden transient demands
can cause it to destabilize.

Just throwing it out there, if it can be shown to help it may be supportive
of moving forward with something like this, either instead of, or along
with, O_DIRECT or other internalized database memory management
strategies.  Lowering context switches, faster page access etc are of
course nice would not be a game changer for the workloads we see which are
pretty varied  (OLTP, analytics) although we don't extremely high
transaction rates.

merlin

---

From: David Geier <[email-masked]>
Date: Fri, 25 Aug 2023 14:01:23 +0200

Hi,

On 8/11/23 14:05, Merlin Moncure wrote:
> situations less likely?   These things are the bane of my existence, 
> and I'm having a hard time finding a solution that prevents them other 
> than running pgbouncer and lowering max_connections, which adds 
> complexity.  I suspect I'm not the only one dealing with this.  
>  What's really scary about these situations is they come without 
> warning.  Here's a pretty typical example per sar -r.
>
> The conjecture here is that lots of idle connections make the server 
> appear to have less memory available than it looks, and sudden 
> transient demands can cause it to destabilize.

It does in the sense that your server will have more memory available in 
case you have many long living connections around. Every connection has 
less kernel memory overhead if you will. Of course even then a runaway 
query will be able to invoke the OOM killer. The unfortunate thing with 
the OOM killer is that, in my experience, it often kills the 
checkpointer. That's because the checkpointer will touch all of shared 
buffers over time which makes it likely to get selected by the OOM 
killer. Have you tried disabling memory overcommit?

-- 
David Geier
(ServiceNow)