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

Question

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

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

Я старался:

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

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

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

Answer 1

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

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

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

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

• В зависимости от ваших настроек автовакуума это может помочь запустить 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 настройка в руководстве. Этот метод быстр, пока таблица помещается в память или, по крайней мере, большую ее часть. Имейте в виду, что вы можете потерять данные, если ваш сервер выйдет из строя в середине этой операции. Вы можете заключить все это в транзакцию, чтобы сделать ее более безопасной.

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

• Оптимизировать диапазон запросов Postgres timestamp

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

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

Прочитайте раздел " Примечания " для TRUNCATE в руководстве. В частности (как отметил Педро в своем комментарии):

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

А также:

TRUNCATE не будет стрелять ON DELETE триггеры, которые могут существовать для таблиц.

Answer 2

Я сам столкнулся с этой проблемой, и для меня самым быстрым методом было использование запросов WITH в сочетании с USING

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

WITH to_delete AS (
   SELECT item_id FROM other_table WHERE condition_x = true
)
DELETE FROM table 
USING to_delete 
WHERE table.item_id = to_delete.item_id 
  AND NOT to_delete.item_id IS NULL;

Конечно SELECT внутри WITH-запроса может быть таким же сложным, как и любой другой выбор с несколькими объединениями и т. д. Он просто должен вернуть один или несколько столбцов, которые используются для идентификации элементов в целевой таблице, которые необходимо удалить.

ПРИМЕЧАНИЕ:AND NOT to_delete.item_id IS NULL скорее всего не нужно, но я не решился попробовать.

Также следует учитывать

1. создание индексов для других таблиц, ссылающихся на эту через внешний ключ. Что может сократить время удаления, занимающее часы, до секунд в определенных ситуациях.
2. откладывание проверок ограничений: неясно, насколько это возможно, если вообще будет достигнуто какое-либо улучшение, но, согласно этому, это может повысить производительность. Обратной стороной является то, что если у вас есть нарушение внешнего ключа, вы узнаете об этом только в самый последний момент.
3. ОПАСНЫЙ, но большой возможный импульс: отключите проверки констант и триггеры во время удаления

Answer 3

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

Тем не менее, мы все еще можем сделать это в производстве базы данных. Вот моя идея:

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

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

Убедитесь, что id Столбцы обеих таблиц являются индексами, так как будут работать быстрее.

Answer 4

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

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

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

Answer 5

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

      CREATE OR REPLACE PROCEDURE delete_customer()
LANGUAGE plpgsql
AS $$
BEGIN
    ALTER TABLE customer DISABLE trigger all;
    ALTER TABLE order DISABLE trigger all;
    WHILE EXISTS (SELECT FROM customer
        WHERE NOT EXISTS (SELECT FROM order WHERE order.customer_id = customer.id) LIMIT 1) 
    LOOP
        DELETE FROM customer WHERE customer.id IN 
        (SELECT customer.id FROM customer 
            WHERE NOT EXISTS (SELECT FROM order WHERE order.customer_id = customer.id) LIMIT 250000);
        COMMIT;
    END LOOP;
    ALTER TABLE customer ENABLE trigger all;
    ALTER TABLE order ENABLE trigger all;
END;
$$;

      CALL delete_customer(); --start procedure
SELECT * FROM pg_stat_activity WHERE state = 'active'; --find pid of procedure
SELECT pg_cancel_backend(<pid>); --stop procedure

Убедитесь, что триггеры снова включены, если вы останавливаете процедуру вручную. Отключение триггеров дает реальные улучшения производительности, как упоминал @Erwin Brandstetter, но для меня это было возможно только в течение короткого периода обслуживания.

Answer 6

Я постепенно удаляю миллионы строк партиями с минимальными блокировками с помощью одной процедуры.loop_execute(). Есть прогресс выполнения в процентах и ​​прогноз времени окончания работы!

Answer 7

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

Это не совет специалиста.

Answer 8

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

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

2. Возможно, вам придется поместить это заявление в транзакцию.

Answer 9

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

Answer 10

Если на таблицу, из которой вы удаляете, есть ссылка 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)

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