I have three tables:
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
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?