5 ms·

Sorry if I offend anybody, but this sounds like such a bad idea. I have been running various versions of postgres in production for 15 years with thousands of p

Sorry if I offend anybody, but this sounds like such a bad idea. I have been running various versions of postgres in production for 15 years with thousands of processes on super beefy machines, and I can tell you without a doubt that sometimes those processes crash - specially if you are running any of the extensions. Nevertheless, Postgres has 99% of the time proven to be resilient. The idea that a bad client can bring the whole cluster down because it hit a bug sounds scary. Every try creating a spatial index on thousands/millions of records that have nasty overly complex or badly digitized geometries? Sadly, crashes are part of that workflow, and changing this from process to threading would mean all the other clients also crashing and cutting connections. This as a potential problem because I want to avoid context switching overhead or cache misses, no thanks.

zeroimpl 3y ago
However, it's already the case that if a postgres process crashes, the whole cluster gets restarted. I've occasionally seen this message: WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing
- niccl 3y ago
  yes, but postmaster is still running to roll back the transaction. If you crash a single multi-threaded process, you may lose postmaster as well and then sadness would ensue
  - jtc331 3y ago
    If you read the thread you’d see the discussion includes still having e.g. postmaster as a separate process.
  - moonchrome 3y ago
    Restart on crash doesn't sound that difficult to do.
  - mattashii 3y ago
    The threaded design wouldn't necessarily be single-process, it would just not have 1 process for every connection. Things like crash detection could still be handled in a separate process. The reason to use threading in most cases is to reduce communication and switching overhead, but for low-traffic backends like a crash handler the overhead of it being a process is quite limited - when it gets triggered context switching overhead is the least of your problems.
    - Yoric 3y ago
      Seconded. For instance, Firefox' crash reporter has always been a separate process, even at the time Firefox was mostly single-process, single-threaded. Last time I checked, this was still the case.
  - cyberax 3y ago
    PostgreSQL can recover from abruptly aborted transactions (think "pulled the power cord") by replaying the journal. This is not going to change anyway.
  - cogman10 3y ago
    Transaction roll back is a part of the WAL. Databases write to the disk an intent to change things, what should be changed, and a "commit" of the change when finished so that all changes happen as a unit. If the DB process is interrupted during that log write then all changes associated with that transaction are rolled back. Threaded vs process won't affect that.
    - dfox 3y ago
      Running the whole DBMS as a bunch of threads in single process changes how fast is the recovery from some kind of temporary inconsistency. In the ideal world, this should not happen, but in reality it does and you do not want to bring the whole thing down because of some superficial data corruption. On the other hand, all cases of fixable corrupted data in PostgreSQL I have seen were result of somebody doing something totally dumb (rsyncing live cluster, even between architectures), while on InnoDB it seems to happen somewhat randomly without any obvious reason of somebody doing stupid things.
  - anarazel 3y ago
    We would still have a separate process doing that part of postmaster's work.
  - tracker1 3y ago
    You can still have a master control process separate from the client connections.
- lelanthran 3y ago
  > However, it's already the case that if a postgres process crashes, the whole cluster gets restarted. I've occasionally seen this message: Sure, but the blast radius of corruption is limited to that shared memory, not all the memory of all the processes. You can at least use the fact that a process has crashed to ensure that the corruption doesn't spread. (This is why it restarts: there is no guarantee that the shared memory is valid, so the other processes are stopped before they attempt to use that potentially invalid memory) With threads, all memory is shared memory. A single thread that crashes can make other threads data invalid before the detection of the crash.
mike_hock 3y ago
Also, reducing context switching overhead (or any other CPU overhead) is probably not gonna fix the garbage I/O performance.
dan-robertson 3y ago
Is the actual number you got 99%? Seems low to me but I don’t really know about Postgres. That’s 3 and a half days of downtime per year, or an hour and a half per week.
- dfox 3y ago
  Well, hour and half per week is the amount of downtime that you need for modestly sized database (units of TB) accessed by legacy clients that have ridiculously long running transactions that interfere with autovacuum.
Shorel 3y ago
Reading your comment makes me think it is not only a good idea, it is a necessity. Relying on crashing as a bug recovery system is a good idea? Crashing is just part of the workflow? That's insane, and a good argument against PostgreSQL in any production system. It is possible PostgreSQL doesn't migrate to a thread based model, and I am not arguing they should. But debug and patch the causes of these crashes? Absolutely yes, and the sooner, the better.
- bhaney 3y ago
  > Relying on crashing as a bug recovery system is a good idea? Crashing is just part of the workflow? That's insane Erlang users don't seem to agree with you
- paulddraper 3y ago
  Cars are designed with airbags?! Like, they are supposed to crash?!?
- anarazel 3y ago
  We do fix crashes etc, even if the postgres manages to restart. I think the post upthread references an out-of-core extension we don't control, which in turn depends on many external libraries it doesn't control either...
- rtpg 3y ago
  We don't want stuff to crash. But we also want data integrity to be maintained. We also want things to work. In a world with extensions written in C to support a lot of cool things with Postgres, you want to walk and chew bubblegum on this front. Though to your point, a C extension can totally destroy your data in other ways, and there are likely ways to add more barriers. And hey, we should fix bugs!
- mindwok 3y ago
  They are still debugging and patching the causes. The crash detection is just to try and prevent a single bug from bringing down the whole system.
- dialogbox 3y ago

zeroimpl 3y ago

However, it's already the case that if a postgres process crashes, the whole cluster gets restarted. I've occasionally seen this message: WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing

niccl 3y ago
yes, but postmaster is still running to roll back the transaction. If you crash a single multi-threaded process, you may lose postmaster as well and then sadness would ensue
- jtc331 3y ago
  If you read the thread you’d see the discussion includes still having e.g. postmaster as a separate process.
- moonchrome 3y ago
  Restart on crash doesn't sound that difficult to do.
- mattashii 3y ago
  The threaded design wouldn't necessarily be single-process, it would just not have 1 process for every connection. Things like crash detection could still be handled in a separate process. The reason to use threading in most cases is to reduce communication and switching overhead, but for low-traffic backends like a crash handler the overhead of it being a process is quite limited - when it gets triggered context switching overhead is the least of your problems.
  - Yoric 3y ago
    Seconded. For instance, Firefox' crash reporter has always been a separate process, even at the time Firefox was mostly single-process, single-threaded. Last time I checked, this was still the case.
- cyberax 3y ago
  PostgreSQL can recover from abruptly aborted transactions (think "pulled the power cord") by replaying the journal. This is not going to change anyway.
- cogman10 3y ago
  Transaction roll back is a part of the WAL. Databases write to the disk an intent to change things, what should be changed, and a "commit" of the change when finished so that all changes happen as a unit. If the DB process is interrupted during that log write then all changes associated with that transaction are rolled back. Threaded vs process won't affect that.
  - dfox 3y ago
    Running the whole DBMS as a bunch of threads in single process changes how fast is the recovery from some kind of temporary inconsistency. In the ideal world, this should not happen, but in reality it does and you do not want to bring the whole thing down because of some superficial data corruption. On the other hand, all cases of fixable corrupted data in PostgreSQL I have seen were result of somebody doing something totally dumb (rsyncing live cluster, even between architectures), while on InnoDB it seems to happen somewhat randomly without any obvious reason of somebody doing stupid things.
- anarazel 3y ago
  We would still have a separate process doing that part of postmaster's work.
- tracker1 3y ago
  You can still have a master control process separate from the client connections.
lelanthran 3y ago
> However, it's already the case that if a postgres process crashes, the whole cluster gets restarted. I've occasionally seen this message: Sure, but the blast radius of corruption is limited to that shared memory, not all the memory of all the processes. You can at least use the fact that a process has crashed to ensure that the corruption doesn't spread. (This is why it restarts: there is no guarantee that the shared memory is valid, so the other processes are stopped before they attempt to use that potentially invalid memory) With threads, all memory is shared memory. A single thread that crashes can make other threads data invalid before the detection of the crash.

mike_hock 3y ago

Also, reducing context switching overhead (or any other CPU overhead) is probably not gonna fix the garbage I/O performance.

dan-robertson 3y ago

Is the actual number you got 99%? Seems low to me but I don’t really know about Postgres. That’s 3 and a half days of downtime per year, or an hour and a half per week.

dfox 3y ago
Well, hour and half per week is the amount of downtime that you need for modestly sized database (units of TB) accessed by legacy clients that have ridiculously long running transactions that interfere with autovacuum.

Shorel 3y ago

Reading your comment makes me think it is not only a good idea, it is a necessity. Relying on crashing as a bug recovery system is a good idea? Crashing is just part of the workflow? That's insane, and a good argument against PostgreSQL in any production system. It is possible PostgreSQL doesn't migrate to a thread based model, and I am not arguing they should. But debug and patch the causes of these crashes? Absolutely yes, and the sooner, the better.

bhaney 3y ago
> Relying on crashing as a bug recovery system is a good idea? Crashing is just part of the workflow? That's insane Erlang users don't seem to agree with you
paulddraper 3y ago
Cars are designed with airbags?! Like, they are supposed to crash?!?
anarazel 3y ago
We do fix crashes etc, even if the postgres manages to restart. I think the post upthread references an out-of-core extension we don't control, which in turn depends on many external libraries it doesn't control either...
rtpg 3y ago
We don't want stuff to crash. But we also want data integrity to be maintained. We also want things to work. In a world with extensions written in C to support a lot of cool things with Postgres, you want to walk and chew bubblegum on this front. Though to your point, a C extension can totally destroy your data in other ways, and there are likely ways to add more barriers. And hey, we should fix bugs!
mindwok 3y ago
They are still debugging and patching the causes. The crash detection is just to try and prevent a single bug from bringing down the whole system.
dialogbox 3y ago