2

I have a db which has 223 tables and I have to delete some of the records from 10 of them, each has apprx. 1.5million records. Those tables are storing the temperatures every 7seconds. We have decided to remove all records but the first record for every minute. So now there are 8 records for each minutes and after this process it will store 1 record per minute which are older than 3 months.

Should I do it by delete and vacuum full afterwards or by truncate? There are also some views related to some of those tables. If I do it by truncate, would those views work afterwards?

6

If you don't have concurrent transactions that would prohibit you from getting an exclusive lock on the table, I would:

  1. Select the (relatively few) surviving rows into a temporary table.
    Make sure you have enough RAM available for the temporary tables (for this session only). Read about temp_buffers in this related answer:
    Optimizing bulk update performance in PostgreSQL
  2. Truncate the table.
  3. Re-insert the survivors.

This way, views stay intact. They would prohibit you from dropping involved tables.
You also do not need any vacuuming, no dead rows at all this way. While being at it, you may want to ORDER BY your rows for the INSERT for optimized SELECT performance.

Here and here are closely related cases with more details on SO.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.