PostgreSQL Antipatterns: boj proti hordam »mrtvih«

Posebnosti notranjih mehanizmov PostgreSQL omogočajo, da je v nekaterih situacijah zelo hiter, v drugih pa "ne zelo hiter". Danes se bomo osredotočili na klasičen primer konflikta med tem, kako deluje DBMS in kaj razvijalec počne z njim - UPDATE proti načelom MVCC.

Kratka zgodba iz odličen članek:

Ko je vrstica spremenjena z ukazom UPDATE, se dejansko izvedeta dve operaciji: DELETE in INSERT. IN trenutna različica niza xmax je nastavljen enako številu transakcije, ki je izvedla POSODOBITEV. Potem se ustvari novo različico ista vrstica; njegova vrednost xmin sovpada z vrednostjo xmax prejšnje različice.

Nekaj ​​časa po tem, ko je ta transakcija zaključena, stara ali nova različica, odvisno od COMMIT/ROOLBACK, bo prepoznan "mrtev" (mrtve tuple) ob prehodu VACUUM po tabeli in očiščeno.

A to se ne bo zgodilo takoj, temveč lahko težave z "mrtvimi" pridobimo zelo hitro - s ponavljajočimi se oz množično posodabljanje zapisov v veliki tabeli, malo kasneje pa boste naleteli na isto situacijo VAKUUM ne bo mogel pomagati.

#1: Rad se premikam

Recimo, da vaša metoda deluje na poslovni logiki in nenadoma ugotovi, da bi bilo treba posodobiti polje X v nekem zapisu:

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

Potem, ko izvedba napreduje, se izkaže, da je treba posodobiti tudi polje Y:

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

... in potem tudi Z - zakaj bi izgubljal čas za malenkosti?

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

Koliko različic tega zapisa imamo zdaj v bazi podatkov? Ja, 4 kosi! Od teh je eden ustrezen, 3 pa bo moral za vami pospraviti [avto]VAKUUM.

Ne počni tega na ta način! Uporaba posodobitev vseh polj v eni zahtevi — skoraj vedno je logiko metode mogoče spremeniti takole:

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

#2: Uporaba se RAZLIKUJE OD Luke!

Torej ste še vedno želeli posodobite veliko, veliko zapisov v tabeli (na primer med uporabo skripte ali pretvornika). In nekaj takega prileti v scenarij:

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

Zahteva v približno tej obliki se pojavi precej pogosto in skoraj vedno, da ne izpolnite praznega novega polja, ampak da popravite nekatere napake v podatkih. Hkrati tudi ona sama pravilnost obstoječih podatkov sploh ni upoštevana - ampak zaman! Se pravi, zapisnik se prepiše, tudi če je vseboval natanko tisto, kar se je želelo – a zakaj? Popravimo:

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

Mnogi ljudje se ne zavedajo obstoja tako čudovitega operaterja, zato je tukaj goljufija IS DISTINCT FROM in drugi logični operatorji za pomoč:

... in nekaj o operacijah na kompleksu ROW()-izrazi:

#3: Svojega dragega prepoznam po ... blokiranju

se lansirajo dva identična vzporedna procesa, od katerih vsak poskuša označiti vnos, da je "v teku":

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

Tudi če ti procesi dejansko počnejo stvari neodvisno drug od drugega, vendar znotraj istega ID-ja, bo drugi odjemalec na to zahtevo "zaklenjen", dokler prva transakcija ni dokončana.

Rešitev #1: naloga se zmanjša na prejšnjo

Samo še enkrat ga dodajmo IS DISTINCT FROM:

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

V tej obliki druga zahteva preprosto ne bo spremenila ničesar v bazi podatkov, vse je že tako, kot mora biti - zato do blokade ne bo prišlo. Nato obdelamo dejstvo "ne najdenja" zapisa v uporabljenem algoritmu.

Rešitev #2: svetovalne ključavnice

Velika tema za ločen članek, v katerem lahko preberete metode uporabe in »rake« priporočljive blokade.

Rešitev #3: neumni klici

A prav to bi se vam moralo zgoditi sočasno delo z istim zapisom? Ali pa si zamočil na primer algoritme za priklic poslovne logike na odjemalski strani? In če pomislite?..

Vir: www.habr.com