Будет ли индекс полезен при удалении большого количества записей в таблице с множеством индексов

Что проверяют

Различаете ли вы две роли индексов в DELETE: один помогает найти строки, остальные приходится обслуживать. Ответ «индексы ускоряют чтение и замедляют запись» здесь слишком общий. Следом спрашивают, как удалить десятки миллионов строк на живой базе и не положить её.

Как отвечать

Разделите индексы на три группы:

• индекс по условию WHERE: полезен, без него база сканирует всю таблицу. Но если удаляется большая доля строк, планировщик и так выберет полный скан;
• индексы на внешних ключах в дочерних таблицах: критичны. На каждую удалённую строку база проверяет ссылки, и без индекса по колонке FK каждая проверка становится полным сканом дочерней таблицы. PostgreSQL такие индексы сам не создаёт;
• остальные индексы: чистая стоимость. В InnoDB их записи помечаются удалёнными и вычищаются фоновым purge. В PostgreSQL DELETE помечает саму строку, а записи из всех индексов убирает VACUUM, так что счёт приходит позже.

Стратегии для большого объёма:

• пачки: в PostgreSQL DELETE ... WHERE id IN (SELECT id ... LIMIT 10000), в MySQL DELETE ... LIMIT 10000, коммит после каждой. Короткие транзакции не держат блокировки и не раздувают WAL и лаг реплики;
• партиционирование: при удалении по времени DROP или DETACH PARTITION снимает раздел почти мгновенно;
• копия и подмена: если оставить нужно меньшинство строк, быстрее скопировать их в новую таблицу, построить индексы и переименовать.

В Python помните про ORM. Django QuerySet.delete() ради on_delete и сигналов pre_delete/post_delete загружает объекты в память, на миллионах строк это тупик. Большие удаления пишут отдельной командой с пачками и логом прогресса.

Где спотыкаются

Советуют снести все индексы перед операцией. Вместе с ними пропадает и тот, что ищет строки, а пересборка на большой таблице бывает дольше самого удаления.

Удаляют всё одним запросом. Многочасовая транзакция держит блокировки, копит WAL и мешает автовакууму.

Забывают про место. После DELETE в PostgreSQL файл таблицы не уменьшается: место переиспользуется, но на диске остаётся раздутая таблица.