Хорошие новости для тех, кто всё ещё использует row-level локи в PostgreSQL

Еще один серьезный минус row-lock состоит в том, что запись флага HEAP_XMAX_LOCK_ONLY в тупл провоцирует пересчет чексуммы страницы и, как следствие, сброс FPW в WAL, т.е. еще и WAL трафик растет.

Ещё в случае с advisory есть вопросы ко времени восстановления после сбоя.

Можно ли хотя бы в двух словах тут в комментариях пояснить, как и почему advisory может аффектить crash recovery?

Серьёзный минус advisory-локов в ограничении на размер блокируемого идентификатора. Если система построена на uuid-ах, либо имеются большие таблички с bigint в ключе и при этом в одной БД активно работает пяток различных малосвязанных процессов, требующих блокировки, появляются ненулевые риски коллизий (особенно, если в части таблиц идшники коррелирующие с коэффициентом близким к 1). А если между блокируемыми идшниками появляется взаимосвязь — это путь к deadlock-ам. Конечно, в 95-98% сессий никаких проблем не будет вообще и это позволит получить ускорение в среднем, но также появится необходимость обработки ошибок, если вдруг используются advisory-локи уровня сессии. И именно это останавливает каждый раз при мысли использовать advisory-блокировку: вот сейчас задействую, а потом появится вариант, когда такая блокировка более желательна, но она будет мешать предыдущему использованию.

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

Кстати, замечу, что во многих случаях, когда используют for update, на самом деле правильнее использовать for no key update. Недавно наткнулся на deadlock, исправленный как раз сменой for update на for no key update. Отличаются эти блокировки в наличии конфликта с блокировкой при вставке в таблицу, у которой fk ссылается на заблокированную.
Для оптимизации, мы в родительской таблице хранили массив значений одного из полей родительской таблицы (так пришлось сделать из-за сложных правил доступа и больших объёмов данных) и в функции изменения дочерней таблицы превентивно блокировали родительскую (иначе возможен deadlock из-за того, что в другой функции сперва редактируется родительская таблица, затем дочерняя). В итоге, если в одной транзакции сперва вставлялась запись в дочернюю, затем апдейтилось поле с апдейтом родительской таблицы, то несколько таких сессий приводило к deadlock-у, т.к. вставленная другой сессией запись блокировала for update в первой, а затем она также пыталась выполнить for update и происходил deadlock. При смене на for no key update, блокировка происходила только при повторном for no key update.