Подтвердить что ты не робот

Лучший способ удалить миллионы строк по ID

Мне нужно удалить около 2 миллионов строк из моей базы данных PG. У меня есть список идентификаторов, которые мне нужно удалить. Тем не менее, любой способ, которым я пытаюсь это сделать, - это занять несколько дней.

Я попытался поместить их в таблицу и сделать это партиями по 100. Через 4 дня это все еще выполняется, удалив только 297268 строк. (Мне нужно было выбрать 100 идентификаторов из таблицы идентификаторов, удалить там, где IN в этом списке, удалить из таблицы идентификаторов 100, которые я выбрал).

Я пробовал:

DELETE FROM tbl WHERE id IN (select * from ids)

Это тоже навсегда. Трудно определить, сколько времени, так как я не вижу его прогресса до завершения, но запрос все еще работает через 2 дня.

Просто найдите наиболее эффективный способ удаления из таблицы, когда я знаю, какой идентификатор удаляется, и есть миллионы идентификаторов.

4b9b3361

ОТВЕТЫ

Ответ 1

Все зависит...

  • Удалите все индексы (кроме тех, которые вам нужны для удаления)

    Сопоставьте их позже (= намного быстрее, чем инкрементные обновления индексов)

  • Проверьте, есть ли у вас триггеры, которые можно временно удалить/отключить

  • Помогают ли иностранные ключи вашей таблице? Могут ли они быть удалены? Временно удален?

  • В зависимости от ваших настроек autovacuum это может помочь выполнить VACUUM ANALYZE перед операцией.

  • Если вы удалите большие части таблицы, а остальные вписываются в ОЗУ, самым быстрым и простым способом будет следующее:

SET temp_buffers = 1000MB -- or whatever you can spare temporarily

CREATE TEMP TABLE tmp AS
SELECT t.*
FROM   tbl t
LEFT   JOIN del_list d USING (id)
WHERE  d.id IS NULL;      -- copy surviving rows into temporary table

TRUNCATE tbl;             -- empty table - truncate is very fast for big tables

INSERT INTO tbl
SELECT * FROM tmp;        -- insert back surviving rows.

Таким образом, вам не нужно воссоздавать представления, внешние ключи или другие зависимые объекты. Ознакомьтесь с параметром temp_buffers в руководстве. Этот метод работает до тех пор, пока таблица вписывается в память или, по крайней мере, в большинстве случаев. Имейте в виду, что вы можете потерять данные, если ваш сервер выходит из строя в середине этой операции. Вы можете перенести все это в транзакцию, чтобы сделать ее более безопасной.

Кроме того, рекомендуется:

TRUNCATE не может использоваться в таблице с ссылками на внешние ключи из других таблиц, если все такие таблицы также не усекаются в одной команде.

Запустите ANALYZE после. Или VACUUM ANALYZE, если вы не отправили маршрут усечения, или VACUUM FULL ANALYZE, если вы хотите довести его до минимального размера. Для больших таблиц рассмотрим альтернативы CLUSTER/pg_repack:

Для небольших таблиц простой DELETE вместо TRUNCATE часто выполняется быстрее:

DELETE FROM tbl t
USING  del_list d
WHERE  t.id = d.id;

Ответ 2

Мы знаем, что производительность обновления/удаления PostgreSQL не так сильна, как Oracle. когда нам нужно удалить миллионы или 10 миллионов строк, это действительно сложно и занимает много времени.

Однако мы все еще можем сделать это в dbs производства. Вот моя идея:

Сначала мы должны создать таблицу журналов с двумя столбцами - id и flag (id означает идентификатор, который вы хотите удалить; flag может быть Y или null, с Y, означающее, что запись успешно удалена).

Позже мы создаем функцию. Мы делаем задачу удаления каждые 10 000 строк. Вы можете увидеть более подробную информацию о моем блоге. Хотя на китайском языке вы все равно можете получить нужную информацию из кода SQL.

Убедитесь, что столбец id для обеих таблиц - это индексы, так как он будет работать быстрее.

Ответ 3

Вы можете попытаться скопировать все данные из таблицы кроме идентификаторов, которые вы хотите удалить, в новую таблицу, а затем переименовать, а затем обменивать таблицы (при условии, что у вас достаточно ресурсов для этого).

Это не экспертный совет.

Ответ 4

Самый простой способ сделать это - удалить все ваши ограничения, а затем удалить.

Ответ 5

Два возможных ответа:

  • При попытке удалить запись в вашей таблице может быть множество ограничений или триггеров. Это потребует много циклов процессора и проверки из других таблиц.

  • Вам может потребоваться включить этот оператор в транзакцию.

Ответ 6

Сначала убедитесь, что у вас есть индекс в полях ID, как в таблице, которую вы хотите удалить, так и в таблице, которую вы используете для идентификаторов удаления.

100 за один раз кажется слишком маленьким. Попробуйте 1000 или 10000.

Нет необходимости удалять что-либо из таблицы идентификаторов удаления. Добавьте новый столбец для номера партии и заполните его 1000 для партии 1, 1000 для партии 2 и т.д. И убедитесь, что запрос удаления включает номер партии.

Ответ 7

Если в таблице, на которую вы удаляете ссылку, ссылается some_other_table (и вы не хотите временно отбрасывать внешние ключи), убедитесь, что у вас есть индекс в столбце ссылок в some_other_table!

У меня была аналогичная проблема, и я использовал auto_explain с auto_explain.log_nested_statements = true, который показал, что delete фактически выполнял seq_scans на some_other_table:

    Query Text: SELECT 1 FROM ONLY "public"."some_other_table" x WHERE $1 OPERATOR(pg_catalog.=) "id" FOR KEY SHARE OF x    
    LockRows  (cost=[...])  
      ->  Seq Scan on some_other_table x  (cost=[...])  
            Filter: ($1 = id)

По-видимому, он пытается заблокировать ссылки на строки в другой таблице (которые не должны существовать, или удаление не будет выполнено). После того, как я создал индексы в ссылочных таблицах, удаление было на порядок быстрее.