Is there an option to set deletion without getting logged? I have to delete two years of historical data but would like to keep this year’s data on my 80MB of rows. I created a new table for this year’s data and I truncated the old table. Is there a better way to do this?
Unfortunately, there is no way to have a delete statement operate as an unlogged operation. So your way is fine.
As you’ve realized, deleting a year’s worth of data at once is not a simple task. If I did have to delete many records, I would break it up into smaller operations. So perhaps I would delete records for each month or week at a time. In this way, the Server won’t check by being asked to affect millions of rows in one operation.