PostgreSQL - PG_REPACK : przenoszenie, przebudowa i klastrowanie tabel i indeksów online

Ze względu na specyfikę działania mechanizmów MVCC postgres ma charakterystyczny problem, nazywany bloatem/poziomem nadmuchania tabel oraz indeksów. Jest to wolna przestrzeń, w której możemy zapisywać nowe wiersze, ale przez nieoptymalne ustawienia procesu autovacuum, a czasami po prostu przez specyfikę obciążenia, na bazie której nie ogarnie nawet idealnie dostrojony autovacuum, bloatu możemy mieć znacznie więcej niż potrzebujemy do zapisania wszystkich nowych wierszy. Bloat im większy, tym bardziej wpływa na wydajność zapytań. Postgres czytając napompowane tabele i indeksy, musi również "czytać" wolne przestrzenie. Jest to operacja raczej szybka, ale przy nagromadzeniu powyżej 25% tabeli lub 1-10GB spadek wydajności zaczyna być zauważalny.
Aby się go pozbyć używając narzędzi dostępnych domyślnie po zainstalowaniu postgresa, mamy do dyspozycji VACUUM FULL, CLUSTER lub możemy wykonać pg_dump i pg_restore. Każda z tych metod jest dość inwazyjna. VACUUM FULL i CLUSTER powoduje nałożenie ekskluzywnej blokady na dany obiekt, co uniemożliwia zapis oraz odczyt aż do zakończenia całej operacji. Podczas pg_dumpa i pg_restora również nie powinniśmy zmieniać żadnych danych, ponieważ zmiany mogą zostać utracone.
W poważnych implementacjach postgresa, gdzie mamy wymagania 4,5,6 dziewiątek w SLA, takie operacje są niedopuszczalne, ponieważ w zależności od wielkości tabeli - wymagają często kilku, kilkunastu albo nawet kilkudziesięciu godzin okna serwisowego. Wtedy z pomocą przychodzi pg_repack. Pozwala on na wykonanie większości operacji online, wymagając jedynie dwóch krótkich blokad ekskluzywnych na obiekcie, kilku do kilkudziesięciu milisekund na początku oraz na końcu operacji, a jedyna blokada, którą trzyma podczas całej operacji, jest ACCESS SHARE - pozwalająca na wszystkie operacje na danych, ale nie pozwala na modyfikację struktury obiektu.
Pierwsza wymagana blokada ekskluzywna potrzebna jest na czas transakcji, w której zostanie stworzona tabela pomocnicza do logowania zmian w tabeli źródłowej podczas działania pg_repacka oraz dodania triggera, który będzie zapisywał informacje o zmianach z tabeli źródłowej do tabeli loga. Następnie już poza blokiem transakcji - tworzy tabele, do której kopiuje wszystkie wiersze z tabeli źródłowej, zakłada na niej indeksy oraz aplikuje wszystkie zmiany z tabeli loga. Na koniec potrzebuje jeszcze chwilowej blokady na zmianę nazw tabeli pomocniczej z źródłową w słownikach systemowych oraz usunięcie tabeli źródłowej.
Na podobnej zasadzie działa dla indeksów. Tworzy nowy index, używając opcji CREATE INDEX CONCURRENTLY, następnie chwilowo blokuje tabele blokadą ekskluzywną i podmienia w słownikach nazwy nowego i starego indeksu oraz usuwa stary indeks.
Jednym z wymogów pg_repacka jest, aby przebudowywana tabela posiadała klucz główny lub unique constraint, musi być wywołany z uprawnieniem superusera oraz wolna przestrzeń równa dwukrotności rozmiaru przebudowywanego obiektu.

Warte uwagi parametry pg_repacka to:

• "-t nazwa_tabeli" przepakowanie wybranej tabeli i jej indeksów,
• "-i nazwa_indeksu" przepakowanie wybranego indeksu,
• "-x nazwa_tabeli" przepakowanie wszystkich indeksów dla wybranej tabeli,
• "-s nazwa_przestrzeni" przepakowanie tabeli lub indeksu do innej przestrzeni tabel,
• "-S" przenieś również indeksy dla przepakowywanej tabeli,
• "-o nazwa_kolumny" przepakowanie tabeli i sortowanie według kolumny,
• "-j X" użyj X procesów podczas przepakowywania, warto zaznaczyć, że jedna tabela zawsze będzie korzystała tylko z jednego procesu, ale kiedy zakończy się przepakowywanie tabeli, pg_repack zacznie zakładać X indeksów jednocześnie,
• "-T X" - czas który pg_repack będzie oczekiwał na możliwość założenia blokady ekskluzywnej oraz po którym zabije blokujący go proces, domyślnie 60 sekund,
• "-D" - przełącznik wyłączający powyższą opcję, przerwie proces pg_repacka zamiast zabić blokujący proces,
• "-k" - pominięcie sprawdzenia uprawnień superużytkownika, przydatne w przypadku środowisk DBaaS, np. RDS, gdzie nie mamy dostępu do superusera, ale posiadamy wymagane uprawnienia.

Sprawdźmy różnice pomiędzy vacuum full a pg_repackiem. Stwórzmy nową tabelę i dodajmy do niej klucz główny lub unikalny constraint, aby spełnić wymagania pg_repacka. Następnie stwórzmy trochę "bloatu" oraz spróbujmy wykonać obie operacje, jednocześnie próbując edytować jeden z wierszy z drugiego terminala.

Terminal 1:

postgres=# create table do_repacka (x integer,y varchar);

CREATE TABLE

Dodanie klucza głównego lub unique constraint:

postgres=# alter table do_repacka add constraint do_repacka_pk primary key (x);

ALTER TABLE





postgres=# alter table do_repacka alter column x set not null;

ALTER TABLE

postgres=# alter table do_repacka add CONSTRAINT do_repacka_unique_x unique (x);

ALTER TABLE

Pompowanie tabeli:

postgres=# insert into do_repacka values (generate_series(1,10000000), 1);



postgres=# select pg_size_pretty(pg_total_relation_size('do_repacka'));

 pg_size_pretty

----------------

 560 MB

(1 row)



postgres=# update do_repacka SET y = x + 1;



postgres=# select pg_size_pretty(pg_total_relation_size('do_repacka'));

 pg_size_pretty

----------------

 1197 MB

(1 row)

Wykonanie vacuum full i w tym samym czasie próba edycji tabeli:

postgres=# \timing

Timing is on.



postgres=# vacuum full do_repacka;

Terminal 2:

update do_repacka set y = 1 where x = 10;

Spróbujmy teraz to samo tylko z pomocą pg_repacka.
Najpierw zainstalujmy paczkę z pg_repackiem.

sudo apt-get -y install postgresql-15-repack



postgres=# create extension pg_repack;

CREATE EXTENSION

Jeszcze raz wygenerujemy trochę bloatu

postgres=# update do_repacka SET y = x + 3;



postgres=# select pg_size_pretty(pg_total_relation_size('do_repacka'));

Na terminalu 1 uruchomimy pg_repack

postgres@ubuntu:~$ time pg_repack -t do_repacka -h 127.0.0.1 -p 5432 -d postgres -U postgres

INFO: repacking table "public.do_repacka"

Z terminalu 2 spróbujmy wykonać update

update do_repacka set y = 5 where x = 20;



postgres=# select pg_size_pretty(pg_total_relation_size('do_repacka'));

pg_repack trwał trochę dłużej, ale pozwolił nam na zmianę danych w czasie przepakowywania tabeli.

Przenoszenie obiektów pomiędzy przestrzeniami tabel online

Dzięki temu, że pg_repack tworzy nowe obiekty, możemy za jego pomocą przenieść je do innych przestrzeni tabel również online.

Tworzymy nową przestrzeń tabel i próbujemy przenieść do niej tabele za pomocą pg_repack.

sudo mkdir /ssd

sudo chown postgres: /ssd

sudo chmod 700 /ssd



postgres=# create tablespace nowa location '/ssd';

CREATE TABLESPACE



postgres=# select schemaname,tablename,tablespace from pg_tables where tablename = 'mvcc';

 schemaname | tablename - | tablespace

------------+------------+------------

 public      | do_repacka |

(1 row)



postgres@ubuntu:~$ pg_repack -t do_repacka -h 127.0.0.1 -p 5432 -d postgres -U postgres -s nowa

INFO: repacking table "public.do_repacka"



postgres=# select schemaname,tablename,tablespace from pg_tables where tablename = 'do_repacka';

 schemaname | tablename - | tablespace

------------+------------+------------

 public      | do_repacka | nowa

(1 row)

W ten sposób możemy jednocześnie pozbyć się bloatu z tabeli, przenieść ją do innej przestrzeni tabel, a nawet poklastrować/posortować dane wg wybranej kolumny to wszystko online, bez potrzeby okna serwisowego.

Szkolenie Administracja, replikacja i tuning baz danych PostgreSQL →

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5