VACUUM zwracając miejsce na dysku do systemu operacyjnego

VACUUMzwykle nie zwraca miejsca na dysku do systemu operacyjnego, z wyjątkiem niektórych szczególnych przypadków.
Z dokumentów:

Standardowa forma VACUUMusuwania martwych wierszy w tabelach i indeksach oraz oznacza miejsce dostępne do ponownego użycia w przyszłości. Jednak nie zwróci miejsca do systemu operacyjnego, z wyjątkiem specjalnego przypadku, w którym jedna lub więcej stron na końcu tabeli staje się całkowicie wolnych i można łatwo uzyskać wyłączną blokadę stołu. Natomiast VACUUM FULLaktywnie kompaktuje tabele, pisząc kompletną nową wersję pliku tabeli bez martwej przestrzeni. Minimalizuje to rozmiar stołu, ale może zająć dużo czasu. Wymaga również dodatkowego miejsca na dysku dla nowej kopii tabeli, dopóki operacja się nie zakończy.

Pytanie brzmi: w jaki sposób można określić tę bazę danych, kiedy one or more pages at the end of a table become entirely freezostanie osiągnięta? Można to zrobić za pośrednictwem VACUUM FULL, ale nie mam wystarczająco dużo miejsca, aby to zaimplementować. Czy są więc jakieś inne możliwości?

Aby zwrócić miejsce w systemie operacyjnym, użyj VACUUM FULL. Będąc przy tym, myślę, że uciekasz VACUUM FULL ANALYZE. Cytuję instrukcję :

FULL

Wybiera „pełną” próżnię, która może odzyskać więcej miejsca , ale zajmuje znacznie więcej czasu i blokuje wyłącznie stół. Ta metoda wymaga również dodatkowego miejsca na dysku, ponieważ zapisuje nową kopię tabeli i nie zwalnia starej kopii, dopóki operacja nie zostanie zakończona. Zwykle należy tego używać tylko wtedy, gdy znaczna ilość miejsca wymaga odzyskania z wnętrza stołu.

Odważny nacisk moje.

CLUSTER osiąga to również jako efekt uboczny.

Zwykły VACUUMzwykle nie osiąga celu ( „jedna lub więcej stron na końcu stołu całkowicie za darmo” ). Nie zmienia kolejności wierszy i przycina tylko puste strony z fizycznego końca pliku, gdy pojawia się taka możliwość - tak jak w cytacie z instrukcji.

Możesz uzyskać puste strony na końcu pliku fizycznego, gdy masz INSERTpartię wierszy i DELETEje przed dołączeniem innych krotek. Lub może się to zdarzyć zbiegiem okoliczności, jeśli wystarczająca liczba wierszy zostanie usunięta.

Istnieją również specjalne ustawienia, które mogą uniemożliwić VACUUM FULLodzyskanie miejsca. Widzieć:

• Jak zoptymalizować tabelę dla aktualizacji o bardzo wysokiej częstotliwości?

Przygotuj puste strony na końcu tabeli do przetestowania

Kolumna systemowa ctidreprezentuje fizyczną pozycję wiersza. Musisz zrozumieć tę kolumnę:

• Jak rozłożyć ctid na numery stron i wierszy?

Możemy z tym pracować i przygotować tabelę, usuwając wszystkie wiersze z ostatniej strony:

DELETE FROM tbl t
USING (
   SELECT (split_part(ctid::text, ',', 1) || ',0)')::tid     AS min_tid
        , (split_part(ctid::text, ',', 1) || ',65535)')::tid AS max_tid
   FROM   tbl
   ORDER  BY ctid DESC
   LIMIT  1
   ) d
WHERE t.ctid BETWEEN d.min_tid AND d.max_tid;

Ostatnia strona jest pusta. To ignoruje równoczesne zapisy. Albo jesteś jedynym, który pisze do tej tabeli, albo musisz wziąć blokadę zapisu, aby uniknąć zakłóceń.

Zapytanie jest zoptymalizowane, aby szybko identyfikować kwalifikujące się wiersze. Druga liczba a tidto indeks krotek przechowywany jako niepodpisany int2i 65535jest maksimum dla tego typu ( 2^16 - 1), więc jest to bezpieczna górna granica.

SQL Fiddle (ponowne użycie prostej tabeli z innego przypadku).

Narzędzia do pomiaru wielkości wiersza / tabeli:

• Zmierz rozmiar wiersza tabeli PostgreSQL

Dysk jest pełny

Do wykonania każdej z tych operacji potrzebujesz miejsca na dysku. Istnieje również narzędzie społecznościowe pg_repackzastępujące VACUUM FULL/ CLUSTER. Unika ekskluzywnych zamków, ale potrzebuje również wolnego miejsca do pracy. Instrukcja:

Wymaga wolnego miejsca na dysku dwa razy większej niż docelowe tabele i indeksy.

W ostateczności możesz uruchomić cykl zrzutu / przywracania. To usuwa również wszelkie wzdęcia z tabel i indeksów. Ściśle powiązane pytanie:

• Muszę uruchomić VACUUM FULL bez dostępnego miejsca na dysku

Odpowiedź jest dość radykalna. Jeśli Twoja sytuacja na to pozwala (brak kluczy obcych lub innych referencji uniemożliwiających usunięcie wierszy) i brak równoczesnego dostępu do tabeli), możesz po prostu:

Zrzuć tabelę na dysk łączący się ze zdalnego komputera z dużą ilością miejsca na dysku ( -adla --data-only):

Ze zdalnej powłoki zrzuć dane tabeli:

pg_dump -h <host_name> -p <port> -t mytbl -a mydb > db_mytbl.sql

W sesji pg TRUNCATEtabela:

-- drop all indexes and constraints here for best performance
TRUNCATE mytbl;

Ze zdalnej powłoki przywróć do tej samej tabeli:

psql -h <host_name> -p <port> mydb -f db_mytbl.sql
-- recreate all indexes and constraints here

Jest teraz wolny od martwych rzędów i wzdęć.

Ale może możesz mieć to prostsze?

• Czy możesz zrobić wystarczająco dużo miejsca na dysku, usuwając (przenosząc) niepowiązane pliki?

• Czy możesz VACUUM FULLnajpierw pomniejszyć tabele, jeden po drugim, a tym samym zwolnić wystarczająco dużo miejsca na dysku?

• Czy możesz uruchomić REINDEX TABLElub REINDEX INDEXzwolnić miejsce na dysku z rozdętych indeksów?

Cokolwiek robisz, nie bądź pochopny . W razie wątpliwości najpierw wykonaj kopię zapasową w bezpiecznej lokalizacji.