Question about postgres commits to table in UNLOGGED mode 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?
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, ...)