Re: Best way to delete big amount of records from big table

Поиск
Список
Период
Сортировка
От Ekaterina Amez
Тема Re: Best way to delete big amount of records from big table
Дата
Msg-id CAFijohh6BvLirtr_7RRsR9n0EC9ytFBFVa0OwGeeNsN=vv716g@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Best way to delete big amount of records from big table  (Michael Lewis <mlewis@entrata.com>)
Список pgsql-performance
Sorry, I sent my response only to you, I'm sending it again to the group in a minute...

El vie., 27 mar. 2020 a las 15:41, Michael Lewis (<mlewis@entrata.com>) escribió:
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 по дате отправления:

Предыдущее
От: Laurenz Albe
Дата:
Сообщение: Re: Best way to delete big amount of records from big table
Следующее
От: Ekaterina Amez
Дата:
Сообщение: Re: Best way to delete big amount of records from big table