Dokładny pomiar fragmentacji tabel i indeksów

PostgreSQL używa mechanizmu MVCC (wielowersyjnej kontroli współbieżności). Gdy wykonują Państwo UPDATE lub DELETE dla wiersza, jego stara wersja nie jest od razu usuwana. Staje się martwym krotką, która nadal zajmuje miejsce, dopóki VACUUM nie oznaczy jej jako możliwej do ponownego użycia.

• Puchnięcie = miejsce zajmowane przez martwe krotki oraz niewykorzystane wolne miejsce, którego tabela lub indeks już nie potrzebuje.
• Puchnięcie zwiększa rozmiar danych na dysku, spowalnia skanowania sekwencyjne i zmniejsza efektywność pamięci podręcznej.
• Indeksy również puchną: strony B-tree zachowują wskaźniki do martwych krotek sterty, dopóki nie zostaną wyczyszczone.

Przed wyborem rozwiązania (VACUUM, VACUUM FULL, pg_repack lub REINDEX) należy najpierw zmierzyć, jak duże jest rzeczywiste puchnięcie. Zgadywanie prowadzi do zbędnych i zakłócających prac utrzymaniowych.

Najtańszy pierwszy sygnał pochodzi ze zbieracza statystyk. pg_stat_user_tables śledzi szacunkową liczbę żywych i martwych krotek w każdej tabeli; dane te są aktualizowane przez ANALYZE i autovacuum.

• n_live_tup — szacunkowa liczba żywych wierszy.
• n_dead_tup — szacunkowa liczba martwych wierszy oczekujących na oczyszczenie.
• Wysoki stosunek n_dead_tup sugeruje, że autovacuum nie nadąża.

Jest to szacunek, a nie pomiar z dokładnością do bajta, ale nic nie kosztuje i świetnie nadaje się do wstępnej selekcji.

Istnieją dwie grupy metod pomiaru puchnięcia, z których każda ma swoje kompromisy:

• Zapytania szacujące odczytują wyłącznie statystyki katalogowe (pg_class, pg_statistic). Są szybkie i nie wymagają blokad, ale przybliżone — ich dokładność zależy od aktualności ANALYZE i założeń dotyczących szerokości kolumn.
• pgstattuple fizycznie skanuje relację, aby policzyć dokładną liczbę bajtów żywych i martwych. Jest precyzyjne, ale na dużych tabelach intensywnie obciąża operacje wejścia/wyjścia.

Praktyczny sposób pracy: użyć taniego szacowania do znalezienia kandydatów, a następnie użyć pgstattuple do potwierdzenia najgorszych przypadków przed podjęciem działań naprawczych.

pgstattuple to rozszerzenie contrib dostarczane razem z PostgreSQL. Przed użyciem należy je włączyć osobno w każdej bazie danych.

• Włączenie rozszerzenia wymaga uprawnień superużytkownika lub roli z uprawnieniem CREATE do bazy danych.
• Do uruchamiania jego funkcji dla dowolnych relacji wymagana jest rola pg_stat_scan_tables (lub uprawnienia superużytkownika).

Po zainstalowaniu dostępne są funkcje pgstattuple(), pgstatindex() oraz lżejsza pgstattuple_approx().

pgstattuple('relation') wykonuje pełne skanowanie i zwraca jeden wiersz zawierający dokładne informacje o stercie w bajtach.

• table_len — całkowity rozmiar relacji w bajtach.
• tuple_count / tuple_len — liczba i łączny rozmiar żywych krotek.
• dead_tuple_count / dead_tuple_len — liczba martwych krotek i zajmowane przez nie bajty.
• free_space / free_percent — wolne miejsce możliwe do ponownego użycia.

Najważniejszym sygnałem puchnięcia jest dead_tuple_percent wraz z free_percent: wartości te pokazują łącznie, jaka część pliku nie zawiera żywych danych.

pgstattuple() odczytuje każdą stronę relacji. W przypadku tabeli o rozmiarze 500 GB oznacza to dużo operacji wejścia/wyjścia i może usunąć przydatne dane z pamięci podręcznej.

• Funkcja pobiera tylko blokadę ACCESS SHARE, więc nie blokuje odczytów ani zapisów — obciążenie operacjami wejścia/wyjścia jest jednak rzeczywiste.
• W przypadku dużych tabel należy preferować pgstattuple_approx(), które korzysta z mapy widoczności, pomija strony w pełni widoczne i próbuje pozostałe.
• approx zwraca wartości approx_free_percent i dead_tuple_percent zbliżone do dokładnych, przy ułamku kosztu.

Praktyczna zasada: najpierw szacować, na tabelach średniej wielkości uruchamiać approx, a dokładne pgstattuple() stosować dopiero do ostatecznego potwierdzenia w przypadku konkretnego podejrzanego obiektu.

Indeksy puchną niezależnie od tabeli, do której należą. W przypadku indeksów B-tree należy użyć pgstatindex(), aby uzyskać szczegółowe informacje o ich strukturze.

• avg_leaf_density — odsetek stron liści wypełnionych użytecznymi danymi. Zdrowe indeksy osiągają wartości bliskie 90%; spadek w kierunku 50% sygnalizuje silne puchnięcie.
• leaf_fragmentation — stopień nieuporządkowania stron liści; wysoka fragmentacja pogarsza skanowania zakresów.
• index_size oraz internal_pages / leaf_pages opisują strukturę drzewa.

Niska wartość avg_leaf_density jest najmocniejszym argumentem za użyciem REINDEX (najlepiej REINDEX ... CONCURRENTLY).

Gdy nie można sobie pozwolić na skanowanie, społecznościowe zapytanie szacujące puchnięcie (z check_postgres / pgsql-bloat-estimation) oblicza oczekiwany rozmiar na podstawie statystyk i porównuje go z rzeczywistym rozmiarem.

Jego podstawowa idea jest następująca:

• Pobrać średnią szerokość wiersza z pg_statistic (wartość avg_width dla każdej kolumny zarejestrowaną przez ANALYZE).
• Dodać narzut nagłówka krotki i wyrównania, a następnie podzielić rozmiar tabeli przez oczekiwaną liczbę krotek na stronę.
• Różnica między oczekiwaną a rzeczywistą liczbą stron to szacowane puchnięcie.

Wynik jest przybliżony i wrażliwy na nieaktualne statystyki, ale zapytanie wykonuje się w milisekundach dla całej bazy danych.

Dokładność szacowania gwałtownie spada, gdy dane wejściowe są nieprawidłowe. Należy uważać na następujące pułapki:

• Nieaktualne statystyki: jeśli ANALYZE nie uruchamiano niedawno, wartości avg_width i liczby wierszy są nieaktualne. Przed zaufaniem szacunkom należy uruchomić ANALYZE.
• Szerokie kolumny o zmiennej długości: duża zmienność szerokości wartości text/jsonb sprawia, że średnie dla wierszy są niewiarygodne.
• TOAST: duże wartości przechowywane poza wierszem znajdują się w osobnej tabeli TOAST; szacunki dla sterty całkowicie pomijają to miejsce.
• Fillfactor: tabela utworzona z fillfactor < 100 celowo pozostawia wolne miejsce — nie jest to puchnięcie.

Zaskakujący wynik szacowania należy zawsze zweryfikować za pomocą pgstattuple_approx() przed podjęciem działań.

Duże wartości kolumn są przenoszone do ukrytej tabeli TOAST, która puchnie niezależnie. Sterta może wyglądać na uporządkowaną, podczas gdy powiązana z nią relacja TOAST jest ogromna.

• Relację TOAST można znaleźć za pomocą pg_class.reltoastrelid.
• Należy uruchomić pgstattuple() bezpośrednio dla identyfikatora OID relacji TOAST, aby zmierzyć zajmowane przez nią martwe miejsce.

Tabele z często aktualizowanymi kolumnami jsonb lub bytea często ukrywają większość puchnięcia właśnie w TOAST.

Po uzyskaniu dokładnych danych należy przypisać je do odpowiedniej ścieżki naprawczej:

• wysokie dead_tuple_percent i wysokie free_percent: autovacuum nie nadąża — zwykłe VACUUM (lub dostrojenie autovacuum) zazwyczaj odzyska miejsce możliwe do ponownego użycia bez przepisywania pliku;
• bardzo wysokie free_percent, ale tabela się nie zmniejsza: plik zawiera wolne miejsce na końcu, którego VACUUM nie może zwrócić systemowi operacyjnemu — należy rozważyć pg_repack (online) lub VACUUM FULL (blokuje tabelę);
• niska wartość Index avg_leaf_density: REINDEX CONCURRENTLY.

Należy ustawić próg (np. działać dopiero powyżej około 20% puchnięcia i przy znaczącym rozmiarze bezwzględnym), aby nie uruchamiać zakłócających prac utrzymaniowych dla niewielkich korzyści.

Mają już Państwo warstwową metodę określania puchnięcia przed podjęciem działań:

• Wstępna selekcja za pomocą pg_stat_user_tables (stosunek n_dead_tup) — bezpłatna i natychmiastowa.
• Szacowanie dla całej bazy danych za pomocą zapytań opartych na statystykach — szybkie, ale wymagające weryfikacji za pomocą aktualnego ANALYZE.
• Precyzyjne potwierdzenie za pomocą pgstattuple() lub pgstattuple_approx() dla dużych tabel, z odczytem dead_tuple_percent i free_percent.
• Indeksy: należy używać pgstatindex() i obserwować avg_leaf_density oraz leaf_fragmentation.
• Nie należy zapominać o TOAST ani pomijać celowo pozostawionego wolnego miejsca wynikającego z fillfactor.

Dopiero gdy wartości przekroczą istotny próg, należy wybrać VACUUM, pg_repack, VACUUM FULL lub REINDEX — pomiary wyznaczają działania naprawcze, nigdy odwrotnie.