🥇Postgres: bloat, pg_repack na ihe mgbochi ewepụrụ

Mmetụta nke bloat na tebụl na indexes bụ nke a maara nke ọma na ọ bụghị naanị na Postgres. Enwere ụzọ isi mesoo ya site na igbe, dị ka VACUUM FULL ma ọ bụ CLUSTER, mana ha na-emechi tebụl n'oge arụ ọrụ ya mere enweghị ike iji ya mgbe niile.

Isiokwu a ga-enwe ntakịrị tiori banyere otú bloat si eme, otu ị ga-esi lụso ya ọgụ, gbasara ihe mgbochi ndị echere na nsogbu ndị ha na-eweta na iji pg_repack ndọtị.

Edere akụkọ a dabere na okwu m na PgConf.Russia 2020.

Gịnị mere bloat ji eme?

Postgres gbadoro ụkwụ na ụdị ụdị dị iche iche (MVCC). Ihe kachasị mkpa bụ na ahịrị ọ bụla dị na tebụl nwere ike ịnwe ọtụtụ nsụgharị, ebe azụmahịa anaghị ahụ ihe karịrị otu n'ime nsụgharị ndị a, ma ọ bụchaghị otu. Nke a na-enye ohere ka ọtụtụ azụmahịa na-arụ ọrụ n'otu oge na ọ fọrọ nke nta ka ọ ghara inwe mmetụta ọ bụla na ibe ha.

N'ụzọ doro anya, a ga-echekwa ụdịdị ndị a niile. Postgres na-arụ ọrụ na ibe ebe nchekwa site na ibe yana ibe bụ ntakịrị data enwere ike ịgụ na diski ma ọ bụ dee. Ka anyị leba anya n’obere ihe atụ iji ghọta ka nke a si eme.

Ka anyị kwuo na anyị nwere tebụl nke anyị gbakwunyere ọtụtụ ndekọ na ya. Data ọhụrụ apụtala na ibe mbụ nke faịlụ ebe echekwara tebụl. Ndị a bụ ụdị ahịrị ndị dị ndụ maka azụmahịa ndị ọzọ mgbe emechara (maka ịdị mfe, anyị ga-eche na ọkwa dịpụrụ adịpụ na-agụ).

Anyị emelitere otu n'ime ndenye, si otú ahụ kaa ụdị ochie ahụ ka ọ dịghịzi mkpa.

Nzọụkwụ site na nzọụkwụ, na-emelite na ihichapụ nsụgharị ahịrị, anyị kwụsịrị na ibe nke ihe dịka ọkara nke data bụ "ihe mkpofu". Azụmahịa ọ bụla anaghị ahụ data a.

Postgres nwere usoro AHỤ, nke na-ehichapụ nsụgharị ochie ma na-enye ohere maka data ọhụrụ. Ma ọ bụrụ na ahazighị ya nke ọma ma ọ bụ na-arụsi ọrụ ike na-arụ ọrụ na tebụl ndị ọzọ, mgbe ahụ "data mkpofu" na-anọgide, anyị ga-eji ibe ndị ọzọ maka data ọhụrụ.

Yabụ na ihe atụ anyị, n'oge ụfọdụ, tebụl ga-enwe peeji anọ, mana naanị ọkara n'ime ya ga-enwe data dị ndụ. N'ihi ya, mgbe ị na-abanye na tebụl, anyị ga-agụ ọtụtụ data karịa ka ọ dị mkpa.

Ọbụlagodi na VACUUM na-ehichapụ ụdị ahịrị niile na-adịghị mkpa, ọnọdụ ahụ agaghị adị mma nke ukwuu. Anyị ga-enwe ohere efu na ibe ma ọ bụ ọbụna ibe niile maka ahịrị ọhụrụ, mana anyị ka ga na-agụ data karịa ka ọ dị mkpa.
Site n'ụzọ, ọ bụrụ na ibe zuru ezu (nke abụọ na ihe atụ anyị) dị na njedebe nke faịlụ ahụ, mgbe ahụ VACUUM ga-enwe ike ibelata ya. Ma ugbu a, ọ nọ n'etiti, n'ihi ya, ọ dịghị ihe a ga-eme ya.

Mgbe ọnụ ọgụgụ nke ndị dị otú ahụ efu ma ọ bụ nke ukwuu na-adịghị na peeji nke na-aghọ nnukwu, nke a na-akpọ bloat, ọ na-amalite imetụta arụmọrụ.

Ihe niile a kọwara n'elu bụ usoro ihe omume nke bloat na tebụl. Na ndeksi, nke a na-eme n'otu ụzọ ahụ.

Enwere m afọ ntachi?

Enwere ọtụtụ ụzọ iji chọpụta ma ị nwere afọ ntachi. Echiche nke mbụ bụ iji ọnụ ọgụgụ Postgres dị n'ime, nke nwere ozi gbasara ọnụ ọgụgụ nke ahịrị na tebụl, ọnụ ọgụgụ nke ahịrị "ndụ", wdg. Ị nwere ike ịchọta ọtụtụ ọdịiche nke ederede dị njikere na Ịntanetị. Anyị weere dị ka ndabere edemede site na ndị ọkachamara PostgreSQL, nke nwere ike nyochaa tebụl bloat yana toast na btree indexes. Na ahụmịhe anyị, njehie ya bụ 10-20%.

Ụzọ ọzọ bụ iji ndọtị ahụ pgstattuple, nke na-enye gị ohere ileba anya n'ime ibe ma nweta ma atụmatụ atụmatụ na kpọmkwem ọnụ ahịa bloat. Ma n'okwu nke abụọ, ị ga-enyocha tebụl dum.

Anyị na-atụle obere ọnụ ọnụ bloat, ruo 20%, anabatara. Enwere ike iwere ya dị ka ihe analog nke fillfactor maka tebụl и index. Na 50% na karịa, nsogbu arụmọrụ nwere ike ịmalite.

Ụzọ ịlụ ọgụ bloat

Postgres nwere ọtụtụ ụzọ isi mesoo bloat n'ime igbe ahụ, mana ha anaghị adabara onye ọ bụla.

Hazie AUTOVACUUM ka afọ ntachi ghara ime. Ma ọ bụ karịa kpọmkwem, idowe ya n'ọkwa dị gị mma. Nke a dị ka ndụmọdụ "onyeisi", ma n'eziokwu nke a anaghị adị mfe mgbe niile. Dịka ọmụmaatụ, ị nwere mmepe na-arụ ọrụ yana mgbanwe mgbe niile na atụmatụ data, ma ọ bụ ụdị mbugharị data na-ewere ọnọdụ. N'ihi ya, profaịlụ ibu gị nwere ike na-agbanwe ugboro ugboro ma ọ ga-adịgasị iche site na tebụl ruo na tebụl. Nke a pụtara na ị ga-achọ ka ị na-arụ ọrụ obere oge n'ihu wee mezie AUTOVACUUM na profaịlụ na-agbanwe agbanwe nke tebụl ọ bụla. Mana o doro anya na nke a adịghị mfe ime.

Ihe ọzọ kpatara AUTOVACUUM enweghị ike ịkwado tebụl bụ n'ihi na enwere azụmahịa na-adịte aka na-egbochi ya ihichapụ data dị na azụmahịa ndị ahụ. Nkwanye ebe a bụkwa ihe doro anya - wepụ azụmahịa "na-akụda" ma belata oge azụmahịa na-arụ ọrụ. Ma ọ bụrụ na ibu dị na ngwa gị bụ ngwakọ nke OLAP na OLTP, mgbe ahụ ị nwere ike ịnwe ọtụtụ mmelite ugboro ugboro na ajụjụ dị mkpirikpi, yana ọrụ ogologo oge - dịka ọmụmaatụ, ịmepụta akụkọ. N'ọnọdụ dị otú ahụ, ọ bara uru iche echiche banyere ịgbasa ibu ahụ n'ofe dị iche iche, nke ga-enye ohere ka ọ dịkwuo mma nke ọ bụla n'ime ha.

Ihe atụ ọzọ - ọ bụrụgodị na profaịlụ ahụ dị n'otu, ma nchekwa data dị n'okpuru nnukwu ibu, mgbe ahụ ọbụna AUTOVACUUM kachasị ike nwere ike ọ gaghị anabata ya, na bloat ga-eme. Scaling ( vetikal ma ọ bụ kehoraizin) bụ naanị ngwọta.

Ihe ị ga-eme n'ọnọdụ ebe ịtọlitela AUTOVACUUM, mana bloat na-aga n'ihu na-eto eto.

otu VACUUM zuru ezu na-ewughachi ọdịnaya nke tebụl na ndeksi ma hapụ naanị data dị mkpa n'ime ha. Iji kpochapụ bloat, ọ na-arụ ọrụ nke ọma, mana n'oge a na-egbu ya, a na-ejide mkpọchi pụrụ iche na tebụl (AccessExclusiveLock), nke na-agaghị ekwe ka ịme ajụjụ na tebụl a, ọbụna họrọ. Ọ bụrụ na ị nwere ike ịkwụsị ọrụ gị ma ọ bụ akụkụ ya ruo oge ụfọdụ (site na iri nkeji ruo ọtụtụ awa dabere na nha nke nchekwa data na ngwaike gị), mgbe ahụ nhọrọ a bụ ihe kacha mma. N'ụzọ dị mwute, anyị enweghị oge iji na-agba ọsọ VACUUM FULL n'oge a na-emezi nhazi, ya mere usoro a adịghị adabara anyị.

otu Ụyọkọ Na-ewughachi ọdịnaya nke tebụl n'otu ụzọ ahụ dị ka VACUUM FULL, ma na-enye gị ohere ịkọwapụta index dị ka data a ga-esi nye iwu n'ụzọ anụ ahụ na diski (mana n'ọdịnihu anaghị ekwe nkwa maka ahịrị ọhụrụ). N'ọnọdụ ụfọdụ, nke a bụ ezigbo njikarịcha maka ọtụtụ ajụjụ - na-agụ ọtụtụ ndekọ site na index. Ọdịmma nke iwu ahụ bụ otu ihe ahụ na nke VACUUM FULL - ọ na-akpọchi tebụl n'oge ọrụ.

otu REINDEX yiri nke abụọ gara aga, mana wughachi otu ndeksi ma ọ bụ ndeksi niile nke tebụl. Mkpọchi na-esiwanye ike: ShareLock na tebụl (na-egbochi mgbanwe, mana na-enye ohere ịhọrọ) yana AccessExclusiveLock na ndeksi na-ewughachi (na-egbochi ajụjụ site na iji ndeksi a). Agbanyeghị, na ụdị nke 12 nke Postgres, parameter pụtara N'otu oge, nke na-enye gị ohere iwughachi index na-egbochighị mgbakwunye, mgbanwe ma ọ bụ ihichapụ ndekọ.

Na ụdị mbụ nke Postgres, ị nwere ike nweta nsonaazụ yiri REINDEX CONCURRENTLY iji Mepụta INDEX N'otu oge. Ọ na-enye gị ohere ịmepụta ndeksi na-enweghị mkpọchi siri ike (ShareUpdateExclusiveLock, nke na-adịghị egbochi ajụjụ ndị yiri ya), wee jiri nke ọhụrụ dochie index ochie wee hichapụ index ochie. Nke a na-enye gị ohere iwepụ index bloat na-enweghị egbochi gị ngwa. Ọ dị mkpa ịtụle na mgbe ị na-ewughachi index, a ga-enwekwu ibu na subsystem nke diski.

Ya mere, ọ bụrụ na indexes e nwere ụzọ iji kpochapụ bloat "na ofufe," mgbe ahụ ọ dịghị maka tebụl. Nke a bụ ebe ndọtị mpụga dị iche iche na-abata: pg_repack (nke bụbu pg_reorg), pgcompact, pgcompactable na ndị ọzọ. N'isiokwu a, agaghị m atụnyere ha ma na-ekwu naanị banyere pg_repack, nke, mgbe ụfọdụ mgbanwe, anyị na-eji onwe anyị.

Kedu ka pg_repack si arụ ọrụ

Ka anyị kwuo na anyị nwere tebụl nkịtị kpamkpam - na indexes, mgbochi na, ọ dị mwute ikwu, na bloat. Nzọụkwụ mbụ nke pg_repack bụ ịmepụta tebụl ndekọ iji chekwaa data gbasara mgbanwe niile mgbe ọ na-agba ọsọ. Ihe mkpalite ga-emegharị mgbanwe ndị a maka ntinye ọ bụla, melite na ihichapụ. Mgbe ahụ, a na-emepụta tebụl, dị ka nke mbụ na nhazi, ma na-enweghị indexes na mgbochi, ka ọ ghara ịdalata usoro ntinye data.

Na-esote, pg_repack na-ebufe data site na tebụl ochie gaa na tebụl ọhụrụ, na-ehichapụ ahịrị niile na-adịghị mkpa na-akpaghị aka, wee mepụta indexes maka tebụl ọhụrụ ahụ. N'oge a na-arụ ọrụ ndị a niile, mgbanwe na-agbakọta na tebụl log.

Nzọụkwụ ọzọ bụ ịnyefe mgbanwe na tebụl ọhụrụ. A na-eme njem ahụ n'elu ọtụtụ iterations, na mgbe ihe na-erughị ntinye 20 fọdụrụ na tebụl log, pg_repack na-enweta mkpọchi siri ike, na-akwaga data kachasị ọhụrụ, ma dochie tebụl ochie na nke ọhụrụ na tebụl usoro Postgres. Nke a bụ naanị na obere oge mgbe ị na-agaghị enwe ike ịrụ ọrụ na tebụl. Mgbe nke a gasịrị, a na-ehichapụ okpokoro ochie na tebụl nwere ndekọ ma hapụ ohere na usoro faịlụ. Usoro zuru ezu.

Ihe niile dị mma na tiori, ma gịnị na-eme na omume? Anyị nwalere pg_repack na-enweghị ibu yana n'okpuru ibu, wee lelee ọrụ ya ma ọ bụrụ na ọ kwụsịtụrụ (ya bụ, iji Ctrl + C). Nlele niile dị mma.

Anyị gara ụlọ ahịa nri - mgbe ahụ ihe niile emeghị ka anyị tụrụ anya ya.

Pancake mbụ na-ere

Na ụyọkọ nke mbụ anyị nwetara njehie gbasara mmebi nke mmachi pụrụ iche:

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

Mmachi a nwere aha ewepụtara akpaaka index_16508 - pg_repack mere ya. Dabere na njirimara ndị agụnyere na nhazi ya, anyị na-ekpebi ihe mgbochi "anyị" nke kwekọrọ na ya. Nsogbu a tụgharịrị bụrụ na nke a abụghị njedebe nkịtị, kama ọ bụ nke ewegharịrị (mmachi echere), i.e. A na-eme nkwenye ya ma emechaa karịa iwu sql, nke na-eduga na nsonaazụ na-atụghị anya ya.

Ihe mgbochi echere: ihe kpatara ha ji chọọ na otu ha si arụ ọrụ

Obere tiori gbasara mmachi agbagoro.
Ka anyị tụlee ihe atụ dị mfe: anyị nwere akwụkwọ ndekọ ụgbọ ala nke nwere àgwà abụọ - aha na usoro nke ụgbọ ala na ndekọ.

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

Ka anyị kwuo na anyị kwesịrị ịgbanwe ụgbọ ala nke mbụ na nke abụọ. Ihe ngwọta ziri ezi bụ imelite uru mbụ gaa na nke abụọ, na nke abụọ na nke mbụ:

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

Ma mgbe anyị na-agba ọsọ koodu a, anyị na-atụ anya mmebi iwu n'ihi na usoro nke ụkpụrụ dị na tebụl pụrụ iche:

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

Kedu ka m ga-esi mee ya dị iche? Nhọrọ nke mbụ: tinye mgbakwunye mgbakwunye uru na iwu nke ekwenyere na ọ gaghị adị na tebụl, dịka ọmụmaatụ "-1". Na mmemme, a na-akpọ nke a "ịgbanwe ụkpụrụ nke mgbanwe abụọ site na nke atọ." Naanị ihe ndọghachi azụ nke usoro a bụ mmelite ọzọ.

Nhọrọ nke abụọ: Hagharịa tebụl ka ọ jiri ụdị data na-ese n'elu ese n'elu maka uru usoro kama iji ọnụọgụgụ. Mgbe ahụ, mgbe ị na-emelite uru site na 1, dịka ọmụmaatụ, na 2.5, ntinye mbụ ga-eguzo na-akpaghị aka n'etiti nke abụọ na nke atọ. Ihe ngwọta a na-arụ ọrụ, ma enwere njedebe abụọ. Nke mbụ, ọ gaghị arụ ọrụ maka gị ma ọ bụrụ na a na-eji uru ahụ eme ihe n'ebe dị na interface. Nke abụọ, dabere na nkenke nke ụdị data, ị ga-enwe ọnụ ọgụgụ dị nta nke ntinye ga-ekwe omume tupu ị gbakọọ ụkpụrụ nke ndekọ niile.

Nhọrọ nke atọ: mee ka ihe mgbochi ahụ kwụsịtụ ka elele ya naanị n'oge ime:

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

Ebe ọ bụ na ezi uche nke arịrịọ mbụ anyị na-eme ka a mara na ụkpụrụ niile pụrụ iche n'oge a na-eme ya, ọ ga-aga nke ọma.

Ihe atụ a tụlere n'elu bụ, n'ezie, ezigbo sịntetik, ma ọ na-ekpughe echiche ahụ. N'ime ngwa anyị, anyị na-eji mmachi agbagoro iji mejuputa mgbagha nke na-ahụ maka idozi esemokwu mgbe ndị ọrụ na-arụ ọrụ n'otu oge na ihe wijetị na-ekekọrịta na bọọdụ. Iji ihe mgbochi ndị dị otú ahụ na-enye anyị ohere ime ka koodu ngwa dị ntakịrị mfe.

N'ozuzu, dabere na ụdị mmachi, Postgres nwere ọkwa atọ nke granularity maka ịlele ha: ahịrị, azụmahịa, na ọkwa nkwupụta.

isi: na-akpagbu

A na-enyocha elele na ọ bụghị NULL mgbe niile na ọkwa ahịrị; maka mgbochi ndị ọzọ, dịka enwere ike ịhụ na tebụl, enwere nhọrọ dị iche iche. Ị nwere ike ịgụkwu ebe a.

Iji chịkọta nkenke, mmachi agbagoro n'ọtụtụ ọnọdụ na-enye koodu enwere ike ịgụ karịa yana iwu ole na ole. Otú ọ dị, ị ga-akwụ ụgwọ maka nke a site n'ịgbagwoju anya usoro nhazi ahụ, ebe ọ bụ na oge njehie na-eme na oge ị chọpụtara banyere ya na-ekewa n'oge. Nsogbu ọzọ nwere ike ime bụ na onye nhazi oge nwere ike ọ gaghị enwe ike ịmepụta atụmatụ kachasị mma ma ọ bụrụ na arịrịọ ahụ gụnyere ihe mgbochi echere.

Mmelite nke pg_repack

Anyị ekpuchila ihe mgbochi ndị ewegharịrị bụ, mana olee otu ha si metụta nsogbu anyị? Ka anyị cheta njehie anyị nwetara na mbụ:

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

Ọ na-eme mgbe e depụtaghachiri data site na tebụl log gaa na tebụl ọhụrụ. Nke a dị ịtụnanya n'ihi na ... A na-etinye data na tebụl log yana data dị na tebụl isi iyi. Ọ bụrụ na ha emeju ihe mgbochi nke tebụl mbụ, olee otú ha ga-esi mebie otu ihe mgbochi na nke ọhụrụ?

Dị ka ọ na-apụta, mgbọrọgwụ nke nsogbu ahụ dị na nzọụkwụ gara aga nke pg_repack, nke na-emepụta naanị indexes, ma ọ bụghị ihe mgbochi: okpokoro ochie nwere ihe mgbochi pụrụ iche, nke ọhụrụ ahụ kere index pụrụ iche kama.

Ọ dị mkpa iburu n'uche ebe a na ọ bụrụ na ihe mgbochi ahụ bụ ihe nkịtị ma ghara ịkwụsị ya, mgbe ahụ, ntinye aha pụrụ iche nke e mepụtara kama ọ bụ ihe mgbochi a, n'ihi na A na-emejuputa mmachi pụrụ iche na Postgres site na imepụta ndeksi pụrụ iche. Ma n'ihe gbasara mmachi echere, omume ahụ abụghị otu, n'ihi na index enweghị ike ịkwụsị ma na-enyocha ya mgbe niile n'oge a na-eme iwu sql.

Ya mere, isi ihe kpatara nsogbu ahụ dị na "igbu oge" nke nlele ahụ: na tebụl mbụ ọ na-eme n'oge a na-eme ihe, na na tebụl ọhụrụ n'oge a na-eme iwu sql. Nke a pụtara na anyị kwesịrị ijide n'aka na a na-eme nlele nlele ahụ n'ọnọdụ abụọ ahụ: ma ọ bụ na-egbu oge mgbe niile, ma ọ bụ ozugbo ozugbo.

Yabụ kedu echiche anyị nwere?

Mepụta ndeksi yiri nke ewegharịrị

Echiche mbụ bụ ịme nyocha abụọ na ọnọdụ ozugbo. Nke a nwere ike ịmepụta ọtụtụ mmachi dị mma, ma ọ bụrụ na ọ dị ole na ole n'ime ha, nke a ekwesịghị imetụta ọrụ nke ndị ọrụ, ebe ọ bụ na esemokwu dị otú ahụ bụ ọnọdụ nkịtị maka ha. Ha na-eme, dịka ọmụmaatụ, mgbe ndị ọrụ abụọ malitere idezi otu wijetị n'otu oge, na onye ahịa nke onye ọrụ nke abụọ enweghị oge iji nweta ozi na egbochila wijetị maka onye ọrụ mbụ dezie. N'ọnọdụ dị otú ahụ, ihe nkesa na-ajụ onye ọrụ nke abụọ, onye ahịa ya na-atụgharịghachi mgbanwe ndị ahụ ma gbochie wijetị ahụ. Mgbe obere oge gachara, mgbe onye ọrụ mbụ dechara ndezi, nke abụọ ga-enweta ozi na egbochighị wijetị ahụ ma nwee ike ịmegharị ihe ha mere.

Iji hụ na ndenye ego na-adị mgbe niile na ọnọdụ anaghị ebugharị, anyị mepụtara ndeksi ọhụrụ nke yiri mmachi izizi ebugharị:

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

Na gburugburu ule, anyị nwetara naanị mperi ole na ole tụrụ anya. Ihe ịga nke ọma! Anyị gbara pg_repack ọzọ na mmepụta wee nweta mperi 5 na ụyọkọ mbụ n'ime elekere ọrụ. Nke a bụ nsonaazụ anabatara. Agbanyeghị, ugbua na ụyọkọ nke abụọ ọnụọgụ mperi abawanyela nke ukwuu ma anyị kwụsịrị pg_repack.

Gịnị mere o ji mee? Ohere nke njehie na-adabere na ole ndị ọrụ na-eji otu wijetị arụ ọrụ n'otu oge. N'ụzọ doro anya, n'oge ahụ enwere mgbanwe asọmpi dị ntakịrị na data echekwara na ụyọkọ mbụ karịa na ndị ọzọ, ya bụ. anyị bụ nnọọ "kechioma".

Echiche ahụ arụghị ọrụ. N'oge ahụ, anyị hụrụ ngwọta abụọ ọzọ: degharịa koodu ngwa anyị iji wepụta ihe mgbochi ndị echere, ma ọ bụ "kuzie" pg_repack ka gị na ha rụọ ọrụ. Anyị họọrọ nke abụọ.

Dochie ndenye aha na tebụl ọhụrụ site na mmachi ewegharịrị site na tebụl mbụ

Ebumnuche nke ntụgharị ahụ doro anya - ma ọ bụrụ na tebụl mbụ nwere ihe mgbochi echere, mgbe ahụ maka nke ọhụrụ ị ga-emepụta ihe mgbochi dị otú ahụ, ọ bụghị index.

Iji nwalee mgbanwe anyị, anyị dere ule dị mfe:

tebụl nwere ihe mgbochi echere na otu ndekọ;
tinye data n'ime loop nke megidere ndekọ dị adị;
mee mmelite - data anaghịzi esemokwu;
mee mgbanwe.

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;

Ụdị mbụ nke pg_repack na-adaba mgbe niile na ntinye nke mbụ, ụdị gbanwetụrụ na-arụ ọrụ na-enweghị njehie. Nnukwu.

Anyị na-aga mmepụta ma nwetakwa njehie ọzọ n'otu oge nke iṅomi data site na tebụl log gaa na nke ọhụrụ:

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

Ọnọdụ kpochapụ: ihe niile na-arụ ọrụ na gburugburu ule, ma ọ bụghị na mmepụta ?!

APPLY_COUNT na njikọ nke ogbe abụọ

Anyị malitere nyochaa koodu ahụ n'ụzọ nkịtị site na ahịrị wee chọpụta isi ihe dị mkpa: a na-ebufe data site na tebụl log gaa na nke ọhụrụ na batches, APPLY_COUNT na-egosikarị nha nke batch:

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

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

Nsogbu bụ na data sitere na azụmahịa mbụ, nke ọtụtụ ọrụ nwere ike imebi ihe mgbochi ahụ, mgbe a na-ebufe ya, nwere ike ejedebe na nkwụsị nke batches abụọ - ọkara nke iwu ga-eme na nke mbụ, na ọkara nke ọzọ. na nke abụọ. Na ebe a, dabere na chioma gị: ọ bụrụ na otu ndị ahụ adịghị emebi ihe ọ bụla na nke mbụ, mgbe ahụ, ihe niile dị mma, ma ọ bụrụ na ha emee, njehie na-eme.

APPLY_COUNT ha nhata na ndekọ 1000, nke na-akọwa ihe kpatara ule anyị ji gaa nke ọma - ha ekpuchighị okwu nke "batch junction". Anyị na-eji iwu abụọ - fanye na melite, ya mere, kpọmkwem 500 azụmahịa nke iwu abụọ na-etinye mgbe niile na batch na anyị enweghị nsogbu ọ bụla. Mgbe ịgbakwunye mmelite nke abụọ, ndezi anyị kwụsịrị ịrụ ọrụ:

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;

Ya mere, ọrụ ọzọ bụ ijide n'aka na data sitere na tebụl mbụ, nke gbanwere na otu azụmahịa, na-ejedebe na tebụl ọhụrụ nakwa n'ime otu azụmahịa.

Ọjụjụ site na batching

Na ọzọ anyị nwere abụọ ngwọta. Nke mbụ: ka anyị hapụ kpamkpam ikewa n'ime batches ma nyefee data n'otu azụmahịa. Uru nke ngwọta a bụ ịdị mfe ya - mgbanwe koodu achọrọ dị ntakịrị (n'ụzọ, na nsụgharị ochie pg_reorg rụrụ ọrụ dị ka nke ahụ). Ma enwere nsogbu - anyị na-emepụta azụmahịa na-adịte aka, na nke a, dị ka e kwuru na mbụ, bụ ihe iyi egwu na mpụta nke ọhụụ ọhụrụ.

Ngwọta nke abụọ dị mgbagwoju anya, ma eleghị anya karịa nke ọma: mepụta kọlụm na tebụl log na njirimara nke azụmahịa nke gbakwunyere data na tebụl. Mgbe ahụ, mgbe anyị detuo data, anyị nwere ike ịchịkọta ya site na njirimara a wee hụ na agbanwere mgbanwe ndị yiri ya ọnụ. A ga-emepụta ogbe ahụ site na ọtụtụ azụmahịa (ma ọ bụ otu nnukwu) na nha ya ga-adịgasị iche dabere na ego gbanwere na azụmahịa ndị a. Ọ dị mkpa iburu n'uche na ebe ọ bụ na data sitere na azụmahịa dị iche iche na-abanye na tebụl log n'usoro n'usoro, ọ gaghị enwe ike ịgụ ya n'usoro, dịka ọ dị na mbụ. seqscan maka arịrịọ ọ bụla yana nzacha site na tx_id dị oke ọnụ, achọrọ ndeksi, mana ọ ga-ebelata usoro ahụ n'ihi oke imelite ya. N'ozuzu, dị ka mgbe niile, ịkwesịrị ịchụ ihe.

Ya mere, anyị kpebiri ịmalite na nhọrọ mbụ, dịka ọ dị mfe. Nke mbụ, ọ dị mkpa ịghọta ma ogologo azụmahịa ọ ga-abụ ezigbo nsogbu. Ebe ọ bụ na isi na-ebufe data site na tebụl ochie gaa na nke ọhụrụ na-emekwa n'otu azụmahịa ogologo oge, ajụjụ a gbanwere n'ime "ego ole ka anyị ga-amụba azụmahịa a?" Ogologo oge azụmahịa nke mbụ dabere na nha nke tebụl. Ogologo oge nke ọhụrụ na-adabere n'ọtụtụ mgbanwe na-agbakọta na tebụl n'oge nnyefe data, ya bụ. na ike nke ibu. Ọsọ pg_repack mere n'oge ibu ọrụ pere mpe, na olu mgbanwe dị ntakịrị ma e jiri ya tụnyere nha mbụ nke tebụl. Anyị kpebiri na anyị nwere ike ileghara oge nke azụmahịa ọhụrụ (maka ntụnyere, na nkezi ọ bụ 1 hour na 2-3 nkeji).

Nnwale ndị ahụ dị mma. Malite na mmepụta kwa. Maka idoanya, ebe a bụ foto nwere nha nke otu ọdụ data mgbe ọ gbachara:

Ebe ọ bụ na anyị nwere afọ ojuju zuru ezu na ngwọta a, anyị agbalịghị iji mejuputa nke abụọ, ma anyị na-atụle ohere nke ikwurịta ya na ndị mmepe ndọtị. Ndozigharị anyị ugbu a, ọ dị mwute ikwu, adịbeghị njikere maka mbipụta, ebe ọ bụ na anyị edozila nsogbu ahụ na ihe mgbochi pụrụ iche na-egbochi ya, na maka nkwụsị zuru ezu ọ dị mkpa iji nye nkwado maka ụdị ndị ọzọ. Anyị nwere olileanya na anyị ga-enwe ike ime nke a n'ọdịnihu.

Ikekwe ị nwere ajụjụ, gịnị mere anyị ji tinye aka na akụkọ a site na mgbanwe nke pg_repack, ma ọ bụghị, dịka ọmụmaatụ, jiri analogues ya? N'oge ụfọdụ, anyị na-echekwa banyere nke a, ma ahụmahụ dị mma nke iji ya na mbụ, na tebụl na-enweghị ihe mgbochi ndị a na-ebugharị, kpaliri anyị ịgbalị ịghọta isi nsogbu ahụ ma dozie ya. Tụkwasị na nke a, iji ngwọta ndị ọzọ na-achọkwa oge iji na-eduzi ule, ya mere, anyị kpebiri na anyị ga-ebu ụzọ gbalịa dozie nsogbu dị na ya, ma ọ bụrụ na anyị achọpụta na anyị enweghị ike ime nke a n'oge kwesịrị ekwesị, mgbe ahụ, anyị ga-amalite ile anya na analogues. .

Nchoputa

Ihe anyị nwere ike ịkwado dabere na ahụmịhe nke anyị:

Nyochaa afọ ntachi gị. Dabere na data nleba anya, ị nwere ike ịghọta ka esi ahazi autovacuum.
Gbanwee AUTOVACUUM ka ọ bụrụ afọ ntachi n'ogo a na-anabata.
Ọ bụrụ na bloat ka na-eto eto ma ị nweghị ike imeri ya site na iji ngwaọrụ ndị na-apụ apụ, atụla egwu iji mgbatị mpụga. Isi ihe bụ ịnwale ihe niile nke ọma.
Atụla egwu ịgbanwe ihe ngwọta dị na mpụga iji kwado mkpa gị - mgbe ụfọdụ nke a nwere ike ịdị irè karị na ọbụna dị mfe karịa ịgbanwe koodu nke gị.

isi: www.habr.com

Postgres: bloat, pg_repack na mmachi ebugharị