Postgres: bloat, pg_repack an deferred Contrainten

Den Effekt vum Bloat op Dëscher an Indexen ass wäit bekannt an ass präsent net nëmmen am Postgres. Et gi Weeër mat et aus der Këscht ze këmmeren, wéi VACUUM VOLLZÄIT oder CLUSTER, mä si Spär Dëscher während Operatioun an dofir kann net ëmmer benotzt ginn.

Den Artikel enthält eng kleng Theorie iwwer wéi Bloat geschitt, wéi Dir et kämpfe kënnt, iwwer ofgeschloss Aschränkungen an d'Problemer déi se fir d'Benotzung vun der pg_repack Extensioun bréngen.

Dësen Artikel ass geschriwwen baséiert op meng Ried op PgConf.Russia 2020.

Firwat geschitt Bloat?

Postgres baséiert op engem Multi-Version Modell (MVCC). Seng Essenz ass, datt all Zeil an der Tabell e puer Versiounen hunn kann, iwwerdeems Transaktiounen gesinn net méi wéi eng vun dëse Versiounen, awer net onbedéngt déi selwecht. Dëst erlaabt e puer Transaktiounen gläichzäiteg ze schaffen an hunn quasi keen Impakt op all aner.

Natierlech mussen all dës Versioune gespäichert ginn. Postgres schafft mat Erënnerung Säit fir Säit an eng Säit ass de Mindestbetrag vun Daten, déi vun der Disk gelies oder geschriwwe kënne ginn. Loosst eis e klengt Beispill kucken fir ze verstoen wéi dëst geschitt.

Loosst eis soen, mir hunn en Dësch, un deem mir e puer Rekorder bäigefüügt hunn. Nei Donnéeën sinn op der éischter Säit vun der Datei opgetaucht wou den Dësch gespäichert ass. Dëst sinn Live Versioune vu Reihen, déi fir aner Transaktiounen no engem Engagement disponibel sinn (fir Simplicitéit wäerte mir dovun ausgoen datt d'Isolatiounsniveau Read Committed ass).

Mir hunn dann eng vun den Entréen aktualiséiert, an domat déi al Versioun als net méi relevant markéiert.

Schrëtt fir Schrëtt, d'Aktualiséierung an d'Läschen vun der Reieversioun, hu mir op eng Säit opgehalen, an där ongeféier d'Halschent vun den Donnéeën "Dreck" ass. Dës Donnéeë sinn net fir all Transaktioun sichtbar.

Postgres huet e Mechanismus VAKUUM, déi verouderte Versioune botzt a Plaz fir nei Donnéeën mécht. Awer wann et net aggressiv genuch konfiguréiert ass oder beschäftegt ass an aneren Dëscher ze schaffen, da bleift "Mülldaten" a mir mussen zousätzlech Säiten fir nei Donnéeën benotzen.

Also an eisem Beispill, iergendwann wäert den Dësch aus véier Säiten besteet, awer nëmmen d'Halschent dovun enthält Live-Daten. Als Resultat, wann Dir op den Dësch kënnt, liesen mir vill méi Daten wéi néideg.

Och wann VACUUM elo all irrelevant Rei Versiounen läscht, wäert d'Situatioun net dramatesch verbesseren. Mir wäerten fräi Plaz op Säiten oder souguer ganze Säiten fir nei Zeile hunn, mä mir wäerten nach méi Daten liesen wéi néideg.
Iwwregens, wann eng komplett eidel Säit (déi zweet an eisem Beispill) um Enn vun der Datei stoung, da kéint VACUUM se ofschneiden. Mee elo ass si an der Mëtt, sou kann näischt mat hir gemaach ginn.

Wann d'Zuel vun esou eidel oder héich spatzen Säiten grouss gëtt, déi bloat genannt gëtt, fänkt et un d'Leeschtung ze beaflossen.

Alles uewen beschriwwen ass d'Mechanik vun der Optriede vun bloat an Dëscher. An Indexen geschitt dat op vill déi selwecht Manéier.

Hunn ech bloat?

Et gi verschidde Weeër fir ze bestëmmen ob Dir Bloat hutt. D'Iddi vun der éischter ass intern Postgres Statistiken ze benotzen, déi geschätzte Informatioun iwwer d'Zuel vun de Reihen an den Dëscher, d'Zuel vun de "live" Reihen, asw. Mir hunn als Basis geholl Schrëft aus PostgreSQL Experten, déi bloat Dëscher zesumme mat Toast an bloat btree Indexen evaluéieren kann. An eiser Erfahrung ass säi Feeler 10-20%.

Eng aner Manéier ass d'Extensioun ze benotzen pgstattuple, wat Iech erlaabt Iech an de Säiten ze kucken a souwuel e geschätzte wéi och e genee Bloat-Wäert ze kréien. Awer am zweete Fall musst Dir de ganzen Dësch scannen.

Mir betruechten e klenge Bloatwäert, bis zu 20%, akzeptabel. Et kann als Analog vu Fillfaktor ugesi ginn Dëscher и Indizes. Bei 50% a méi héich kënnen d'Leeschtungsproblemer ufänken.

Weeër fir Bloat ze bekämpfen

Postgres huet verschidde Weeër fir mat Bloat aus der Këscht ze këmmeren, awer si sinn net ëmmer gëeegent fir jiddereen.

Konfiguréieren AUTOVACUUM sou datt Bloat net geschitt. Oder méi präzis, fir et op engem Niveau ze halen, deen Iech akzeptabel ass. Dëst schéngt wéi de "Kapitän" Berodung, awer a Wierklechkeet ass dëst net ëmmer einfach ze erreechen. Zum Beispill hutt Dir eng aktiv Entwécklung mat reegelméissegen Ännerungen am Dateschema, oder eng Zort Datemigratioun fënnt statt. Als Resultat kann Äre Laaschtprofil dacks änneren a wäert typesch vun Dësch zu Dësch variéieren. Dëst bedeit datt Dir stänneg e bëssen viru muss schaffen an AUTOVACUUM un de verännerleche Profil vun all Dësch ajustéieren. Awer natierlech ass dëst net einfach ze maachen.

En anere gemeinsame Grond firwat AUTOVACUUM net mat Dëscher hale kann ass well et laang lafende Transaktioune gëtt, déi et verhënneren datt d'Donnéeën opmaachen, déi fir dës Transaktioune verfügbar sinn. D'Empfehlung hei ass och offensichtlech - lass vun "dangling" Transaktiounen lass an d'Zäit vun aktive Transaktiounen minimiséieren. Awer wann d'Laascht op Är Applikatioun en Hybrid vun OLAP an OLTP ass, da kënnt Dir gläichzäiteg vill heefeg Updates a kuerz Ufroen hunn, souwéi laangfristeg Operatiounen - zum Beispill e Bericht bauen. An esou enger Situatioun ass et derwäert ze denken iwwer d'Verbreedung vun der Belaaschtung iwwer verschidde Basen, wat fir jiddereng vun hinnen méi Feintuning erlaabt.

En anert Beispill - och wann de Profil homogen ass, awer d'Datebank ass ënner enger ganz héijer Belaaschtung, da kann och den aggressivsten AUTOVACUUM net eens ginn, an e Bloat wäert optrieden. Skaléieren (vertikal oder horizontal) ass déi eenzeg Léisung.

Wat ze maachen an enger Situatioun wou Dir AUTOVACUUM ageriicht hutt, awer de Bloat wuesse weider.

Equipe VAKUUM VOLLZÄIT baut den Inhalt vun Dëscher an Indexen op a léisst nëmmen relevant Donnéeën dran. Fir bloat ze eliminéieren, funktionnéiert et perfekt, awer während senger Ausféierung gëtt en exklusive Spär op den Dësch erfaasst (AccessExclusiveLock), wat net erlaabt Ufroen op dësem Dësch auszeféieren, och wielt. Wann Dir Iech leeschte kënnt Äre Service oder en Deel dovun fir eng Zäit ze stoppen (vun Zénger vu Minutten bis e puer Stonnen ofhängeg vun der Gréisst vun der Datebank an Ärer Hardware), dann ass dës Optioun déi bescht. Leider hu mir keng Zäit fir VACUUM FULL während dem geplangten Ënnerhalt ze lafen, sou datt dës Method net fir eis gëeegent ass.

Equipe CLUSTER Rebuilds den Inhalt vun Dëscher an déi selwecht Art a Weis wéi VACUUM VOLLZÄIT, mee erlaabt Iech en Index ze uginn no deem d'Donnéeën kierperlech op Scheif bestallt ginn (awer an Zukunft ass d'Uerdnung net garantéiert fir nei Zeile). A bestëmmte Situatiounen ass dëst eng gutt Optimisatioun fir eng Zuel vu Ufroen - mat der Liesung vu multiple records no Index. Den Nodeel vum Kommando ass d'selwecht wéi dee vum VACUUM FULL - et gespaart den Dësch wärend der Operatioun.

Equipe REINDEX ähnlech wéi déi zwee virdrun, awer baut e spezifeschen Index oder all Indizes vun der Tabell op. Spären sinn liicht méi schwaach: ShareLock op den Dësch (verhënnert Ännerungen, mee erlaabt auswielen) an AccessExclusiveLock op den Index nees opgebaut (blockéiert Ufroen mat dësem Index). Wéi och ëmmer, an der 12. Versioun vum Postgres ass e Parameter opgetaucht GEMEENSCHT, wat Iech erlaabt den Index opzebauen ouni gläichzäiteg Zousatz, Ännerung oder Läschen vun Opzeechnungen ze blockéieren.

A fréiere Versioune vu Postgres kënnt Dir e Resultat erreechen ähnlech wéi REINDEX CONCURRENTLY benotzt SCHAFFT INDEX gläichzäiteg. Et erlaabt Iech en Index ouni strikt Sperrung ze kreéieren (ShareUpdateExclusiveLock, deen net mat parallelen Ufroen stéiert), ersetzt dann den alen Index mat engem neien an läscht den alen Index. Dëst erlaabt Iech Indexbloat ze eliminéieren ouni Är Applikatioun ze stéieren. Et ass wichteg ze berücksichtegen datt wann Dir Indexen nei opbaut et eng zousätzlech Belaaschtung op den Disk Subsystem gëtt.

Also, wann et fir Indizes Weeër ginn fir Bloat "op der Flucht" ze eliminéieren, da gëtt et keng fir Dëscher. Dëst ass wou verschidde extern Extensiounen an d'Spill kommen: pg_repack (fréier pg_reorg), pg kompakt, pg kompakttabel an anerer. An dësem Artikel wäert ech se net vergläichen a wäert nëmmen iwwer pg_repack schwätzen, déi, no e puer Ännerungen, mir selwer benotzen.

Wéi funktionéiert pg_repack

Loosst eis soen datt mir e ganz gewéinleche Dësch hunn - mat Indexen, Restriktiounen a leider mat Bloat. Den éischte Schrëtt vum pg_repack ass eng Logtabel ze kreéieren fir Daten iwwer all Ännerungen ze späicheren wärend se leeft. Den Ausléiser replizéiert dës Ännerungen fir all Insert, Update a Läschen. Da gëtt en Dësch erstallt, ähnlech wéi déi ursprénglech a Struktur, awer ouni Indexen a Restriktiounen, fir de Prozess vun der Aféierung vun Donnéeën net ze luesen.

Als nächst transferéiert pg_repack d'Donnéeën vun der aler Tabell op den neien Dësch, filtert automatesch all irrelevant Reihen aus, an erstellt dann Indexe fir den neien Dësch. Wärend der Ausféierung vun all dësen Operatiounen accumuléieren Ännerungen an der Logtabel.

De nächste Schrëtt ass d'Ännerungen op den neien Dësch ze transferéieren. D'Migratioun gëtt iwwer e puer Iteratiounen duerchgefouert, a wann et manner wéi 20 Entréen an der Logtabel bleiwen, kritt pg_repack e staarke Spär, migréiert déi lescht Donnéeën an ersetzt déi al Tabell mat der neier an de Postgres System Dëscher. Dëst ass déi eenzeg a ganz kuerz Zäit wou Dir net fäeg sidd mam Dësch ze schaffen. Duerno ginn déi al Dësch an den Dësch mat Logbicher geläscht a Plaz am Dateiesystem fräigelooss. De Prozess ass fäerdeg.

Alles gesäit gutt an der Theorie, awer wat geschitt an der Praxis? Mir getest pg_repack ouni Laascht an ënner Laascht, a kontrolléiert seng Operatioun am Fall vun virzäitegen Stoppen (an anere Wierder, benotzt Ctrl + C). All Tester ware positiv.

Mir sinn an de Liewensmëttelgeschäft gaang - an dunn ass alles net gaangen wéi mir et erwaart hunn.

Éischt Pancake am Verkaf

Am éischte Stärekoup hu mir e Feeler iwwer eng Violatioun vun enger eenzegaarteger Aschränkung kritt:

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

Dës Begrenzung hat en auto-generéierten Numm index_16508 - et gouf vum pg_repack erstallt. Baséierend op d'Attributer, déi a senger Zesummesetzung abegraff sinn, hu mir "eis" Aschränkung festgeluecht, déi domat entsprécht. De Problem huet sech erausgestallt datt dëst net eng ganz ordinär Limitatioun ass, mee eng ofgeschloss (ausgestallte Beschränkung), d.h. seng Verifizéierung gëtt méi spéit gemaach wéi de sql Kommando, wat zu onerwaarte Konsequenzen féiert.

Deferred Contrainten: firwat si gebraucht ginn a wéi se funktionnéieren

Eng kleng Theorie iwwer ofgeschloss Restriktiounen.
Loosst eis en einfacht Beispill betruechten: mir hunn en Dësch-Referenzbuch vun Autoen mat zwee Attributer - den Numm an d'Uerdnung vum Auto am Verzeechnes.

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

Loosst eis soen, mir hu missen déi éischt an zweet Autoen austauschen. Déi einfach Léisung ass den éischte Wäert op den zweeten ze aktualiséieren, an den zweeten op den éischten:

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

Awer wa mir dëse Code lafen, erwaarden mir eng Zwangsverletzung well d'Uerdnung vun de Wäerter an der Tabell eenzegaarteg ass:

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

Wéi kann ech et anescht maachen? Optioun eent: Füügt en zousätzleche Wäertersatz fir eng Bestellung déi garantéiert net an der Tabell existéiert, zum Beispill "-1". An der Programméierung gëtt dëst "d'Wäerter vun zwou Variabelen duerch eng Drëttel austauschen." Deen eenzegen Nodeel vun dëser Method ass den zousätzlechen Update.

Optioun zwee: Redesign den Dësch fir e Floating Point Datentyp fir den Uerderwäert ze benotzen anstatt ganz Zuelen. Dann, wann Dir de Wäert vun 1 aktualiséiert, zum Beispill, op 2.5, wäert déi éischt Entrée automatesch tëscht dem zweeten an drëtten "Stand". Dës Léisung funktionnéiert, awer et ginn zwou Aschränkungen. Als éischt funktionnéiert et net fir Iech wann de Wäert iergendwou an der Interface benotzt gëtt. Zweetens, ofhängeg vun der Präzisioun vum Datetyp, hutt Dir eng limitéiert Zuel vu méiglechen Inserts ier Dir d'Wäerter vun allen Opzeechnungen nei berechnen.

Optioun dräi: maacht d'Zwang ausgesat sou datt se nëmmen am Moment vum Engagement iwwerpréift gëtt:

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

Zënter der Logik vun eiser initialer Ufro garantéiert datt all Wäerter eenzegaarteg sinn zum Zäitpunkt vum Engagement, wäert et geléngen.

D'Beispill hei uewen diskutéiert ass natierlech ganz synthetesch, awer et weist d'Iddi op. An eiser Applikatioun benotze mir ausgestallte Contrainten fir Logik ëmzesetzen déi verantwortlech ass fir Konflikter ze léisen wann d'Benotzer gläichzäiteg mat gemeinsame Widgetobjekter um Bord schaffen. Mat esou Restriktiounen erlaabt eis den Applikatiounscode e bësse méi einfach ze maachen.

Am Allgemengen, ofhängeg vun der Aart vun der Zwang, huet Postgres dräi Niveaue vu Granularitéit fir se ze kontrolléieren: Reihen, Transaktiouns- an Ausdrockniveauen.

Source: begriffs

CHECK an NET NULL ginn ëmmer um Zeilniveau gepréift; fir aner Restriktiounen, wéi aus der Tabell gesi ka ginn, ginn et verschidden Optiounen. Dir kënnt méi liesen hei.

Fir kuerz ze resuméieren, ofgeschloss Aschränkungen an enger Zuel vu Situatioune bidden méi liesbare Code a manner Kommandoen. Wéi och ëmmer, Dir musst dofir bezuelen andeems Dir den Debuggingprozess komplizéiert, well de Moment wou de Feeler geschitt an de Moment wou Dir doriwwer erausfënnt, sinn an der Zäit getrennt. En anere méigleche Problem ass datt de Scheduler net ëmmer fäeg ass en optimalen Plang ze konstruéieren wann d'Ufro eng ausgestallte Beschränkung involvéiert.

Verbesserung vun pg_repack

Mir hunn iwwerdeckt wat ausgestallte Contrainten sinn, awer wéi bezéie se sech mat eisem Problem? Loosst eis de Feeler erënneren dee mir virdru krut:

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

Et geschitt wann Daten aus engem Logtabel op eng nei Tabell kopéiert ginn. Dëst gesäit komesch aus well ... d'Donnéeën am Logtabel sinn zesumme mat den Daten an der Quelltabel engagéiert. Wann se d'Aschränkungen vun der ursprénglecher Tabell erfëllen, wéi kënne se déi selwecht Contrainten an der neier verletzen?

Wéi et sech erausstellt, läit d'Wurzel vum Problem am virege Schrëtt vum pg_repack, deen nëmmen Indexen erstellt, awer net Aschränkungen: déi al Tabell hat eng eenzegaarteg Aschränkung, an deen neien huet amplaz en eenzegaartegen Index erstallt.

Et ass wichteg hei ze bemierken datt wann d'Begrenzung normal ass an net ausgesat ass, dann ass den eenzegaartegen Index erstallt amplaz gläichwäerteg mat dëser Aschränkung, well Eenzegaarteg Contrainten am Postgres ginn ëmgesat andeems en eenzegaartegen Index erstallt gëtt. Awer am Fall vun enger ausgesater Beschränkung ass d'Verhalen net datselwecht, well den Index kann net ausgesat ginn an ëmmer kontrolléiert gëtt zur Zäit wou de sql Kommando ausgefouert gëtt.

Also läit d'Essenz vum Problem an der "Verzögerung" vum Scheck: an der ursprénglecher Tabell geschitt et zum Zäitpunkt vun der Verpflichtung, an an der neier Tabell zur Zäit wou de sql Kommando ausgefouert gëtt. Dat heescht, mir mussen dofir suergen, datt d'Kontrollen a béide Fäll d'selwecht gemaach ginn: entweder ëmmer verspéit oder ëmmer direkt.

Also wat Iddien hu mir?

Erstellt en Index ähnlech wéi deferred

Déi éischt Iddi ass béid Kontrollen am direkten Modus auszeféieren. Dëst kann e puer falsch positiv Restriktiounen generéieren, awer wann et e puer vun hinnen sinn, sollt dëst d'Aarbecht vun de Benotzer net beaflossen, well sou Konflikter eng normal Situatioun fir si sinn. Si geschéien, zum Beispill, wann zwee Benotzer ufänken de selwechte Widget gläichzäiteg z'änneren, an de Client vum zweete Benotzer huet keng Zäit fir Informatioun ze kréien, datt de Widget scho fir d'Ännerung vum éischte Benotzer blockéiert ass. An esou enger Situatioun refuséiert de Server den zweete Benotzer, a säi Client rullt d'Ännerungen zréck a blockéiert de Widget. E bësse méi spéit, wann den éischte Benotzer d'Änneren ofgeschloss huet, kritt deen zweeten Informatioun datt de Widget net méi blockéiert ass a kënnen hir Handlung widderhuelen.

Fir sécherzestellen datt d'Schecken ëmmer am Net-deferred Modus sinn, hu mir en neien Index erstallt ähnlech wéi déi ursprénglech deferred Constraint:

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

Am Testëmfeld krute mir nëmmen e puer erwaart Feeler. Erfolleg! Mir lafen pg_repack erëm op Produktioun a krut 5 Feeler op den éischte Stärekoup an enger Stonn Aarbecht. Dëst ass en akzeptabelt Resultat. Wéi och ëmmer, schonn um zweete Cluster ass d'Zuel vun de Feeler däitlech eropgaang a mir hu misse pg_repack stoppen.

Firwat ass et geschitt? D'Wahrscheinlechkeet datt e Feeler geschitt hänkt dovun of wéi vill Benotzer mat deeneselwechte Widgets zur selwechter Zäit schaffen. Anscheinend gouf et dee Moment vill manner kompetitiv Ännerungen mat den Daten, déi um éischte Cluster gespäichert sinn, wéi op deenen aneren, d.h. mir waren just "Gléck".

D'Iddi huet net geschafft. Zu deem Zäitpunkt hu mir zwou aner Léisunge gesinn: eis Applikatiounscode nei schreiwen fir ausgestallte Contrainten ze verzichten, oder "léieren" pg_repack fir mat hinnen ze schaffen. Mir hunn déi zweet gewielt.

Ersetzen d'Indexen an der neier Tabell mat ofgeschlossene Contrainten aus der ursprénglecher Tabell

Den Zweck vun der Revisioun war offensichtlech - wann den ursprénglechen Dësch eng ofgeschloss Beschränkung huet, da musst Dir fir déi nei esou eng Aschränkung erstellen an net en Index.

Fir eis Ännerungen ze testen, hu mir en einfachen Test geschriwwen:

• Dësch mat enger ausgeschlosser Zwang an engem Rekord;
• Daten an eng Loop setzen, déi mat engem existente Rekord konfliktt;
• maachen en Update - d'Donnéeën net méi Konflikter;
• d'Ännerungen engagéieren.

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éi ursprénglech Versioun vum pg_repack ass ëmmer um éischten Insert erofgefall, déi geännert Versioun huet ouni Feeler geschafft. Super.

Mir ginn op d'Produktioun a kréien erëm e Feeler an der selwechter Phase fir Daten aus der Logtabel op eng nei ze kopéieren:

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

Klassesch Situatioun: alles funktionnéiert an Testëmfeld, awer net an der Produktioun?!

APPLY_COUNT an d'Kräizung vun zwee Chargen

Mir hunn ugefaang de Code wuertwiertlech Zeil fir Zeil ze analyséieren an e wichtege Punkt entdeckt: Daten ginn aus der Logtabel op eng nei a Chargen transferéiert, d'APPLY_COUNT Konstant huet d'Gréisst vun der Batch uginn:

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

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

De Problem ass datt d'Donnéeën vun der ursprénglecher Transaktioun, an där verschidde Operatioune potenziell d'Begrenzung verletze kënnen, wann se iwwerdroe ginn, kënnen op der Kräizung vun zwee Chargen ophalen - d'Halschent vun de Kommandoen ginn an der éischter Partie engagéiert, an déi aner Halschent an der zweeter. An hei, ofhängeg vun Ärem Gléck: wann d'Equipen näischt an der éischter Partie verletzen, dann ass alles gutt, awer wann se et maachen, geschitt e Feeler.

APPLY_COUNT ass gläich wéi 1000 Opzeechnungen, wat erkläert firwat eis Tester erfollegräich waren - si hunn net de Fall vun "Batch Kräizung" ofgedeckt. Mir hunn zwee Kommandoen benotzt - Insert an Update, also genee 500 Transaktioune vun zwee Kommandoen goufen ëmmer an enger Batch plazéiert a mir hu keng Problemer erlieft. Nodeems mir den zweeten Update bäigefüügt hunn, ass eis Ännerung opgehalen ze schaffen:

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;

Also, déi nächst Aufgab ass sécherzestellen datt Daten aus der ursprénglecher Tabell, déi an enger Transaktioun geännert goufen, an der neier Tabell och an enger Transaktioun ophalen.

Refus vu Batching

An erëm hu mir zwou Léisungen. Als éischt: loosst eis d'Partitionéierung a Chargen komplett opginn an Daten an enger Transaktioun transferéieren. De Virdeel vun dëser Léisung war seng Einfachheet - déi néideg Code Ännerungen waren minimal (iwwregens, an eeler Versiounen pg_reorg huet genau esou geschafft). Awer et gëtt e Problem - mir kreéieren eng laangjähreg Transaktioun, an dëst, wéi virdru gesot, ass eng Bedrohung fir d'Entstoe vun engem neie Bloat.

Déi zweet Léisung ass méi komplex, awer wahrscheinlech méi korrekt: erstellt eng Kolonn an der Logtabel mat dem Identifizéierer vun der Transaktioun, déi Daten un den Dësch bäigefüügt huet. Dann, wa mir Daten kopéieren, kënne mir se mat dësem Attribut gruppéieren an dofir suergen datt verbonne Ännerunge matenee transferéiert ginn. D'Batch gëtt aus e puer Transaktiounen (oder enger grousser) geformt a seng Gréisst wäert variéieren jee wéi vill Daten an dësen Transaktiounen geännert goufen. Et ass wichteg ze bemierken datt zënter Daten aus verschiddenen Transaktiounen an enger zoufälleger Uerdnung an de Logtabel erakommen, et net méi méiglech ass se sequentiell ze liesen, wéi et virdru war. seqscan fir all Ufro mat Filteren duerch tx_id ass ze deier, en Index ass néideg, awer et wäert och d'Method verlangsamen wéinst der Iwwerschlag fir se ze aktualiséieren. Am Allgemengen, wéi ëmmer, musst Dir eppes opferen.

Also hu mir beschloss mat der éischter Optioun unzefänken, well et méi einfach ass. Als éischt war et néideg ze verstoen ob eng laang Transaktioun e richtege Problem wier. Zënter datt den Haapttransfer vun Daten vum alen Dësch op den neien och an enger laanger Transaktioun geschitt, ass d'Fro ëmgewandelt an "wéi vill wäerte mir dës Transaktioun erhéijen?" D'Dauer vun der éischter Transaktioun hänkt haaptsächlech op der Gréisst vum Dësch. D'Dauer vun engem neien hänkt dovun of wéi vill Ännerungen an der Tabell während dem Datenübertragung accumuléieren, d.h. op d'Intensitéit vun der Laascht. De pg_repack Laf ass während enger Zäit vu minimaler Servicebelaaschtung geschitt, an de Volume vun den Ännerungen war onproportional kleng am Verglach mat der ursprénglecher Gréisst vum Dësch. Mir hunn décidéiert datt mir d'Zäit vun enger neier Transaktioun vernoléissegen (zum Verglach, am Duerchschnëtt ass et 1 Stonn an 2-3 Minutten).

D'Experimenter ware positiv. Lancéiere och op Produktioun. Fir Kloerheet, hei ass e Bild mat der Gréisst vun enger vun den Datenbanken nodeems se lafen:

Well mir mat dëser Léisung komplett zefridde waren, hu mir net probéiert déi zweet ëmzesetzen, awer mir iwwerleeën d'Méiglechkeet et mat den Extensiounsentwéckler ze diskutéieren. Eis aktuell Versioun, leider, ass nach net prett fir Verëffentlechung, well mir de Problem nëmmen mat eenzegaartege deferred Restriktiounen geléist, a fir eng voll-vollwäerteg Patch ass et néideg Ënnerstëtzung fir aner Zorte ze bidden. Mir hoffen dat an Zukunft kënnen ze maachen.

Vläicht hutt Dir eng Fro, firwat hu mir iwwerhaapt an dëser Geschicht mat der Ännerung vum pg_repack involvéiert, an hunn net zum Beispill seng Analoga benotzt? Irgendwann hu mir och iwwer dëst geduecht, awer déi positiv Erfarung fir et fréier ze benotzen, op Dëscher ouni ofgeschloss Aschränkungen, huet eis motivéiert fir d'Essenz vum Problem ze verstoen an ze fixéieren. Zousätzlech brauch d'Benotzung vun anere Léisungen och Zäit fir Tester ze maachen, also hu mir beschloss datt mir als éischt probéieren de Problem dran ze fixéieren, a wa mir gemierkt hunn datt mir dat net an enger raisonnabeler Zäit kéinte maachen, da géife mir no Analoga kucken .

Conclusiounen

Wat mir recommandéieren op Basis vun eiser eegener Erfahrung:

1. Iwwerwaacht Är Bloat. Baséierend op Iwwerwaachungsdaten, kënnt Dir verstoen wéi gutt Autovakuum konfiguréiert ass.
2. Ajustéiert AUTOVACUUM fir Bloat op engem akzeptablen Niveau ze halen.
3. Wann de Bloat nach ëmmer wiisst an Dir kënnt et net iwwerwannen mat Out-of-the-Box Tools, fäert net extern Extensiounen ze benotzen. Den Haapt Saach ass alles gutt ze testen.
4. Gitt net Angscht extern Léisungen ze änneren fir Är Bedierfnesser ze passen - heiansdo kann dëst méi effektiv an nach méi einfach sinn wéi Ären eegene Code z'änneren.

Source: will.com