VACUUM FULL blokuje tabelę na godziny - jak odzyskać miejsce online przez pg_repack

PostgreSQL

VACUUM FULL blokuje tabelę na godziny - jak odzyskać miejsce online przez pg_repack

W skrócie

Tabela po latach aktualizacji i usuwania wierszy zajmuje kilka razy więcej, niż wymagają dane, a odzyskanie miejsca przez VACUUM FULL wymaga okna serwisowego. Ten problem dotyczy systemów, które nie mogą zatrzymać odczytu ani zapisu na czas przebudowy dużej tabeli. Pokażemy na PostgreSQL 16.4, jaką blokadę zakłada VACUUM FULL i jak zobaczyć ją w pg_locks, a potem przeprowadzimy przebudowę online narzędziem pg_repack.

Jak to wygląda w praktyce

Po uruchomieniu VACUUM FULL każde zapytanie do tabeli czeka, także zwykły SELECT. Odtworzyliśmy to na PostgreSQL 16.4 na tabeli orders_big z 3 mln wierszy, która po UPDATE wszystkich wierszy zajmowała 754 MB. W pierwszej sesji uruchomiliśmy VACUUM FULL orders_big, w dwóch kolejnych SELECT count(*) FROM orders_big, a z czwartej podejrzeliśmy blokady:

SELECT left(a.query, 31) AS query, l.mode, l.granted
FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation = 'orders_big'::regclass
  AND l.pid <> pg_backend_pid();

              query              |        mode         | granted
---------------------------------+---------------------+---------
 VACUUM FULL orders_big          | AccessExclusiveLock | t
 SELECT count(*) FROM orders_big | AccessShareLock     | f
 SELECT count(*) FROM orders_big | AccessShareLock     | f

VACUUM FULL ma przyznaną blokadę AccessExclusiveLock, a oba SELECT czekają (granted = f, w pg_stat_activity mają wait_event_type = 'Lock'). Zapytanie z ustawionym lock_timeout = '1s' zakończyło się błędem:

ERROR:  canceling statement due to lock timeout

To samo dzieje się, gdy VACUUM FULL dopiero czeka na blokadę, bo trwa długa transakcja czytająca tabelę. W naszym teście nowy SELECT ustawił się w kolejce za oczekującym VACUUM FULL i po 3 sekundach przerwał go lock_timeout tym samym błędem (SQLSTATE 55P03).

Zobacz też: Szkolenie Administracja, replikacja i tuning baz danych PostgreSQL

Dlaczego tak się dzieje

VACUUM FULL przepisuje całą tabelę do nowego pliku bez wolnego miejsca i przez cały czas pracy trzyma blokadę ACCESS EXCLUSIVE. Ta blokada koliduje ze wszystkimi trybami, także z ACCESS SHARE, który bierze zwykły SELECT. Czas blokady rośnie z rozmiarem tabeli: w naszym teście 754 MB przebudowało się do 377 MB w 2,8 s, ale duże tabele produkcyjne potrafią wymagać okna serwisowego na kilka, kilkanaście, a nawet kilkadziesiąt godzin.

Zwykły VACUUM i autovacuum biorą słabszą blokadę SHARE UPDATE EXCLUSIVE: w naszym teście UPDATE wykonany w trakcie zwykłego VACUUM skończył się po 0,01 s. Zwykły VACUUM nie oddaje jednak miejsca systemowi operacyjnemu, więc plik tabeli nie maleje.

pg_repack, czyli rozszerzenie do przebudowy tabel i indeksów online, działa inaczej. Na krótko bierze ACCESS EXCLUSIVE, żeby założyć tabelę logu i trigger zapisujący do niej zmiany. Potem kopiuje wiersze do nowej tabeli, buduje indeksy i nanosi zmiany z logu, trzymając tylko SHARE UPDATE EXCLUSIVE, więc INSERT, UPDATE i DELETE działają normalnie (do wersji 1.5.2 włącznie pg_repack trzymał w tej fazie ACCESS SHARE, zmianę wprowadziło wydanie 1.5.3). Na koniec znowu na chwilę bierze ACCESS EXCLUSIVE, podmienia tabele w katalogu systemowym i usuwa starą. W trakcie pracy pg_repack nie wykonasz poleceń DDL na tej tabeli.

Jak to rozwiązać krok po kroku

1. Zainstaluj pakiet dla swojej wersji serwera. Na Red Hat, Rocky i Alma z repozytorium PGDG: sudo dnf install -y pg_repack_16, na Debianie i Ubuntu z repozytorium PGDG: sudo apt-get install -y postgresql-16-repack. Potem w bazie z tabelą wykonaj CREATE EXTENSION pg_repack;.
2. Sprawdź wymagania. Tabela musi mieć klucz główny albo indeks unikalny bez warunku WHERE na kolumnach NOT NULL. Oba przypadki pokaże SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders' AND indexdef LIKE 'CREATE UNIQUE%';, bo indeks klucza głównego też ma tu definicję CREATE UNIQUE INDEX. Pusty wynik oznacza, że pg_repack zgłosi must have a primary key or not-null unique keys. Wolnego miejsca potrzebujesz około dwa razy tyle, ile zajmuje tabela z indeksami: SELECT pg_size_pretty(pg_total_relation_size('orders'));. Narzędzie uruchamia superużytkownik albo właściciel tabeli, wtedy z opcją -k.
3. Upewnij się, że nie trwa długa transakcja, bo pg_repack potrzebuje na początku i na końcu krótkiej blokady wyłącznej:

   SELECT pid, now() - xact_start AS xact_age, state,
          left(query, 40) AS query
   FROM pg_stat_activity
   WHERE xact_start IS NOT NULL
     AND pid <> pg_backend_pid()
   ORDER BY xact_start;

4. Zrób próbę bez zmian. Polecenie pg_repack -h 127.0.0.1 -U postgres -d shop -t public.orders --dry-run tylko wypisuje, co zostałoby przebudowane.
5. Uruchom przebudowę: pg_repack -h 127.0.0.1 -U postgres -d shop -t public.orders -j 4 -T 60. Opcja -j buduje indeksy równolegle dodatkowymi połączeniami. -T to liczba sekund oczekiwania na blokadę wyłączną (domyślnie 60): gdy minie, pg_repack anuluje kolidujące zapytania, a po dwukrotności tego okresu rozłącza sesje funkcją pg_terminate_backend(). Jeśli wolisz pominąć tabelę, zamiast przerywać cudze zapytania, dodaj -D.
6. Gdy przebieg zostanie przerwany, a kolejny zgłosi ostrzeżenie, że tabela ma już trigger repack_trigger, posprzątaj ręcznie w tej bazie: DROP EXTENSION pg_repack CASCADE;, a potem ponownie CREATE EXTENSION pg_repack;.

Jak sprawdzić, że zadziałało

W trakcie przebudowy uruchom z drugiej sesji zapytanie do pg_locks z początku tekstu, z nazwą swojej tabeli zamiast orders_big. Zamiast AccessExclusiveLock trzymanej przez cały czas zobaczysz ShareUpdateExclusiveLock sesji pg_repack, a zapytania aplikacji poza krótkimi chwilami na początku i końcu nie będą czekać. Liczbę sesji czekających na blokady sprawdzisz tak:

SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock';

Po zakończeniu porównaj pg_total_relation_size('orders') z wartością sprzed operacji i upewnij się, że nie został trigger pomocniczy: SELECT tgname FROM pg_trigger WHERE tgrelid = 'orders'::regclass AND NOT tgisinternal; powinno zwrócić tylko triggery Twojej aplikacji.

Powiązane problemy

• Jeśli zastanawiasz się, czy w ogóle potrzebujesz przebudowy, przejdź do tekstu VACUUM vs VACUUM FULL, czyli czym się różnią i kiedy który.
• Zanim wybierzesz tabelę do przebudowy, zmierz ją według tekstu jak sprawdzić rozmiar bazy, tabeli i indeksów.
• Gdy aplikacja już czeka na blokadę, sesję blokującą znajdziesz według tekstu zapytanie wisi zablokowane, czyli jak znaleźć blokującą sesję.

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