PostgreSQL Antipatterns: pakikipaglaban sa sangkawan ng "patay"

Ang mga kakaiba ng mga panloob na mekanismo ng PostgreSQL ay nagbibigay-daan sa ito na maging napakabilis sa ilang mga sitwasyon at "hindi masyadong mabilis" sa iba. Ngayon ay tututuon tayo sa isang klasikong halimbawa ng isang salungatan sa pagitan ng kung paano gumagana ang isang DBMS at kung ano ang ginagawa ng developer dito - I-UPDATE kumpara sa mga prinsipyo ng MVCC.

Maikling kwento mula sa mahusay na artikulo:

Kapag ang isang hilera ay binago ng isang UPDATE na utos, dalawang operasyon ang aktwal na isinasagawa: DELETE at INSERT. SA kasalukuyang bersyon ng string Nakatakda ang xmax na katumbas ng bilang ng transaksyon na nagsagawa ng UPDATE. Pagkatapos ito ay nilikha isang bagong bersyon ang parehong linya; ang xmin value nito ay tumutugma sa xmax value ng nakaraang bersyon.

Ilang oras pagkatapos makumpleto ang transaksyong ito, ang luma o bagong bersyon, depende sa COMMIT/ROOLBACK, makikilala "patay" (patay na tuples) kapag dumadaan VACUUM ayon sa talahanayan at nilinis.

Ngunit hindi ito mangyayari kaagad, ngunit ang mga problema sa "patay" ay maaaring makuha nang napakabilis - sa paulit-ulit o mass update ng records sa isang malaking mesa, at ilang sandali ay makakatagpo ka ng parehong sitwasyon Hindi makakatulong ang VACUUM.

#1: Gusto Kong Ilipat Ito

Sabihin nating gumagana ang iyong pamamaraan sa lohika ng negosyo, at bigla nitong napagtanto na kakailanganing i-update ang X field sa ilang talaan:

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

Pagkatapos, habang umuusad ang pagpapatupad, lumalabas na dapat ding i-update ang field ng Y:

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

... at saka Z - bakit mag-aaksaya ng oras sa mga bagay na walang kabuluhan?

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

Ilang bersyon ng record na ito ang mayroon tayo ngayon sa database? Oo, 4 piraso! Sa mga ito, ang isa ay may kaugnayan, at 3 ay kailangang linisin pagkatapos mo sa pamamagitan ng [auto]VACUUM.

Huwag gawin ito sa ganitong paraan! Gamitin pag-update ng lahat ng mga field sa isang kahilingan — halos palaging ang lohika ng pamamaraan ay maaaring mabago tulad nito:

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

#2: IBA ANG Gamitin, Luke!

So, gusto mo pa i-update ang marami, maraming record sa isang table (sa panahon ng paggamit ng isang script o converter, halimbawa). At ang isang bagay na tulad nito ay lumilipad sa script:

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

Ang isang kahilingan sa humigit-kumulang na form na ito ay nangyayari nang madalas at halos palaging hindi upang punan ang isang walang laman na bagong field, ngunit upang itama ang ilang mga error sa data. At the same time, siya mismo ang kawastuhan ng umiiral na data ay hindi isinasaalang-alang sa lahat - ngunit walang kabuluhan! Iyon ay, ang rekord ay muling isinulat, kahit na naglalaman ito ng eksakto kung ano ang nais - ngunit bakit? Ayusin natin ito:

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

Maraming tao ang hindi nakakaalam ng pagkakaroon ng napakagandang operator, kaya narito ang isang cheat sheet sa IS DISTINCT FROM at iba pang lohikal na operator para tumulong:

... at kaunti tungkol sa mga operasyon sa complex ROW()-mga expression:

#3: Kinikilala ko ang aking syota sa pamamagitan ng... pagharang

ay inilunsad dalawang magkaparehong parallel na proseso, ang bawat isa ay sumusubok na markahan ang entry na ito ay "kasalukuyang":

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

Kahit na ang mga prosesong ito ay aktwal na gumagawa ng mga bagay na hiwalay sa isa't isa, ngunit sa loob ng parehong ID, ang pangalawang kliyente ay "mai-lock" sa kahilingang ito hanggang sa makumpleto ang unang transaksyon.

Solusyon #1: ang gawain ay nabawasan sa nauna

Idagdag na lang natin IS DISTINCT FROM:

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

Sa form na ito, ang pangalawang kahilingan ay hindi magbabago ng anuman sa database, ang lahat ay nasa dapat na - samakatuwid, ang pagharang ay hindi magaganap. Susunod, pinoproseso namin ang katotohanan ng "hindi paghahanap" ng tala sa inilapat na algorithm.

Solusyon #2: advisory lock

Isang malaking paksa para sa isang hiwalay na artikulo, kung saan maaari mong basahin ang tungkol sa mga paraan ng aplikasyon at "rake" ng pagharang ng rekomendasyon.

Solusyon #3: mga katangahang tawag

Ngunit ito mismo ang dapat mangyari sa iyo sabay-sabay na gawain na may parehong talaan? O nagulo mo ba ang mga algorithm para sa pagtawag sa lohika ng negosyo sa panig ng kliyente, halimbawa? At kung iisipin mo?..

Pinagmulan: www.habr.com