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?