Let's make PostgreSQL multi-threaded
postgresql.org
postgresql.org
Converting code into multithreaded code tends to make it harder to test and debug FOREVER, as well as being more limited by default for certain system resources than a multi-process solution. Viewing and managing threads from the outside is harder, and killing a rogue thread is much more likely to crash a MT solution than killing a process in a typical resilient MP solution. Above all else, I need a database to be utterly reliable (or as close to it as possible) - including being able to back off in a mature fashion in cases of memory exhaustion (I have overcommit disabled to restore classical memory handling, i.e. malloc() can fail), and file system exhaustion. MT throws a wrench through most of the workings of a complex program, and unless some specific gain can be identified that compensates for adding complexity and fragility to virtually any change going forward, then... um... why? I read a bit "well, the other guys are doing it" handwaving:
"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."
The author starts by citing a decent variety of sources to have already expressed interest here, who see this as progress.
Migrating a bunch of per-process global variables to have scope (per thread or per session) may be risky, but gee, it just sounds like vaguely reasonable architecture to have these days to me.
I'm not trying to win an argument or anything here, I'm just highlighting from my and others' experiences that multithreading is a tradeoff not to be made casually. It makes some things faster, especially if not I/O bound, but it also increases dev and debug cost, and reduces the number of developers who can assist. That downside tends to permanent.
This was the famous evolution of Apache Httpd 1 being a forking multi-process model, and in v2 gaining a new pluggable strategy system, including thread pooled models. For great scalability wins. https://httpd.apache.org/docs/2.4/mod/worker.html
Context switching between processes is just such a taxing thing to do. So many caches to reset. Especially with all the mitigations most folks run, it's such a drain.
Sure it sounds interesting to try a branch of pg with, for example, just the sessions being multithreaded - but then how DOES one forcibly stomp on some session that has grabbed some critical lock without crashing other users' sessions? Killing off a session's thread inside of a MT'ed session handler without putting any other threads at risk would be the first problem (and an admin is likely to use "ps -Lef" to find the thread ID and then "kill"). Many MT programs I see lose their little minds if a thread is killed from outside.
Going too crazy with threads can also cause performance issues, since there is overhead - just less than for processes - around thread creation/switching/etc, and is why thread pools are common. There's a short article about this at:
https://stackoverflow.com/questions/5961536/what-is-best-a-single-threaded-or-a-multi-threaded-server/5964238#5964238
This is a pretty good page on the options in general, though dated (anyone already know of a newer equivalent to it?):
http://www.kegel.com/c10k.html
Presumably as one does now, through pg_terminate_backend()/pg_cancel_backend().
> Killing off a session's thread inside of a MT'ed session handler without putting any other threads at risk would be the first problem (and an admin is likely to use "ps -Lef" to find the thread ID and then "kill")
ps+kill already puts all of Postgres' processes at risk in Postgres' MP system, because processes that unexpectedly exit may have corrupted shared state, so in those situations PG restarts. MT would not significantly change that.
> Going too crazy with threads can also cause performance issues, since there is overhead - just less than for processes - around thread creation/switching/etc, and is why thread pools are common.
(emphasis mine)
Considering that PostgreSQL currently is a multi-process architecture, surely replacing the Process primitive with the Thread primitive will reduce the overhead of connection backends, all else being equal.
The big functional gain would be better connection handling. The current process-per-connection model has overhead and it's pretty common to see large database instances with double-digit max connection limits. Because connections are expensive and in (artificially) limited supply, application developers work around the limitations with connection pooling and/or proxy services.
Theoretically, a multi-threaded postgres could easily deal with thousands of concurrent connections - not just a performance improvement but a game changer in terms of application developer UX. When connections are cheap, the application just connects when it needs to communicate, no pgbouncer or connection pools needed.
I have no idea if the multi-threading proposal here is viable, but if it can make connections easier to manage it might be worth it.
https://www.citusdata.com/blog/2020/10/25/improving-postgres...
Edit: OrioleDB! https://github.com/orioledb/orioledb https://news.ycombinator.com/item?id=30462695
The 2021 slidedeck on problems & possibilities in postgres was super fun to read! Solving postgresql's wicked problems. https://www.slideshare.net/AlexanderKorotkov/solving-postgre...
Edit: I hadn't heard of Oriole. Neat!
What was missing from the new thread (it’s ref’d in footnotes) is why.. atm the hypothesis is (quoting from linked experiment on multi threading a while back)
Direct link which has more discussion,
https://www.postgresql.org/message-id/flat/9defcb14-a918-13f...
‘’’
What are the advantages of using threads instead of processes?
1. No need to use shared memory. So there is no static limit for amount of memory which can be used by Postgres. No need in distributed shared memory and other stuff designed to share memory between backends and bgworkers. 2. Threads significantly simplify implementation of parallel algorithms: interaction and transferring data between threads can be done easily and more efficiently. 3. It is possible to use more efficient/lightweight synchronization primitives. Postgres now mostly relies on its own low level sync.primitives which user-level implementation is using spinlocks and atomics and then fallback to OS semaphores/poll. I am not sure how much gain can we get by replacing this primitives with one optimized for threads. My colleague from Firebird community told me that just replacing processes with threads can obtain 20% increase of performance, but it is just first step and replacing sync. primitive can give much greater advantage. But may be for Postgres with its low level primitives it is not true. 4. Threads are more lightweight entities than processes. Context switch between threads takes less time than between process. And them consume less memory. It is usually possible to spawn more threads than processes. 5. More efficient access to virtual memory. As far as all threads are sharing the same memory space, TLB is used much efficiently in this case. 6. Faster backend startup. Certainly starting backend at each user's request is bad thing in any case. Some kind of connection pooling should be used in any case to provide acceptable performance. But in any case, start of new backend process in postgres causes a lot of page faults which have dramatical impact on performance. And there is no such problem with threads. ‘’’
regards, tom lane”
Given that Tom Lane objects to this, I’ve yet to see such a hugely compelling case to go against his advice.
https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd...
I wonder which layer would benefit the most.
You connect as pg_client to pg_server, you make a request (SQL query). It has to get tokenized/query planned, then probably some detection layer that already exists that says "do this all in a single thread or spread it across multiple". How does a single threaded process that spawns multiple threads -> a multi-threaded process benefit in these situations given the overhead of having to sync/message in between threads messages back and forth?
That’s what I assumed. I guess what I’m confused (and concerned) about is the types of “disaster” described, which aren’t just about trade offs, but a long tail of potentially undetectable error conditions. I would have expected that to be either a solved problem for Postgres or at least mostly agnostic to the concurrency mechanism. I would figure the change would be questionable for performance and broad computational complexity reasons, not correctness.
Maybe that’s par for the course and my naivety isn’t (just) distance from Postgres internals but the realities of concurrent programming in C?
Heikki: let’s make Postgres multithreaded, unless any of you have objections
Most of the rest of the core team: it would be a disaster or at the very least incredibly difficult
Heikki: okay so I’m not hearing any objections so let’s go
Most of the rest of the core team: …
Heikki (covering ears): lah lah lah
There was a very poignant message in amongst that mess which to summarize said:
Doing this will stall Postgres development for several versions (read years) and will divert development resources away from features while the devs are busy putting out fires.
Basically Postgres loses 10% performance vs threads[1] because of its Process based model, and leaves a bunch of caching related performance on the table but has the benefit that a process crash doesn’t bring down the entire database.
[1] the 10% figure is mentioned more than once
Personal View: Postgres development stalled in the early/mid 2000s and MySQL rose to prominence (so around V7/V8 time). While there were many people involved, I believe Tom Moor was instrumental in “dragging” Postgres development into V9/V10 land where it really started out-evolving it’s competition, terraforming the landscape for all the features it has now (thanks to every contributor who helped make it what it is today) this proposal while maybe well intentioned has the potential to drag Postgres into a multi year “dark age” of buggy code and perceived lack of progress. To be hyperbolic about it - that could be the start of an extinction event
There's definitely some ayes, including the people mentioned in the post upthread.
> Basically Postgres loses 10% performance vs threads[1] because of its Process based model, and leaves a bunch of caching related performance on the table but has the benefit that a process crash doesn’t bring down the entire database.
I don't think that's accurate at all. One of the big problems is that it forces us to make bad architectural choices to deal with the limitations, including a lot of code duplication.
> Basically Postgres loses 10% performance vs threads[1] because of its Process based model, and leaves a bunch of caching related performance on the table but has the benefit that a process crash doesn’t bring down the entire database.
A process crash does bring the entire database down today.
> Personal View: Postgres development stalled in the early/mid 2000s and MySQL rose to prominence (so around V7/V8 time). While there were many people involved, I believe Tom Moor was instrumental in “dragging” Postgres development into V9/V10 land where it really started out-evolving it’s competition, terraforming the landscape for all the features it has now (thanks to every contributor who helped make it what it is today) this proposal while maybe well intentioned has the potential to drag Postgres into a multi year “dark age” of buggy code and perceived lack of progress. To be hyperbolic about it - that could be the start of an extinction event
Nobody of that name has worked meaningfully on postgres from what I can tell. And I worked on PG during that time.
But I believe you knew that but pedantry stopped you from just suggesting it.
In addition my post should in no way be considered a slight towards the rest of the Postgres developers, I just remember him as having a singular focus to product that I have a lot of respect for
I was wondering about it, but thought it wouldn't really make sense, given that Tom was the instrumental developer starting far before "V9/V10 land". If anything that time was marked by Tom having a bit less of an outsize role (by virtue of more others contributing, not because he wasn't doing important work anymore).