PostgreSQL Antipatterns: kumenyana ndi magulu a "akufa"

Zomwe zimapangidwira mkati mwa PostgreSQL zimalola kuti ikhale yachangu kwambiri nthawi zina komanso "osati mwachangu" mwa ena. Lero tiyang'ana pa chitsanzo chapamwamba cha mkangano pakati pa momwe DBMS imagwirira ntchito ndi zomwe wopanga mapulogalamu amachita nayo - UPDATE vs MVCC mfundo.

Nkhani yachidule yochokera nkhani yabwino:

Mzere ukasinthidwa ndi lamulo la UPDATE, ntchito ziwiri zimachitikadi: DELETE ndi INSERT. MU mtundu wamakono wa chingwe xmax yakhazikitsidwa yofanana ndi kuchuluka kwa ndalama zomwe zidachitika UPDATE. Kenako amalengedwa Baibulo latsopano mzere womwewo; mtengo wake wa xmin umagwirizana ndi mtengo wa xmax wa mtundu wakale.

Patapita nthawi ntchito imeneyi itatha, mtundu wakale kapena watsopano, kutengera COMMIT/ROOLBACK, adzazindikiridwa "akufa" (akufa) podutsa VACUUM monga mwa gome ndi kuyeretsedwa.

Koma izi sizidzachitika nthawi yomweyo, koma mavuto ndi "akufa" angapezeke mofulumira kwambiri - mobwerezabwereza kapena kusinthidwa kwakukulu kwa marekodi patebulo lalikulu, ndipo pakapita nthawi mudzakumana ndi mkhalidwe womwewo VACUUM sichitha kuthandiza.

#1: Ndimakonda Kusuntha

Tiyerekeze kuti njira yanu ikugwira ntchito pazolinga zabizinesi, ndipo mwadzidzidzi zimazindikira kuti zingakhale zofunikira kusinthira gawo la X mu mbiri ina:

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

Kenako, kuphedwa kukupitilira, zikuwoneka kuti gawo la Y liyenera kusinthidwanso:

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

... ndiyenonso Z - chifukwa chiyani mukutaya nthawi pazinthu zazing'ono?

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

Kodi tsopano tili ndi mitundu ingati ya rekodiyi? Inde, zidutswa 4! Mwa izi, imodzi ndiyofunikira, ndipo 3 iyenera kuyeretsedwa pambuyo panu ndi [auto]VACUUM.

Osachita mwanjira iyi! Gwiritsani ntchito kukonzanso magawo onse mu pempho limodzi - pafupifupi nthawi zonse malingaliro a njirayo amatha kusinthidwa motere:

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

#2: Kugwiritsa Ntchito NDIKUSINTHA NDI, Luka!

Kotero, iwe umafunabe sinthani zambiri, zolemba zambiri patebulo (panthawi yogwiritsira ntchito script kapena converter, mwachitsanzo). Ndipo chinthu chonga ichi chikuwulukira mu script:

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

Pempho pafupifupi mu fomu iyi limapezeka kawirikawiri ndipo pafupifupi nthawi zonse kuti musadzaze gawo latsopano lopanda kanthu, koma kukonza zolakwika zina mu data. Pa nthawi yomweyo, iye mwini kulondola kwa deta yomwe ilipo sikukuganiziridwa nkomwe - koma pachabe! Ndiko kuti, zolembazo zimalembedwanso, ngakhale zili ndi zomwe zimafunidwa - koma chifukwa chiyani? Tiyeni tikonze:

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

Anthu ambiri sadziwa za kukhalapo kwa wogwiritsa ntchito wabwino chonchi, ndiye apa pali pepala lachinyengo IS DISTINCT FROM ndi ena ogwira ntchito zomveka kuti athandize:

... ndi pang'ono za ntchito pa zovuta ROW()-mawu:

#3: Ndimazindikira wokondedwa wanga mwa... kutsekereza

zikuyambitsidwa njira ziwiri zofanana, iliyonse yomwe imayesa kuyika chizindikiro kuti "ikuchitika":

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

Ngakhale ngati njirazi zikuchita zinthu popanda wina ndi mzake, koma mkati mwa ID yomweyo, kasitomala wachiwiri "adzatsekedwa" pa pempholi mpaka ntchito yoyamba itatha.

Yankho # 1: ntchitoyo imachepetsedwa kukhala yoyamba

Tiyeni tingowonjezeranso IS DISTINCT FROM:

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

Mu mawonekedwe awa, pempho lachiwiri silingasinthe chilichonse mu database, zonse zili kale momwe ziyenera kukhalira - chifukwa chake, kutsekereza sikudzachitika. Kenako, timakonza mfundo yakuti "osapeza" zolemba mu algorithm yogwiritsidwa ntchito.

Yankho # 2: alangizi maloko

Mutu waukulu wankhani yosiyana, momwe mungawerenge njira zogwiritsira ntchito ndi "kufufuza" zoletsa zovomerezeka.

Yankho # 3: mafoni opusa

Koma izi ndi zomwe ziyenera kuchitika kwa inu ntchito munthawi yomweyo ndi mbiri yomweyo? Kapena mudasokoneza ma aligorivimu oyitanitsa malingaliro abizinesi kumbali ya kasitomala, mwachitsanzo? Ndipo ngati mukuganiza? ..

Source: www.habr.com