PostgreSQL Antipatterns: la dagaalanka tirada badan ee " dhintay"

Noocyada hababka gudaha ee PostgreSQL waxay u oggolaanayaan inay aad u dhaqso badan yihiin xaaladaha qaarkood iyo "aan degdeg ahayn" kuwa kale. Maanta waxaan diiradda saari doonaa tusaale caadi ah oo isku dhaca u dhexeeya sida DBMS u shaqeeyo iyo waxa horumariyahu ku sameeyo UPDATE vs mabaadiida MVCC.

Sheeko kooban oo ka timid article weyn:

Marka safka lagu beddelo amarka UPDATE, laba hawlgal ayaa dhab ahaantii la sameeyaa: Tirtir iyo Geli. IN nooca hadda ee xadhigga xmax waxa loo dhigay si le'eg tirada wax kala iibsiga ee sameeyay UPDATE Kadibna waa la abuuraa nooc cusub xariiq isku mid ah; qiimihiisu xmin waxa uu ku beegan yahay xmax qiimaha noocii hore.

Muddo ka dib marka la dhammeeyo wax kala iibsiga, nooca hore ama kan cusub, iyadoo ku xidhan COMMIT/ROOLBACK, waa la aqoonsan doonaa " dhintay" (tuples dhintay) marka la dhaafo VACUUM sida ku cad shaxda iyo nadiifinta.

Laakiin tani isla markiiba ma dhici doonto, laakiin dhibaatooyinka kuwa dhintay ayaa si dhakhso ah loo heli karaa - soo noqnoqda ama update tirada badan ee diiwaanada miis weyn, wax yar ka dib waxaad la kulmi doontaa xaalad la mid ah VACUUM ma awoodi doonto inay ku caawiso.

#1: Waxaan Jeclahay inaan Dhaqaaqo

Aynu nidhaahno habkaagu wuxuu ku shaqeynayaa caqli-gal ganacsi, oo si lama filaan ah waxay u ogaatay inay lagama maarmaan tahay in la cusbooneysiiyo goobta X ee rikoodhada qaarkood:

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

Dabadeed, sida fulintu sii socoto, waxay soo baxday in goobta Y sidoo kale la cusboonaysiiyo:

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

... ka dibna sidoo kale Z - maxaa wakhti ku luminaya waxyaabaha yaryar?

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

Immisa nooc oo diiwaankan ah ayaan hadda ku haynaa kaydka xogta? Haa, 4 xabbo! Kuwaas, mid ka mid ah ayaa quseeya, iyo 3 waa in lagu nadiifiyaa ka dib [auto] VACUUM.

Ha samayn habkan! Isticmaal cusboonaysiinta dhammaan goobaha hal codsi - Had iyo jeer caqliga habka waxa loo bedeli karaa sidan:

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

#2: Isticmaalku wuu ka duwan yahay, Luukos!

Markaa, wali waad rabtay Cusbooneysii diiwaanno badan oo badan oo miis ku jira (inta lagu jiro isticmaalka qoraalka ama beddelka, tusaale ahaan). Oo wax sidan oo kale ah ayaa u duulaya qoraalka:

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

Codsiga ku saabsan foomkan wuxuu dhacaa marar badan oo had iyo jeer maaha in la buuxiyo meel cusub oo madhan, laakiin si loo saxo khaladaadka qaarkood ee xogta. Isla markaana, iyada lafteeda saxnaanta xogta jirta gabi ahaanba lama tixgelin - laakiin micne lahayn! Taasi waa, diiwaanka dib ayaa loo qoraa, xitaa haddii ay ka kooban yihiin wixii la rabay - laakiin waa maxay sababta? Aan hagaajino:

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

Dad badan ayaan ka warqabin jiritaanka hawl wadeen cajiib ah, markaa halkan waa xaashi khiyaamo ah IS DISTINCT FROM iyo hawlwadeeno kale oo macquul ah si ay u caawiyaan:

... iyo in yar oo ku saabsan hawlgallada adag ROW()- weedho:

#3: Waxaan gacaliyahayga ku garanayaa... xannibidda

ayaa la bilaabayaa laba hab oo isku mid ah oo isku mid ah, mid kasta oo ka mid ah kuwaas oo isku dayaya in ay calaamadiyaan gelitaanka in ay "socotaa":

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

Xitaa haddii hababkani ay dhab ahaantii sameeyaan waxyaabo ka madax bannaan midba midka kale, laakiin isla aqoonsi isku mid ah, macmiilka labaad waa la "xiri doonaa" codsigan ilaa macaamilka ugu horreeya la dhammeeyo.

Xalka # 1: hawshu waxay ku soo koobtay tii hore

Aan mar kale ku darno IS DISTINCT FROM:

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

Foomkan, codsiga labaad si fudud ma beddeli doono wax ku jira kaydka xogta, wax kastaa waa horeba sidii ay ahayd - sidaas darteed, xannibaadda ma dhici doonto. Marka xigta, waxaan ka baaraandegi karnaa xaqiiqda "aan helin" diiwaanka algorithm ee la dabaqay.

Xalka # 2: quful talo

Mawduuc weyn oo loogu talagalay maqaal gaar ah, kaas oo aad ka akhrisan karto hababka loo isticmaalo iyo "rake" ee xannibaadda talada.

Xalka # 3: baaqyo nacasnimo ah

Laakiin tani waa dhab ahaan waxa kugu dhici doona shaqo isku mar ah oo leh rikoor isku mid ah? Mise ma ku khalkhalisay algorithms-ka wacitaanka macquulka ganacsiga ee dhinaca macmiilka, tusaale ahaan? Oo haddii aad ka fikirto?..

Source: www.habr.com