PostgreSQL Antipatterns: сражаемся с ордами «мертвецов»
Особенности работы внутренних механизмов PostgreSQL позволяют ему быть очень быстрым в одних ситуация и «не очень» в других. Сегодня остановимся на классическом примере конфликта между тем, как работает СУБД и тем, что делает с ней разработчик — UPDATE vs принципы MVCC.
Кратко сюжет из отличной статьи:
Когда строка изменяется командой UPDATE, фактически выполняются две операции: DELETE и INSERT. В текущей версии строки устанавливается xmax, равный номеру транзакции, выполнившей UPDATE. Затем создается новая версия той же строки; значение xmin у нее совпадает с значением xmax предыдущей версии.
Через какое-то время после завершения этой транзакции старая или новая версии, в зависимости от COMMIT/ROOLBACK, будут признаны «мертвыми» (dead tuples) при проходе VACUUM по таблице и зачищены.
Но это произойдет далеко не сразу, а вот проблемы с «мертвецами» можно нажить очень быстро — при многократном или массовом обновлении записей в большой таблице, а чуть позже столкнуться с ситуацией, что и VACUUM не сможет помочь.
#1: I Like To Move It
Допустим, ваш метод на бизнес-логике работает себе, и вдруг понимает, что надо бы обновить поле X в какой-то записи:
UPDATE tbl SET X = WHERE pk = $1;
Потом, по ходу выполнения, выясняет, что поле Y надо бы обновить тоже:
UPDATE tbl SET Y = WHERE pk = $1;
…, а потом еще и Z — чего уж мелочиться-то?
UPDATE tbl SET Z = WHERE pk = $1;
Сколько версий этой записи теперь имеем в базе? Ага, 4 штуки! Из них одна актуальная, а 3 должен будет прибрать за вами [auto]VACUUM.
Не надо так! Используйте обновление всех полей за один запрос — почти всегда логику работы метода можно так изменить:
UPDATE tbl SET X = , Y = , Z = WHERE pk = $1; #2: Use IS DISTINCT FROM, Luke!
Итак, вам все-таки захотелось обновить много-много записей в таблице (в ходе применения скрипта или конвертера, например). И в скрипт летит что-то такое:
UPDATE tbl SET X = WHERE pk BETWEEN $1 AND $2;
Примерно в таком виде запрос встречается достаточно часто и почти всегда не для заполнения пустого нового поля, а для коррекции каких-то ошибок в данных. При этом сама корректность уже существующих данных вообще не учитывается —, а зря! То есть запись переписывается, даже если там лежало ровно то, что и хотелось —, а зачем? Поправим:
UPDATE tbl SET X = WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM ;
Многие не в курсе про существование такого замечательного оператора, поэтому вот шпаргалка по IS DISTINCT FROM и другим логическим операторам в помощь:
… и немного про операции над сложными ROW()-выражениями:
#3: А я милого узнаю по… блокировке
Запускаются два одинаковых параллельных процесса, каждый из которых пытается пометить на записи, что она находится «в работе»:
UPDATE tbl SET processing = TRUE WHERE pk = $1;
Даже если эти процессы предметно делают независимые друг от друга вещи, но в рамках одного ID, на этом запросе второй клиент «залочится», пока не закончится первая транзакция.
Решение №1: задача сведена к предыдущей
Просто снова добавим IS DISTINCT FROM:
UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;
В таком виде второй запрос просто ничего не будет менять в базе, там и так уже «все как надо» — поэтому и блокировка не возникнет. Дальше факт «ненахождения» записи уже обрабатываем в прикладном алгоритме.
Решение №2: advisory locks
Большая тема для отдельной статьи, в которой можно почитать про способы применений и «грабли» рекомендательных блокировок.
Решение №3: без[д]умные вызовы
А вот точно-точно у вас должна происходить одновременная работа с одной и той же записью? Или вы все-таки накосячили с алгоритмами вызовов бизнес-логики со стороны клиента, например? А если подумать?…
