Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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