PostgreSQL Antipatterns: võitleme «surnute» hordidega

PostgreSQL sisemiste mehhanismide tööpõhimõtted võimaldavad tal olla teatud olukordades väga kiire ning teistes „vähem efektiivne”. Täna keskendume klassikalisele konfliktile, mis esineb andmebaasi töö ja arendaja tegevuse vahel — UPDATE vs MVCC põhimõtted.

Lühidalt, põhjalik ülevaade suurepärasest artiklist:

Kui rida muudetakse UPDATE käsu abil, toimub tegelikult kaks toimingut: DELETE ja INSERT. Uues rea versioonis seatakse xmax, mis vastab tehingu numbrile, mis oli UPDATE-i teinud. Siis luuakse uus versioon sama rea uus versioon; väärtus xmin on sellel sama kui eelmisel versioonil olev xmax.

Mõne aja pärast pärast selle tehingu lõpetamist tunnustatakse vana või uus versioon, sõltuvalt COMMIT/ROLLBACK, „surnud” (dead tuples) tabelit läbides ja puhastatakse. Aga see ei juhtu kohe, kuid probleemid „surnud” ridadega võivad tekkida väga kiiresti — massiivsete või VACUUM korduvalt toimuva ridade uuendamise korral

suures tabelis, ja hiljem võidakse kokku puutuda olukorraga, kus isegi VACUUM ei suuda aidata. Oletame, et teie äriloogika meetod töötab kenasti ja äkitselt mõistate, et peate uuendama välja X mingis kirjes: UPDATE tbl SET X = <newX> WHERE pk = $1;.

#1: I Like To Move It

Siis, käigu pealt, järsku selgub, et ka väli Y tuleks uuendada:

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

… ja siis veel Z — milleks pisiasjadesse kinni jääda?

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

Kui palju versioone sellest kirjest me nüüd andmebaasis omame? Ah jaa, 4 tükki! Nendest üks on aktuaalne, ja 3 tuleb pärast teie [auto]VACUUM-i alt eemaldada.

Ärge tehke nii! Kasutage

kõikide väljade uuendamist ühe päringu abil — peaaegu alati saab meetodi töö loogikat nii muuta:

UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1; Nii et, teil ikkagi tekib soov uuendada palju kirjeid tabelis

(nt skripti või konverteri rakendamise käigus). Ja skripti läheb midagi taolist:

#2: Use IS DISTINCT FROM, Luke!

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2; Sellises vormis päringut ilmneb piisavalt sageli ja peaaegu alati mitte tühja uue väljaga täitmiseks, vaid andmete vigade parandamiseks. Sellest hoolimata ei arvestata olemasolevate andmete korrektsusega üldse

— ja asjata! See tähendab, et kirje kirjutatakse üle, isegi kui seal oli täpselt see, mida oodati — aga miks? Parandame:

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>; корректность уже существующих данных вообще не учитывается — а зря! То есть запись переписывается, даже если там лежало ровно то, что и хотелось — а зачем? Поправим:

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

Paljud ei tea sellise suurepärase operaatori olemasolust, seega siin on abivahend IS DISTINCT FROM ja teiste loogiliste operaatorite jaoks:

… ja natuke keeruliste ROW()-väljendite toimingutest:

#3: А я милого узнаю по… блокировке

Käivituvad kaks identset paralleelset protsessi, millest igaüks üritab registreerida, et see on "töös":

UPDATE tbl SET processing = TRUE WHERE pk = $1;

Isegi kui need protsessid teevad sisuliselt üksteisest sõltumatuid asju, lukustub teine klient selle päringu raames ühe ID taga, kuni esimene tehing on lõppenud.

Lahendus nr 1: ülesanne on muudetud eelnevale

Lihtsalt lisame uuesti IS DISTINCT FROM:

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

Selles vormis ei muuda teine päring andmebaasis lihtsalt midagi, seal on niigi "kõik korras" — seega lukustust ei teki. Edasi töötleme "mittelocated" kirje tunnustamisel rakenduse algoritmis.

Lahendus nr 2: nõuande lukud

Suur teema eraldi artikli jaoks, kus saab lugeda rakendusmeetoditest ja soovituslikest lukutest.

Lahendus nr 3: mõtlematud kutse

Siiski peab teil kindlasti toimuma samal ajal töötamine ühe ja sama kirje suhtes? Или вы все-таки накосячили с алгоритмами вызовов бизнес-логики со стороны клиента, например? А если подумать?..

Allikas: habr.com