Postgres: wzdęcia, pg_repack i ograniczenia odroczone

Wpływ wzdęć na tabele i indeksy jest powszechnie znany i występuje nie tylko w Postgresie. Istnieją gotowe sposoby poradzenia sobie z tym problemem, takie jak VACUUM FULL lub CLUSTER, ale blokują one stoły podczas pracy i dlatego nie zawsze można ich używać.

W artykule będzie trochę teorii o tym, jak powstaje wzdęcie, jak z nim walczyć, o odroczonych ograniczeniach i problemach, jakie niosą ze sobą korzystanie z rozszerzenia pg_repack.

Artykuł ten został napisany na podstawie moje przemówienie na PgConf.Russia 2020.

Dlaczego pojawiają się wzdęcia?

Postgres opiera się na modelu wielowersyjnym (MVCC). Jego istotą jest to, że każdy wiersz w tabeli może mieć kilka wersji, natomiast transakcje widzą nie więcej niż jedną z tych wersji, ale niekoniecznie tę samą. Dzięki temu kilka transakcji może działać jednocześnie i nie mają na siebie praktycznie żadnego wpływu.

Oczywiście wszystkie te wersje muszą być przechowywane. Postgres działa z pamięcią strona po stronie, a strona to minimalna ilość danych, które można odczytać z dysku lub zapisać. Spójrzmy na mały przykład, aby zrozumieć, jak to się dzieje.

Załóżmy, że mamy tabelę, do której dodaliśmy kilka rekordów. Na pierwszej stronie pliku, w którym przechowywana jest tabela, pojawiły się nowe dane. Są to aktywne wersje wierszy, które po zatwierdzeniu są dostępne dla innych transakcji (dla uproszczenia założymy, że poziom izolacji to Odczyt zatwierdzony).

Następnie zaktualizowaliśmy jeden z wpisów, oznaczając w ten sposób starą wersję jako nieaktualną.

Krok po kroku, aktualizując i usuwając wersje wierszy, otrzymaliśmy stronę, na której około połowa danych to „śmieci”. Dane te nie są widoczne przy żadnej transakcji.

Postgres ma mechanizm ODKURZAĆ, który usuwa przestarzałe wersje i zwalnia miejsce na nowe dane. Jeśli jednak nie jest skonfigurowany wystarczająco agresywnie lub jest zajęty pracą w innych tabelach, wówczas pozostają „dane śmieciowe” i musimy użyć dodatkowych stron dla nowych danych.

Zatem w naszym przykładzie w pewnym momencie tabela będzie składać się z czterech stron, ale tylko połowa z niej będzie zawierać aktualne dane. Dzięki temu wchodząc do tabeli odczytamy znacznie więcej danych, niż jest to konieczne.

Nawet jeśli VACUUM usunie teraz wszystkie nieistotne wersje wierszy, sytuacja nie poprawi się radykalnie. Będziemy mieli wolne miejsce na stronach lub nawet całych stronach na nowe wiersze, ale nadal będziemy czytać więcej danych niż to konieczne.
Swoją drogą, gdyby na końcu pliku znajdowała się zupełnie pusta strona (druga w naszym przykładzie), to VACUUM byłby w stanie ją przyciąć. Ale teraz jest pośrodku, więc nic nie można z nią zrobić.

Kiedy liczba takich pustych lub bardzo rzadkich stron staje się duża, co nazywa się wzdęciem, zaczyna to mieć wpływ na wydajność.

Wszystko opisane powyżej to mechanika występowania wzdęć w tabelach. W indeksach dzieje się to w podobny sposób.

Czy mam wzdęcia?

Istnieje kilka sposobów ustalenia, czy masz wzdęcia. Ideą pierwszego jest wykorzystanie wewnętrznych statystyk Postgres, które zawierają przybliżone informacje o liczbie wierszy w tabelach, liczbie „żywych” wierszy itp. W Internecie można znaleźć wiele odmian gotowych skryptów. Przyjęliśmy jako podstawę scenariusz od ekspertów PostgreSQL, którzy mogą oceniać wzdęte tabele wraz z tostowymi i wzdętymi indeksami btree. Z naszego doświadczenia wynika, że ​​jego błąd wynosi 10-20%.

Innym sposobem jest użycie rozszerzenia pgstattuple, co pozwala zajrzeć do wnętrza stron i uzyskać zarówno szacunkową, jak i dokładną wartość rozdęcia. Ale w drugim przypadku będziesz musiał przeskanować całą tabelę.

Za akceptowalną uznajemy niewielką wartość wzdęcia, do 20%. Można go uznać za odpowiednik współczynnika wypełnienia stoły и Indeks. Przy wartości 50% i wyższej mogą rozpocząć się problemy z wydajnością.

Sposoby na walkę z wzdęciami

Postgres ma kilka gotowych sposobów radzenia sobie z wzdęciami, ale nie zawsze są one odpowiednie dla wszystkich.

Skonfiguruj AUTOVACUUM, aby nie występowało wzdęcie. A dokładniej, aby utrzymać go na akceptowalnym przez Ciebie poziomie. Wygląda to na radę „kapitańską”, ale w rzeczywistości nie zawsze jest to łatwe do osiągnięcia. Na przykład prowadzisz aktywny rozwój z regularnymi zmianami w schemacie danych lub odbywa się pewnego rodzaju migracja danych. W rezultacie profil obciążenia może się często zmieniać i zazwyczaj będzie się różnić w zależności od stołu. Oznacza to, że trzeba stale pracować trochę do przodu i dostosowywać AUTOVACUUM do zmieniającego się profilu każdego stołu. Ale oczywiście nie jest to łatwe.

Innym częstym powodem, dla którego AUTOVACUUM nie może nadążać za tabelami, jest to, że istnieją długotrwałe transakcje, które uniemożliwiają mu oczyszczenie danych dostępnych dla tych transakcji. Zalecenie tutaj jest również oczywiste - pozbądź się „zawieszonych” transakcji i zminimalizuj czas aktywnych transakcji. Jeśli jednak obciążenie Twojej aplikacji jest hybrydą OLAP i OLTP, możesz jednocześnie mieć wiele częstych aktualizacji i krótkich zapytań, a także operacji długoterminowych - na przykład budowania raportu. W takiej sytuacji warto pomyśleć o rozłożeniu obciążenia na różne bazy, co pozwoli na lepsze dostrojenie każdej z nich.

Inny przykład - nawet jeśli profil jest jednorodny, ale baza danych jest pod bardzo dużym obciążeniem, to nawet najbardziej agresywne AUTOVACUUM może sobie nie poradzić i wystąpią wzdęcia. Jedynym rozwiązaniem jest skalowanie (pionowe lub poziome).

Co zrobić w sytuacji, gdy skonfigurowałeś AUTOVACUUM, ale wzdęcia nadal rosną.

Zespół PRÓŻNIA PEŁNA przebudowuje zawartość tabel i indeksów i pozostawia w nich tylko istotne dane. Aby wyeliminować wzdęcia, działa idealnie, ale podczas jego wykonywania przechwytywana jest wyłączna blokada na tabeli (AccessExclusiveLock), która nie pozwoli na wykonywanie zapytań na tej tabeli, nawet selekcji. Jeśli możesz sobie pozwolić na zatrzymanie usługi lub jej części na jakiś czas (od kilkudziesięciu minut do kilku godzin w zależności od wielkości bazy danych i posiadanego sprzętu), to ta opcja jest najlepsza. Niestety nie mamy czasu na uruchomienie VACUUM FULL podczas zaplanowanej konserwacji, więc ta metoda nie jest dla nas odpowiednia.

Zespół CLUSTER Przebudowuje zawartość tabel w taki sam sposób jak VACUUM FULL, ale pozwala określić indeks, według którego dane będą fizycznie uporządkowane na dysku (ale w przyszłości kolejność nie jest gwarantowana dla nowych wierszy). W niektórych sytuacjach jest to dobra optymalizacja dla wielu zapytań - z odczytem wielu rekordów według indeksu. Wada polecenia VACUUM FULL jest taka sama jak w przypadku polecenia VACUUM FULL - blokuje stół podczas pracy.

Zespół PONOWNE INDEKS podobnie jak dwa poprzednie, ale odbudowuje określony indeks lub wszystkie indeksy tabeli. Nieco słabsze są blokady: ShareLock na tabeli (zapobiega modyfikacjom, ale umożliwia zaznaczenie) i AccessExclusiveLock na przebudowywanym indeksie (blokuje zapytania wykorzystujące ten indeks). Jednak w 12. wersji Postgresa pojawił się parametr Równocześnie, co umożliwia odbudowanie indeksu bez blokowania jednoczesnego dodawania, modyfikowania lub usuwania rekordów.

We wcześniejszych wersjach Postgresa można było uzyskać efekt podobny do REINDEX CONCURRENTLY TWÓRZ INDEKS RÓWNOCZEŚNIE. Umożliwia utworzenie indeksu bez ścisłego blokowania (ShareUpdateExclusiveLock, który nie koliduje z zapytaniami równoległymi), a następnie zastąpienie starego indeksu nowym i usunięcie starego indeksu. Pozwala to wyeliminować wzdęcia indeksu bez zakłócania pracy aplikacji. Należy wziąć pod uwagę, że podczas odbudowy indeksów nastąpi dodatkowe obciążenie podsystemu dysku.

Tak więc, jeśli w przypadku indeksów istnieją sposoby na wyeliminowanie wzdęć „w locie”, to nie ma ich w przypadku tabel. Tutaj w grę wchodzą różne rozszerzenia zewnętrzne: pg_repack (dawniej pg_reorg), pgkompaktowy, pgkompaktowy i inni. W tym artykule nie będę ich porównywał, a jedynie opowiem o pg_repack, którego po pewnych modyfikacjach sami używamy.

Jak działa pg_repack

Powiedzmy, że mamy zupełnie zwyczajną tabelę - z indeksami, ograniczeniami i niestety z wzdęciami. Pierwszym krokiem pg_repack jest utworzenie tabeli dzienników do przechowywania danych o wszystkich zmianach podczas jego działania. Wyzwalacz będzie replikował te zmiany przy każdym wstawianiu, aktualizowaniu i usuwaniu. Następnie tworzona jest tabela o strukturze podobnej do pierwotnej, ale bez indeksów i ograniczeń, aby nie spowalniać procesu wstawiania danych.

Następnie pg_repack przenosi dane ze starej tabeli do nowej, automatycznie odfiltrowując wszystkie nieistotne wiersze, a następnie tworzy indeksy dla nowej tabeli. Podczas wykonywania wszystkich tych operacji zmiany gromadzą się w tabeli dziennika.

Następnym krokiem jest przeniesienie zmian do nowej tabeli. Migracja odbywa się w kilku iteracjach, a gdy w tabeli dziennika pozostanie mniej niż 20 wpisów, pg_repack uzyskuje silną blokadę, migruje najnowsze dane i zastępuje starą tabelę nową w tabelach systemu Postgres. To jedyny i bardzo krótki czas, kiedy nie będziesz mógł pracować ze stołem. Następnie stara tabela i tabela z dziennikami zostaną usunięte, a miejsce w systemie plików zostanie zwolnione. Proces został zakończony.

Wszystko wygląda świetnie w teorii, ale co się dzieje w praktyce? Przetestowaliśmy pg_repack bez obciążenia i pod obciążeniem oraz sprawdziliśmy jego działanie w przypadku przedwczesnego zatrzymania (innymi słowy, przy użyciu Ctrl+C). Wszystkie testy wypadły pozytywnie.

Poszliśmy do sklepu spożywczego - i wtedy nie wszystko poszło tak, jak się spodziewaliśmy.

Pierwszy naleśnik w sprzedaży

Na pierwszym klastrze otrzymaliśmy błąd o naruszeniu ograniczenia unikalnego:

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

To ograniczenie miało automatycznie wygenerowaną nazwę indeks_16508 - zostało utworzone przez pg_repack. Na podstawie atrybutów wchodzących w skład jego składu określiliśmy „nasze” ograniczenie, które mu odpowiada. Problem okazał się taki, że nie jest to ograniczenie zupełnie zwyczajne, ale odroczone (odroczone ograniczenie), tj. jego weryfikacja odbywa się później niż poleceniem sql, co prowadzi do nieoczekiwanych konsekwencji.

Odroczone ograniczenia: dlaczego są potrzebne i jak działają

Trochę teorii na temat odroczonych ograniczeń.
Rozważmy prosty przykład: mamy tabelę-referencję samochodów z dwoma atrybutami - nazwą i kolejnością samochodu w katalogu.

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

Powiedzmy, że musieliśmy zamienić pierwszy i drugi samochód. Prostym rozwiązaniem jest aktualizacja pierwszej wartości do drugiej, a drugiej do pierwszej:

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

Ale kiedy uruchomimy ten kod, spodziewamy się naruszenia ograniczenia, ponieważ kolejność wartości w tabeli jest unikalna:

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

Jak mogę to zrobić inaczej? Opcja pierwsza: dodaj dodatkową wartość zamienną do zamówienia, którego na pewno nie będzie w tabeli, na przykład „-1”. W programowaniu nazywa się to „wymianą wartości dwóch zmiennych na trzecią”. Jedyną wadą tej metody jest dodatkowa aktualizacja.

Opcja druga: Przeprojektuj tabelę, aby używać typu danych zmiennoprzecinkowych dla wartości zamówienia zamiast liczb całkowitych. Następnie, przy aktualizacji wartości z np. 1 do 2.5, pierwszy wpis automatycznie „stanie” pomiędzy drugim a trzecim. To rozwiązanie działa, ale istnieją dwa ograniczenia. Po pierwsze, to nie zadziała, jeśli wartość zostanie użyta gdzieś w interfejsie. Po drugie, w zależności od precyzji typu danych, będziesz mieć ograniczoną liczbę możliwych wstawień przed ponownym obliczeniem wartości wszystkich rekordów.

Opcja trzecia: odłóż ograniczenie, aby było sprawdzane tylko w momencie zatwierdzenia:

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

Ponieważ logika naszego początkowego żądania zapewnia, że ​​w momencie zatwierdzenia wszystkie wartości będą unikalne, to się powiedzie.

Omówiony powyżej przykład jest oczywiście bardzo syntetyczny, ale oddaje ideę. W naszej aplikacji wykorzystujemy odroczone ograniczenia do implementacji logiki odpowiedzialnej za rozwiązywanie konfliktów, gdy użytkownicy jednocześnie pracują ze udostępnionymi obiektami widgetów na tablicy. Stosowanie takich ograniczeń pozwala nam nieco uprościć kod aplikacji.

Ogólnie rzecz biorąc, w zależności od rodzaju ograniczeń, Postgres ma trzy poziomy szczegółowości ich sprawdzania: poziomy wierszy, transakcji i wyrażeń.

Źródło: żebracy

CHECK i NOT NULL są zawsze sprawdzane na poziomie wiersza; w przypadku innych ograniczeń, jak widać z tabeli, istnieją różne opcje. Możesz przeczytać więcej tutaj.

Krótko mówiąc, odroczone ograniczenia w wielu sytuacjach zapewniają bardziej czytelny kod i mniej poleceń. Trzeba jednak za to zapłacić, komplikując proces debugowania, gdyż moment wystąpienia błędu i moment, w którym się o nim dowiesz, są oddzielone w czasie. Innym możliwym problemem jest to, że osoba planująca nie zawsze będzie w stanie skonstruować optymalny plan, jeśli żądanie obejmuje ograniczenie odroczone.

Ulepszenie pg_repack

Omówiliśmy już, czym są ograniczenia odroczone, ale jaki mają one związek z naszym problemem? Przypomnijmy błąd, który otrzymaliśmy wcześniej:

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

Występuje, gdy dane są kopiowane z tabeli dziennika do nowej tabeli. Wygląda to dziwnie, ponieważ... dane w tabeli dziennika są zatwierdzane wraz z danymi w tabeli źródłowej. Jeśli spełniają ograniczenia oryginalnej tabeli, jak mogą naruszyć te same ograniczenia w nowej?

Jak się okazuje, źródło problemu leży w poprzednim kroku pg_repack, który tworzy tylko indeksy, ale nie ograniczenia: stara tabela miała unikalne ograniczenie, a nowa zamiast tego utworzyła unikalny indeks.

Należy tutaj zauważyć, że jeśli ograniczenie jest normalne i nie jest odroczone, wówczas utworzony zamiast tego unikalny indeks jest równoważny temu ograniczeniu, ponieważ Unikalne ograniczenia w Postgresie są implementowane poprzez utworzenie unikalnego indeksu. Jednak w przypadku ograniczenia odroczonego zachowanie nie jest takie samo, ponieważ indeksu nie można odłożyć i jest on zawsze sprawdzany w momencie wykonania polecenia sql.

Istota problemu polega zatem na „opóźnieniu” sprawdzenia: w oryginalnej tabeli następuje to w momencie zatwierdzenia, a w nowej tabeli w momencie wykonania polecenia sql. Oznacza to, że musimy upewnić się, że kontrole są przeprowadzane w ten sam sposób w obu przypadkach: albo zawsze z opóźnieniem, albo zawsze natychmiast.

Jakie więc mieliśmy pomysły?

Utwórz indeks podobny do odroczonego

Pierwszy pomysł polega na wykonaniu obu kontroli w trybie natychmiastowym. Może to generować kilka fałszywie pozytywnych ograniczeń, ale jeśli jest ich niewiele, nie powinno to mieć wpływu na pracę użytkowników, ponieważ takie konflikty są dla nich normalną sytuacją. Występują one np. wtedy, gdy dwóch użytkowników jednocześnie rozpoczyna edycję tego samego widgetu, a klient drugiego użytkownika nie ma czasu na otrzymanie informacji, że edycja widgetu jest już zablokowana przez pierwszego użytkownika. W takiej sytuacji serwer odrzuca drugiego użytkownika, a jego klient wycofuje zmiany i blokuje widget. Nieco później, gdy pierwszy użytkownik zakończy edycję, drugi otrzyma informację, że widget nie jest już zablokowany i będzie mógł powtórzyć swoją akcję.

Aby mieć pewność, że kontrole zawsze odbywają się w trybie nieodroczonym, stworzyliśmy nowy indeks podobny do pierwotnego ograniczenia odroczonego:

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

W środowisku testowym otrzymaliśmy tylko kilka oczekiwanych błędów. Powodzenie! Uruchomiliśmy ponownie pg_repack na produkcji i otrzymaliśmy 5 błędów w pierwszym klastrze w ciągu godziny pracy. Jest to akceptowalny wynik. Jednak już w drugim klastrze liczba błędów znacznie wzrosła i musieliśmy zatrzymać pg_repack.

Dlaczego to się stało? Prawdopodobieństwo wystąpienia błędu zależy od tego, ilu użytkowników pracuje jednocześnie z tymi samymi widżetami. Najwyraźniej w tym momencie zmian konkurencyjnych z danymi przechowywanymi w pierwszym klastrze było znacznie mniej niż w pozostałych, tj. po prostu mieliśmy „szczęście”.

Pomysł nie wypalił. W tym momencie widzieliśmy dwa inne rozwiązania: przepisać nasz kod aplikacji, aby zrezygnować z odroczonych ograniczeń, lub „nauczyć” pg_repack pracy z nimi. Wybraliśmy to drugie.

Zastąp indeksy w nowej tabeli odroczonymi ograniczeniami z oryginalnej tabeli

Cel rewizji był oczywisty - jeśli oryginalna tabela ma odroczone ograniczenie, to dla nowej trzeba utworzyć takie ograniczenie, a nie indeks.

Aby przetestować nasze zmiany, napisaliśmy prosty test:

• tabela z odroczonym ograniczeniem i jednym rekordem;
• wstaw dane do pętli, która koliduje z istniejącym rekordem;
• wykonaj aktualizację – dane nie są już sprzeczne;
• zatwierdzić zmiany.

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;

Oryginalna wersja pg_repack zawsze zawieszała się przy pierwszej wstawce, zmodyfikowana wersja działała bez błędów. Świetnie.

Przechodzimy do produkcji i ponownie wyskakuje błąd na tej samej fazie kopiowania danych z tabeli logów do nowej:

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

Klasyczna sytuacja: wszystko działa na środowiskach testowych, ale nie na produkcji?!

APPLY_COUNT i połączenie dwóch partii

Zaczęliśmy analizować kod dosłownie linia po linii i odkryliśmy ważny punkt: dane są przesyłane z tabeli dziennika do nowej partiami, stała APPLY_COUNT wskazywała rozmiar partii:

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

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

Problem w tym, że dane z pierwotnej transakcji, w której kilka operacji mogłoby potencjalnie naruszyć ograniczenie, po przesłaniu mogą wylądować na styku dwóch partii – połowa poleceń zostanie wykonana w pierwszej partii, a druga połowa w sekundę. I tutaj, w zależności od szczęścia: jeśli drużyny w pierwszej partii niczego nie naruszą, to wszystko jest w porządku, ale jeśli tak się stanie, pojawia się błąd.

APPLY_COUNT równa się 1000 rekordów, co wyjaśnia, dlaczego nasze testy zakończyły się sukcesem - nie obejmowały przypadku „połączenia wsadowego”. Użyliśmy dwóch poleceń - wstaw i aktualizuj, więc w partii zawsze umieszczano dokładnie 500 transakcji dwóch poleceń i nie doświadczyliśmy żadnych problemów. Po dodaniu drugiej aktualizacji nasza edycja przestała działać:

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;

Zatem kolejnym zadaniem jest upewnienie się, że dane z oryginalnej tabeli, które zostały zmienione w jednej transakcji, znajdą się w nowej tabeli również w ramach jednej transakcji.

Odmowa dozowania

I znowu mieliśmy dwa rozwiązania. Po pierwsze: zrezygnujmy całkowicie z dzielenia na partie i przenieśmy dane w jednej transakcji. Zaletą tego rozwiązania była jego prostota - wymagane zmiany w kodzie były minimalne (swoją drogą, w starszych wersjach pg_reorg działało dokładnie tak). Jest jednak problem – tworzymy transakcję długoterminową, a to, jak już wcześniej powiedziano, grozi pojawieniem się nowego wzdęcia.

Drugie rozwiązanie jest bardziej złożone, ale chyba bardziej poprawne: utwórz w tabeli logów kolumnę zawierającą identyfikator transakcji, która dodała dane do tabeli. Następnie kopiując dane, możemy je pogrupować według tego atrybutu i mieć pewność, że powiązane zmiany zostaną przeniesione razem. Paczka będzie utworzona z kilku transakcji (lub jednej dużej) i jej wielkość będzie się różnić w zależności od tego, ile danych zostało zmienionych w tych transakcjach. Należy pamiętać, że ponieważ dane z różnych transakcji wprowadzane są do tabeli dziennika w losowej kolejności, nie będzie już możliwe ich sekwencyjne odczytywanie, jak miało to miejsce wcześniej. seqscan dla każdego żądania z filtrowaniem według tx_id jest zbyt drogie, potrzebny jest indeks, ale również spowolni metodę ze względu na obciążenie związane z jej aktualizacją. Generalnie jak zawsze trzeba coś poświęcić.

Postanowiliśmy więc zacząć od pierwszej opcji, ponieważ jest ona prostsza. Najpierw należało zrozumieć, czy długa transakcja nie będzie stanowić realnego problemu. Ponieważ główne przeniesienie danych ze starej tabeli do nowej również odbywa się w jednej długiej transakcji, pytanie przekształciło się w „o ile zwiększymy tę transakcję?” Czas trwania pierwszej transakcji zależy głównie od wielkości stołu. Czas trwania nowego zależy od tego, ile zmian zgromadzi się w tabeli podczas przesyłania danych, tj. od intensywności obciążenia. Uruchomienie pg_repack nastąpiło w czasie minimalnego obciążenia usługi, a ilość zmian była nieproporcjonalnie mała w porównaniu z pierwotnym rozmiarem tabeli. Uznaliśmy, że czas nowej transakcji możemy pominąć (dla porównania średnio jest to 1 godzina i 2-3 minuty).

Eksperymenty wypadły pozytywnie. Uruchom także produkcję. Dla jasności oto obrazek z rozmiarem jednej z baz danych po uruchomieniu:

Ponieważ byliśmy w pełni usatysfakcjonowani tym rozwiązaniem, nie próbowaliśmy wdrażać drugiego, ale rozważamy możliwość omówienia go z twórcami rozszerzenia. Nasza obecna wersja niestety nie jest jeszcze gotowa do publikacji, ponieważ rozwiązaliśmy problem jedynie z unikalnymi odroczonymi ograniczeniami, a w przypadku pełnoprawnej łatki konieczne jest zapewnienie obsługi innych typów. Mamy nadzieję, że uda nam się to zrobić w przyszłości.

Być może masz pytanie, dlaczego w ogóle zajęliśmy się tą historią z modyfikacją pg_repack, a nie skorzystaliśmy np. z jego analogów? W pewnym momencie też o tym pomyśleliśmy, jednak pozytywne doświadczenia z wcześniejszego użycia go, na stołach bez odroczonych ograniczeń, zmotywowały nas do podjęcia próby zrozumienia istoty problemu i naprawienia go. Poza tym korzystanie z innych rozwiązań również wymaga czasu na przeprowadzenie testów, dlatego zdecydowaliśmy, że najpierw spróbujemy naprawić w nim problem, a jeśli zdamy sobie sprawę, że nie uda nam się tego zrobić w rozsądnym czasie, to zaczniemy szukać analogów .

odkrycia

Co możemy polecić na podstawie własnego doświadczenia:

1. Monitoruj swoje wzdęcia. Na podstawie danych monitorowania można zrozumieć, jak dobrze skonfigurowana jest automatyczna próżnia.
2. Dostosuj AUTOVACUUM, aby utrzymać wzdęcia na akceptowalnym poziomie.
3. Jeśli wzdęcia nadal rosną i nie możesz sobie z nimi poradzić za pomocą gotowych narzędzi, nie bój się skorzystać z zewnętrznych rozszerzeń. Najważniejsze jest, aby wszystko dobrze przetestować.
4. Nie bój się modyfikować rozwiązań zewnętrznych pod swoje potrzeby - czasami może to być skuteczniejsze i nawet łatwiejsze niż zmiana własnego kodu.

Źródło: www.habr.com