PostgreSQL Antipatterns: борим се с ордите на „мъртвеците“

Особеностите на вътрешната работа на механизмите на PostgreSQL му позволяват да бъде много бърз в определени ситуации и „не толкова“ в други. Днес ще се спрем на класическия пример за конфликт между начина, по който работи СУБД, и това, което прави с нея разработчикът — UPDATE срещу принципите на MVCC.

Накратко сюжет от отлична статия:

Когато редът се променя с командата UPDATE, всъщност се извършват две операции: DELETE и INSERT. В текущата версия на реда се задава xmax, равен на номера на транзакцията, извършила UPDATE. След това се създава нова версия същият ред; стойността xmin за него съвпада с стойността xmax на предишната версия.

След известно време след приключването на тази транзакция старата или новата версия, в зависимост от COMMIT/ROLLBACK, ще бъдат признати за „мъртви“ (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: без[д]умни извиквания

Точно при вас със сигурност трябва да става съвместна работа с един и същи запис? Или вы все-таки накосячили с алгоритмами вызовов бизнес-логики со стороны клиента, например? А если подумать?..

Източник: habr.com