PostgreSQL Antipatterns: borba protiv hordi "mrtvih"

Osobitosti unutarnjih mehanizama PostgreSQL-a dopuštaju mu da bude vrlo brz u nekim situacijama i "ne baš brz" u drugim. Danas ćemo se usredotočiti na klasičan primjer sukoba između načina na koji DBMS radi i onoga što programer radi s njim - UPDATE protiv MVCC principa.

Kratka priča iz odličan članak:

Kada se red mijenja naredbom UPDATE, zapravo se izvode dvije operacije: DELETE i INSERT. U trenutna verzija niza xmax je postavljen jednak broju transakcije koja je izvršila AŽURIRANJE. Tada se stvara nova verzija ista linija; njegova xmin vrijednost podudara se s xmax vrijednošću prethodne verzije.

Neko vrijeme nakon što je ova transakcija dovršena, stara ili nova verzija, ovisno o tome COMMIT/ROOLBACK, prepoznat će se "mrtav" (mrtve torke) prilikom prolaska VACUUM prema tablici i očišćeno.

Ali to se neće dogoditi odmah, ali problemi s "mrtvima" mogu se dobiti vrlo brzo - s ponovljenim ili masovno ažuriranje zapisa u velikom stolu, a nešto kasnije naići ćete na istu situaciju VAKUUM neće moći pomoći.

#1: Volim se kretati

Recimo da vaša metoda radi na poslovnoj logici i odjednom shvati da bi bilo potrebno ažurirati polje X u nekom zapisu:

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

Zatim, kako izvođenje napreduje, ispostavlja se da polje Y također treba ažurirati:

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

... a onda i Z - čemu gubiti vrijeme na sitnice?

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

Koliko verzija ovog zapisa sada imamo u bazi podataka? Da, 4 komada! Od njih je jedan relevantan, a 3 će za vama morati počistiti [auto]VAKUUM.

Nemojte to raditi na ovaj način! Koristiti ažuriranje svih polja u jednom zahtjevu — gotovo uvijek se logika metode može promijeniti ovako:

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

#2: Upotreba se RAZLIKUJE OD, Luke!

Dakle, ipak ste htjeli ažurirati mnogo, mnogo zapisa u tablici (tijekom korištenja skripte ili pretvarača, na primjer). I ovako nešto uleti u scenarij:

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

Zahtjev otprilike u ovom obliku javlja se dosta često i gotovo uvijek da se ne popuni prazno novo polje, već da se isprave neke greške u podacima. Istovremeno i ona sama uopće se ne vodi računa o ispravnosti postojećih podataka - ali uzalud! Odnosno, zapisnik se prepisuje, pa makar sadržavao upravo ono što se tražilo – ali zašto? Popravimo to:

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

Mnogi ljudi nisu svjesni postojanja tako divnog operatera, pa evo varalice IS DISTINCT FROM i drugi logički operatori za pomoć:

... i malo o operacijama na kompleksu ROW()-izrazi:

#3: Svog dragog prepoznajem po... blokiranju

se lansiraju dva identična paralelna procesa, od kojih svaki pokušava označiti unos da je "u tijeku":

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

Čak i ako ovi procesi zapravo rade stvari neovisno jedan o drugom, ali unutar istog ID-a, drugi klijent će biti "zaključan" na ovaj zahtjev dok se prva transakcija ne završi.

Rješenje broj 1: zadatak se svodi na prethodni

Dodajmo ga ponovno IS DISTINCT FROM:

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

U ovom obliku, drugi zahtjev jednostavno neće promijeniti ništa u bazi podataka, sve je već kako treba - dakle, blokiranje se neće dogoditi. Zatim obrađujemo činjenicu "nepronalaženja" zapisa u primijenjenom algoritmu.

Rješenje broj 2: savjetodavne brave

Velika tema za poseban članak, u kojem možete čitati metode primjene i “grablje” preporučljivog blokiranja.

Rješenje broj 3: glupi pozivi

Ali to je upravo ono što bi vam se trebalo dogoditi istovremeni rad s istim zapisom? Ili si zeznuo algoritme za pozivanje poslovne logike na klijentskoj strani npr.? A ako malo razmislite?..

Izvor: www.habr.com