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

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

Кратка история от страхотна статия:

Когато ред се модифицира чрез команда UPDATE, всъщност се изпълняват две операции: DELETE и INSERT. IN текущата версия на низа xmax е зададен равен на номера на транзакцията, която е извършила АКТУАЛИЗАЦИЯТА. След това се създава нова версия същата линия; неговата xmin стойност съвпада с xmax стойността на предишната версия.

Известно време след приключване на тази транзакция, старата или новата версия, в зависимост от това COMMIT/ROOLBACK, ще бъдат разпознати "мъртъв" (мъртви кортежи) при преминаване VACUUM според таблицата и изчистено.

Но това няма да се случи веднага, но проблемите с „мъртвите“ могат да се придобият много бързо - с повтарящи се или масово актуализиране на записи в голяма маса, а малко по-късно ще срещнете същата ситуация VACUUM няма да може да помогне.

#1: Обичам да го движа

Да приемем, че вашият метод работи върху бизнес логиката и изведнъж осъзнава, че ще е необходимо да актуализира полето 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: Използването Е РАЗЛИЧНО ОТ Люк!

Значи все пак искаше актуализирайте много, много записи в таблица (например по време на използване на скрипт или конвертор). И нещо подобно лети в скрипта:

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;

Дори ако тези процеси действително извършват неща, независими един от друг, но в рамките на един и същи идентификатор, вторият клиент ще бъде „заключен“ при тази заявка, докато първата транзакция не бъде завършена.

Решение #1: задачата се свежда до предишната

Нека просто го добавим отново IS DISTINCT FROM:

UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;

В тази форма втората заявка просто няма да промени нищо в базата данни, всичко вече е както трябва - следователно няма да настъпи блокиране. След това обработваме факта „ненамиране“ на записа в приложения алгоритъм.

Решение #2: съветни ключалки

Голяма тема за отделна статия, в която можете да прочетете методи на прилагане и „рейк“ на препоръчително блокиране.

Решение #3: глупави обаждания

Но точно това трябва да ви се случи едновременна работа с един и същи запис? Или си объркал алгоритмите за извикване на бизнес логиката от страна на клиента например? И ако се замислите?..

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