0

I have three tables: data, processed, and processing_status. The first one has rows coming in quite quickly. Every few minutes, I check the highest ID in data and compare it to processing_status. If enough new datapoints have come in, the new ones are processed and aggregated into processed table, and processing_status is updated to the highest ID. Obviously, both of those happen in one transaction, so that if there's a crash, everything stays sane, and neither table is updated without the other.

I'd like to improve the performance of this, regarding the bloat to the WAL logs. If I set processed and processing_status to UNLOGGED in Postgres, will I retain the property that when I commit the transaction, either both tables are updated, or neither? Even in the event of a crash?

I believe that if I set UNLOGGED, the difference is that in a crash, it will always revert both, whereas in LOGGED mode, the WAL could be used to include a few extra in-progress transactions right during the crash. Is this understanding accurate?

0

Your understanding is correct. UNLOGGED tables participate in transactions just like regular, logged tables.

The only difference is the crash-safety, so you may lose data if your server crashes (unexpected power-outage, operating system crash, Postgres bug, ...)

5
  • Wow, that's really cool. So postgres is able to guarantee an "either both tables, or neither table gets updated" transaction, but without a WAL log? Amazing. Turning this on right away! – lurf jurv Jul 30 '20 at 5:00
  • Just curious: what would happen if I had one LOGGED table and one UNLOGGED table. I update both and commit, then there's a crash. Would I still get an "both, or neither" transaction? If so, how does that possibly work? – lurf jurv Jul 30 '20 at 5:02
  • You would be left with a corrupted database in that case. – a_horse_with_no_name Jul 30 '20 at 5:20
  • Corrupt in the sense that only one would get updated? Or like really corrupted and unusable? – lurf jurv Jul 30 '20 at 5:24
  • Corrupted in the sense that the data isn't valid anymore because the contents of the tables don't match what it should be (or is expected), e.g. invalid foreign key values. – a_horse_with_no_name Jul 30 '20 at 5:27
0

I believe that if I set UNLOGGED, the difference is that in a crash, it will always revert both,

No, after a crash the unlogged tables will be truncated. That is, they will have no rows in them. You would have to recompute processed and processing_status in their entirely from data, which may or may not be feasible.

whereas in LOGGED mode, the WAL could be used to include a few extra in-progress transactions right during the crash. Is this understanding accurate?

This would be true only if the commit of the transaction was in-progress, or if a single-statement transaction was occuring in auto-commit mode. If the body but not the commit of the transaction was in-progress, it will always be rolled back after a crash.

2
  • Are they always truncated after a crash? Or, only when there was a transaction in progress? – lurf jurv Jul 30 '20 at 22:10
  • @lurfjurv Always truncated. It would be nice if you could protect the table by marking them read only, but alas that doesn't work. – jjanes Jul 31 '20 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.