Jak sprawdzić rozmiar bazy, tabeli i indeksów

PostgreSQL

Jak sprawdzić rozmiar bazy, tabeli i indeksów

W skrócie

Prędzej czy później każdy administrator PostgreSQL musi odpowiedzieć na pytanie "co zajmuje tyle miejsca". Patrzenie na katalog danych z poziomu systemu operacyjnego nie wystarcza, bo widzisz jeden wielki katalog z plikami o nic nie mówiących nazwach. PostgreSQL ma za to komplet wbudowanych funkcji, które podadzą rozmiar bazy, konkretnej tabeli, jej indeksów, a nawet pojedynczego schematu. Pokażemy, jak z nich korzystać i jak odróżnić realny rozmiar danych od miejsca zajętego przez martwe wiersze.

Jak to wygląda w praktyce

Dostajesz alert o kończącym się miejscu na dysku bazy albo widzisz, że backup rośnie z tygodnia na tydzień szybciej, niż powinien. Chcesz namierzyć winowajcę, ale df pokazuje tylko, że katalog danych PostgreSQL jest ogromny, bez podziału na obiekty. Nie wiesz, czy to jedna wielka tabela z logami, czy może przerośnięte indeksy, czy raczej tabela, która przez ciągłe UPDATE nabrała martwych wierszy i puchnie mimo umiarkowanej liczby aktualnych rekordów. Bez podziału na obiekty nie da się podjąć sensownej decyzji - czy archiwizować dane, czy przebudować indeks, czy odpalić porządny VACUUM.

Dlaczego tak się dzieje

PostgreSQL trzyma każdą tabelę i każdy indeks w osobnych plikach o nazwach będących wewnętrznymi identyfikatorami, więc z poziomu systemu plików nie sposób powiedzieć, co jest czym. Do tego dochodzi specyfika samej bazy: rozmiar tabeli to nie tylko żywe dane. PostgreSQL działa na modelu wielowersyjnym, w którym zaktualizowany albo usunięty wiersz nie znika od razu, tylko zostaje jako wersja martwa do czasu sprzątania. Dlatego tabela z aktywnym ruchem może fizycznie zajmować znacznie więcej miejsca, niż wynikałoby z liczby aktualnych rekordów. Osobno miejsce zajmują indeksy - przy kilku indeksach na tabeli potrafią one łącznie przekroczyć rozmiar samych danych. Żeby to wszystko rozplątać, trzeba pytać samą bazę, bo tylko ona wie, który plik należy do którego obiektu i ile z niego to realne dane.

Jak to rozwiązać krok po kroku

1. Sprawdź rozmiar całej bazy: SELECT pg_size_pretty(pg_database_size('moja_baza'));. Funkcja pg_size_pretty zamienia bajty na czytelne MB i GB.
2. Znajdź największe tabele wraz z ich indeksami. pg_total_relation_size liczy tabelę plus jej indeksy plus dane TOAST: SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;.
3. Rozdziel same dane od indeksów dla podejrzanej tabeli. Rozmiar samych danych: SELECT pg_size_pretty(pg_relation_size('zamowienia'));. Rozmiar wszystkich indeksów tabeli: SELECT pg_size_pretty(pg_indexes_size('zamowienia'));.
4. Rozmiar pojedynczego indeksu sprawdzisz tak samo jak tabeli, podając jego nazwę: SELECT pg_size_pretty(pg_relation_size('idx_zamowienia_data'));. Tak wyłapiesz indeks, który urósł nieproporcjonalnie.
5. W psql masz też skrót bez pisania funkcji - polecenie \dt+ pokaże tabele z kolumną rozmiaru, a \di+ zrobi to samo dla indeksów.
6. Jeśli tabela jest duża głównie przez martwe wiersze, sięgnij po rozszerzenie pgstattuple albo widok statystyk, żeby ocenić realny narzut i zdecydować, czy pomoże VACUUM, czy dopiero VACUUM FULL lub przebudowa.

Jak sprawdzić, że zadziałało

Zestaw wyniki w jednej tabelce i policz, czy części składają się w całość: rozmiar danych plus rozmiar indeksów powinien z grubsza odpowiadać wartości z pg_total_relation_size dla tej samej tabeli. Jeśli suma rozmiarów największych obiektów jest dużo mniejsza niż rozmiar całej bazy z pg_database_size, to znaczy, że resztę zjadają liczne mniejsze obiekty albo narzut - warto wtedy rozszerzyć zapytanie o więcej pozycji w rankingu. Po ewentualnym sprzątaniu (VACUUM FULL, przebudowa indeksu) uruchom te same zapytania ponownie i porównaj przed z po - zwolnione miejsce potwierdzi, że działanie przyniosło efekt.

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