3 ms·

Fair enough. I'm sure you are aware that you can safeguard against careless or accidental long-running transactions by setting "statement_timeout". Of course th

Fair enough. I'm sure you are aware that you can safeguard against careless or accidental long-running transactions by setting "statement_timeout". Of course that wouldn't allow intentional long-running transactions, so not really a fix (although you could have a replica with a longer "statement_timeout" for long queries against that). But I get what you are saying.

atombender 2y ago
Long-running transactions are an antipattern, to be sure. Whenever I find one, I try to split it into smaller, shorter-lived ones. In some cases it's tricky because you lose transaction isolation; think of a data export, for example. To preserve snapshot consistency you have to rearchitect the data model itself to become temporally aware. Or the opposite: A big import that must be atomic has to be rearchitected so that the ingested data is not integrated into the running system until you "flip" something at the end. These workarounds should be unnecessary — these things are what ACID transactions are for, after all. As I understand it (I could be wrong), long-running transactions are not such a big problem in other databases that use in-place updates with undo/redo logging.
- atsaloli 2y ago
  I get what you are saying. I don't think you'll find a commercial database that does not have performance struggles of its own. If you need to do a large data export, you can dump it off a read replica. A big import, like you said, can be staged and then swapped into place. Good luck. :)
  - atombender 2y ago
    Surely they do, but some of Postgres' problems are architectural, and other databases with other architectures don't suffer from them. That said, this was not the point of my post, and I was hesitant to mention any concrete issues because I didn't want the thread to be about that, but rather the question of how Postgres today stacks up against commercial databases that are in use.
    - atsaloli 2y ago
      Absolutely. I see you didn’t get much traction here. You could try the DBA Stack Exchange. Or talk to the vendors.

atombender 2y ago

Long-running transactions are an antipattern, to be sure. Whenever I find one, I try to split it into smaller, shorter-lived ones. In some cases it's tricky because you lose transaction isolation; think of a data export, for example. To preserve snapshot consistency you have to rearchitect the data model itself to become temporally aware. Or the opposite: A big import that must be atomic has to be rearchitected so that the ingested data is not integrated into the running system until you "flip" something at the end. These workarounds should be unnecessary — these things are what ACID transactions are for, after all. As I understand it (I could be wrong), long-running transactions are not such a big problem in other databases that use in-place updates with undo/redo logging.

atsaloli 2y ago
I get what you are saying. I don't think you'll find a commercial database that does not have performance struggles of its own. If you need to do a large data export, you can dump it off a read replica. A big import, like you said, can be staged and then swapped into place. Good luck. :)
- atombender 2y ago
  Surely they do, but some of Postgres' problems are architectural, and other databases with other architectures don't suffer from them. That said, this was not the point of my post, and I was hesitant to mention any concrete issues because I didn't want the thread to be about that, but rather the question of how Postgres today stacks up against commercial databases that are in use.
  - atsaloli 2y ago
    Absolutely. I see you didn’t get much traction here. You could try the DBA Stack Exchange. Or talk to the vendors.