Tabela puchnie mimo usuwania wierszy - czym jest bloat

PostgreSQL

Tabela puchnie mimo usuwania wierszy - czym jest bloat

W skrócie

Puchnąca tabela przy stałej liczbie rekordów to jeden z najczęstszych alarmów na produkcji PostgreSQL. To nie błąd ani wyciek pamięci - to naturalna konsekwencja MVCC. Pokazujemy, jak zmierzyć bloat i jak go bezpiecznie usunąć, zanim zapcha dysk.

Jak to wygląda w praktyce

Widzisz to na kilka sposobów. Rozmiar tabeli z SELECT pg_size_pretty(pg_total_relation_size('zamowienia')); rośnie z tygodnia na tydzień, choć SELECT count(*) FROM zamowienia; zwraca w kółko podobną liczbę. Zapytania po tej tabeli zwalniają, bo silnik musi przeczesać więcej stron danych. W skrajnym przypadku indeksy są większe niż same dane. Rozszerzenie pgstattuple pokaże to wprost - kolumna dead_tuple_percent sięga kilkudziesięciu procent.

Dlaczego tak się dzieje

PostgreSQL działa w modelu MVCC (Multi-Version Concurrency Control). Każde UPDATE nie nadpisuje wiersza w miejscu - tworzy jego nową wersję, a stara zostaje jako martwa, żeby wciąż mogły ją widzieć starsze transakcje. DELETE tylko oznacza wiersz jako martwy. Te martwe wersje zajmują miejsce w pliku tabeli aż do momentu, gdy proces VACUUM oznaczy je jako wolne do ponownego użycia.

Jeśli tabela jest mocno obciążona zapisami, a autovacuum nie nadąża (albo długo trwająca transakcja trzyma stary horyzont widoczności i blokuje sprzątanie), martwych wierszy przybywa szybciej niż VACUUM je usuwa. Wolne miejsce owszem wraca do puli tabeli, ale plik na dysku sam z siebie się nie kurczy - dlatego mówimy o bloacie, czyli spuchniętej tabeli.

Jak to rozwiązać krok po kroku

1. Zmierz skalę problemu. Włącz rozszerzenie CREATE EXTENSION IF NOT EXISTS pgstattuple; i sprawdź SELECT * FROM pgstattuple('zamowienia'); - interesują Cię dead_tuple_percent i free_percent. Alternatywnie policz martwe wiersze z widoku: SELECT relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;.
2. Sprawdź, czy sprzątania nie blokuje stara transakcja: SELECT pid, state, xact_start, query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_start; oraz SELECT pid FROM pg_stat_activity WHERE state = 'idle in transaction';. Zamknij lub rozłącz wiszącą transakcję, inaczej żaden VACUUM nie odzyska miejsca.
3. Uruchom zwykły VACUUM, który oznaczy martwe wiersze jako wolne do ponownego użycia bez blokowania odczytów i zapisów: VACUUM (VERBOSE, ANALYZE) zamowienia;. To wystarcza, gdy chcesz zatrzymać dalszy wzrost i odzyskać miejsce wewnątrz pliku.
4. Jeśli plik ma już oddać miejsce systemowi plików (bloat rzędu kilkudziesięciu procent), zaplanuj VACUUM FULL zamowienia;. Uwaga: bierze on blokadę wyłączną (ACCESS EXCLUSIVE) i przepisuje całą tabelę - rób to w oknie serwisowym.
5. Zamiast VACUUM FULL na produkcji rozważ pg_repack - przebudowuje tabelę i indeksy bez długiej blokady wyłącznej. Instalujesz rozszerzenie i uruchamiasz pg_repack -t zamowienia -d twoja_baza.
6. Zapobiegaj nawrotom: dostrój autovacuum dla gorącej tabeli, np. ALTER TABLE zamowienia SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_cost_delay = 2);, żeby czyścił częściej i szybciej.

Jak sprawdzić, że zadziałało

Po zabiegu ponownie zmierz rozmiar: SELECT pg_size_pretty(pg_total_relation_size('zamowienia')); - po VACUUM FULL albo pg_repack powinien wyraźnie spaść. Powtórz SELECT dead_tuple_percent, free_percent FROM pgstattuple('zamowienia');: martwe wiersze powinny być bliskie zeru. Na koniec zajrzyj do pg_stat_user_tables i sprawdź kolumny last_vacuum oraz last_autovacuum - potwierdzają, że sprzątanie faktycznie się odbyło, a nowa konfiguracja działa.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5