6 ms·

Let's make PostgreSQL multi-threaded

56 points by Icathian 3y ago

Icathian 3y ago
Pretty neat pitch by a very prolific PostgreSQL hacker about refactoring from the current multiprocessing architecture to a multi-threaded one. I don't know how it'll play out but personally I think it looks promising.
rektide 3y ago
OT & Apologies for being unspecific, but can anyone else jog my memory about the open source fork 2-3 years ago that was going to try to pretty heavily edit internals/try some of new architectural designs? Some searches have not lead me back to the name of it... Edit: OrioleDB! https://github.com/orioledb/orioledb https://github.com/orioledb/orioledb https://news.ycombinator.com/item?id=30462695 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-postgresql-wicked-problems https://www.slideshare.net/AlexanderKorotkov/solving-postgre...
- Icathian 3y ago
  There have been a good handful of those. Greenplum is much older, PostgresPro was 2015. I'm sure there have been others too. Edit: I hadn't heard of Oriole. Neat!
  - kapilvt 3y ago
    Fwiw my understanding is that Oriole wants to be an extension, and lives atm as a transient fork for the purpose of improving issues on storage apis upstream, re a bunch of active up streaming work on table access methods (TAM) aka pg -luggable storage, to handle different backend trade offs (per oriole’s benefits).
erlkonig 3y ago
[I wrote this mostly imagining the idea was about converting the entire Postgresql service to a single monolithic process. I'm not a fan so far. If is actually around coalescing like processes down to a single multithreaded process, that's more reasonable but still comes at a future cost - and whether pointful is still a question] 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." But that isn't a functional gain. And: "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." But this is NOT a worthwhile goal. Fun, perhaps. Diverting or challenging, perhaps. A disaster, quite possibly. But without identifying a goal that can only be achieved by walking into the multithreading pit, the project is a waste of time for end users. Possibly a growth experience for the experimenters, regardless of whether successful.
- rektide 3y ago
  This feels a bit like you are using an image of absolute safety to hold hostages, not allowing the potential for change & improvement. 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.
MuffinFlavored 3y ago
How massive of an undertaking is this going to be? There is a mention they think they could get it done in 1-2 releases. https://pgpedia.info/postgresql-versions/index.html https://pgpedia.info/postgresql-versions/index.html they seem to release once a year from what I can deduct. 1-2 years, not bad. I wonder how many people would work on this (full time/part time) and how many resource hours would go into this. 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?
- Icathian 3y ago
  Postgres in particular has many other processes that do a ton of bookkeeping and maintenance. Autovacuum, checkpointer, walwriter, etc. Each of these having process overhead and also having to deal with shared memory is probably the target of this proposal if I had to guess.
  - tristan957 3y ago
    This proposal is more concerned with each connection being its own process. I think the current thoughts are to keep utility processes around.
tonymillion 3y ago
Summary: 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
- Icathian 3y ago
  That's really not right. Andres Freund and Robert Haas at least are pretty clearly theoretically in support (which is all anyone can offer at this point). Those are some big names to have in the "aye" column.
  - tonymillion 3y ago
    I saw a bunch of nays and a lot of “abstains” a lack of vote against something is not a vote of approval. 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
    - anarazel 3y ago
      > I saw a bunch of nays and a lot of “abstains” a lack of vote against something is not a vote of approval. 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.
kapilvt 3y ago
Fwiw I agree with Tom lane, there’s a bunch of extension code out there, that would need to be upgraded and verification around multithreaded safety. It’s a python 2 to 3 transition except assuming correct multi threading in c (most popular lang on extension atm) aka potential disasters for end users. 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-13fe-4b80-a0b02ff85527@postgrespro.ru 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. ‘’’
eyelidlessness 3y ago
At a very far distance from any real familiarity with Postgres internals, it’s more than a little bit alarming to me that a RDBMS, which already offers strong concurrency guarantees and which already employs multi-process concurrency to do meaningful work, would even have “disaster” uttered in a discussion of a change to its higher level concurrency mechanics. Maybe that’s very naive and I’m very far out of my depth? Is Postgres concurrency really so close to the metal that it should even matter whether it’s operating between processes, threads, or some other shared workload abstraction?
- kapilvt 3y ago
  It’s not higher level concurrency mechanics, it’s different, and changing between assumptions of single process concurrency to multi-threaded are generally non trivial for a long established project in c with a vibrant ecosystem (note the evaluation on tcl, perl, and python runtimes). And in general PostgreSQL is a user space application, albeit a database, and generally portable. WRT to metal, this is a few layers removed, but yeah most databases care about their concurrency model, trade offs on x clients, n transactions, y queries, z nodes. In this case the difference between processes and threads causes the concern re switching. In most oss projects this type of change if it happens might take some time. Per my other comment in this thread, pg has a vibrant ecocsystem community both oss, commercial and within org that this will also impact.
  - eyelidlessness 3y ago
    > It’s not higher level concurrency mechanics 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?
alberth 3y ago
>”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” 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-3cd9-af5b-ac0d801163f4%40iki.fi https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd...

56 points by Icathian 3y ago

Pretty neat pitch by a very prolific PostgreSQL hacker about refactoring from the current multiprocessing architecture to a multi-threaded one. I don't know how it'll play out but personally I think it looks promising.

rektide 3y ago

OT & Apologies for being unspecific, but can anyone else jog my memory about the open source fork 2-3 years ago that was going to try to pretty heavily edit internals/try some of new architectural designs? Some searches have not lead me back to the name of it... Edit: OrioleDB! https://github.com/orioledb/orioledb https://github.com/orioledb/orioledb https://news.ycombinator.com/item?id=30462695 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-postgresql-wicked-problems https://www.slideshare.net/AlexanderKorotkov/solving-postgre...

Icathian 3y ago
There have been a good handful of those. Greenplum is much older, PostgresPro was 2015. I'm sure there have been others too. Edit: I hadn't heard of Oriole. Neat!
- kapilvt 3y ago
  Fwiw my understanding is that Oriole wants to be an extension, and lives atm as a transient fork for the purpose of improving issues on storage apis upstream, re a bunch of active up streaming work on table access methods (TAM) aka pg -luggable storage, to handle different backend trade offs (per oriole’s benefits).

erlkonig 3y ago

[I wrote this mostly imagining the idea was about converting the entire Postgresql service to a single monolithic process. I'm not a fan so far. If is actually around coalescing like processes down to a single multithreaded process, that's more reasonable but still comes at a future cost - and whether pointful is still a question] 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." But that isn't a functional gain. And: "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." But this is NOT a worthwhile goal. Fun, perhaps. Diverting or challenging, perhaps. A disaster, quite possibly. But without identifying a goal that can only be achieved by walking into the multithreading pit, the project is a waste of time for end users. Possibly a growth experience for the experimenters, regardless of whether successful.

rektide 3y ago
This feels a bit like you are using an image of absolute safety to hold hostages, not allowing the potential for change & improvement. 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.

MuffinFlavored 3y ago

How massive of an undertaking is this going to be? There is a mention they think they could get it done in 1-2 releases. https://pgpedia.info/postgresql-versions/index.html https://pgpedia.info/postgresql-versions/index.html they seem to release once a year from what I can deduct. 1-2 years, not bad. I wonder how many people would work on this (full time/part time) and how many resource hours would go into this. 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?

Icathian 3y ago
Postgres in particular has many other processes that do a ton of bookkeeping and maintenance. Autovacuum, checkpointer, walwriter, etc. Each of these having process overhead and also having to deal with shared memory is probably the target of this proposal if I had to guess.
- tristan957 3y ago
  This proposal is more concerned with each connection being its own process. I think the current thoughts are to keep utility processes around.

tonymillion 3y ago

Summary: 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

Icathian 3y ago
That's really not right. Andres Freund and Robert Haas at least are pretty clearly theoretically in support (which is all anyone can offer at this point). Those are some big names to have in the "aye" column.
- tonymillion 3y ago
  I saw a bunch of nays and a lot of “abstains” a lack of vote against something is not a vote of approval. 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
  - anarazel 3y ago
    > I saw a bunch of nays and a lot of “abstains” a lack of vote against something is not a vote of approval. 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.

kapilvt 3y ago

Fwiw I agree with Tom lane, there’s a bunch of extension code out there, that would need to be upgraded and verification around multithreaded safety. It’s a python 2 to 3 transition except assuming correct multi threading in c (most popular lang on extension atm) aka potential disasters for end users. 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-13fe-4b80-a0b02ff85527@postgrespro.ru 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. ‘’’

eyelidlessness 3y ago

At a very far distance from any real familiarity with Postgres internals, it’s more than a little bit alarming to me that a RDBMS, which already offers strong concurrency guarantees and which already employs multi-process concurrency to do meaningful work, would even have “disaster” uttered in a discussion of a change to its higher level concurrency mechanics. Maybe that’s very naive and I’m very far out of my depth? Is Postgres concurrency really so close to the metal that it should even matter whether it’s operating between processes, threads, or some other shared workload abstraction?

kapilvt 3y ago
It’s not higher level concurrency mechanics, it’s different, and changing between assumptions of single process concurrency to multi-threaded are generally non trivial for a long established project in c with a vibrant ecosystem (note the evaluation on tcl, perl, and python runtimes). And in general PostgreSQL is a user space application, albeit a database, and generally portable. WRT to metal, this is a few layers removed, but yeah most databases care about their concurrency model, trade offs on x clients, n transactions, y queries, z nodes. In this case the difference between processes and threads causes the concern re switching. In most oss projects this type of change if it happens might take some time. Per my other comment in this thread, pg has a vibrant ecocsystem community both oss, commercial and within org that this will also impact.
- eyelidlessness 3y ago
  > It’s not higher level concurrency mechanics 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?

alberth 3y ago

>”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” 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-3cd9-af5b-ac0d801163f4%40iki.fi https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd...