Re: Best way to delete big amount of records from big table - Mailing list pgsql-performance

From Michael Lewis
Subject Re: Best way to delete big amount of records from big table
Date
Msg-id CAHOFxGp+3jXUEin6BdEbkJKy2t+f3rceVmE0bm-LvnYUj1cfTQ@mail.gmail.com
Whole thread Raw
In response to Best way to delete big amount of records from big table  (Ekaterina Amez <ekaterina.amez@zunibal.com>)
Responses Re: Best way to delete big amount of records from big table  (Ekaterina Amez <ekaterina.amez@zunibal.com>)
Re: Best way to delete big amount of records from big table  (Ekaterina Amez <ekaterina.amez@zunibal.com>)
Re: Best way to delete big amount of records from big table  (Justin Pryzby <pryzby@telsasoft.com>)
List pgsql-performance
If you can afford the time, I am not sure the reason for the question. Just run it and be done with it, yes?

A couple of thoughts-
1) That is a big big transaction if you are doing all the cleanup in a single function call. Will this be a production system that is still online for this archiving? Having a plpgsql function that encapsulates the work seems fine, but I would limit the work to a month at a time or something and call the function repeatedly. Get the min month where records exist still, delete everything matching that, return. Rinse, repeat.
2) If you are deleting/moving most of the table (91 of 150 million), consider moving only the records you are keeping to a new table, renaming old table, and renaming new table back to original name. Then you can do what you want to shift the data in the old table and delete it.

pgsql-performance by date:

Previous
From: Ekaterina Amez
Date:
Subject: Best way to delete big amount of records from big table
Next
From: Laurenz Albe
Date:
Subject: Re: Best way to delete big amount of records from big table