Let's make PostgreSQL multi-threaded

Heikki Linnakangas <hlinnaka@iki.fi> 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

On Mon Jun 5, 2023 at 9:51 AM CDT, Heikki Linnakangas wrote:

# Global variables

We have a lot of global and static variables:

$ objdump -t bin/postgres | grep -e "\.data" -e "\.bss" | grep -v
"data.rel.ro" | wc -l
1666

Some of them are pointers to shared memory structures and can stay as
they are. But many of them are per-connection state. The most
straightforward conversion for those is to turn them into thread-local
variables, like Konstantin did in [0].

It might be good to have some kind of a Session context struct that we
pass everywhere, or maybe have a single thread-local variable to hold
it. Many of the global variables would become fields in the Session. But
that's future work.

+1 to the session context idea after the more simple thread_local
storage idea.

# Extensions

A lot of extensions also contain global variables or other things that
break in a multi-threaded environment. We need a way to label extensions
that support multi-threading. And in the future, also extensions that
*require* a multi-threaded server.

Let's add flags to the control file to mark if the extension is
thread-safe and/or process-safe. If you try to load an extension that's
not compatible with the server's mode, throw an error.

We might need new functions in addition _PG_init, called at connection
startup and shutdown. And background worker API probably needs some changes.

It would be a good idea to start exposing a variable through pkg-config
to tell whether the backend is multi-threaded or multi-process.

# Exposed PIDs

We expose backend process PIDs to users in a few places.
pg_stat_activity.pid and pg_terminate_backend(), for example. They need
to be replaced, or we can assign a fake PID to each connection when
running in multi-threaded mode.

Would it be possible to just transparently slot in the thread ID
instead?

# Thread-safe libraries

Need to switch to thread-safe versions of library functions, e.g.
uselocale() instead of setlocale().

Seems like a good starting point.

The Python interpreter has a Global Interpreter Lock. It's not possible
to create two completely independent Python interpreters in the same
process, there will be some lock contention on the GIL. Fortunately, the
python community just accepted https://peps.python.org/pep-0684/. That's
exactly what we need: it makes it possible for separate interpreters to
have their own GILs. It's not clear to me if that's in Python 3.12
already, or under development for some future version, but by the time
we make the switch in Postgres, there probably will be a solution in
cpython.

3.12 is the currently in-development version of Python. 3.12 is planned
for release in October of this year.

A workaround that some projects seem to do is to use multiple Python
interpreters[0]https://peps.python.org/pep-0684/#existing-use-of-multiple-interpreters, though it seems uncommon. It might be important to note
depending on the minimum version of Python Postgres aims to support (not
sure on this policy).

The C-API of Python also provides mechanisms for releasing the GIL. I am
not familiar with how Postgres uses Python, but I have seen huge
improvements to performance with well-placed GIL releases in
multi-threaded contexts. Surely this API would just become a no-op after
the PEP is implemented.

[0]: https://peps.python.org/pep-0684/#existing-use-of-multiple-interpreters

--
Tristan Partin
Neon (https://neon.tech)

On 05/06/2023 11:18, Tom Lane wrote:

Heikki Linnakangas <hlinnaka@iki.fi> 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.

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)

On 05/06/2023 11:28, Tristan Partin wrote:

On Mon Jun 5, 2023 at 9:51 AM CDT, Heikki Linnakangas wrote:

# Extensions

A lot of extensions also contain global variables or other things that
break in a multi-threaded environment. We need a way to label extensions
that support multi-threading. And in the future, also extensions that
*require* a multi-threaded server.

Let's add flags to the control file to mark if the extension is
thread-safe and/or process-safe. If you try to load an extension that's
not compatible with the server's mode, throw an error.

We might need new functions in addition _PG_init, called at connection
startup and shutdown. And background worker API probably needs some changes.

It would be a good idea to start exposing a variable through pkg-config
to tell whether the backend is multi-threaded or multi-process.

I think we need to support both modes without having to recompile the
server or the extensions. So it needs to be a runtime check.

# Exposed PIDs

We expose backend process PIDs to users in a few places.
pg_stat_activity.pid and pg_terminate_backend(), for example. They need
to be replaced, or we can assign a fake PID to each connection when
running in multi-threaded mode.

Would it be possible to just transparently slot in the thread ID
instead?

Perhaps. It might break applications that use the PID directly with e.g.
'kill <PID>', though.

The Python interpreter has a Global Interpreter Lock. It's not possible
to create two completely independent Python interpreters in the same
process, there will be some lock contention on the GIL. Fortunately, the
python community just accepted https://peps.python.org/pep-0684/. That's
exactly what we need: it makes it possible for separate interpreters to
have their own GILs. It's not clear to me if that's in Python 3.12
already, or under development for some future version, but by the time
we make the switch in Postgres, there probably will be a solution in
cpython.

3.12 is the currently in-development version of Python. 3.12 is planned
for release in October of this year.

A workaround that some projects seem to do is to use multiple Python
interpreters[0], though it seems uncommon. It might be important to note
depending on the minimum version of Python Postgres aims to support (not
sure on this policy).

The C-API of Python also provides mechanisms for releasing the GIL. I am
not familiar with how Postgres uses Python, but I have seen huge
improvements to performance with well-placed GIL releases in
multi-threaded contexts. Surely this API would just become a no-op after
the PEP is implemented.

[0]: https://peps.python.org/pep-0684/#existing-use-of-multiple-interpreters

Oh, cool. I'm inclined to jump straight to PEP-684 and require python
3.12 in multi-threaded mode, though, or just accept that it's slow. But
let's see what the state of the world is when we get there.

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

On 05/06/2023 11:18, Tom Lane wrote:

Heikki Linnakangas <hlinnaka(at)iki(dot)fi> 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.

I fully agreed with Tom.

First, it is not clear what are the benefits of architecture change?

Performance?

Development becomes much more complicated and error-prone.

There are still many low-hanging fruit to be had that can improve
performance.
And the code can gradually and safely remove multithreading barriers.

1. gradual reduction of global variables
2. introduction of local context structures
3. shrink current structures (to fit in 32, 64 boundaries)

4. scope reduction

My 2c.

regards,

Ranier Vilela

On Mon, Jun 5, 2023 at 01:26:00PM -0300, Ranier Vilela wrote:

On 05/06/2023 11:18, Tom Lane wrote:

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.

I fully agreed with Tom.

First, it is not clear what are the benefits of architecture change?

Performance?

Development becomes much more complicated and error-prone.

I agree the costs of going threaded have been reduced with compiler and
library improvements, but I don't know if they are reduced enough for
the change to be a net benefit, except on Windows where the process
creation overhead is high.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Em seg., 5 de jun. de 2023 às 13:42, Bruce Momjian <bruce@momjian.us>
escreveu:

On Mon, Jun 5, 2023 at 01:26:00PM -0300, Ranier Vilela wrote:

On 05/06/2023 11:18, Tom Lane wrote:

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.

I fully agreed with Tom.

First, it is not clear what are the benefits of architecture change?

Performance?

Development becomes much more complicated and error-prone.

I agree the costs of going threaded have been reduced with compiler and
library improvements, but I don't know if they are reduced enough for
the change to be a net benefit, except on Windows where the process
creation overhead is high.

Yeah, but process creation, even on windows, is a tiny part of response
time.
SGDB has one connection per user, so one process or thread.

Unlike a webserver like Nginx, with hundreds of thousands connections.
For the record, Nginx is multithread and uses -Werror for default. (Make
all warnings into errors)

regards,
Ranier Vilela

nOn Mon, Jun 5, 2023 at 05:51:57PM +0300, Heikki Linnakangas wrote:

# Restart on crash

If a backend process crashes, postmaster terminates all other backends and
restarts the system. That's hard (impossible?) to do safely if everything
runs in one process. We can continue have a separate postmaster process that
just monitors the main process and restarts it on crash.

It would be good to know what new class of errors would cause server
restarts, e.g., memory allocation failures?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On 05/06/2023 12:26, Ranier Vilela wrote:

First, it is not clear what are the benefits of architecture change?

Performance?

I doubt it makes much performance difference, at least not initially. It
might help a little with backend startup time, and maybe some other
things. And might reduce the overhead of context switches and TLB cache
misses.

In the long run, a single-process architecture makes it easier to have
shared catalog caches, plan cache, etc. which can improve performance.
And it can make it easier to launch helper threads for things where
worker processes would be too heavy-weight. But those benefits will
require more work, they won't happen just by replacing processes with
threads.

The ease of developing things like that is my motivation.

Development becomes much more complicated and error-prone.

I don't agree with that.

We currently bend over backwards to make all allocations fixed-sized in
shared memory. You learn to live with that, but a lot of things would be
simpler if you could allocate and free in shared memory more freely.
It's no panacea, you still need to be careful with locking and
concurrency. But a lot simpler.

We have built dynamic shared memory etc. over the years to work around
the limitations of shared memory. But it's still a lot more complicated.

Code that doesn't need to communicate with other processes/threads is
simple to write in either model.

There are still many low-hanging fruit to be had that can improve
performance.
And the code can gradually and safely remove multithreading barriers.

1. gradual reduction of global variables
2. introduction of local context structures
3. shrink current structures (to fit in 32, 64 boundaries)

4. scope reduction

Right, the reason I started this thread is to explicitly note that it is
a worthy goal. If it's not, the above steps would be pointless. But if
we agree that it is a worthy goal, we can start to incrementally work
towards it.

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

On 05/06/2023 13:10, Bruce Momjian wrote:

nOn Mon, Jun 5, 2023 at 05:51:57PM +0300, Heikki Linnakangas wrote:

# Restart on crash

If a backend process crashes, postmaster terminates all other backends and
restarts the system. That's hard (impossible?) to do safely if everything
runs in one process. We can continue have a separate postmaster process that
just monitors the main process and restarts it on crash.

It would be good to know what new class of errors would cause server
restarts, e.g., memory allocation failures?

You mean "out of memory"? No, that would be horrible.

I don't think there would be any new class of errors that would cause
server restarts. In theory, having a separate address space for each
backend gives you some protection. In practice, there are a lot of
shared memory structures anyway that you can stomp over, and a segfault
or unexpected exit of any backend process causes postmaster to restart
the whole system anyway.

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

On Mon, Jun 5, 2023 at 12:25 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:

We currently bend over backwards to make all allocations fixed-sized in
shared memory. You learn to live with that, but a lot of things would be
simpler if you could allocate and free in shared memory more freely.
It's no panacea, you still need to be careful with locking and
concurrency. But a lot simpler.

Would this help with oom killer in linux?

Isn't it true that pgbouncer provides a lot of the same benefits?

merlin

On 6/5/23 11:33 AM, Heikki Linnakangas wrote:

On 05/06/2023 11:18, Tom Lane wrote:

Heikki Linnakangas <hlinnaka@iki.fi> 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.

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.

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

On 05/06/2023 13:32, Merlin Moncure wrote:

Would this help with oom killer in linux?

Hmm, I guess the OOM killer would better understand what Postgres is
doing, it's not very smart about accounting shared memory. You still
wouldn't want the OOM killer to kill Postgres, though, so I think you'd
still want to disable it in production systems.

Isn't it true that pgbouncer provides a lot of the same benefits?

I guess there is some overlap, although I don't really think of it that
way. Firstly, pgbouncer has its own set of problems. Secondly, switching
to threads would not make connection poolers obsolete. Maybe in the
distant future, Postgres could handle thousands of connections with
ease, and threads would make that easier to achieve that, but that would
need a lot of more work.

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

On Mon, Jun 5, 2023 at 08:29:16PM +0300, Heikki Linnakangas wrote:

On 05/06/2023 13:10, Bruce Momjian wrote:

nOn Mon, Jun 5, 2023 at 05:51:57PM +0300, Heikki Linnakangas wrote:

# Restart on crash

If a backend process crashes, postmaster terminates all other backends and
restarts the system. That's hard (impossible?) to do safely if everything
runs in one process. We can continue have a separate postmaster process that
just monitors the main process and restarts it on crash.

It would be good to know what new class of errors would cause server
restarts, e.g., memory allocation failures?

You mean "out of memory"? No, that would be horrible.

I don't think there would be any new class of errors that would cause server
restarts. In theory, having a separate address space for each backend gives
you some protection. In practice, there are a lot of shared memory
structures anyway that you can stomp over, and a segfault or unexpected exit
of any backend process causes postmaster to restart the whole system anyway.

Uh, yes, but don't we detect failures while modifying shared memory and
force a restart? Wouldn't the scope of failures be much larger?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On 05/06/2023 14:04, Bruce Momjian wrote:

On Mon, Jun 5, 2023 at 08:29:16PM +0300, Heikki Linnakangas wrote:

I don't think there would be any new class of errors that would cause server
restarts. In theory, having a separate address space for each backend gives
you some protection. In practice, there are a lot of shared memory
structures anyway that you can stomp over, and a segfault or unexpected exit
of any backend process causes postmaster to restart the whole system anyway.

Uh, yes, but don't we detect failures while modifying shared memory and
force a restart? Wouldn't the scope of failures be much larger?

If one process writes over shared memory that it shouldn't, it can cause
a crash in that process or some other process that reads it. Same with
multiple threads, no difference there.

With a single process, one thread can modify another thread's "backend
private" memory, and cause the other thread to crash. Perhaps that's
what you meant?

In practice, I don't think it's so bad. Even in a multi-threaded
environment, common bugs like buffer overflows and use-after-free are
still much more likely to access memory owned by the same thread, thanks
to how memory allocators work. And a completely random memory access is
still more likely to cause a segfault than corrupting another thread's
memory. And tools like CLOBBER_FREED_MEMORY/MEMORY_CONTEXT_CHECKING and
valgrind are pretty good at catching memory access bugs at development
time, whether it's multiple processes or threads.

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

On 2023-06-05 Mo 11:18, Tom Lane wrote:

Heikki Linnakangas<hlinnaka@iki.fi> 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.

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

Hi

In the long run, a single-process architecture makes it easier to have

shared catalog caches, plan cache, etc. which can improve performance.
And it can make it easier to launch helper threads for things where
worker processes would be too heavy-weight. But those benefits will
require more work, they won't happen just by replacing processes with
threads.

The shared plan cache is not a silver bullet. The good management of shared
plan cache is very very difficult. Our heuristic about custom plans in
prepared statements is nothing, and you should reduce the usage of custom
plans too.

There are a lot of issues known from Oracle. The benefits can be just for
very primitive very fast queries, or extra complex queries where generic
plan is used. Current implementation of local plan caches has lot of
issues (that cannot be fixed), but shared plan cache is another level of
complexity

Regards

Pavel

On 6/5/23 14:51, Andrew Dunstan wrote:

On 2023-06-05 Mo 11:18, Tom Lane wrote:

Heikki Linnakangas<hlinnaka@iki.fi> 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.

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.

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

On Mon, Jun 5, 2023 at 8:18 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

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