postgres‐vacuume‐pg_repack‐cloudsql pg_squeeze - ghdrako/doc_snipets GitHub Wiki

pg_repack

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.

pg_repack offers a more efficient and non-disruptive alternative for eliminating database bloat and reclaiming disk space then vacuum full.

pg_repack requires additional disk space and specific prerequisites, such as a primary key or unique index, which are not mandatory for VACUUM FULL .

pg_repack is a PostgreSQL extension designed to address bloat caused by frequent updates and deletes while allowing normal operations to continue. It achieves this by creating a temporary log table to track changes during the process, ensuring data consistency. The tool generates a new, optimized version of the table, applies logged changes, and performs a quick final swap to replace the old table. This efficient, minimal downtime approach makes pg_repack an excellent choice for maintaining performance and reclaiming space in large databases. Its key benefits include flexibility to target specific tables or indexes, online operation with reduced downtime, and improvements in disk usage and query efficiency. Despite its advantages, it is essential to test pg_repack in a controlled environment and ensure sufficient resources are available to mitigate potential performance impacts during the process.

pg_repack works by creating a new copy of the table being processed, setting up triggers to replicate new data while it fills up with the existing data. An exclusive full table lock is required both at the start and finish of the process when swapping the old and new tables.

pg_repack is very effective in reclaiming space and can work with all types of bloat. It's available out of the box both on Amazon RDS and Google Cloud SQL.

The drawback you might experience when terminating the process (which may be necessary for various reasons, such as impact on the running environment) is that it won’t clean up all the fragments as it won't remain connected to the target database.

psql -U postgres -d testdb;  # connect as cloudsqlsuperuser typical postgres
GRANT testuser TO postgres;
CREATE EXTENSION pg_repack;

$> pg_repack -h <hostname> -d testdb -U csuper1 -k -t t1

REVOKE testuser FROM csuper1;

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.

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

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)

pg_squeeze

Like pg_repack, pg_squeeze creates a new table and copies the existing data from the bloated table. Logical replication is involved in streaming changes from the original table to the newly created one in real-time. This allows the new table to stay up-to-date during the process without unnecessary impact on the regular operations performed on the bloated table. Thus, pg_squeeze significantly reduces the need for locking. The exclusive lock is needed only during the final phase of the operation, when the old table is swapped out for the new, optimized table. The duration of the exclusive lock can also be configured.

While pg_squeeze is also available as an extension, its deployment necessitates configuration changes involving wal_level, max_replication_slots, and shared_preload_libraries. Due to this, a restart of the cluster is required.

On the other hand, pg_squeeze is designed for regular, rather than ad-hoc processing only. You can register a table for regular processing, and whenever the table meets the criteria to be "squeezed," a task will be added to a queue, where it will be sequentially processed in the order they were created.

While pg_squeeze might be considered superior to pg_repack in terms of maintenance operations and impact, it comes with a significant caveat when reclaiming space—compared to pg_repack, it copies the full rows as they are. This behaviour renders it ineffective at removing bloat created due to dropped columns.

As already mentioned, pg_squeeze can use a specified index for clustering when needed. The limitation you might find is that clustering cannot be performed on a partial index. Compared to pg_repack, it always seems to clean up all the artefacts accordingly (thanks to the always-running worker process).

Unfortunately at the moment of writing the article pg_squeeze is not available for Amazon RDS, only Google Cloud SQL.

⚠️ **GitHub.com Fallback** ⚠️