Комментарии 29
BEGIN;
...
ALTER TABLE task RENAME TO task_old;
ALTER TABLE task_new RENAME TO task; -- новая таблица теперь task
...
COMMIT;
...
DROP TABLE task CASCADE; -- удаляем новую...
Что касается партиционирования — рецепт на случай, когда сразу его не предусмотрели, но настал момент когда нужно почистить табличку (после чистки уже можно и партиционирование настроить, кстати)
Списаться с разработчиками софтин которые эту таблицу tasks запонолняют и переделать в несколько таблиц по годам?
tasks_2020, tasks_2021? Можно даже по кварталам разбить tasks_2020q1, tasks_2020q2…
Просто я работал с большой базой 200 гб на хардах.
Реально если предотвратить рост таблицы заранее — база всегда будет довольно шутсро работать.
Почему
решение почти в лоб
delete from task where id < 1000;
delete from task where id < 2000;
...не подходит? Нагрузку можно в этом случае можно подобрать такую, чтобы прод ее не ощущал. И тестироваться такое должно проще.
Почему нам нужно это сделать быстро? Если таблица уже есть и она как-то работает, то немного подождать не должно быть большой проблемой.
Может быть я «параноик», но использование хитрых трюков для удаления данных из базы, по-моему, должно как-то более серьезно обосновываться, чем «так быстрее».
Нам нужно было убрать 100 млн строк — такая скорость нам не подходила.
Всего то 6 дней работы и удалили бы все.
Но раз вас клюнул резко петух в зад и ждать не вмоготу. С другой стороны возникает вопрос: а какого черта вы тянули резину и копили данные? И будите копить и дальше? Рецепт описанный в статье понятен, но непонятна мораль сей истории.
А сколько переливались данные из старой таблицы в новкю?
Кажется так будет чуть прямее?
- Сделать партиционированную таблицу с текущей таблицей в качестве единственной партиции
- Добавить новую партицию, перенаправить запись туда
- Создать ещё одну табличку, куда заселектить нужные данные из старой, и переподключить её на место старой. Или спокойно удалить записи прямо из старой (если запись туда не производится, то можно хоть за один запрос, не?).
И сколько потом все эти индексы и констрейнты снова строились?
В чём контроль-то? по-любому процесс либо прошёл полностью, и получился ожидаемый результат, либо нет, и всё надо начинать сначала, исправив ошибки. Контролировать течение процесса, следя за изменением количества уже перенесённых записей? так и в процедуре это элементарно, просто надо понизить уровень транзакции и разрешить грязные чтения. Оборвать на середине, если что пошло не так? ну так и выгрузить процесс с процедурой — не проблема.
Или речь о том, что надо дополнительно на экране нарисовать прогресс-бар? ну тогда да, хранимка с этим не справится…
Вы забыли о том, что при изменении имени таблицы её внутренний tableoid не меняется и все объекты которые используют данную таблицу нужно будет менять — функции, представления...
Потому можно проще, делаете таблицу с триггером чтобы не терять новые данные во время работ, далее копируете те данные что нужны в новую таблицу (лучше через copy) и делаете truncate на текущую таблицу и возвращаете все данные назад из новой таблицы.
Утверждение "Для простоты предположим, что в базе нет входящих foreign key на таблицу task (при их наличии решение задачи немного усложняется)." содержит некое лукавство. Потому как наличие 2-3 слоев зависимостей по ключам приводит к необходимости отдельно собирать идентификаторы строк переносимых из каждой таблицы и заботиться о синхронизации переноса. Сбор этих данных "на лету" начинает очень дорого стоить.
Обрезаем большую таблицу PostgreSQL в production