🥇Postgres: bloat, pg_repack uye deferred contraints

Mhedzisiro yekuputika pamatafura uye indexes inozivikanwa zvakanyanya uye haipo chete muPostgres. Pane nzira dzekugadzirisa nayo kunze kwebhokisi, seVACUUM FULL kana CLUSTER, asi inokiya matafura panguva yekushanda uye naizvozvo haigoni kushandiswa nguva dzose.

Chinyorwa chacho chinenge chine dzidziso shoma pamusoro pekuti bloat inoitika sei, kuti ungarwisa sei, nezve zvipingamupinyi zvakadzoserwa uye matambudziko avanounza mukushandiswa kwepg_repack yekuwedzera.

Ichi chinyorwa chakanyorwa zvichibva pa kutaura kwangu paPgConf.Russia 2020.

Sei bloat ichiitika?

Postgres yakavakirwa pamhando dzakasiyana-siyana (MVCC) Chinokosha chayo ndechekuti mutsara wega wega mutafura unogona kuva neshanduro dzakati wandei, nepo kutengeserana kusingaone kupfuura imwe yeidzi shanduro, asi kwete hazvo yakafanana. Izvi zvinobvumira kuti matransaction akawanda ashande panguva imwe chete uye haana kana chaanokanganisa pane chimwe nechimwe.

Zviripachena, ese aya mavhezheni anoda kuchengetwa. Postgres inoshanda nememory peji nepeji uye peji ndiyo ishoma huwandu hwe data inogona kuverengwa kubva kudhisiki kana kunyorwa. Ngatitarisei muenzaniso mudiki kuti tinzwisise kuti izvi zvinoitika sei.

Ngatitii tine tafura yatakawedzera marekodhi akawanda. Itsva data yakaonekwa pane yekutanga peji refaira panochengetwa tafura. Aya ndiwo mavhezheni emitsara anowanikwa kune mamwe matransaction mushure mekuita (kuti zvive nyore, isu tichafunga kuti iyo yekuzviparadzanisa nhanho ndeye Read Committed).

Takazogadziridza chimwe chezvinyorwa, nokudaro tichimaka iyo yekare vhezheni seisina basa.

Nhanho nhanho, kuvandudza uye kudzima mitsara shanduro, takaguma nepeji umo inenge hafu yedata iri "marara". Iyi data haionekwe kune chero kutengeserana.

Postgres ine michina VACUUM, iyo inochenesa shanduro dzechinyakare uye inoita nzvimbo yedata idzva. Asi kana isina kurongedzerwa zvine hukasha kana yakabatikana kushanda mune mamwe matafura, ipapo "marara data" inosara, uye isu tinofanirwa kushandisa mamwe mapeji kune data nyowani.

Saka mumuenzaniso wedu, pane imwe nguva tafura ichange iine mapeji mana, asi hafu chete yayo ichange iine data mhenyu. Nekuda kweizvozvo, kana tichipinda patafura, tinoverenga data rakawanda kupfuura zvinodiwa.

Kunyangwe kana VACUUM ikozvino ikadzima ese asina basa mutsara shanduro, mamiriro acho haagone kuita zvakanaka. Tichava nenzvimbo yemahara mumapeji kana kunyangwe mapeji ese emitsara mitsva, asi isu tichave tichiverenga data rakawanda kupfuura zvinodiwa.
Nenzira, kana peji isina zvachose (yechipiri mumuenzaniso wedu) yaiva pamagumo efaira, ipapo VACUUM yaizokwanisa kuidzikisa. Asi iye zvino ari pakati, saka hapana chingaitwa naye.

Kana nhamba yemapeji akadaro asina chinhu kana akawandisa akakura, anonzi bloat, anotanga kukanganisa kushanda.

Zvese zvinotsanangurwa pamusoro apa ma mechanics ekuitika kwe bloat mumatafura. Mune indexes izvi zvinoitika nenzira imwecheteyo.

Ndine bloat here?

Pane nzira dzakawanda dzekuona kana uine bloat. Pfungwa yekutanga ndeyekushandisa iyo yemukati Postgres nhamba, iyo ine ruzivo rwakakwana nezvehuwandu hwemitsara mumatafura, nhamba ye "kurarama" mitsara, nezvimwe. Takatora sehwaro script kubva kuPostgreSQL Nyanzvi, iyo inogona kuongorora bloat matafura pamwe netositi uye bloat btree indexes. Muchiitiko chedu, kukanganisa kwayo ndeye 10-20%.

Imwe nzira ndeye kushandisa kuwedzera pgstattuple, iyo inokutendera kuti utarise mukati memapeji uye uwane zvese zvinofungidzirwa uye chaiyo bloat kukosha. Asi mune yechipiri kesi, iwe uchafanirwa kuongorora tafura yese.

Isu tinofunga diki bloat kukosha, kusvika ku20%, inogamuchirwa. Inogona kutorwa seanalogue ye fillfactor ye tafura и indices. Pa50% uye pamusoro, matambudziko ekuita anogona kutanga.

Nzira dzekurwisa bloat

Postgres ine nzira dzinoverengeka dzekugadzirisa bloat kunze kwebhokisi, asi haisi nguva dzose yakakodzera munhu wese.

Gadzirisa AUTOVACUUM kuitira kuti bloat irege kuitika. Kana kuti zvakanyanya, kuichengeta iri pamwero unogamuchirika kwauri. Izvi zvinoratidzika sezano re "kaputeni", asi muchokwadi izvi hazvisi nyore nguva dzose kuwana. Semuyenzaniso, iwe une kushingaira kusimudzira neanogara achichinja kune data schema, kana imwe mhando yekufambisa data iri kuitika. Nekuda kweizvozvo, yako mutoro profile inogona kuchinja kazhinji uye inosiyana kubva patafura netafura. Izvi zvinoreva kuti iwe unofanirwa kugara uchishanda zvishoma kumberi uye kugadzirisa AUTOVACUUM kune inochinja chimiro chetafura yega yega. Asi zviri pachena kuti izvi hazvisi nyore kuita.

Chimwe chikonzero chakajairika nei AUTOVACUUM isingakwanise kuenderana nematafura imhaka yekuti kune kutengeserana kwenguva refu kunoidzivirira kubva pakuchenesa iyo data inowanikwa kune izvo zvekutengesa. Kurudziro pano iri pachena zvakare - bvisa "kurembera" kutengeserana uye kuderedza nguva yekushanda inoshanda. Asi kana mutoro uri pachikumbiro chako uri musanganiswa weOLAP neOLTP, saka iwe unogona panguva imwe chete kuva neakawanda anogarogadziridzwa uye mibvunzo mipfupi, pamwe nekushanda kwenguva refu - semuenzaniso, kuvaka rondedzero. Mumamiriro ezvinhu akadaro, zvakakodzera kufunga pamusoro pekuparadzira mutoro kune zvakasiyana-siyana, izvo zvinobvumira kugadzirisa kwakanyatsonaka kweumwe neumwe wavo.

Mumwe muenzaniso - kunyange kana chimiro chiri homogeneous, asi dhatabhesi iri pasi pemutoro wakanyanya, saka kunyange iyo AUTOVACUUM ine hutsinye haigone kurarama, uye bloat ichaitika. Kuyera (kudzika kana kuchinjika) ndiyo yega mhinduro.

Chii chaunofanira kuita mumamiriro ezvinhu apo iwe wakamisa AUTOVACUUM, asi bloat inoramba ichikura.

chikwata VACUUM FULL inovaka zvakare zviri mukati mematafura uye indexes uye inosiya chete yakakosha data mazviri. Kubvisa bloat, inoshanda zvakakwana, asi panguva yekuitwa kwayo kukiya kwakasarudzika patafura kunotorwa (AccessExclusiveLock), iyo isingatenderi kuita mibvunzo patafura iyi, kunyange inosarudza. Kana iwe uchikwanisa kumisa sevhisi yako kana chikamu chayo kwenguva yakati (kubva pamakumi emaminitsi kusvika kumaawa akati wandei zvichienderana nehukuru hwe database uye hardware yako), saka iyi sarudzo ndiyo yakanyanya kunaka. Zvinosuruvarisa, isu hatina nguva yekumhanya VACUUM FULL panguva yekugadzirisa kwakarongwa, saka iyi nzira haina kukodzera kwatiri.

chikwata KUSVIRA Inovakazve zviri mukati mematafura nenzira imwecheteyo seVACUUM FULL, asi inokubvumira kuti utaure index zvichienderana nekuti iyo data ichave yakarongedzerwa mumuviri pa diski (asi mune ramangwana kurongeka hakuna kuvimbiswa kune mitsva mitsva). Mune mamwe mamiriro, iyi yakanaka optimization yehuwandu hwemibvunzo - nekuverenga akawanda marekodhi neindex. Kukanganisa kwemurairo kwakafanana neiyo yeVACUUM FULL - inovhara tafura panguva yekushanda.

chikwata REINDEX zvakafanana neaya maviri apfuura, asi inovakazve imwe index kana ese ma indexes etafura. Makiyi haana kusimba zvishoma: ShareLock patafura (inodzivirira kugadziridzwa, asi inobvumira sarudza) uye AccessExclusiveLock pane index iri kuvakwa patsva (inovhara mibvunzo uchishandisa iyi index). Nekudaro, muiyo 12th vhezheni yePostgres parameter yakaonekwa PANGUVA DZOSE, izvo zvinokutendera kuti uvakezve index pasina kuvharira pamwe chete kuwedzera, kugadziridza, kana kudzima marekodhi.

Muzvinyorwa zvekare zvePostgres, unogona kuwana mhedzisiro yakafanana neREINDEX CONCURRENTLY uchishandisa GADZIRA INDEX PAMWE chete. Zvinokutendera kuti ugadzire index isina kukiyiwa zvakasimba (ShareUpdateExclusiveLock, iyo isingakanganise mibvunzo yakafanana), wobva watsiva iyo yekare index neitsva uye wodzima iyo yekare index. Izvi zvinokutendera kuti ubvise index bloat pasina kukanganisa application yako. Zvakakosha kufunga kuti pakuvakazve indexes pachava nekuwedzera mutoro pane disk subsystem.

Saka, kana kune indekisi pane nzira dzekubvisa bloat "pane nhunzi," saka hapana yematafura. Apa ndipo panouya akasiyana ekunze ekuwedzera: pg_repack (yaimbova pg_reorg), pgcompact, pgcompacttable nevamwe. Muchikamu chino, ini handisi kuzozvienzanisa uye ndichataura chete nezve pg_repack, iyo, mushure meimwe shanduko, isu tinoshandisa isu pachedu.

Iyo pg_repack inoshanda sei

Ngatiti isu tine tafura yakajairika - ine indexes, zvirambidzo uye, zvinosuruvarisa, ine bloat. Nhanho yekutanga ye pg_repack ndeyekugadzira tafura yelogi kuchengetedza data nezve shanduko dzese ichiri kushanda. Iyo inokonzeresa inodzokorora shanduko idzi kune yega yega, kugadzirisa uye kudzima. Zvadaro tafura inogadzirwa, yakafanana neyepakutanga mukugadzirisa, asi pasina indexes uye zvirambidzo, kuitira kuti usanonoke nzira yekuisa data.

Tevere, pg_repack inotamisa iyo data kubva patafura yekare kuenda kutafura nyowani, ichisefa otomatiki mitsetse yese isina basa, uyezve inogadzira indexes yetafura nyowani. Panguva yekuitwa kwese kuita izvi, shanduko dzinoungana mutafura yelogi.

Nhanho inotevera ndeyekuendesa shanduko kutafura itsva. Kutama kunoitwa pamusoro pekudzokororwa kwakati wandei, uye kana paine zvishoma pane 20 zvinyorwa zvasara murogi tafura, pg_repack inowana kukiya yakasimba, inotamisa data yazvino, uye inotsiva tafura yekare neitsva mumatafura ePostgres system. Iyi ndiyo chete uye nguva pfupi yausingazokwanise kushanda netafura. Mushure meizvi, tafura yekare uye tafura ine matanda zvinobviswa uye nzvimbo inosunungurwa mufaira system. Nzira yacho yakakwana.

Zvese zvinotaridzika zvakanaka mudzidziso, asi chii chinoitika mukuita? Takaedza pg_repack isina mutoro uye pasi pemutoro, uye takatarisa kushanda kwayo kana nguva isati yasvika (nemamwe mazwi, uchishandisa Ctrl + C). All tests were positive.

Takaenda kuchitoro chekudya - uyezve zvese hazvina kufamba sezvataitarisira.

Kutanga pancake kutengeswa

Paboka rekutanga takagamuchira chikanganiso pamusoro pekutyorwa kwechisungo chakasiyana:

$ ./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.

Ichi chinogumira chaive neato-yakagadzirwa zita index_16508 - yakagadzirwa na pg_repack. Zvichienderana nehunhu hunosanganisirwa mukuumbwa kwayo, isu takasarudza "yedu" chinomanikidza chinoenderana nacho. Dambudziko rakazove rekuti ichi hachisi chigadziro chakajairwa, asi chakamisikidzwa (deferred constraint), i.e. kusimbiswa kwayo kunoitwa gare gare kupfuura murairo we sql, izvo zvinotungamirira kumigumisiro isingatarisirwi.

Deferred constraints: nei ichidikanwa uye kuti inoshanda sei

Dzidziso shoma pamusoro pezvirambidzo zvakaverengerwa.
Ngatitarisei muenzaniso wakapfava: tine tafura-referensi bhuku remotokari dzine hunhu huviri - zita uye kurongeka kwemota mudhairekitori.

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

Ngatitii taida kuchinjanisa mota yekutanga neyechipiri. Mhinduro yakatwasuka ndeyekuvandudza kukosha kwekutanga kune yechipiri, uye yechipiri kune yekutanga:

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

Asi kana isu tichimhanya iyi kodhi, isu tinotarisira kutyorwa kwezvisungo nekuti kurongeka kwehunhu huri patafura hwakasiyana:

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

Ndingazviita sei zvakasiyana? Sarudzo yekutanga: wedzera imwe kukosha kutsiva kune odha yakavimbiswa kusavapo patafura, semuenzaniso "-1". Muchirongwa, izvi zvinodaidzwa kuti "kuchinjana hunhu hwemhando mbiri kuburikidza neyechitatu." Iyo chete drawback yeiyi nzira ndeye yekuwedzera update.

Sarudzo yechipiri: Gadzirisa tafura kuti ushandise iyo inoyangarara point data mhando yehukoshi hwekurongeka panzvimbo yehuwandu. Zvadaro, kana uchivandudza kukosha kubva pa1, semuenzaniso, kusvika ku2.5, kupinda kwekutanga kuchangoerekana "kumira" pakati pechipiri nechitatu. Iyi mhinduro inoshanda, asi pane zvipingamupinyi zviviri. Kutanga, hazvishande kwauri kana kukosha kwakashandiswa kumwe kunhu mune interface. Chechipiri, zvichienderana nekurongeka kwerudzi rwe data, iwe uchave uine yakaganhurwa nhamba yezvinogona kuiswa usati waverengera kukosha kwese marekodhi.

Sarudzo yechitatu: ita kuti chibvumirano chidzoserwe kuitira kuti chitariswe chete panguva yekuzvipira:

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

Sezvo pfungwa yechikumbiro chedu chekutanga ichivimbisa kuti hunhu hwese hwakasiyana panguva yekuzvipira, huchabudirira.

Muenzaniso wakurukurwa pamusoro apa, hongu, wakanyatsogadzirwa, asi unoburitsa pfungwa. Mukushandisa kwedu, isu tinoshandisa zvakamisikidzwa zvipingaidzo kuti tiise pfungwa ine basa rekugadzirisa kusawirirana apo vashandisi vanoshanda panguva imwe chete nezvinhu zvakagovaniswa zvewijeti pabhodhi. Kushandisa zvirambidzo zvakadaro kunotibvumira kuita kuti kodhi yekushandisa iite nyore.

Kazhinji, zvichienderana nerudzi rwekumanikidza, Postgres ine mazinga matatu e granularity yekuatarisa: mutsara, kutengeserana, uye mazinga ekutaura.

Source: begriffs

CHECK uye KWETE NULL inogara yakatariswa padanho remutsara; kune zvimwe zvirambidzo, sezvazvinoonekwa kubva patafura, kune akasiyana sarudzo. Unogona kuverenga zvakawanda pano.

Kupfupisa muchidimbu, zvipingamupinyi zvakamisikidzwa mumamiriro akati wandei zvinopa kodhi inoverengeka uye mirairo mishoma. Nekudaro, iwe unofanirwa kubhadhara kune izvi nekuomesera maitiro ekugadzirisa, kubva panguva iyo kukanganisa kunoitika uye nguva yaunowana nezvazvo yakaparadzaniswa nenguva. Rimwe dambudziko rinogoneka nderekuti mugadziri anogona kusagara achikwanisa kugadzira hurongwa hwakakwana kana chikumbiro chinosanganisira chinomisikidzwa.

Kuvandudzwa kwe pg_repack

Isu takavhara kuti zvirambidzo zvakamisikidzwa ndezvipi, asi zvinoenderana sei nedambudziko redu? Ngatirangarire kukanganisa kwatakagamuchira kare:

$ ./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.

Izvo zvinoitika kana data ichikopwa kubva patafura yelogi kuenda kune itsva tafura. Izvi zvinotaridzika zvinoshamisa nekuti... iyo data iri murogi tafura inoitwa pamwe chete neiyo data iri mune sosi tafura. Kana ivo vachigutsa zvisungo zvetafura yekutanga, vangatyora sei zvisungo zvakafanana mune itsva?

Sezvazvinoitika, mudzi wedambudziko uri munhanho yapfuura ye pg_repack, iyo inogadzira chete indexes, asi kwete zvipingamupinyi: tafura yekare yakanga ine chisungo chakasiyana, uye itsva yakagadzira index yakasiyana.

Izvo zvakakosha kuti uzive pano kuti kana iyo inomanikidza yakajairika uye isina kudzoserwa, saka iyo yakasarudzika index yakagadzirwa panzvimbo yakaenzana neichi chinomanikidza, nekuti. Zvipingamupinyi zvakasarudzika muPostgres zvinoshandiswa nekugadzira yakasarudzika index. Asi kana iri nyaya yekumisikidzwa kwakamisikidzwa, maitiro haana kufanana, nekuti index haigone kuverengerwa uye inogara ichitariswa panguva iyo sql command inoitwa.

Nokudaro, chinangwa chechinetso chiri mu "kunonoka" kwecheki: mutafura yepakutanga inoitika panguva yekuzvipira, uye mutafura itsva panguva iyo murairo we sql unoitwa. Izvi zvinoreva kuti isu tinofanirwa kuve nechokwadi chekuti cheki dzinoitwa zvakafanana mune ese ari maviri kesi: ingave inononoka nguva dzose, kana nguva dzose nekukasira.

Saka taive nepfungwa dzipi?

Gadzira index yakafanana neyakaverengerwa

Pfungwa yekutanga ndeyekuita ese macheki mune yekukurumidza modhi. Izvi zvinogona kuburitsa zvirambidzo zvakati wandei zvenhema, asi kana paine zvishoma zvacho, izvi hazvifanirwe kukanganisa basa revashandisi, nekuti kusawirirana kwakadaro kwakajairwa kwavari. Izvo zvinoitika, semuenzaniso, apo vashandisi vaviri vanotanga kugadzirisa iyo widget panguva imwe chete, uye mutengi wemushandisi wechipiri haana nguva yekugamuchira ruzivo rwekuti widget yakatovharwa kuti igadziriswe nemushandisi wekutanga. Mumamiriro ezvinhu akadaro, sevha inoramba mushandisi wechipiri, uye mutengi wayo anodzosera kumashure shanduko uye anovhara iyo widget. Kwapera kanguva, kana mushandisi wekutanga apedza kugadzirisa, wechipiri anogashira ruzivo rwekuti widget haichavharwi uye ichakwanisa kudzokorora chiito chavo.

Kuti tive nechokwadi chekuti cheki dzinogara dziri mumodhi isina kudzoserwa, isu takagadzira index nyowani yakafanana neyekutanga deferred constrict:

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

Munzvimbo yekuyedza, takangogamuchira zvikanganiso zvishoma zvaitarisirwa. Success! Takamhanya pg_repack zvakare pakugadzira uye takawana zvikanganiso zvishanu pane yekutanga cluster muawa yebasa. Ichi chigumisiro chinogamuchirwa. Nekudaro, patova pachikwata chechipiri nhamba yezvikanganiso yakawedzera zvakanyanya uye taifanira kumira pg_repack.

Sei zvakaitika? Iko mukana wekukanganisa kunoitika zvinoenderana nekuti vangani vashandisi vari kushanda nemajeti akafanana panguva imwe chete. Sezviri pachena, panguva iyoyo pakanga paine shanduko shoma shoma dzekukwikwidza nedata rakachengetwa pachikwata chekutanga pane kune mamwe, i.e. takangoti "lucky".

Pfungwa iyi haina kushanda. Panguva iyoyo, takaona dzimwe mhinduro mbiri: nyorazve kodhi yedu yekushandisa kuti iparadze zvipingamupinyi zvakamisikidzwa, kana "dzidzisa" pg_repack kushanda navo. Takasarudza wechipiri.

Tsiva indexes mutafura itsva nezvimisikidzo zvakamisikidzwa kubva patafura yekutanga

Chinangwa chekudzokorora chaive pachena - kana tafura yepakutanga iine chisungo chakadzoserwa, saka kune chitsva iwe unofanirwa kugadzira chisungo chakadaro, uye kwete index.

Kuedza shanduko dzedu, takanyora bvunzo yakapusa:

tafura ine chisungo chakamisikidzwa uye rekodhi imwe;
isa data muloop inopesana nerekodhi iripo;
ita update - iyo data haisisiri kupokana;
ita shanduko.

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;

Iyo yepakutanga vhezheni ye pg_repack yaigara ichirovera pane yekutanga kuisa, iyo yakagadziridzwa vhezheni yakashanda isina zvikanganiso. Hukuru.

Isu tinoenda kunogadzira uye zvakare tinowana chikanganiso pachikamu chimwe chete chekukopa data kubva patafura yelogi kuenda kune itsva:

$ ./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.

Classic mamiriro: zvese zvinoshanda munzvimbo dzekuyedza, asi kwete mukugadzira?!

APPLY_COUNT pamwe nekusangana kwemabhechi maviri

Takatanga kuongorora kodhi chaiyo mutsara nemutsara uye takawana chinhu chakakosha: data inotamiswa kubva patafura yelogi kuenda kune imwe nyowani mumabhechi, iyo APPLY_COUNT inogara yakaratidza saizi yebhechi:

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

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

Dambudziko nderekuti iyo data kubva kune yepakutanga kutengeserana, umo akati wandei mashandiro anogona kukanganisa chisungo, kana aendeswa, anogona kupedzisira asvika panosangana mabheti maviri - hafu yemirairo ichaitwa mubatch yekutanga, uye imwe hafu. mune yechipiri. Uye pano, zvichienderana nerombo rako: kana zvikwata zvisingatyori chero chinhu mubatch yekutanga, saka zvese zvakanaka, asi kana zvikadaro, kukanganisa kunoitika.

APPLY_COUNT yakaenzana ne1000 zvinyorwa, izvo zvinotsanangura kuti sei bvunzo dzedu dzakabudirira - hadzina kubata nyaya ye "batch junction". Isu takashandisa mirairo miviri - pinza uye gadziridza, saka chaizvo mazana mashanu ekutengeserana emirairo miviri aigara akaiswa mubatch uye isu hatina kusangana nematambudziko. Mushure mekuwedzera yechipiri update, dhizaini yedu yakamira kushanda:

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;

Saka, basa rinotevera nderekuita chokwadi chekuti data kubva patafura yepakutanga, iyo yakashandurwa mune imwe kutengeserana, inopera mutafura itsva zvakare mukati mekutengesa kumwe.

Kuramba kubva pakurova

Uye zvakare takava nemhinduro mbiri. Chekutanga: ngatisiyei zvachose kupatsanura mumabhechi uye kuendesa data mune imwe kutengeserana. Mukana wemhinduro iyi yaive nyore - iyo yaidiwa kodhi shanduko yaive shoma (nenzira, mushanduro dzekare pg_reorg yakashanda saizvozvo). Asi pane dambudziko - isu tiri kugadzira kutengeserana kwenguva refu, uye izvi, sezvakambotaurwa, zvinotyisidzira kubuda kwehutsva hutsva.

Yechipiri mhinduro yakanyanya kuomarara, asi pamwe yakanyanya kurongeka: gadzira mbiru murogi tafura ine identifier yekutengeserana iyo yakawedzera data patafura. Zvino, kana isu tichikopa data, tinogona kuiunganidza nehunhu uhu uye tive nechokwadi chekuti shanduko dzine hukama dzinoendeswa pamwechete. Iyo batch ichaumbwa kubva kune akati wandei kutengeserana (kana imwe hombe) uye saizi yaro ichasiyana zvichienderana nekuti yakawanda sei data yakashandurwa mune izvi kutengeserana. Zvakakosha kuziva kuti sezvo data kubva kune zvakasiyana-siyana zvekutengeserana inopinda patafura yerogi mune imwe nzira, hazvichakwanisi kuiverenga sequentially, sezvazvaive kare. seqscan yechikumbiro chega chega nekusefa ne tx_id inodhura zvakanyanya, index inodiwa, asi zvakare inononoka nzira nekuda kwepamusoro pekugadzirisa. Kazhinji, senguva dzose, iwe unofanirwa kupa chimwe chinhu.

Saka, takasarudza kutanga neyokutanga sarudzo, sezvo iri nyore. Kutanga, zvaive zvakakodzera kunzwisisa kana kutengeserana kwenguva refu kwaizova dambudziko chairo. Sezvo kuendeswa kukuru kwe data kubva patafura yekare kuenda kune itsva kunoitikawo mune imwe kutengeserana kwenguva refu, mubvunzo unoshandurwa kuva "tichawedzera sei kutengeserana uku?" Nguva yekutengeserana kwekutanga inoenderana zvakanyanya nehukuru hwetafura. Nguva yehutsva hunoenderana nekuti ingani shanduko dzinounganidza mutafura panguva yekuendesa data, i.e. pakusimba kwemutoro. Iyo pg_repack yekumhanya yakaitika panguva yekushomeka kwesevhisi mutoro, uye huwandu hwekuchinja hwaive hudiki hudiki kana uchienzaniswa nehukuru hwekutanga hwetafura. Takasarudza kuti tinogona kuregeredza nguva yekutengeserana kutsva (yekuenzanisa, paavhareji i1 awa uye 2-3 maminitsi).

Kuedza kwacho kwaiva kwakanaka. Tanga pakugadzirwa zvakare. Kuti ujekeswe, heino mufananidzo une saizi yeimwe dhatabhesi mushure mekumhanya:

Sezvo isu takagutsikana zvizere nemhinduro iyi, hatina kuedza kushandisa yechipiri, asi isu tiri kufunga nezve mukana wekuikurukura nevagadziri vekuwedzera. Ongororo yedu yazvino, zvinosuruvarisa, haisati yagadzirira kudhindwa, sezvo isu takangogadzirisa dambudziko neyakagadzikiswa yakadzoserwa zvirambidzo, uye kune yakazara-yakazara chigamba zvakakosha kupa rutsigiro kune mamwe marudzi. Tinovimba kuti tichakwanisa kuita izvi mune ramangwana.

Zvichida iwe une mubvunzo, nei isu takatombopindira munyaya iyi nekugadziriswa kwepg_repack, uye hatina, semuenzaniso, kushandisa analogues? Pane imwe nguva takafungawo pamusoro peizvi, asi chiitiko chakanaka chekushandisa kare, pamatafura pasina zvipingamupinyi zvakanonoka, zvakatikurudzira kuedza kunzwisisa chinangwa chechinetso uye kuchigadzirisa. Pamusoro pezvo, kushandisa dzimwe mhinduro kunodawo nguva yekuitisa bvunzo, saka takasarudza kuti titange taedza kugadzirisa dambudziko riri mariri, uye kana tikaona kuti hatigone kuita izvi nenguva yakafanira, tobva tatanga kutarisa analogues. .

zvakawanikwa

Zvatinga kurudzira zvichibva pane zvatinosangana nazvo pachedu:

Chengetedza kuputika kwako. Zvichienderana nedatha yekutarisa, unogona kunzwisisa kuti autovacuum yakagadziriswa sei.
Rongedza AUTOVACUUM kuti irambe yakazvimba padanho rinogamuchirika.
Kana iyo bloat ichiri kukura uye iwe haugone kuikunda uchishandisa kunze-kwe-the-box maturusi, usatya kushandisa ekunze ekuwedzera. Chinhu chikuru ndechokuedza zvose zvakanaka.
Usatya kushandura mhinduro dzekunze kuti dzienderane nezvido zvako - dzimwe nguva izvi zvinogona kunyatsoshanda uye kunyange nyore pane kuchinja yako kodhi.

Source: www.habr.com

Postgres: bloat, pg_repack uye zvipingamupinyi zvakadzoserwa