Друзья, интересный кейс). Проблема в базе postgresql(v10.18) рабочего сайта, в

двух таблицах скопилось много мусора(не был правильно выстроен процесс удаления продуктов):
spree_datafeed_rows(~250млн записей, >100GB, 1 индекс)
связана 1 к 1 с
spree_datafeed_products(>300млн записей, >400GB, 8 индексов!)
также связана один к одному с spree_products(~3 млн актуальных записей).
Написал следующую функцию:

CREATE OR REPLACE FUNCTION delete_extra_products_datafeeds()
RETURNS void AS
$func$
declare
i record;
BEGIN
FOR i IN (SELECT * FROM "spree_datafeed_products")
LOOP
IF NOT EXISTS (SELECT * FROM "spree_products" WHERE "spree_products"."id" = i.product_id LIMIT 1) THEN
DELETE FROM "spree_datafeed_products" WHERE "spree_datafeed_products"."id" = i.id;
END IF;
END LOOP;
END;
$func$ LANGUAGE plpgsql;
С расчетом для одной таблицы вначале запустить, после для другой(логика схожая).
Если правильно понимаю, она пытается отработать в одной транзакции(что в таких масштабах наврядли возможно). Как правильно сегментировать транзакции, и паралельно все это дело запустить?)
Есть понимание, что сильно мешают индексы, но даже удаляются они долго. С чем это связано и можно ли на это повлиять?)
Наверно у postgres еще есть другие инструменты или подходы, для решения подобных проблем?)

Не вчитывался в подробности, но вам явно надо почитать https://habr.com/ru/post/523536

>Написал следующую функцию: Зачем? Пока что это выглядит как очень плохо написанная функцыя, которая зачем-то заменяет один-два тривиальных оператора DELETE (один, в случае если там есть REFERENCE KEY ON DELETE CASCADE правильный). >она пытается отработать в одной транзакции(что в таких масштабах наврядли возможно). Возможно. Дажэ места не особо много займёт (вот если бы UPDATE -- то могло бы всё раза в два разбухнуть, а DELETE-то вообще только всё перезапишэт). >Как правильно сегментировать транзакции, и паралельно все это дело запустить?) У меня есть подозрение, что в память это всё у вас не очень влезает -- потому запускать параллельно это будет достаточно бесполезно. А как сегментировать -- да хоть по id. Тысяч по 100 за раз.

Пришел к такому запросу: with batch(id) as ( select id from "spree_datafeed_rows" where NOT EXISTS (SELECT * FROM "spree_datafeed_products" WHERE "spree_datafeed_products"."id" = "spree_datafeed_rows"."datafeed_product_id" LIMIT 1) order by id limit 10000 for update skip locked ), del as ( delete from "spree_datafeed_rows" where id in (select id from batch) returning id ) select now(), count(*) from batch; Но не знаю как его запустить для всей таблицы(с шагом в 10000) без процедур

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

Если буду писать цикл на клиенте, нужно будет добавить ещё один where (для разбиения по айдишникам), учитывая что в таблице >300млн записей, такой селект "дорого обходится")

Обычно выборка небольшого диапазона (десятки тысяч) по id наоборот шустро работает, потому что задействует гарантированно имеющийся индекс по первичному ключу

Ок попробую, ещё если правильно понимаю при таком удалении у меня будет много записей помеченных как удаленные. Чтобы это не мешало производительности как их "чистить"?

А если цикл в запросе/процедуре, то локи останутся до конца транзакции

У psql нет инструментов для комитов внутри транзакции?))

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

Он тяжело и долго отрабатывает, настройки стандартные (из коробки), падозреваю что он чаще падает) Также на одной таблице 8 индексов ~40гб каждый, что делает задачу веселее Х)

Настройки авовакуума «из коробки» недостаточно агрессивные, их обычно рекомендуют менять

Вообще была мысль отключить вакуум на время, изолировать таблицы с мусором для чистки, но пока не понимаю как это можно сделать

Автовакуум можно отключать для отдельных таблиц: ALTER TABLE sometable SET ( autovacuum_enabled = false, toast.autovacuum_enabled = false ); https://postgrespro.ru/docs/postgresql/14/sql-createtable#RELOPTION-AUTOVACUUM-ENABLED

1) Зачем вам FOR UPDATE? У вас правда кто-то будет писать в эту таблицу в это время? И столько писать, что повторить запрос -- какая-то проблема? 2) LIMIT 1 теоретически не имеет смысла, а практически -- можэт сломать какую-нибудь оптимизацыю. 3) Запускать это последовательно для всей таблицы -- я бы советовал на каком-нибудь процэдурном клиентском языке. Из питона там или ноды, смотря что знаете. В десятке, собственно, разбить это на куски внутри процэдуры -- невозможно. В смысле разбить-то можно, только смысла в этом будет ноль -- оно всё равно будет в одной транзакцыи. Можно сразу запускать на все 100 миллионов.