Menu

PostgreSQL VACUUM i autovacuum: martwe wiersze i bloat

VACUUM usuwa martwe wersje wierszy, które zostawiają UPDATE i DELETE, żeby ich miejsce mogło zostać ponownie użyte. Autovacuum robi to za ciebie; ta strona pokazuje kiedy, jak go dostroić i kiedy VACUUM FULL jest wart swojej blokady.

Na tej stronie są działające edytory: edytuj, uruchamiaj i od razu zobacz wynik.

VACUUM usuwa martwe wersje wierszy, czyli stare kopie, które UPDATE i DELETE zostawiają w tabeli, i oznacza ich miejsce jako wolne dla nowych wierszy. VACUUM ANALYZE dodatkowo odświeża statystyki, których używa planer. VACUUM FULL przepisuje całą tabelę, żeby oddać miejsce systemowi operacyjnemu. Autovacuum, domyślnie włączony, uruchamia za ciebie VACUUM i ANALYZE w tle.

Po DELETE tabela ma 700 żywych i 300 martwych wierszy; po VACUUM 0 martwych. Widoki statystyk aktualizują się z opóźnieniem; pg_stat_force_next_flush() (PostgreSQL 15+) sprawia, że w tym przykładzie aktualizują się od razu.

Dlaczego PostgreSQL zostawia martwe wiersze

PostgreSQL trzyma stare wersje wierszy, żeby każda transakcja widziała spójny snapshot (MVCC). UPDATE zapisuje nową wersję wiersza i oznacza starą jako wygasłą; DELETE tylko ją oznacza. Stara wersja zostaje, dopóki żadna działająca transakcja nie może jej potrzebować, a wtedy VACUUM może ją usunąć. ctid, fizyczne położenie wiersza, pokazuje przeniesienie:

Wiersz 1 zaczyna w (0,1), strona 0, slot 1, a po aktualizacji jest w (0,3). Slot 1 nadal trzyma starą wersję, dopóki nie zostanie posprzątana. Często aktualizowana tabela ciągle produkuje martwe wiersze, więc autovacuum stale do niej wraca.

VACUUM vs VACUUM FULL

Zwykłe VACUUM zostawia plik w tym samym rozmiarze: wolne miejsce zostaje w tabeli na przyszłe wstawienia. VACUUM FULL zapisuje nową, zwartą kopię i usuwa stary plik.

VACUUMVACUUM FULL
Blokadapozwala na odczyty i zapisyblokuje każdy dostęp
Miejsceużywane ponownie przez tę samą tabelęoddawane systemowi operacyjnemu
Dodatkowe miejsce na dyskuniemiejsce na pełną kopię tabeli
Kiedyregularnie (robi to autovacuum)rzadko, po usunięciu większości dużej tabeli

Opcje podaje się w nawiasach:

  • VACUUM (VERBOSE, ANALYZE) items; wypisuje, co zrobiono, i odświeża statystyki.
  • VACUUM (PARALLEL 4) (PostgreSQL 13+) czyści indeksy równoległymi workerami.
  • VACUUM (SKIP_LOCKED) pomija tabele, których nie może od razu zablokować.

Jak autovacuum decyduje, kiedy działać

Autovacuum sprawdza każdą tabelę co autovacuum_naptime. Czyści tabelę, gdy jej martwe wiersze przekroczą autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * table rows: przy wartościach domyślnych 50 wierszy plus 20 procent tabeli. Analizuje tabelę, gdy liczba wierszy zmienionych od ostatniej analizy przekroczy 50 plus 10 procent. Od PostgreSQL 13 vacuum uruchamiają też same wstawienia. Wartości domyślne w postgresql.conf:

autovacuum = on
autovacuum_naptime = 1min
autovacuum_max_workers = 3
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_insert_threshold = 1000
autovacuum_vacuum_insert_scale_factor = 0.2
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

Przy tabeli ze 100 milionami wierszy 20 procent oznacza 20 milionów martwych wierszy, zanim vacuum ruszy. Obniżaj współczynnik tylko dla dużych, intensywnie używanych tabel:

ALTER TABLE events RESET (autovacuum_vacuum_scale_factor); przywraca tabeli ustawienie z całego serwera. Rozdział o rutynowym vacuum omawia pozostałe ustawienia.

Co zrobił autovacuum

pg_stat_user_tables pokazuje martwe wiersze oraz ostatnie ręczne i automatyczne vacuum każdej tabeli:

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Jeśli martwych wierszy przybywa, choć autovacuum działa, poszukaj starych otwartych transakcji, które nie pozwalają mu usunąć niczego nowszego niż ich snapshot:

SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE xact_start < now() - interval '10 minutes'
ORDER BY xact_start;

Zamrażanie i przekręcenie identyfikatorów transakcji

Identyfikatory transakcji to 32-bitowe liczniki, które się przekręcają. Żeby stare wiersze pozostały widoczne, vacuum je zamraża, oznaczając je jako starsze od każdej transakcji. Gdy najstarszy niezamrożony identyfikator tabeli przekroczy autovacuum_freeze_max_age (domyślnie 200 milionów transakcji), autovacuum uruchamia się na niej nawet wtedy, gdy jest wyłączony.

Jeśli zamrażanie mimo to mocno zostaje w tyle, PostgreSQL przestaje przydzielać nowe identyfikatory transakcji, a zapisy kończą się błędem, dopóki vacuum nie nadrobi zaległości. To zapytanie pokazuje, jak blisko tego jest każda baza:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Najczęściej zadawane pytania

Czy VACUUM blokuje tabelę?

Zwykłe VACUUM zakłada blokadę, która nadal pozwala na SELECT, INSERT, UPDATE i DELETE; koliduje tylko ze zmianami schematu, budową indeksów, ANALYZE i innymi vacuum. VACUUM FULL zakłada blokadę ACCESS EXCLUSIVE, która wstrzymuje każde zapytanie do tabeli do końca.

Czy wyłączyć autovacuum?

Nie. Bez niego martwe wiersze się gromadzą, statystyki się starzeją, a baza w końcu musi przestać przyjmować zapisy, żeby zapobiec przekręceniu licznika identyfikatorów transakcji. Zamiast tego dostrój progi dla poszczególnych tabel.

Dlaczego tabela nie zmniejsza się po VACUUM?

Zwykłe VACUUM oznacza miejsce martwych wierszy jako wolne dla nowych wierszy w tej samej tabeli; oddaje miejsce systemowi operacyjnemu tylko wtedy, gdy puste strony są na końcu pliku. VACUUM FULL przepisuje tabelę i oddaje miejsce.

Dlaczego VACUUM nie usuwa martwych wierszy?

Wiersz można usunąć dopiero wtedy, gdy żadna działająca transakcja nie może go już potrzebować. Blokuje to długo otwarta transakcja (często bezczynna sesja, która wykonała BEGIN), stary slot replikacji albo porzucona transakcja przygotowana.

Ilustracja języków programowania w Coddy

Ucz się programowania z Coddy

Zacznij