PostgreSQL antipatern: borba protiv hordi 'mrtvih' podataka

Unutarnji mehanizmi PostgreSQL-a omogućuju mu da bude vrlo brz u nekim situacijama i 'ne baš' u drugima. Danas ćemo se osvrnuti na klasičan primjer sukoba između načina rada DBMS-a i onoga što programer s njim radi — UPDATE vs principi MVCC.

Kratki sažetak iz odličnog članka:

Kada se redak mijenja putem komande UPDATE, zapravo se izvršavaju dvije operacije: DELETE i INSERT. U trenutnoj verziji retka postavlja se xmax, koji odgovara broju transakcije koja je izvršila UPDATE. Zatim se kreira nova verzija tog istog retka; vrijednost xmin za nju podudara se s vrijednošću xmax prethodne verzije.

Nakon nekog vremena nakon završetka ove transakcije, stara ili nova verzija, ovisno o COMMIT/ROLLBACK, bit će proglašena 'mrtvima' (dead tuples) prilikom prolaska VACUUM-a kroz tablicu i očišćena.

Međutim, to se neće dogoditi odmah, dok se problemi s 'mrtvima' mogu brzo pojaviti — tijekom višekratnih ili masovnih ažuriranja zapisa u velikoj tablici, a kasnije se može dogoditi da ni VACUUM ne bude od pomoći.

#1: I Like To Move It

Pretpostavimo da vaša poslovna logika radi i iznenada shvati da bi trebalo ažurirati polje X u nekom zapisu:

UPDATE tbl SET X =  WHERE pk = $1;

Kasnije, tijekom izvršavanja, otkriva da bi također trebalo ažurirati polje Y:

UPDATE tbl SET Y =  WHERE pk = $1;

... a zatim još i Z — zašto se zamarati?

UPDATE tbl SET Z =  WHERE pk = $1;

Koliko sada imamo verzija ovog zapisa u bazi? Ah, 4 komada! Od toga je jedan aktualan, a 3 će morati očistiti [auto]VACUUM.

Nemojte tako! Koristite ažuriranje svih polja u jednom upitu — gotovo uvijek je logiku rada metode moguće promijeniti ovako:

UPDATE tbl SET X = , Y = , Z =  WHERE pk = $1;

#2: Use IS DISTINCT FROM, Luke!

Dakle, ipak ste odlučili ažurirati mnogo, mnogo zapisa u tablici ( tijekom primjene skripte ili konvertera, na primjer). I u skriptu se nalaze ovakve stvari:

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

Otprilike u tom obliku, upit se često susreće i gotovo uvijek ne radi se o popunjavanju novog praznog polja, već o ispravci nekih grešaka u podacima. Pri tome se sama ispravnost već postojećih podataka uopće ne uzima u obzir — a to je šteta! Dakle, zapis se prepisuje, čak i ako je tamo bilo točno ono što se željelo — a zašto? Ispravimo:

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

Mnogi nisu svjesni postojanja takvog izvrsnog operatora, stoga evo kratke pomagala za IS DISTINCT FROM i drugim logičkim operatorima u pomoć:

… i malo o operacijama nad složenim ROW()-ekspresijama:

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

Pokreću se dva identična paralelna procesa, od kojih svaki pokušava označiti zapis kao 'u obradi':

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

Čak i ako ti procesi suštinski rade neovisne stvari jedni od drugih, ali unutar jednog ID-a, na tom upitu drugi klijent će 'zaključati' dok se ne završi prva transakcija.

Rješenje br. 1: zadatak se svodi na prethodni

Jednostavno ponovo dodajmo IS DISTINCT FROM:

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

U tom obliku drugi upit jednostavno neće mijenjati bazu, već je već 'sve kako treba' — stoga neće doći do zaključavanja. Dodatno, činjenicu 'nepronađenosti' zapisa već obrađujemo u aplikativnom algoritmu.

Rješenje br. 2: savjetodavne zaključavanje

Velika tema za poseban članak, u kojem se može pročitati o načinih primjena i 'zamkama' preporučivih zaključavanja.

Rješenje br. 3: bez[š]umne pozive

A upravo bi trebalo doći do istovremenog rada s istim zapisom? Или вы все-таки накосячили с алгоритмами вызовов бизнес-логики со стороны клиента, например? А если подумать?..

Izvor: habr.com