Подскажите пути, как грохнуть дубли в здоровой таблице (около 1млрд

записей, около 500 гб), секционирована на около 60-70 секций

Есть уникальный для каждой записи db_id,есть chat_id и id, которые должны быть уникальными, но прокрались дубли.

Вариант по всей таблице
DELETE FROM messages
WHERE db_id NOT in (
SELECT min(db_id)
FROM messages
GROUP BY chat_id, id
);

Отваливается по памяти (съедает 8гб оперативы и 10гб свопа и через 4 часа рушится).

Он же по секции молотит больше 2часов, упираясь в ЦПУ (выедает одно ядро) без видимых результатов.




Нашел рабочий вариант - скопировать секцию в таблицу

SELECT min(db_id) as id into test_delete_temp
FROM messages_2021_m04
GROUP BY chat_id, id;

построить индекс и грохнуть всё в секции:

delete from messages_2021_m04 as t where not exists(select id from test_delete_temp where id=t.db_id);,

Он занимает около получаса на секцию. Вопрос: можно ли быстрее?

Когда-то давно удалял пачками по 5000-50000 записей в зависимости от нагрузки. В цикле выбираем записи в список - темповую таблицу, потом удаляем по этому списку. Не знаю как в новых версиях, а в старой надо было не просохатить момент опустошения партиции и успеть сделать analyse.

Вы бы хоть планы (просто EXPLAIN) этих запросов посмотрели... а то непонятно, что и где там "отваливается".

Eshu Marabo

Это на уровне "где-то происходит какая-то ошибка" (детализации-то нет). Смотрите планы.

Спасибо)

Я в таких случаях всегда сначала дубликвты выбираю (group by chat_id, id having count(*) > 1). Притом в отдельную табличку. А поиом ужэ думаю — как так получилось и как их отстреливать. Понятно, что отработает это только при наличии индэкса (chat_id, id, ...)

Ну я в итоге пошел тем же путем примерно, только выбрал не дубликаты а набор уникальных id и занялся отстрелом тех, что к ним не относится

А вот чего он по памяти оиваливается — мне непонятно. По идее, сожрав shared_buffers и work_mem — должэн начать на диск всё перекладывать.

он схавал всю оперативку сервера (8гб) + весь своп (10гб)

Небольшая проблема такого подхода в том, что их там 500 миллиардов. Но это мелочи, да. Если нет дубликатов между разными партицыями — то дажэ без проблем отработает.

дубликатов - около 250 млн из миллиарда

По моим представлегиям — хавать он должэн только shared_buffers+work_mem.

мне тоже так казалось😂

А они точно дубликаты информацыи? А не только пары айдишников?

увы, точно

А если взять ваш любимый язык программирования и сделать скриптик, который берет одну строку ищет и удаляет ее дубли, запустить и оставить на недельку?

я прикинул - обработка займет около 15 лет😂😂😂