Удалить 300 млн старых строк из живой таблицы

Question

В таблице events 1 млрд строк, нужно удалить всё старше года (около 300 млн), и база при этом обслуживает прод. Как это сделать?

Техпрогон · Accepted Answer

Одним DELETE нельзя: это одна многочасовая транзакция, которая держит снимок (VACUUM всё это время ничего не чистит), пишет гигабайты WAL, вызывает лаг реплик, а при ошибке всё откатывается. Один DELETE на 300 млн плох: долгая транзакция, огромный WAL, лаг реплик, мёртвые строки. Удалять пачками по ключу (например, по 10 тыс. id) с паузами и отдельными транзакциями. Следить за лагом реплик и работой autovacuum во время чистки. Если удалить надо больше, чем остаётся, быстрее скопировать нужное в новую таблицу и переключиться. На будущее: партиционирование по времени и удаление партиций целиком. Одним DELETE нельзя: это одна многочасовая транзакция, которая держит снимок (VACUUM всё это время ничего не чистит), пишет гигабайты WAL, вызывает лаг реплик, а при ошибке всё откатывается. Удаляю пачками: DELETE FROM events WHERE id IN (SELECT id FROM events WHERE createdat < now() - interval '1 year' ORDER BY id LIMIT 10000), каждая пачка в своей транзакции, с паузой и контролем лага реплик и ndeadtup. Пачка должна находиться по индексу, иначе каждая итерация сканирует таблицу. Место после удаления переиспользуется, но файл не уменьшится: если это важно, нужен pgrepack. Если удалить надо 70% таблицы, проще скопировать живые 30% в новую таблицу, догнать изменения и переключить переименованием. А чтобы не повторять, перевожу таблицу на партиции по месяцам: тогда старое удаляется через DETACH и DROP.

Удалить 300 млн старых строк из живой таблицы

Вопрос

Попробуйте сами

Коротко

Что должно прозвучать в ответе

Что могут спросить следом

Разбор

Термины по теме

Ещё по теме «Проектирование и нормализация»