I-Postgres: i-bloat, i-pg_repack kunye nezithintelo ezimisiweyo

Impembelelo yeetafile zokuqhaqha kunye nezalathisi (i-bloat) ziyaziwa ngokubanzi kwaye azikho kuphela kwi-Postgres. Kukho iindlela zokujongana nayo ngaphandle kwebhokisi, njenge-VACUUM FULL okanye i-CLUSTER, kodwa zitshixa iitafile ngexesha lokusebenza kwaye ke azinakusetyenziswa rhoqo.

Inqaku liza kuqulatha ithiyori encinci malunga nokuba i-bloat yenzeka njani, ungayilwa njani, malunga nemithintelo erhoxisiweyo kunye neengxaki abazizisayo ekusebenziseni ulwandiso lwe-pg_repack.

Eli nqaku libhalwe ngokusekelwe intetho yam kwi-PgConf.Russia 2020.

Kutheni i-bloat isenzeka?

I-Postgres isekwe kwimodeli yeenguqulelo ezininzi (MVCC). Undoqo wayo kukuba umqolo ngamnye kwitheyibhile unokuba neenguqulelo ezininzi, ngelixa iintengiselwano zingaboni ngaphezu kweyodwa yezi nguqulelo, kodwa kungekhona enye efanayo. Oku kuvumela iintengiselwano ezininzi ukuba zisebenze ngaxeshanye kwaye zingabinampembelelo enye kwenye.

Ngokucacileyo, zonke ezi nguqulelo kufuneka zigcinwe. I-Postgres isebenza ngephepha lememori ngephepha kunye nephepha lelona nani lincinci ledatha enokufundwa kwidiski okanye ebhaliweyo. Makhe sijonge kumzekelo omncinci ukuqonda ukuba kwenzeka njani oku.

Masithi sinetafile esifake kuyo iirekhodi ezininzi. Idatha entsha ivele kwiphepha lokuqala lefayile apho itafile igcinwe khona. Ezi ziinguqulelo ezibukhoma zemiqolo ekhoyo kwezinye iintengiselwano emva kokuzibophelela (ukuze kube lula, siya kucinga ukuba inqanaba lokuzahlula liFundiwe Uzibophelele).

Emva koko siye sahlaziya enye yamangeno, ngaloo ndlela siphawula inguqulelo endala njengengasasebenzi.

Inyathelo ngenyathelo, ukuhlaziya kunye nokucima iinguqulelo zomqolo, sagqiba ngephepha apho malunga nesiqingatha sedatha "yinkunkuma". Le datha ayibonakali kuyo nayiphi na intengiselwano.

I-Postgres inesixhobo VACUUM, ecoca iinguqulelo eziphelelwe lixesha kwaye yenza indawo yedatha entsha. Kodwa ukuba ayicwangciswanga ngokukrakra ngokwaneleyo okanye ixakeke ngokusebenza kwezinye iitafile, ngoko "idatha yenkunkuma" ihlala, kwaye kufuneka sisebenzise amaphepha ongezelelweyo kwidatha entsha.

Ke kumzekelo wethu, ngexesha elithile itheyibhile iya kuba namaphepha amane, kodwa isiqingatha sawo siya kuba nedatha ephilayo. Ngenxa yoko, xa sifikelela kwitheyibhile, siya kufunda idatha eninzi kunokuba kuyimfuneko.

Nokuba i-VACUUM ngoku icima zonke iinguqulelo zerowu ezingabalulekanga, imeko ayizukuphucuka kakhulu. Siya kuba nesithuba esikhululekileyo kumaphepha okanye amaphepha apheleleyo kwimiqolo emitsha, kodwa siya kufunda idatha eninzi kunokuba kuyimfuneko.
Ngendlela, ukuba iphepha elingenanto ngokupheleleyo (elesibini kumzekelo wethu) belisekupheleni kwefayile, ngoko iVACUUM iya kukwazi ukuyinciphisa. Kodwa ngoku uphakathi, ngoko akukho nto inokwenziwa ngaye.

Xa inani lamaphepha anjalo angenanto okanye anqabile kakhulu, abizwa ngokuba yi-bloat, iqala ukuchaphazela ukusebenza.

Yonke into echazwe ngasentla bubuchwephesha bokuvela kwe-bloat kwiitafile. Kwizalathisi oku kwenzeka ngendlela efanayo.

Ngaba ndinedumba?

Kukho iindlela ezininzi zokujonga ukuba une-bloat. Ingcamango yeyokuqala kukusebenzisa izibalo zePostgres zangaphakathi, eziqulethe ulwazi olusondeleyo malunga nenani lemiqolo kwiitheyibhile, inani lemigca "ephilayo", njl. Sathatha njengesiseko umbhalo ukusuka kwiiNgcali zePostgreSQL, ezinokuthi zivavanye iitafile ze-bloat kunye ne-toast kunye ne-btree indexes. Kumava ethu, impazamo yayo yi-10-20%.

Enye indlela kukusebenzisa ulwandiso pgstattuple, ekuvumela ukuba ujonge ngaphakathi kwamaphepha kwaye ufumane zombini uqikelelo kunye nexabiso elichanekileyo le-bloat. Kodwa kwimeko yesibini, kuya kufuneka uskene yonke itafile.

Siqwalasela ixabiso elincinci le-bloat, ukuya kwi-20%, eyamkelekileyo. Inokuthathwa njenge-analogue ye-fillerfactor iitafile и indices. Kwi-50% nangaphezulu, iingxaki zokusebenza zinokuqala.

Iindlela zokulwa ne-bloat

I-Postgres ineendlela ezininzi zokujongana ne-bloat ngaphandle kwebhokisi, kodwa azisoloko zilungele wonke umntu.

Qwalasela i-AUTOVACUUM ukuze i-bloat ingenzeki. Okanye ngokuchanekileyo, ukuyigcina kwinqanaba elamkelekileyo kuwe. Oku kubonakala ngathi iingcebiso "zomphathi", kodwa enyanisweni oku akusoloko kulula ukufikelela. Umzekelo, unophuhliso olusebenzayo kunye neenguqu eziqhelekileyo kwi-schema yedatha, okanye uhlobo oluthile lokufuduka kwedatha okwenzekayo. Ngenxa yoko, iprofayile yakho yomthwalo inokutshintsha rhoqo kwaye iya kwahluka ngokwetafile ukuya kwetafile. Oku kuthetha ukuba kufuneka uhlale usebenza phambili kancinci kwaye ulungelelanise i-AUTOVACUUM kwiprofayili eguqukayo yetafile nganye. Kodwa ngokucacileyo oku akulula ukukwenza.

Esinye isizathu esiqhelekileyo sokuba kutheni i-AUTOVACUUM ayikwazi ukugcina iitafile kukuba kukho ukuthengiselana kwexesha elide elithintela ukucoca idatha ekhoyo kwezo ntengiselwano. Isindululo esilapha sikwacacile - lahla iintengiselwano "ezijingayo" kwaye unciphise ixesha lentengiselwano esebenzayo. Kodwa ukuba umthwalo kwisicelo sakho yi-hybrid ye-OLAP kunye ne-OLTP, ngoko unako ukuhlaziya ezininzi rhoqo kunye nemibuzo emfutshane, kunye nokusebenza kwexesha elide - umzekelo, ukwakha ingxelo. Kwimeko enjalo, kuyafaneleka ukucinga malunga nokusasaza umthwalo kwiziseko ezahlukeneyo, okuya kuvumela ukulungiswa kakuhle kwazo zonke.

Omnye umzekelo - nokuba iprofayili i-homogeneous, kodwa i-database iphantsi komthwalo ophezulu kakhulu, ngoko nangona i-AUTOVACUUM enobudlova ayinakukwazi ukujamelana nayo, kwaye i-bloat iya kwenzeka. Ukulinganisa (okuthe nkqo okanye okuthe tye) kuphela kwesisombululo.

Yintoni enokuyenza kwimeko apho usethe i-AUTOVACUUM, kodwa i-bloat iyaqhubeka ikhula.

Iqela IVACUUM IGCWELE yakha kwakhona imixholo yeetafile kunye nezalathisi kwaye ishiya kuphela idatha efanelekileyo kuzo. Ukuphelisa i-bloat, isebenza ngokugqibeleleyo, kodwa ngexesha lokuphunyezwa kwayo iqhaga elikhethekileyo etafileni liyabanjwa (AccessExclusiveLock), engayi kuvumela ukuphumeza imibuzo kule theyibhile, nokuba kukhethwe. Ukuba unako ukukwazi ukuyeka inkonzo yakho okanye inxalenye yayo ixesha elithile (ukusuka kumashumi emizuzu ukuya kwiiyure ezininzi kuxhomekeke kubukhulu besiseko sedatha kunye ne-hardware yakho), ngoko olu khetho lungcono. Ngelishwa, asinalo ixesha lokuqhuba i-VACUUM FULL ngexesha lokugcinwa okucwangcisiweyo, ngoko ke le ndlela ayifanelekanga kuthi.

Iqela IKLASITHI Yakha kwakhona imixholo yeetafile ngendlela efanayo ne-VACUUM FULL, kodwa ikuvumela ukuba ukhankanye isalathiso ngokwendlela idatha eya kucwangciswa ngayo ngokwasemzimbeni kwidiski (kodwa kwixesha elizayo umyalelo awuqinisekiswanga kwimiqolo emitsha). Kwiimeko ezithile, oku kukulungelelaniswa okulungileyo kwinani lemibuzo - ngokufunda iirekhodi ezininzi ngesalathiso. Ukungalungi komyalelo kuyafana naleyo yeVACUUM FULL - ivala itafile ngexesha lokusebenza.

Iqela REINDEX iyafana nezimbini zangaphambili, kodwa yakha kwakhona isalathiso esithile okanye zonke izalathisi zetafile. Izitshixo zibuthathaka kancinci: I-ShareLock etafileni (ithintela uhlengahlengiso, kodwa ivumela ukukhetha) kunye ne-AccessExclusiveLock kwisalathiso esakhiwa ngokutsha (ivala imibuzo usebenzisa esi salathisi). Nangona kunjalo, kwinguqulo ye-12 ye-Postgres ipharamitha yavela KWANGXENYE, ekuvumela ukuba uphinde wakhe isalathiso ngaphandle kokuthintela ukongezwa kwangaxeshanye, ukuguqulwa, okanye ukucinywa kweerekhodi.

Kwiinguqulelo zangaphambili zePostgres, unokufikelela kwisiphumo esifana ne-REINDEX NGOKUQHELEKILEYO usebenzisa YENZA ISALATHISO NGAXENYE. Ikuvumela ukuba wenze isalathiso ngaphandle kokutshixa ngokungqongqo (ShareUpdateExclusiveLock, engaphazamisi imibuzo enxuseneyo), emva koko ubuyisele isalathisi esidala ngesitsha kwaye ucime isalathisi esidala. Oku kukuvumela ukuba ususe i-index bloat ngaphandle kokuphazamisa isicelo sakho. Kubalulekile ukuqwalasela ukuba xa kusakhiwa kwakhona izalathisi kuya kubakho umthwalo owongezelelweyo kwi-subsystem yediski.

Ke, ukuba kwizalathisi kukho iindlela zokuphelisa i-bloat "kwi-fly," ke azikho iitafile. Apha kulapho izandiso ezahlukeneyo zangaphandle zingena khona: pg_repack (ngaphambili pg_reorg), pgcompact, pgcompacttable kunye nabanye. Kule nqaku, andiyi kuzithelekisa kwaye ndiya kuthetha kuphela nge-pg_repack, leyo, emva kokuguqulwa okuthile, sizisebenzisa thina.

Isebenza njani i-pg_repack

Masithi sinetafile eqhelekileyo ngokupheleleyo - kunye nezalathisi, izithintelo kwaye, ngelishwa, kunye ne-bloat. Inyathelo lokuqala le-pg_repack kukwenza itafile yelog ukugcina idatha malunga nalo lonke utshintsho ngelixa isebenza. I-trigger iya kuphinda-phinda olu tshintsho kuyo yonke into efakiweyo, ukuhlaziya kunye nokucima. Emva koko itafile yenziwe, ifana neyokuqala kwisakhiwo, kodwa ngaphandle kwezalathisi kunye nezithintelo, ukuze ungacothi inkqubo yokufaka idatha.

Okulandelayo, pg_repack idlulisela idatha ukusuka kwitafile endala ukuya kwitafile entsha, izihluza ngokuzenzekelayo yonke imiqolo engabalulekanga, kwaye yenza izalathisi zetafile entsha. Ngexesha lokwenziwa kwayo yonke le misebenzi, utshintsho luqokelelana kwitafile yelog.

Inyathelo elilandelayo kukudlulisa utshintsho kwitafile entsha. Ukufuduka kwenziwa ngokuphindaphindiweyo, kwaye xa kukho ngaphantsi kwama-20 amangeno ashiye kwitafile yelog, pg_repack ifumana isitshixo esiqinileyo, ifudusa idatha yamva nje, kwaye ibuyisela itafile endala kunye nentsha kwiitafile zenkqubo yePostgres. Eli lixesha kuphela kwaye lifutshane kakhulu apho awuyi kukwazi ukusebenza kunye netafile. Emva koku, itafile endala kunye netafile enezigodo ziyacinywa kwaye indawo iyakhululwa kwinkqubo yefayile. Inkqubo igqityiwe.

Yonke into ibonakala ilungile kwithiyori, kodwa kwenzeka ntoni ekusebenzeni? Sivavanye i-pg_repack ngaphandle komthwalo naphantsi komthwalo, kwaye sajonga ukusebenza kwayo kwimeko yokuyeka ngaphambi kwexesha (ngamanye amagama, usebenzisa i-Ctrl + C). Zonke iimvavanyo zabonisa ukuba unayo.

Saya kwivenkile yokutya-kwaye ke yonke into ayizange ihambe njengoko besilindele.

Ipanekuku yokuqala ethengiswayo

Kwiqela lokuqala sifumene impazamo malunga nokwaphulwa kwesithintelo esisodwa:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

Lo mda ube negama elenziwe ngokuzenzekelayo isalathisi_16508 - yenziwe ngu pg_repack. Ngokusekelwe kwiimpawu ezibandakanyiweyo ekuqulunqweni kwayo, sigqibe "yethu" isithintelo esihambelana nayo. Ingxaki iye yaba kukuba lo ayisosithintelo esiqhelekileyo, kodwa simiselwe (isinyanzelo esimisiweyo), oko kukuthi. ukuqinisekiswa kwayo kwenziwa kamva kunomyalelo we-sql, okhokelela kwimiphumo engalindelekanga.

Izithintelo ezirhoxisiweyo: kutheni zifuneka kwaye zisebenza njani

Ithiyori encinci malunga nezithintelo ezirhoxisiweyo.
Makhe siqwalasele umzekelo olula: sinencwadi yetafile-reference yemoto eneempawu ezimbini - igama kunye nomyalelo wemoto kuluhlu.

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique
);

Masithi bekufuneka sitshintshe imoto yokuqala neyesibini. Isisombululo esithe ngqo kukuhlaziya ixabiso lokuqala ukuya kweyesibini, kwaye elesibini ukuya kwelokuqala:

begin;
  update cars set ord = 2 where name = 'audi';
  update cars set ord = 1 where name = 'bmw';
commit;

Kodwa xa siqhuba le khowudi, silindele ukunyhashwa komqobo kuba ulandelelwano lwamaxabiso etafileni lwahlukile:

[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.

Ndingayenza njani ngokwahlukileyo? Ukhetho lokuqala: yongeza ixabiso elongezelelweyo endaweni yomyalelo oqinisekisiweyo ukuba awuyi kubakho kwitheyibhile, umzekelo "-1". Kwinkqubo, oku kubizwa ngokuba "kukutshintshisa amaxabiso eenguqu ezimbini ukuya kwesesithathu." I-drawback kuphela yale ndlela luhlaziyo olongezelelweyo.

Ukhetho lwesibini: Yiyila ngokutsha itafile ukuze isebenzise uhlobo lwedatha yendawo edadayo ngexabiso lolandelelwano endaweni yeenombolo ezipheleleyo. Emva koko, xa uhlaziywa ixabiso ukusuka kwi-1, umzekelo, ukuya kwi-2.5, ukungena kokuqala kuya "kuma" ngokuzenzekelayo phakathi kweyesibini neyesithathu. Esi sisombululo siyasebenza, kodwa kukho imida emibini. Okokuqala, ayizukusebenza kuwe ukuba ixabiso lisetyenziswe kwindawo ethile kujongano. Okwesibini, kuxhomekeke ekuchanekeni kohlobo lwedatha, uya kuba nenani elilinganiselweyo lokufakwa okunokwenzeka ngaphambi kokubala kwakhona amaxabiso azo zonke iirekhodi.

Ukhetho lwesithathu: yenza isithintelo sirhoxiswe ukuze sijongwe kuphela ngexesha lokuzibophelela:

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique deferrable initially deferred
);

Kuba ingqiqo yesicelo sethu sokuqala iqinisekisa ukuba onke amaxabiso akhethekile ngexesha lokuzibophelela, iya kuphumelela.

Umzekelo oxoxwe ngasentla, ngokuqinisekileyo, wenziwa kakhulu, kodwa utyhila ingcamango. Kwisicelo sethu, sisebenzisa imiqobo emisiweyo ukuphumeza ingqiqo enoxanduva lokusombulula iingxabano xa abasebenzisi ngaxeshanye besebenza ngezinto zewijethi ekwabelwana ngazo ebhodini. Ukusebenzisa izithintelo ezinjalo kusivumela ukuba senze ikhowudi yesicelo ibe lula.

Ngokubanzi, kuxhomekeke kuhlobo lwesithintelo, i-Postgres inamanqanaba amathathu e-granularity yokuwajonga: umqolo, ukuthengiselana, kunye namanqanaba okubonakalisa.

umthombo: ababi

KHANGELA kwaye UNGAKHO NULL zihlala zikhangelwa kwinqanaba lomqolo, kwezinye izithintelo, njengoko kunokubonwa kwitafile, kukho iinketho ezahlukeneyo. Unokufunda ngakumbi apha.

Ukushwankathela ngokufutshane, izithintelo ezirhoxisiweyo kwiimeko ezininzi zibonelela ngekhowudi efundeka ngakumbi kunye nemiyalelo embalwa. Nangona kunjalo, kuya kufuneka uhlawulele oku ngokucofa inkqubo yokulungisa ingxaki, ukusukela oko kwenzeka impazamo kunye nomzuzu owufumanayo malunga nayo yahlulwe ngexesha. Enye ingxaki enokubakho kukuba umcwangcisi angasoloko ekwazi ukwakha isicwangciso esisiso ukuba isicelo sibandakanya umqobo orhoxisiweyo.

Ukuphuculwa kwe pg_repack

Sigubungele ukuba yeyiphi imiqobo erhoxisiweyo, kodwa inxulumana njani nengxaki yethu? Masikhumbule impazamo esiyifumene ngaphambili:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

Kwenzeka xa idatha ikotshwa kwitafile yelog kwitafile entsha. Oku kukhangeleka kungaqhelekanga kuba... idatha kwitafile yelog izibophelele kunye nedatha kwitheyibhile yomthombo. Ukuba bayanelisa imiqobo yetafile yokuqala, banokuthi baphule njani imiqobo efanayo kwintsha?

Njengoko kuvela, ingcambu yengxaki ilele kwinqanaba langaphambili le-pg_repack, eyenza izalathisi kuphela, kodwa kungekhona imiqobo: itafile endala yayinomqobo okhethekileyo, kwaye entsha yenza isalathisi esisodwa endaweni yoko.

Kubalulekile ukuqaphela apha ukuba isithintelo siqhelekile kwaye asirhoxiswa, ngoko ke isalathiso esisodwa senziwe endaweni yoko siyalingana nesi sithintelo, kuba Izithintelo ezizodwa kwi-Postgres ziphunyezwa ngokudala isalathisi esisodwa. Kodwa kwimeko yesithintelo esimisiweyo, ukuziphatha akufani, kuba isalathiso asinakurhoxiswa kwaye sihlala sikhangelwa ngexesha lomyalelo we-sql uphunyezwa.

Ngaloo ndlela, undoqo wengxaki ulele "ukulibaziseka" kwetshekhi: kwitheyibhile yasekuqaleni kwenzeka ngexesha lokuzibophelela, kwaye kwitafile entsha ngexesha lomyalelo we-sql wenziwe. Oku kuthetha ukuba kufuneka siqinisekise ukuba uhlolo lwenziwa ngendlela efanayo kuzo zombini iimeko: mhlawumbi kusoloko kulibaziseka, okanye rhoqo ngoko nangoko.

Zeziphi ke iingcamango ebesinazo?

Yenza isalathiso esifana ne-deferred

Umbono wokuqala kukwenza zombini iitshekhi kwimo yangoku. Oku kunokuvelisa izithintelo ezininzi zobuxoki, kodwa ukuba zimbalwa zazo, oku akufanele kuchaphazele umsebenzi wabasebenzisi, kuba ezo ngxabano ziyimeko eqhelekileyo kubo. Zenzeka, umzekelo, xa abasebenzisi ababini beqala ukuhlela iwijethi efanayo ngexesha elinye, kwaye umxhasi womsebenzisi wesibini akanalo ixesha lokufumana ulwazi ukuba iwijethi sele ivaliwe ukuhlelwa ngumsebenzisi wokuqala. Kwimeko enjalo, umncedisi uyala umsebenzisi wesibini, kwaye umxhasi wakhe ubuyisela umva utshintsho kwaye avimbele iwijethi. Kancinci kamva, xa umsebenzisi wokuqala egqibezela ukuhlela, owesibini uya kufumana ulwazi lokuba iwijethi ayisavalwa kwaye iya kuba nako ukuphinda isenzo sayo.

Ukuqinisekisa ukuba iitshekhi zihlala zikwimo engalityaziswanga, senze isalathiso esitsha esifana nesithintelo sokuqala ebesimisiwe:

CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;

Kwimeko yovavanyo, sifumene iimpazamo ezimbalwa ezilindelekileyo. Impumelelo! Siqhube pg_repack kwakhona kwimveliso kwaye safumana iimpazamo ezi-5 kwiqela lokuqala kwiyure yomsebenzi. Esi sisiphumo esamkelekileyo. Nangona kunjalo, sele kwiqela lesibini inani leempazamo landa kakhulu kwaye kuye kwafuneka siyeke pg_repack.

Kwakutheni ukuze kwenzeke? Ukubakho kwempazamo kuxhomekeke ekubeni bangaphi abasebenzisi abasebenza ngeewijethi ezifanayo ngexesha elinye. Kubonakala ukuba, ngelo xesha kwakukho utshintsho oluncinci olukhuphisanayo kunye nedatha egcinwe kwiqela lokuqala kunezinye, okt. saba “nethamsanqa” nje.

Umbono awuzange usebenze. Ngelo xesha, sabona ezinye izisombululo ezibini: bhala kwakhona ikhowudi yethu yesicelo ukuze sikhuphe imiqobo erhoxisiweyo, okanye "fundisa" pg_repack ukusebenza nabo. Sakhetha eyesibini.

Buyisela izalathisi kwitheyibhile entsha ngezithintelo ezirhoxisiweyo ukusuka kwitheyibhile yokuqala

Injongo yokuhlaziywa yayicacile - ukuba itheyibhile yasekuqaleni inesithintelo esimisiweyo, ngoko ke entsha kufuneka udale umqobo onjalo, kwaye kungekhona isalathisi.

Ukuvavanya utshintsho lwethu, sibhale uvavanyo olulula:

• Itheyibhile enesithintelo esimisiweyo kunye nerekhodi enye;
• faka idatha kwi-loop ephikisana nerekhodi ekhoyo;
• yenza uhlaziyo-idatha ayisangquzulani;
• yenza utshintsho.

create table test_table
(
  id serial,
  val int,
  constraint uk_test_table__val unique (val) deferrable initially deferred 
);

INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    COMMIT;
  END;
END LOOP;

Uguqulelo loqobo lwe pg_repack luhlala luntlitheka kufakelo lokuqala, uguqulelo olulungisiweyo lusebenze ngaphandle kweempazamo. Kakhulu.

Siya kwimveliso kwaye siphinde sifumane impazamo kwinqanaba elifanayo lokukopa idatha ukusuka kwitafile yelog ukuya kwentsha:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

Imeko yeClassic: yonke into isebenza kwiindawo zokuvavanya, kodwa kungekhona kwimveliso?!

APPLY_COUNT kunye nokudibana kweebhetshi ezimbini

Saqala ukuhlalutya ikhowudi ngokoqobo umgca ngomgca kwaye safumanisa inqaku elibalulekileyo: idatha idluliselwa kwitafile yelogi ukuya kwenye entsha kwiibhetshi, i-APPLY_COUNT rhoqo ibonise ubungakanani bebhetshi:

for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  /* there might be still some tuples, repeat. */
...
}

Ingxaki kukuba idatha evela kwintengiselwano yokuqala, apho imisebenzi emininzi inokuthi iphule isithintelo, xa idluliselwe, inokuphela ekudibaneni kweebhetshi ezimbini - isiqingatha semiyalelo siya kwenziwa kwibhetshi yokuqala, kunye nesinye isiqingatha. kweyesibini. Kwaye apha, kuxhomekeke kwinhlanhla yakho: ukuba amaqela awaphuli nantoni na kwibhetshi yokuqala, ngoko yonke into ilungile, kodwa ukuba yenza, kwenzeka iphutha.

APPLY_COUNT ilingana neerekhodi ezili-1000, nto leyo echaza ukuba kutheni uvavanyo lwethu luphumelele - abakhange balikhuphele ityala “lokudibana kwebhetshi”. Sisebenzise imiyalelo emibini - faka kwaye uhlaziywe, ngoko ke ngokuthe ngqo i-500 yentengiselwano yemiyalelo emibini yayihlala ibekwe kwibhetshi kwaye asizange sibe naziphi na iingxaki. Emva kokongeza uhlaziyo lwesibini, ukuhlela kwethu kuyekile ukusebenza:

FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    UPDATE test_table set val = i where id = v_id; -- one more update
    COMMIT;
  END;
END LOOP;

Ngoko ke, umsebenzi olandelayo kukuqinisekisa ukuba idatha esuka kwitheyibhile yokuqala, eyatshintshwa kwintengiselwano enye, iphelela kwitafile entsha kwakhona ngaphakathi kwentengiselwano enye.

Ukwala ukubetha

Kwaye kwakhona saba nezisombululo ezibini. Okokuqala: masikuyeke ngokupheleleyo ukwahlula kwiibhetshi kwaye sidlulise idatha kwintengiselwano enye. Inzuzo yesi sisombululo yayilula - utshintsho olufunekayo lwekhowudi lwaluncinci (ngendlela, kwiinguqulelo ezindala pg_reorg zisebenze kanye ngolo hlobo). Kodwa kukho ingxaki - sidala ukuthengiselana okude, kwaye oku, njengoko bekutshiwo ngaphambili, kuyingozi ekuveleni kwe-bloat entsha.

Isisombululo sesibini sinzima ngakumbi, kodwa mhlawumbi sichanekile ngakumbi: yenza ikholomu kwitafile yelog kunye nesazisi sentengiselwano eyongeze idatha kwitafile. Emva koko, xa sikopa idatha, sinokuyidibanisa ngolu phawu kwaye siqinisekise ukuba utshintsho olunxulumeneyo lugqithiselwa kunye. Ibhetshi iya kwenziwa kwiintengiselwano ezininzi (okanye enye enkulu) kwaye ubungakanani bayo buya kuhluka ngokuxhomekeka ekubeni ingakanani idatha etshintshiweyo kwezi ntengiselwano. Kubalulekile ukuba uqaphele ukuba ekubeni idatha evela kwiintengiselwano ezahlukeneyo ingena kwitafile yelogi ngokulandelelana okungahleliwe, akusayi kuphinda kukwazi ukuyifunda ngokulandelelana, njengoko kwakunjalo ngaphambili. seqscan kwisicelo ngasinye ngokucoca nge tx_id kubiza kakhulu, isalathiso siyafuneka, kodwa iyakucothisa indlela ngenxa yokungaphezulu kokuyihlaziya. Ngokubanzi, njengesiqhelo, kufuneka uncame into ethile.

Ngoko, sagqiba ekubeni siqale ngenketho yokuqala, njengoko ilula. Okokuqala, bekufuneka kuqondwe ukuba ingaba intengiselwano ende ingaba yingxaki yokwenyani. Ekubeni ukuhanjiswa okuphambili kwedatha ukusuka kwitheyibhile endala ukuya kwentsha kwenzeka kwintengiselwano enye ende, umbuzo uguqulelwe ekubeni "siya kwandisa kangakanani le ntengiselwano?" Ubude bentengiselwano yokuqala buxhomekeke ikakhulu kubungakanani betafile. Ubude bexesha elitsha kuxhomekeke ekubeni zingaphi iinguqu eziqokelelwa kwitafile ngexesha lokudluliselwa kwedatha, okt. kubunzima bomthwalo. I-pg_repack run yenzeke ngexesha lomthwalo omncinci wenkonzo, kwaye umthamo wotshintsho wawuncinci ngokulinganayo xa kuthelekiswa nobukhulu bokuqala betafile. Sagqiba kwelokuba singalihoya ixesha lentengiselwano entsha (ukuthelekisa, ngokomyinge yiyure eyi-1 kunye nemizuzu emi-2-3).

Imifuniselo yayilungile. Qalisa kwimveliso kwakhona. Ukucaca, nanku umfanekiso onobungakanani bomnye wedatabase emva kokubaleka:

Ekubeni sanelisekile ngokupheleleyo ngesi sisombululo, asizange sizame ukuphumeza enye yesibini, kodwa siqwalasela ithuba lokuxoxa ngayo nabaphuhlisi bokwandisa. Uhlaziyo lwethu lwangoku, ngelishwa, alukakulungeli ukupapashwa, ekubeni sisombulule ingxaki kuphela ngezithintelo ezihlehliweyo ezikhethekileyo, kwaye isiqwenga esipheleleyo siyimfuneko ukubonelela ngenkxaso kwezinye iindidi. Sinethemba lokuba siya kukwazi ukwenza oku kwixesha elizayo.

Mhlawumbi unombuzo, kutheni siye sabandakanyeka kweli bali ngokuguqulwa kwe-pg_repack, kwaye asizange, umzekelo, sisebenzise ii-analogues zayo? Ngexesha elithile siphinde sacinga ngale nto, kodwa amava afanelekileyo okusebenzisa ngaphambili, kwiitafile ngaphandle kwemiqobo emisiweyo, asishukumisele ukuba sizame ukuqonda undoqo wengxaki kwaye siyilungise. Ukongeza, ukusebenzisa ezinye izisombululo kufuna ixesha lokuqhuba iimvavanyo, ngoko ke sagqiba kwelokuba sizame kuqala ukulungisa ingxaki kuyo, kwaye ukuba sibonile ukuba asikwazi ukwenza oku ngexesha elifanelekileyo, siya kuqala ukujonga ii-analogues. .

ezifunyanisiweyo

Yintoni esinokuyicebisa ngokusekelwe kumava ethu:

1. Beka iliso kwi-bloat yakho. Ngokusekwe kwidatha yokubeka iliso, unokuqonda ukuba i-autovacuum iqwalaselwe kakuhle kangakanani.
2. Lungisa i-AUTOVACUUM ukuze ugcine ukudumba kwinqanaba elamkelekileyo.
3. Ukuba i-bloat isakhula kwaye awukwazi ukuyinqoba usebenzisa izixhobo ezingaphandle kwebhokisi, ungesabi ukusebenzisa izandiso zangaphandle. Into ephambili kukuvavanya yonke into kakuhle.
4. Musa ukoyika ukulungisa izisombululo zangaphandle ukuze zihambelane neemfuno zakho - ngamanye amaxesha oku kunokusebenza ngakumbi kwaye kube lula kunokutshintsha ikhowudi yakho.

umthombo: www.habr.com