Another footgun is that, while `DELETE FROM table_name` is transactional, TRUNCATE is not transactional. Once you push the truncate button, that data is gone in every transaction everywhere all at once.
You should be very hesitant about using TRUNCATE on a production database unless that table (and all related foreign keyed tables) are truly ephemeral. Even if the data is cleared every night at midnight, for example, is there going to be a 10 second analysis transaction running across the midnight boundary that will fail hard with suddenly missing rows?
Running a full delete on the rows and vacuum will still result in a tiny file on storage and doesn't wake me up in a cold sweat when I have a flashback. Even renaming the table is in many ways safer.
The only difference to other DML statements is, that it will put an exclusive lock on the table. So until the TRUNCATE is committed, no other transaction can read from the table.
> TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred.
Sorry, that's what I mean. It's safe in the sense that you can roll it back, but it's not safe in the sense that other concurrent transactions will see the table as empty if they are long-running.
You should be very hesitant about using TRUNCATE on a production database unless that table (and all related foreign keyed tables) are truly ephemeral. Even if the data is cleared every night at midnight, for example, is there going to be a 10 second analysis transaction running across the midnight boundary that will fail hard with suddenly missing rows?
Running a full delete on the rows and vacuum will still result in a tiny file on storage and doesn't wake me up in a cold sweat when I have a flashback. Even renaming the table is in many ways safer.