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 = <newX> WHERE pk = $1;

Потім, під час виконання, з'ясовує, що поле Y треба було б оновити теж:

UPDATE tbl SET Y = <newY> WHERE pk = $1;

… а потім ще й Z — чого вже дрібнитися?

UPDATE tbl SET Z = <newZ> WHERE pk = $1;

Скільки версій цього запису маємо в базі? Ага, 4 штуки! З них одна актуальна, а 3 має прибрати за вами [auto]VACUUM.

Не треба так! Використовуйте оновлення всіх полів за один запит — майже завжди логіку роботи методу можна змінити так:

UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;

#2: Use IS DISTINCT FROM, Luke!

Отже, вам таки захотілося оновити багато записів у таблиці (Під час застосування скрипта або конвертера, наприклад). І в скрипт летить щось таке:

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;

Приблизно в такому вигляді запит зустрічається досить часто і майже не для заповнення порожнього нового поля, а для корекції якихось помилок у даних. При цьому сама коректність вже існуючих даних взагалі не враховується - а даремно! Тобто запис переписується навіть якщо там лежало рівно те, що й хотілося — а навіщо? Виправимо:

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;

Багато хто не в курсі про існування такого чудового оператора, тому шпаргалка по 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: без[д]розумні виклики

А ось точно у вас має відбуватися одночасна робота з одним і тим же записом? Чи ви таки накосячили з алгоритмами викликів бізнес-логіки з боку клієнта, наприклад? А якщо подумати?

Джерело: habr.com