Postgres: bloat, pg_repack en uitgestelde beperkingen

Het effect van bloat op tabellen en indexen is algemeen bekend en is niet alleen aanwezig in Postgres. Er zijn manieren om dit out-of-the-box aan te pakken, zoals VACUUM FULL of CLUSTER, maar deze vergrendelen de tafels tijdens het gebruik en kunnen daarom niet altijd worden gebruikt.

Het artikel zal een kleine theorie bevatten over hoe bloat ontstaat, hoe je het kunt bestrijden, over uitgestelde beperkingen en de problemen die deze met zich meebrengen bij het gebruik van de pg_repack-extensie.

Dit artikel is geschreven op basis van mijn speech op PgConf.Rusland 2020.

Waarom ontstaat er een opgeblazen gevoel?

Postgres is gebaseerd op een multi-versiemodel (MVCC). De essentie ervan is dat elke rij in de tabel meerdere versies kan hebben, terwijl transacties niet meer dan één van deze versies te zien krijgen, maar niet noodzakelijkerwijs dezelfde. Hierdoor kunnen meerdere transacties tegelijkertijd werken en vrijwel geen invloed op elkaar hebben.

Uiteraard moeten al deze versies worden opgeslagen. Postgres werkt pagina voor pagina met geheugen en een pagina is de minimale hoeveelheid gegevens die van schijf kan worden gelezen of geschreven. Laten we een klein voorbeeld bekijken om te begrijpen hoe dit gebeurt.

Laten we zeggen dat we een tabel hebben waaraan we verschillende records hebben toegevoegd. Er zijn nieuwe gegevens verschenen op de eerste pagina van het bestand waarin de tabel is opgeslagen. Dit zijn live versies van rijen die na een commit beschikbaar zijn voor andere transacties (voor de eenvoud gaan we ervan uit dat het isolatieniveau Read Commited is).

Vervolgens hebben we een van de vermeldingen bijgewerkt, waardoor de oude versie als niet langer relevant werd gemarkeerd.

Stap voor stap, door rijversies bij te werken en te verwijderen, kwamen we terecht op een pagina waarop ongeveer de helft van de gegevens “vuilnis” is. Deze gegevens zijn voor geen enkele transactie zichtbaar.

Postgres heeft een mechanisme VACUÜM, waarmee verouderde versies worden opgeruimd en ruimte wordt gemaakt voor nieuwe gegevens. Maar als het niet agressief genoeg is geconfigureerd of druk bezig is met het werken in andere tabellen, blijven er ‘garbage data’ over en moeten we extra pagina’s gebruiken voor nieuwe gegevens.

Dus in ons voorbeeld zal de tabel op een bepaald moment uit vier pagina's bestaan, maar slechts de helft ervan zal live gegevens bevatten. Als gevolg hiervan zullen we bij toegang tot de tabel veel meer gegevens lezen dan nodig is.

Zelfs als VACUUM nu alle irrelevante rijversies verwijdert, zal de situatie niet dramatisch verbeteren. We hebben vrije ruimte op pagina's of zelfs hele pagina's voor nieuwe rijen, maar we zullen nog steeds meer gegevens lezen dan nodig is.
Trouwens, als er een volledig blanco pagina (de tweede in ons voorbeeld) aan het einde van het bestand zou staan, zou VACUUM deze kunnen bijsnijden. Maar nu zit ze er middenin, dus er kan niets meer met haar gedaan worden.

Wanneer het aantal van dergelijke lege of zeer schaarse pagina's groot wordt, wat bloat wordt genoemd, begint dit de prestaties te beïnvloeden.

Alles wat hierboven is beschreven, is de werking van het optreden van bloat in tabellen. Bij indexen gebeurt dit op vrijwel dezelfde manier.

Heb ik een opgeblazen gevoel?

Er zijn verschillende manieren om te bepalen of u een opgeblazen gevoel heeft. Het idee van de eerste is om interne Postgres-statistieken te gebruiken, die geschatte informatie bevatten over het aantal rijen in tabellen, het aantal "live" rijen, enz. Op internet zijn veel varianten van kant-en-klare scripts te vinden. Wij hebben als basis genomen script van PostgreSQL Experts, die bloat-tabellen kunnen evalueren samen met toast- en bloat btree-indexen. Onze ervaring is dat de fout 10-20% bedraagt.

Een andere manier is om de extensie te gebruiken pgstattupel, waarmee u binnen de pagina's kunt kijken en zowel een geschatte als een exacte bloat-waarde kunt krijgen. Maar in het tweede geval moet u de hele tabel scannen.

Wij beschouwen een kleine bloat-waarde, tot 20%, als acceptabel. Het kan worden beschouwd als een analoog van de vulfactor voor tafels и indexen. Bij 50% en hoger kunnen prestatieproblemen beginnen.

Manieren om een ​​opgeblazen gevoel te bestrijden

Postgres heeft verschillende manieren om out-of-the-box met een opgeblazen gevoel om te gaan, maar deze zijn niet altijd voor iedereen geschikt.

Configureer AUTOVACUUM zodat er geen opgeblazen gevoel ontstaat. Of beter gezegd: om het op een voor u aanvaardbaar niveau te houden. Dit lijkt het advies van de kapitein, maar in werkelijkheid is dit niet altijd gemakkelijk te verwezenlijken. Er is bijvoorbeeld sprake van actieve ontwikkeling met regelmatige wijzigingen in het dataschema, of er vindt een vorm van datamigratie plaats. Als gevolg hiervan kan uw laadprofiel vaak veranderen en varieert dit doorgaans van tabel tot tabel. Dit betekent dat u voortdurend een beetje vooruit moet werken en AUTOVACUUM moet aanpassen aan het veranderende profiel van elke tafel. Maar het is duidelijk dat dit niet gemakkelijk is om te doen.

Een andere veel voorkomende reden waarom AUTOVACUUM de tabellen niet kan bijhouden, is omdat er langlopende transacties zijn die voorkomen dat de gegevens die beschikbaar zijn voor die transacties worden opgeschoond. De aanbeveling hier ligt ook voor de hand: ontdoe u van “hangende” transacties en minimaliseer de tijd van actieve transacties. Maar als de belasting van uw applicatie een hybride is van OLAP en OLTP, kunt u tegelijkertijd veel frequente updates en korte vragen hebben, evenals langetermijnbewerkingen, bijvoorbeeld het bouwen van een rapport. In een dergelijke situatie is het de moeite waard om na te denken over het spreiden van de belasting over verschillende bases, waardoor elk van hen beter kan worden afgestemd.

Nog een voorbeeld: zelfs als het profiel homogeen is, maar de database onder een zeer hoge belasting staat, kan zelfs de meest agressieve AUTOVACUUM het misschien niet aan, en zal er een opgeblazen gevoel optreden. Schalen (verticaal of horizontaal) is de enige oplossing.

Wat te doen in een situatie waarin u AUTOVACUUM heeft ingesteld, maar het probleem blijft toenemen.

Team VACUÜM VOL herbouwt de inhoud van tabellen en indexen en laat er alleen relevante gegevens in achter. Om bloat te elimineren, werkt het perfect, maar tijdens de uitvoering ervan wordt een exclusieve vergrendeling op de tafel vastgelegd (AccessExclusiveLock), waardoor het uitvoeren van query's op deze tafel niet mogelijk is, zelfs niet wordt geselecteerd. Als u het zich kunt veroorloven om uw service of een deel ervan voor enige tijd stop te zetten (van tientallen minuten tot enkele uren, afhankelijk van de grootte van de database en uw hardware), dan is deze optie de beste. Helaas hebben wij geen tijd om tijdens het geplande onderhoud VACUÜM VOL te laten draaien, daarom is deze werkwijze voor ons niet geschikt.

Team CLUSTER Herbouwt de inhoud van tabellen op dezelfde manier als VACUUM FULL, maar stelt u in staat een index op te geven op basis waarvan de gegevens fysiek op schijf worden geordend (maar in de toekomst is de volgorde niet gegarandeerd voor nieuwe rijen). In bepaalde situaties is dit een goede optimalisatie voor een aantal zoekopdrachten - waarbij meerdere records per index worden gelezen. Het nadeel van het commando is hetzelfde als dat van VACUUM FULL: het vergrendelt de tafel tijdens bedrijf.

Team HERINDEX vergelijkbaar met de vorige twee, maar herbouwt een specifieke index of alle indexen van de tabel. De vergrendelingen zijn iets zwakker: ShareLock op de tabel (voorkomt wijzigingen, maar maakt selectie mogelijk) en AccessExclusiveLock op de index die opnieuw wordt opgebouwd (blokkeert zoekopdrachten die deze index gebruiken). In de 12e versie van Postgres verscheen echter een parameter CONCURRENTIEEL, waarmee u de index opnieuw kunt opbouwen zonder het gelijktijdig toevoegen, wijzigen of verwijderen van records te blokkeren.

In eerdere versies van Postgres kunt u een resultaat bereiken dat vergelijkbaar is met REINDEX CONCURRENTLY met behulp van MAAK CONCURRENT INDEX. Hiermee kunt u een index maken zonder strikte vergrendeling (ShareUpdateExclusiveLock, die parallelle zoekopdrachten niet verstoort), vervolgens de oude index vervangen door een nieuwe en de oude index verwijderen. Hierdoor kunt u indexzwelling elimineren zonder uw toepassing te verstoren. Het is belangrijk om er rekening mee te houden dat bij het opnieuw opbouwen van indexen het schijfsubsysteem extra wordt belast.

Dus als er voor indexen manieren zijn om bloat “on the fly” te elimineren, dan zijn die er voor tabellen niet. Dit is waar verschillende externe extensies een rol spelen: pg_repack (voorheen pg_reorg), pgcompact, pgcompacttabel en anderen. In dit artikel zal ik ze niet vergelijken en alleen praten over pg_repack, dat we, na enige aanpassing, zelf gebruiken.

Hoe pg_repack werkt

Laten we zeggen dat we een heel gewone tabel hebben - met indexen, beperkingen en, helaas, met een opgeblazen gevoel. De eerste stap van pg_repack is het maken van een logtabel om gegevens over alle wijzigingen op te slaan terwijl het programma actief is. De trigger repliceert deze wijzigingen voor elke invoeging, update en verwijdering. Vervolgens wordt een tabel gemaakt, vergelijkbaar met de originele structuur, maar zonder indexen en beperkingen, om het proces van het invoegen van gegevens niet te vertragen.

Vervolgens brengt pg_repack de gegevens over van de oude tabel naar de nieuwe tabel, waarbij automatisch alle irrelevante rijen worden uitgefilterd, en worden vervolgens indexen voor de nieuwe tabel gemaakt. Tijdens de uitvoering van al deze bewerkingen stapelen de wijzigingen zich op in de logboektabel.

De volgende stap is het overbrengen van de wijzigingen naar de nieuwe tabel. De migratie wordt uitgevoerd over verschillende iteraties, en wanneer er minder dan 20 vermeldingen over zijn in de logtabel, krijgt pg_repack een sterke vergrendeling, migreert de nieuwste gegevens en vervangt de oude tabel door de nieuwe in de Postgres-systeemtabellen. Dit is de enige en zeer korte tijd waarin u niet met de tafel kunt werken. Hierna worden de oude tabel en de tabel met logs verwijderd en wordt er ruimte vrijgemaakt in het bestandssysteem. Het proces is voltooid.

In theorie ziet alles er geweldig uit, maar wat gebeurt er in de praktijk? We hebben pg_repack zonder belasting en onder belasting getest, en de werking ervan gecontroleerd in geval van voortijdige stop (met andere woorden, met behulp van Ctrl+C). Alle tests waren positief.

We gingen naar de levensmiddelenwinkel - en toen verliep alles niet zoals we hadden verwacht.

Eerste pannenkoek in de aanbieding

Op het eerste cluster ontvingen we een foutmelding over een schending van een unieke beperking:

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

Deze beperking had een automatisch gegenereerde naam index_16508 - deze werd gemaakt door pg_repack. Op basis van de attributen die in de samenstelling zijn opgenomen, hebben we “onze” beperking bepaald die ermee overeenkomt. Het probleem bleek te zijn dat dit geen volkomen gewone beperking is, maar een uitgestelde beperking (uitgestelde beperking), d.w.z. de verificatie ervan wordt later uitgevoerd dan de sql-opdracht, wat tot onverwachte gevolgen leidt.

Uitgestelde beperkingen: waarom ze nodig zijn en hoe ze werken

Een beetje theorie over uitgestelde beperkingen.
Laten we een eenvoudig voorbeeld bekijken: we hebben een tabelreferentieboek met auto's met twee attributen: de naam en de volgorde van de auto in de directory.

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

Laten we zeggen dat we de eerste en tweede auto moesten verwisselen. De eenvoudige oplossing is om de eerste waarde bij te werken naar de tweede, en de tweede naar de eerste:

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

Maar wanneer we deze code uitvoeren, verwachten we een schending van de beperking, omdat de volgorde van de waarden in de tabel uniek is:

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

Hoe kan ik het anders doen? Optie één: voeg een extra waardevervanging toe aan een bestelling die gegarandeerd niet in de tabel voorkomt, bijvoorbeeld “-1”. In het programmeren heet dit ‘het uitwisselen van de waarden van twee variabelen met een derde’. Het enige nadeel van deze methode is de extra update.

Optie twee: Ontwerp de tabel opnieuw om een ​​gegevenstype met drijvende komma te gebruiken voor de orderwaarde in plaats van gehele getallen. Als u vervolgens de waarde bijwerkt van bijvoorbeeld 1 naar 2.5, staat de eerste invoer automatisch tussen de tweede en de derde. Deze oplossing werkt, maar er zijn twee beperkingen. Ten eerste zal het niet voor u werken als de waarde ergens in de interface wordt gebruikt. Ten tweede beschikt u, afhankelijk van de nauwkeurigheid van het gegevenstype, over een beperkt aantal mogelijke invoegingen voordat u de waarden van alle records opnieuw berekent.

Optie drie: zorg ervoor dat de beperking wordt uitgesteld, zodat deze alleen wordt gecontroleerd op het moment van vastleggen:

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

Omdat de logica van ons oorspronkelijke verzoek ervoor zorgt dat alle waarden uniek zijn op het moment van commit, zal het slagen.

Het hierboven besproken voorbeeld is natuurlijk erg synthetisch, maar het onthult het idee. In onze applicatie gebruiken we uitgestelde beperkingen om logica te implementeren die verantwoordelijk is voor het oplossen van conflicten wanneer gebruikers tegelijkertijd met gedeelde widgetobjecten op het bord werken. Door dergelijke beperkingen te gebruiken, kunnen we de applicatiecode een beetje eenvoudiger maken.

Over het algemeen heeft Postgres, afhankelijk van het type beperking, drie granulariteitsniveaus om deze te controleren: rij-, transactie- en expressieniveaus.

Bron: smeekt

CHECK en NOT NULL worden altijd op rijniveau gecontroleerd; voor andere beperkingen, zoals uit de tabel blijkt, zijn er verschillende opties. Je kunt meer lezen hier.

Kort samengevat: uitgestelde beperkingen zorgen in een aantal situaties voor beter leesbare code en minder opdrachten. U moet hiervoor echter wel betalen door het foutopsporingsproces ingewikkelder te maken, aangezien het moment waarop de fout optreedt en het moment waarop u erachter komt in de tijd gescheiden zijn. Een ander mogelijk probleem is dat de planner niet altijd in staat is een optimaal plan op te stellen als het verzoek een uitgestelde beperking met zich meebrengt.

Verbetering van pg_repack

We hebben besproken wat uitgestelde beperkingen zijn, maar hoe verhouden ze zich tot ons probleem? Laten we de fout onthouden die we eerder hebben ontvangen:

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

Het treedt op wanneer gegevens worden gekopieerd van een logtabel naar een nieuwe tabel. Dit ziet er vreemd uit, omdat... de gegevens in de logboektabel worden samen met de gegevens in de brontabel vastgelegd. Als ze voldoen aan de beperkingen van de oorspronkelijke tabel, hoe kunnen ze dan dezelfde beperkingen in de nieuwe overtreden?

Het blijkt dat de oorzaak van het probleem ligt in de vorige stap van pg_repack, die alleen indexen creëert, maar geen beperkingen: de oude tabel had een unieke beperking, en de nieuwe creëerde in plaats daarvan een unieke index.

Het is belangrijk om hier op te merken dat als de beperking normaal is en niet is uitgesteld, de unieke index die in plaats daarvan wordt gemaakt gelijkwaardig is aan deze beperking, omdat Unieke beperkingen in Postgres worden geïmplementeerd door een unieke index te maken. Maar in het geval van een uitgestelde beperking is het gedrag niet hetzelfde, omdat de index niet kan worden uitgesteld en altijd wordt gecontroleerd op het moment dat de sql-opdracht wordt uitgevoerd.

De essentie van het probleem ligt dus in de “vertraging” van de controle: in de oorspronkelijke tabel treedt dit op op het moment van commit, en in de nieuwe tabel op het moment dat de sql-opdracht wordt uitgevoerd. Dit betekent dat we ervoor moeten zorgen dat de controles in beide gevallen op dezelfde manier worden uitgevoerd: altijd uitgesteld, of altijd onmiddellijk.

Welke ideeën hadden we dan?

Maak een index die lijkt op uitgesteld

Het eerste idee is om beide controles in de onmiddellijke modus uit te voeren. Dit kan verschillende vals-positieve beperkingen opleveren, maar als er maar een paar zijn, zou dit geen invloed moeten hebben op het werk van gebruikers, aangezien dergelijke conflicten voor hen een normale situatie zijn. Ze komen bijvoorbeeld voor wanneer twee gebruikers tegelijkertijd dezelfde widget beginnen te bewerken en de client van de tweede gebruiker geen tijd heeft om informatie te ontvangen dat de widget al is geblokkeerd voor bewerking door de eerste gebruiker. In een dergelijke situatie weigert de server de tweede gebruiker en draait de client de wijzigingen terug en blokkeert de widget. Even later, wanneer de eerste gebruiker het bewerken heeft voltooid, ontvangt de tweede informatie dat de widget niet langer is geblokkeerd en kan hij zijn actie herhalen.

Om ervoor te zorgen dat controles altijd in de niet-uitgestelde modus plaatsvinden, hebben we een nieuwe index gemaakt die lijkt op de oorspronkelijke uitgestelde beperking:

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

In de testomgeving ontvingen we slechts enkele verwachte fouten. Succes! We hebben pg_repack opnieuw uitgevoerd tijdens de productie en binnen een uur werk kregen we 5 fouten op het eerste cluster. Dit is een acceptabel resultaat. Al op het tweede cluster nam het aantal fouten echter aanzienlijk toe en moesten we pg_repack stoppen.

Waarom gebeurde het? De kans dat er een fout optreedt, hangt af van het aantal gebruikers dat tegelijkertijd met dezelfde widgets werkt. Blijkbaar waren er op dat moment veel minder concurrentieveranderingen met de gegevens die op het eerste cluster waren opgeslagen dan op de andere. we hadden gewoon “geluk”.

Het idee werkte niet. Op dat moment zagen we twee andere oplossingen: onze applicatiecode herschrijven om uitgestelde beperkingen op te heffen, of pg_repack “leren” om ermee te werken. Wij kozen voor de tweede.

Vervang indexen in de nieuwe tabel door uitgestelde beperkingen uit de oorspronkelijke tabel

Het doel van de herziening lag voor de hand: als de oorspronkelijke tabel een uitgestelde beperking heeft, moet u voor de nieuwe zo'n beperking creëren, en geen index.

Om onze wijzigingen te testen, hebben we een eenvoudige test geschreven:

• tabel met een uitgestelde beperking en één record;
• gegevens invoegen in een lus die conflicteert met een bestaand record;
• voer een update uit – de gegevens zijn niet langer conflicterend;
• de wijzigingen doorvoeren.

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;

De originele versie van pg_repack crashte altijd bij de eerste invoeging, de aangepaste versie werkte zonder fouten. Geweldig.

We gaan naar de productie en krijgen opnieuw een foutmelding in dezelfde fase van het kopiëren van gegevens van de logtabel naar een nieuwe:

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

Klassieke situatie: alles werkt in testomgevingen, maar niet in productie?!

APPLY_COUNT en de kruising van twee batches

We begonnen de code letterlijk regel voor regel te analyseren en ontdekten een belangrijk punt: gegevens worden in batches van de logtabel naar een nieuwe overgebracht, de constante APPLY_COUNT gaf de grootte van de batch aan:

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

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

Het probleem is dat de gegevens van de oorspronkelijke transactie, waarbij verschillende bewerkingen mogelijk de beperking zouden kunnen schenden, bij overdracht op het kruispunt van twee batches terecht kunnen komen - de helft van de opdrachten wordt vastgelegd in de eerste batch en de andere helft in de seconde. En hier, afhankelijk van je geluk: als de teams niets overtreden in de eerste batch, dan is alles in orde, maar als ze dat wel doen, treedt er een fout op.

APPLY_COUNT is gelijk aan 1000 records, wat verklaart waarom onze tests succesvol waren: ze hadden geen betrekking op het geval van “batch-junction”. We gebruikten twee commando's: invoegen en bijwerken, dus precies 500 transacties van twee commando's werden altijd in een batch geplaatst en we ondervonden geen problemen. Na het toevoegen van de tweede update werkte onze bewerking niet meer:

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;

De volgende taak is dus om ervoor te zorgen dat gegevens uit de oorspronkelijke tabel, die in één transactie zijn gewijzigd, ook binnen één transactie in de nieuwe tabel terechtkomen.

Weigering van batching

En opnieuw hadden we twee oplossingen. Ten eerste: laten we het partitioneren in batches volledig achterwege laten en gegevens in één transactie overbrengen. Het voordeel van deze oplossing was de eenvoud ervan: de vereiste codewijzigingen waren minimaal (in oudere versies werkte pg_reorg trouwens precies zo). Maar er is een probleem: we creëren een langlopende transactie, en dit vormt, zoals eerder gezegd, een bedreiging voor het ontstaan ​​van een nieuwe opgeblazenheid.

De tweede oplossing is complexer, maar waarschijnlijk correcter: maak een kolom in de logtabel met de identificatie van de transactie die gegevens aan de tabel heeft toegevoegd. Als we vervolgens gegevens kopiëren, kunnen we deze op dit attribuut groeperen en ervoor zorgen dat gerelateerde wijzigingen samen worden overgedragen. De batch zal worden gevormd uit verschillende transacties (of één grote) en de grootte zal variëren afhankelijk van hoeveel gegevens er in deze transacties zijn gewijzigd. Het is belangrijk op te merken dat, aangezien gegevens van verschillende transacties in willekeurige volgorde in de logboektabel terechtkomen, het niet langer mogelijk zal zijn om deze opeenvolgend te lezen, zoals voorheen. seqscan voor elk verzoek met filteren op tx_id is te duur, er is een index nodig, maar het zal de methode ook vertragen vanwege de overhead van het bijwerken ervan. Over het algemeen moet je, zoals altijd, iets opofferen.

Daarom hebben we besloten om met de eerste optie te beginnen, omdat deze eenvoudiger is. Ten eerste was het nodig om te begrijpen of een lange transactie een echt probleem zou zijn. Omdat de belangrijkste overdracht van gegevens van de oude tabel naar de nieuwe ook in één lange transactie plaatsvindt, veranderde de vraag in "hoeveel zullen we deze transactie verhogen?" De duur van de eerste transactie hangt vooral af van de grootte van de tafel. De duur van een nieuwe hangt af van het aantal wijzigingen dat zich tijdens de gegevensoverdracht in de tabel ophoopt, d.w.z. op de intensiteit van de belasting. De pg_repack-run vond plaats tijdens een periode van minimale servicebelasting en het aantal wijzigingen was onevenredig klein vergeleken met de oorspronkelijke grootte van de tabel. We besloten dat we de tijd van een nieuwe transactie konden verwaarlozen (ter vergelijking: gemiddeld is dit 1 uur en 2-3 minuten).

De experimenten waren positief. Lancering ook op productie. Voor de duidelijkheid is hier een afbeelding met de grootte van een van de databases na het uitvoeren:

Omdat we volledig tevreden waren met deze oplossing, hebben we niet geprobeerd de tweede te implementeren, maar overwegen we de mogelijkheid om deze met de extensie-ontwikkelaars te bespreken. Onze huidige herziening is helaas nog niet klaar voor publicatie, omdat we het probleem alleen hebben opgelost met unieke uitgestelde beperkingen, en voor een volwaardige patch is het noodzakelijk om ondersteuning te bieden voor andere typen. Wij hopen dit in de toekomst ook te kunnen doen.

Misschien heb je een vraag: waarom zijn we zelfs bij dit verhaal betrokken geraakt door de aanpassing van pg_repack, en hebben we bijvoorbeeld de analogen ervan niet gebruikt? Op een gegeven moment hebben we hier ook over nagedacht, maar de positieve ervaring van het eerder gebruiken ervan, op tafels zonder uitgestelde beperkingen, motiveerde ons om te proberen de essentie van het probleem te begrijpen en op te lossen. Bovendien kost het gebruik van andere oplossingen ook tijd om tests uit te voeren, dus besloten we dat we eerst zouden proberen het probleem daarin op te lossen, en als we ons realiseerden dat we dit niet binnen een redelijke tijd konden doen, dan zouden we naar analogen gaan kijken .

Bevindingen

Wat wij kunnen aanbevelen op basis van onze eigen ervaring:

1. Houd je zwelling in de gaten. Op basis van monitoringgegevens kunt u begrijpen hoe goed autovacuüm is geconfigureerd.
2. Pas het AUTOVACUUM aan om het opgeblazen gevoel op een acceptabel niveau te houden.
3. Als de zwelling nog steeds groeit en je deze niet kunt overwinnen met kant-en-klare tools, wees dan niet bang om externe extensies te gebruiken. Het belangrijkste is om alles goed te testen.
4. Wees niet bang om externe oplossingen aan te passen aan uw behoeften. Soms kan dit effectiever en zelfs eenvoudiger zijn dan het wijzigen van uw eigen code.

Bron: www.habr.com