Ponieważ powrót do podstaw nikomu jeszcze nie zaszkodził, to proponujemy dziś małe repetytorium z zakresu MVCC i operacji Vacuum. Pamiętamy, że PostgreSQL jest oczywiście systemem transakcyjnym, wspierającym tradycyjne własności ACID dla transakcji realizowanych przez aplikacje użytkowników. W celu prawidłowej realizacji izolacji transakcji, PostgreSQL stosuje technikę MVCC (Multiversion Concurrency Control), w ramach której każda modyfikacja rekordu przyczynia się do powstania jego nowego obrazu, podczas gdy dotychczasowy obraz rekordu jest pozostawiany w tabeli na potrzeby innych transakcji w toku. W podobny sposób, z usunięcie rekordu wiąże się faktycznym pozostawieniem jego starego obrazu dla innych, odizolowanych transakcji. Nieprzyjemnym skutkiem implementacji MVCC w PostgreSQL jest pozostawianie starych obrazów rekordów w bazie danych nawet już po zakończeniu innych, potencjalnie zainteresowanych transakcji (względy wydajnościowe). Te stare obrazy nazywamy wtedy „martwymi rekordami” (dead tuples), ponieważ są one całkowicie zbędne. Aby martwe rekordy nie powodowały nadmiernego marnowania przestrzeni dyskowej i wydajności zapytań, powinny być okresowo „sprzątane” poprzez wykonywanie operacji Vacuum. Manualnie lub automatycznie, przez systemowy proces Autovacuum.
Poniższy rysunek pokazuje rzeczywisty przebieg tych operacji na poziomie dyskowym.
W kroku (1) użytkownik wstawia sześć nowych rekordów do tabeli – rekordy te są zapisywane do kolejnych bloków pliku tabeli tak, aby każdy blok wypełnić maksymalnie (lub do poziomu ograniczanego parametrem FILLFACTOR), ale jednocześnie nie „łamać” rekordu pomiędzy dwa bloki. W naszym przykładzie blok 0 został wypełniony całkowicie, a w bloku 1 pozostaje jeszcze sporo wolnego miejsca. Następnie, w kroku (2), użytkownik dokonuje modyfikacji rekordu ROW_2. Zgodnie z implementacją mechanizmów MVCC, oryginalny rekord ROW_2 nie ulegnie modyfikacji, lecz zamiast tego, zostanie on zdublowany i to na dublecie zostanie zrealizowana modyfikacja zlecona przez użytkownika. PostgreSQL spróbuje umieścić dublet rekordu ROW_2 w tym samym bloku, w którym znajduje się oryginał, ale jeżeli ilość wolnego miejsca będzie niewystarczająca (to nasz przypadek), wtedy z konieczności zapisze go w innym bloku. Mamy teraz do czynienia z dwiema kopiami rekordu ROW_2 – starą i aktualną. Gdy po pewnym czasie stary obraz rekordu ROW_2 nie będzie już potrzebny żadnej innej transakcji w systemie, stanie się on martwym rekordem. Z podobnym zjawiskiem mamy do czynienia w kroku (3), gdzie użytkownik usuwa rekord ROW_4. Rekord ten stanie się martwy, ale wciąż będzie znajdować się na dysku. Zauważmy, że w tym momencie, tabela formalnie zawiera pięć rekordów, ale w jej blokach znajduje się rekordów siedem – wśród nich dwa martwe, zajmujące przestrzeń dyskową i pogarszające wydajność operacji typu Seq Scan. W celu pozbycia się martwych rekordów, administrator lub proces Autovacuum wykonuje w kroku (4) operację Vacuum, która przetwarza wszystkie bloki pliku danych tabeli i fizycznie wymazuje wszystkie martwe rekordy (na podstawie identyfikatorów transakcji). Operacja Vacuum dokonuje też reorganizacji rekordów w obrębie bloków tak, aby defragmentować powstałe wolne miejsce. Nie dokonuje jednak globalnej defragmentacji wolnej przestrzeni. Na to pozwala nam opcjonalna operacja Vacuum Full (krok (5)), która oprócz eliminacji martwych rekordów, optymalizuje też fizyczną organizację rekordów w pliku danych (przepisuje rekordy do nowego pliku, stary usuwa). Ze względu na czasochłonność i inwazyjność (blokada wyłączna na całej tabeli), operacja Vacuum Full stosowana jest dość rzadko.
Aby przekonać się, czy naszym tabelom nie dolega problem nadmiernej liczby martwych rekordów, wynikający na przykład z niewłaściwej konfiguracji procesu Autovacuum, możemy monitorować ich liczbę, posługując się jednym z poniższych zapytań:
select relname, pg_stat_get_live_tuples(c.oid), pg_stat_get_dead_tuples(c.oid) from pg_class c;
select relname, n_live_tup, n_dead_tup from pg_stat_user_tables;