Как распараллелить SELECT и DELETE?
В PostgreSQL мне приходится получать идентификаторы небольшими партиями и удалять их одновременно, чтобы два клиента не получали одинаковые идентификаторы.
К сожалению, DELETE не поддерживает LIMIT, поэтому я придумал такой запрос:
DELETE FROM codes
WHERE id IN (SELECT id FROM codes WHERE product = 'abc' LIMIT 100 FOR UPDATE)
RETURNING id
К сожалению, это медленный и своего рода последовательный алгоритм (при определенных обстоятельствах) - вы не можете SELECT пока DELETE работает, не выбирать один и тот же идентификатор два раза. И ты не сможешь DELETE, пока не получишь SELECT результат.
Что приводит к плохой производительности, когда параллельно заходит много клиентов. Каковы мои лучшие варианты распараллеливания/ускорения всего этого?
PS: когда продукты у разных клиентов отличаются - база данных хорошо его распараллеливает, но когда они одинаковы - время ответа начинает линейно расти с количеством запросов параллельных клиентов.
У вас есть индекс на (product, id)?
Индекс @Charlieface увеличивает время вставки в 3 раза (300%) и дает прирост производительности только на 5%, поэтому он дает гораздо больше недостатков. Проблема не в том, что выбор медленный, выбор сам по себе является асе. и удалить тоже неплохо. в одиночку. проблема начинается, когда они становятся зависимыми друг от друга
Будьте осторожны: распараллеливание звучит хорошо, но только тогда, когда у вас достаточно (виртуальных) аппаратных ресурсов. В основном диск из-за необходимости ввода-вывода. Когда несколько процессов начинают бороться за ресурсы, ваша система может работать даже медленнее, чем при использовании только одного процесса.
Вам придется предоставить нам план EXPLAIN, потому что то, что вы видите, не то, что я ожидал после добавления индекса.
Ответы 1
Предполагая, что вас не волнует порядок использования кодов, на что указывает отсутствие ORDER BY в вашем запросе.
Используйте SKIP LOCKED, чтобы разрешить распараллеливание. В противном случае регулярно накапливается несколько одновременных вызовов, ожидающих завершения предыдущего(их).
Кроме того, материализуйте свой выбор в CTE, поскольку предложение LIMIT плохо взаимодействует с предложением блокировки. Видеть:
WITH sel AS MATERIALIZED (
SELECT id
FROM codes
WHERE product = 'abc'
LIMIT 100
FOR UPDATE SKIP LOCKED
)
DELETE FROM codes c
USING sel s
WHERE c.id = s.id
RETURNING c.id;
Ключевое слово MATERIALIZED не является строго необходимым, так как этот CTE в любом случае не будет встроен. Но для ясности это не помешает.
Теперь вы можете запускать несколько экземпляров этой команды, каждый в отдельном сеансе, одновременно и безопасно.
Чтобы убедиться, что все строки обработаны, проверьте это после завершения обработки. Нравиться:
SELECT EXISTS (SELECT FROM codes WHERE product = 'abc');
Если еще остались строки, выполните последнюю команду без SKIP LOCKED. Чтобы быть абсолютно уверенным, повторите последние два шага. Или просто зациклите команду без SKIP LOCKED.
Индекс codes(product) должен помочь, если фильтр WHERE product = 'abc' на самом деле является выборочным, т. е. только очень небольшой процент от общего числа строк. В противном случае обычно это требует больше затрат, чем помощи, поскольку индекс увеличивает стоимость записи.
Возможно, вам поможет этот вопрос: stackoverflow.com/questions/5170546/… Это не совсем тот же вариант использования, но в любом случае может помочь.