PostgreSQL implementuje Kontrolę Współbieżności Wielowersyjnej (MVCC), tworząc nowe wersje wierszy dla każdego UPDATE i DELETE, zamiast nadpisywać dane w miejscu. Taki projekt eliminuje blokady odczytu, ale pozostawia "martwe krotki" w stercie, które muszą być odzyskane przez proces VACUUM. Gdy tabela doświadcza obciążeń o wysokiej rotacji — takich jak częste aktualizacje statusów oczekujących rekordów lub przełączanie opóźnionych usunięć — nagromadzenie martwych krotek przewyższa proces odkurzania, prowadząc do bloatu tabeli, bloatu indeksów i degradacji wydajności zapytań.
Główny problem wynika z tego, że VACUUM musi przeskanować całą tabelę, aby zidentyfikować martwe krotki, a w monolitycznych, wielotera-bajtowych tabelach ta operacja staje się ograniczona przez I/O i może nie zakończyć się przed obiegiem identyfikatorów transakcji. Bez interwencji tabela rośnie w nieskończoność, mimo że faktyczna liczba wierszy pozostaje stabilna, zużywając nadmierną ilość miejsca i spowalniając skanowanie indeksów.
Rozwiązaniem jest implementacja Deklaratywnego Partycjonowania z użyciem strategii RANGE na kluczu czasowym lub logicznym, takim jak data utworzenia. Poprzez podział tabeli na mniejsze fizyczne partycje (np. miesięczne segmenty), operacje odkurzania działają na poszczególnych partycjach, a nie na całej tabeli. Dodatkowo, PRUNING PARTYCJI zapewnia, że zapytania uzyskują dostęp tylko do odpowiednich segmentów, a starsze partycje mogą być odłączane i archiwizowane, natychmiast odzyskując miejsce bez kosztów odkurzania.
-- Tabela nadrzędna z partycjonowaniem zakresu CREATE TABLE iot_sensor_data ( sensor_id INT NOT NULL, temperature NUMERIC, recorded_at TIMESTAMP NOT NULL, status VARCHAR(20) ) PARTITION BY RANGE (recorded_at); -- Miesięczna partycja dla niedawnych danych o wysokiej rotacji CREATE TABLE iot_sensor_data_2024_06 PARTITION OF iot_sensor_data FOR VALUES FROM ('2024-06-01') TO ('2024-07-01'); -- Indeks na kluczu partycji CREATE INDEX idx_sensor_date ON iot_sensor_data (recorded_at);
Klient z branży inteligentnej produkcji prowadził bazę danych PostgreSQL 14, która przetwarzała telemetry z 50 000 czujników IoT, generując 10 milionów odczytów dziennie, z czego 30% wymagało aktualizacji statusu w ciągu 24 godzin. Tabela sensor_logs urosła do 2TB w ciągu sześciu miesięcy, ponieważ AUTOVACUUM nie mógł przetwarzać tabeli wystarczająco szybko, aby odzyskać martwe krotki z nieustannych aktualizacji flagi statusu. Latencja zapytań wzrosła do 30 sekund, a pg_class ujawniło, że tabela była 400% przepełniona w stosunku do swojego logicznego rozmiaru.
Oceniono trzy rozwiązania architektoniczne. Pierwsze podejście polegało na agresywnym dostrajaniu VACUUM, zmniejszając autovacuum_vacuum_scale_factor do 0.02 i zwiększając maintenance_work_mem do 2GB. Choć to pomogło nieco, proces wymagał ciągłego skanowania całej tabeli o wielkości 2TB, powodując poważną konkurencję I/O z zapytaniami produkcyjnymi i nie kończąc się w nocnych oknach konserwacyjnych. Druga strategia zaproponowała PARTIAL INDEXES wykluczające stare dane, co zredukowało bloat indeksów, ale nie rozwiązało podstawowego problemu bloatu sterty ani nagromadzenia martwych krotek w samej tabeli.
Trzecie rozwiązanie wdrożyło DEKLARATYWNE PARTYCJONOWANIE w oparciu o RANGE na znaczniku czasu recorded_at, tworząc miesięczne partycje o średnio 300GB każda. To podejście zostało wybrane, ponieważ lokalizowało aktywność o wysokiej rotacji do partycji bieżącego miesiąca, co pozwalało VACUUM przetwarzać 300GB zamiast 2TB podczas każdej operacji. Ponadto, partycje starsze niż rok były ODŁĄCZANE i przenoszone do zimnego magazynu, odzyskując przestrzeń bez kosztownych operacji DELETE. Po wdrożeniu czas odkurzania spadł z 8 godzin do 45 minut, zniknęły ryzyka obiegu identyfikatorów transakcji, a wydajność zapytań poprawiła się dziesięciokrotnie dzięki odcięciu partycji.
Jak mechanizm HOT (Heap-Only Tuple) w PostgreSQL redukuje bloat indeksów i w jakim konkretnym przypadku przestaje działać?
Aktualizacje HOT występują, gdy wiersz jest aktualizowany, ale żadne kolumny indeksowane nie są modyfikowane, a w obrębie tej samej strony danych 8KB znajduje się wystarczająca ilość wolnego miejsca. W tym scenariuszu PostgreSQL łączy nową krotkę ze starą w obrębie sterty, nie tworząc nowych wpisów indeksowych, co znacznie redukuje koszty utrzymania indeksu. Jednakże, HOT nie działa natychmiast, jeśli aktualizacja modyfikuje jakąkolwiek kolumnę obecnie znajdującą się w jakimkolwiek indeksie lub jeśli strona nie ma wolnego miejsca poniżej progu fillfactor, co zmusza PostgreSQL do pisania nowych wskaźników indeksowych i tworzenia bloatu. Kandydaci często zakładają, że wszystkie operacje UPDATE wpływają na indeksy w równym stopniu, nie zdając sobie sprawy, że aktualizacja kolumn nieskładowanych jest znacznie tańsza, gdy sukcesywnie korzysta się z HOT.
Jaka jest dokładna różnica między VACUUM, VACUUM FULL a CLUSTER pod względem zachowań blokujących i dostępności tabeli?
VACUUM działa jednocześnie ze wszystkimi operacjami, oznaczając martwe krotki jako ponownie używalną przestrzeń, bez oddawania przestrzeni systemowi operacyjnemu; nie zajmuje żadnych blokad na danych użytkowników. VACUUM FULL przepisuje cały plik tabeli, aby całkowicie wyeliminować bloat, ale nabywa blokadę ACCESS EXCLUSIVE, która blokuje wszystkie operacje odczytu i zapisu przez czas trwania, co może trwać godziny w przypadku dużych tabel. CLUSTER fizycznie przestawia tabelę, aby odpowiadała kolejności indeksu, również wymagając blokady ACCESS EXCLUSIVE, i jest generalnie wolniejszy niż VACUUM FULL dla czystej rekultywacji miejsca, ale zachowuje uporządkowaną kolejność dla przyszłych skanów indeksów zgrupowanych. Kandydaci często niebezpiecznie polecają VACUUM FULL do rutynowej konserwacji, nie rozumiejąc, że powoduje to całkowitą niedostępność tabeli.
Jak mapa widoczności wpływa na wydajność skanowania tylko indeksu i dlaczego częstotliwość odkurzania ma znaczenie dla tej metody dostępu?
Mapa widoczności to binarny bitmap przechowywany razem z tabelą, który śledzi, które strony sterty zawierają tylko krotki widoczne dla wszystkich bieżących i przyszłych transakcji. SKANOWANIE TYLKO INDEKSU może zaspokoić zapytania, używając tylko indeksu i mapy widoczności, bez pobierania krotek ze sterty, ale tylko wtedy, gdy mapa potwierdza, że wszystkie wiersze na tej stronie są widoczne. Jeśli strona zawiera jakiekolwiek martwe krotki lub niezatwierdzone transakcje, bit widoczności jest wyłączony, zmuszając bazę danych do weryfikacji poszczególnych krotek ze sterty. Częste VACUUM aktualizuje bity mapy widoczności, umożliwiając prawdziwy dostęp tylko do indeksu; bez niego, nawet zapytania korzystające z pokrywających indeksów ponoszą przypadkowe I/O, aby sprawdzić widoczność krotek, co uniemożliwia realizację celu optymalizacji.