postgres vacuume analyze - ghdrako/doc_snipets GitHub Wiki

• https://github.com/ghdrako/doc_snipets/wiki/postgres-Autovacuum--Autoanalyze-ANALYZE-SKIP_LOCKED
• https://www.postgresql.org/docs/current/routine-vacuuming.html
• https://www.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql
• https://cloud.google.com/blog/products/databases/monitor-transaction-id-utilization-in-cloud-sql-postgresql
• https://cloud.google.com/solutions/optimizing-monitoring-troubleshooting-vacuum-operations-postgresql ( do pobrania pdf)
• https://www.depesz.com/2022/03/13/is-my-autovacuum-configured-properly/#more-4059

VACUUM powstało z potrzeby skrócenia terminów realizacji. Z czasem zostało połączone z innymi poleceniami (ANALYZE, VACUUM FREEZE) i zajęło się innymi operacjami konserwacyjnymi (na przykład tworzeniem mapy widoczności). Autovacuum to proces instancji PostgreSQL. Jest domyślnie włączony i zdecydowanie zaleca się jego pozostawienie w tym stanie. Zasadniczo jego działanie jest odpowiednie i nie będzie przeszkadzać użytkownikom. Autovacuum nie obsługuje wszystkich wariantów VACUUM (zwłaszcza wariantu FULL).

Polecenie VACUUM najpierw czyści nieaktywne wiersze. Przetwarzanie VACUUM odbywa się w trzech przebiegach.

W pierwszym przebiegu skanuje się tabelę do wyczyszczenia w poszukiwaniu nieaktywnych rekordów. Rekord jest nieaktywny, jeśli jego wartość xmax odpowiada zatwierdzonej transakcji i nie jest on już widoczny w migawce żadnej bieżącej transakcji w bazie danych. Inne nieaktywne wiersze mają wartość xmin anulowanej transakcji. Nieaktywnego rekordu nie można natychmiast usunąć: wskazują na niego rekordy indeksów i również należy go wyczyścić. Sesja wykonująca czyszczenie przechowuje w pamięci listę adresów nieaktywnych rekordów, do ilości określonej parametrem maintenance_work_mem. Jeśli ta przestrzeń jest zbyt mała, aby pomieścić wszystkie nieaktywne rekordy, VACUUM wykonuje kilka serii tych trzech przebiegów.

Drugie przejście odpowiada za czyszczenie wpisów indeksu. VACUUM posiada listę identyfikatorów krotek do unieważnienia. W związku z tym skanuje wszystkie indeksy tabeli w poszukiwaniu tych identyfikatorów tid i usuwa je. W rzeczywistości indeksy są sortowane w celu dopasowania wartości klucza (na przykład indeksowanej kolumny) do identyfikatora tid. Jednak nie jest możliwe bezpośrednie znalezienie identyfikatora tid. Całkowicie puste strony są usuwane z drzewa i zapisywane na liście stron wielokrotnego użytku, tzw. Mapie Wolnego Miejsca (FSM). Aby zaoszczędzić czas, jeśli jest on najważniejszy, tę fazę można pominąć na dwa sposoby. Pierwszym z nich jest wyłączenie opcji INDEX_CLEANUP:

VACUUM (VERBOSE, INDEX_CLEANUP off) nom_table ;

Począwszy od wersji 14 dodano kolejny mechanizm, tym razem automatyczny. Celem jest nadal szybkie wykonanie VACUUM, ale tylko po to, aby uniknąć konieczności ponownego uruchamiania. Gdy tabela osiągnie bardzo wysoki wiek 1,6 miliarda transakcji (domyślnie parametry vacuum_failsafe_age i vacuum_multixact_failsafe_age), proste VACUUM automatycznie wyłączy czyszczenie indeksów, aby przyspieszyć czyszczenie tabeli i umożliwić przejście do najstarszego identyfikatora tabeli. Począwszy od wersji 13, ta faza czyszczenia indeksów może być zrównoleglona (klauzula PARALLEL), a każdy indeks może być przetwarzany przez procesor.

Teraz, gdy nie ma wpisów indeksu wskazujących na zidentyfikowane nieaktywne rekordy, mogą one zniknąć. To jest rola tego trzeciego przejścia. Po usunięciu rekordu z bloku, blok ten jest całkowicie reorganizowany w celu konsolidacji wolnej przestrzeni. Ta przestrzeń jest wprowadzana do Free Space Map (FSM). Po zakończeniu tego przejścia, jeśli skanowanie tabeli nie zostało ukończone w poprzednim przejściu, praca jest wznawiana od miejsca, w którym została przerwana. Jeśli ostatnie bloki tabeli są puste, są one zwracane do systemu (o ile można uzyskać niezbędną blokadę i jeśli opcja TRUNCATE nie jest wyłączona). Jest to jedyny przypadek, w którym VACUUM zmniejsza rozmiar tabeli. Puste (i wielokrotnego użytku) przestrzenie w środku tabeli stanowią rozdęcie (dosłownie „rozdęcie” lub „pęcznienie”, co można również przetłumaczyć jako fragmentację). Statystyki aktywności są również aktualizowane.

Domyślnie VACUUM czyści głównie martwe wiersze. Aby to działało, aktualizuje mapę widoczności i tworzy ją w razie potrzeby. W tym procesie może zablokować niektóre napotkane wiersze. Autovacuum uruchomi tę funkcję dla tabel w zależności od aktywności. Zestaw blokad SHARE UPDATE EXCLUSIVE chroni tabelę przed jednoczesnymi modyfikacjami schematu i generalnie nie zakłóca operacji, z wyjątkiem tych najbardziej inwazyjnych (na przykład zapobiega LOCK TABLE ). Autovacuum spontanicznie zatrzyma VACUUM, którą uruchomiła i która zakłóca; natomiast VACUUM uruchomiony ręcznie będzie kontynuowała działanie do końca.

ANALYZE istnieje jako osobne polecenie, które odświeża statystyki dla próbki danych dla optymalizatora. Autovacuum również uruchamia ANALYZE w oparciu o aktywność. Polecenie VACUUM ANALYZE (lub VACUUM (ANALYZE)) wymusza obliczanie statystyk danych w tym samym czasie co polecenie VACUUM.

VACUUM FREEZE „zamraża” wiersze widoczne dla wszystkich transakcji w toku na instancji, aby uniknąć problemu z zawijaniem identyfikatorów transakcji. Polecenie FREEZE samo w sobie nie istnieje. Dla bezpieczeństwa, podczas prostego VACUUM, automatyczne zamrożenie zamrozi niektóre napotkane wiersze. Dodatkowo, uruchomi VACUUM FREEZE dla tabeli, której najstarsze transakcje przekraczają określony wiek. Może to być bardzo długie i bardzo obciążające dla operacji zapisu, jeśli duża tabela musi zostać całkowicie zamrożona naraz. W przeciwnym razie aktywność jest utrudniona tylko wyjątkowo (patrz poniżej). Operacja zamrożenia zostanie szczegółowo opisana później.

Polecenie VACUUM FULL pozwala na odbudowę tabeli bez pustych miejsc. Jest to bardzo obciążająca operacja, która może blokować inne zapytania ze względu na narzuconą blokadę wyłączną (nie można już nawet odczytać tabeli!), ale jest to jedyna opcja, która pozwala z pewnością zmniejszyć rozmiar tabeli na poziomie systemu plików. Miejsce na dysku musi być zaplanowane (tabela jest odbudowywana obok starej, a następnie stara tabela jest usuwana). Indeksy są odbudowywane w tym samym czasie. Polecenie VACUUM FULL agresywnie zamraża wiersze, wykonując w tym procesie operację ekwiwalentną polecenia FREEZE. Autovacuum nigdy nie uruchomi polecenia VACUUM FULL! Istnieje również polecenie CLUSTER, które umożliwia sortowanie tabeli według jednego z indeksów.

Wprowadzona w PostgreSQL 13 opcja PARALLEL umożliwia równoległe przetwarzanie indeksów. Liczba po PARALLEL określa pożądany poziom paralelizacji. Na przykład:

VACUUM (VERBOSE, PARALLEL 4) matable ;
INFO: vacuuming "public.matable" INFO: launched 3 parallel vacuum workers for index cleanup (planned: 3)

Wreszcie, po wykonaniu VACUUM, nawet na pojedynczej tabeli, aktualizowane jest pole pg_database.datfrozenxid. Zawiera ono numer najstarszej niezamrożonej transakcji w całej bazie danych. Operacja ta wymaga przejrzenia klasy pg_class w celu pobrania najstarszego numeru transakcji z każdej tabeli (relfrozenxid). Aktualizacja ta jest jednak przydatna tylko w przypadku autovacuum i VACUUM FREEZE i rzadko jest pilna.

Od wersji 16 opcja SKIP_DATABASE_STATS instruuje VACUUM, aby ignorował aktualizację identyfikatora transakcji. Zasada działania polega na włączeniu tej opcji w przypadku masowych czyszczeń. Natomiast opcja ONLY_DATABASE_STATS wymaga jedynie aktualizacji datfromzenxid, którą można wykonać tylko raz, na końcu przetwarzania.

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]

The VACUUM command removes dead tuples but does not release the unused space back to the operating system. Instead, it marks the space as available for future use by new rows. The syntax is VACUUM table_name. It can run concurrently with other database operations, making it less disruptive and non-blocking, and does not cause any downtime.

VACUUM FULL reclaims the unused space and returns it to the operating system. It is more aggressive than VACUUM by holding exclusive locks on a table before vacuuming. It physically shrinks the table by compacting it. Due to its Blocking operation, it is not suitable to run on large tables in production, as the locking can be very disruptive for production operations. It is typically used when tables have become very bloated or significant space needs to be reclaimed. The syntax is VACUUM [FULL] [FREEZE] [VERBOSE] [ANALYZE][table_name];

The FULL option rewrites the entire table to reclaim all unused space, which is returned to the operating system. Transaction ID wraparound is prevented by using the FREEZE option. The VERBOSE option reviews details about the VACUUM process when it is running. The ANALYZE option updates PostgreSQL statistics for the query planner to produce a good execution plan while vacuuming the table.

Note that VACUUM or VACUUM FULL can be executed on an entire database or single tables. For the entire database, these commands are executed without specifying the table name. Entire database vacuuming:

VACUUM;
VACUUM FULL;

For individual tables:

VACUUM table_name;
VACUUM FULL table_name;
VACUUM ANALYZE table_name; --This will update the statistics for the query planner.
VACUUM VERBOSE table_name; -- Will show details of the vacuuming process.

Show global vacuum parameters

select * FROM pg_settings where name like '%vacuum%';

show vacuum parameters on table

SELECT
  split_part(opt, '=', 1) AS parameter,
  split_part(opt, '=', 2) AS value
FROM pg_catalog.pg_class c
  JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  CROSS JOIN unnest(coalesce(c.reloptions, ARRAY[]::text[])) AS t(opt)
WHERE n.nspname = 'public'        -- your schema
  AND c.relname  = 'your_table';  

When you want to make sure row visibility is updated and space from dead tuples is reclaimed, you can perform a manual VACUUM operation. Optionally, include the ANALYZE keyword to update table statistics at the same time.

VACUUM and ANALYZE can be executed concurrently

VACUUM ANALYZE table1;
VACUUM ( ANALYZE , VERBOSE) users;

Running VACUUM on psql and using the vacuumdb command have the same effect.

• https://paquier.xyz/postgresql-2/postgres-12-vacuum-skip-locked/

New versions of PostgreSQL continue to add capabilities to Vacuum and Analyze. Version 11 added support for Vacuuming multiple tables at once. Version 12 added SKIP_LOCKED support, which skips locked tables

VACUUM (SKIP_LOCKED) trip_requests, trips;

SELECT    
schemaname,   
relname,
last_autoanalyze,   
last_analyze   
FROM  pg_stat_all_tables   
WHERE  relname =  'vehicles' ;

• https://www.highgo.ca/2020/02/28/parallel-vacuum-in-upcoming-postgresql-13/

Since PostgreSQL 13, parallel Vacuum workers can be configured

SET  MAX_PARALLEL_MAINTENANCE_WORKERS=4;    
VACUUM (PARALLEL 4, VERBOSE) users;

By default, autovacuum is enabled and will perform VACUUM and ANALYZE operations on tables in the database that have a lot of unused space.

Vacuuming detects dead tuples based on the database horizon.

Way Postgres implements changes to rows in a table:

• An UPDATE operation in Postgres is equivalent to a DELETE of the previous tuple, plus an INSERT of the new one.
• A DELETE operation in Postgres does not cause the data to be removed from physical storage. It only causes it to be marked as deleted. This is why Postgres has the autovacuum process.

Table bloat happens when a table’s physical footprint far exceeds the size of the data that it actually holds.

VACUUM VERBOSE table; 

ALTER TABLE testings SET (autovacuum_enabled = true);

Monitoring autovacume

SELECT * FROM pg_stat_progress_vacuum

Above view shows:

• phase — the name of the current vacuum phase (I have described the main ones, but there are actually more of them1)
• heap_blks_total — the total number of pages in a table
• heap_blks_scanned — the number of scanned pages
• heap_blks_vacuumed — the number of vacuumed pages
• index_vacuum_count — the number of index scans 1
  The overall vacuuming progress is defined by the ratio of heap_blks_vacuumed to heap_blks_total, but you have to keep in mind that it changes in spurts because of index scans. In fact, it is more important to pay attention to the number of vacuum cycles: if this value is greater than one, it means that the allocated memory was not enough to complete vacuuming in one go.

Change memory

ALTER SYSTEM SET maintenance_work_mem = '1MB';
SELECT pg_reload_conf();

Set how often autovacume must execute

ALTER SYSTEM SET autovacuum_naptime = '1s';

pg_repack or pg_squeeze can compact tables without exclusive locks.

• https://www.enterprisedb.com/blog/postgresql-vacuum-and-analyze-best-practice-tips
• https://tembo.io/blog/optimizing-postgres-auto-vacuum/
• https://www.cybertec-postgresql.com/en/tuning-autovacuum-postgresql/

autovacuum_vacuum_scale_factor denotes the portion of the table size that, when surpassed by "dead" rows, prompts a vacuum action.

ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1; -- set 10% threshold dead tuples
SELECT pg_reload_conf();

autovacuum_vacuum_scale_insert_factor - fraction of the total number of tuples in a table that must be modified before autovacuum cleans the table.

select last_autovacuum,autovacuum_count,vacuum_count from pg_stat_user_tables;

Dla bardzo dużych tabel( miliard wierszy) współczynnik defaulowy 0.2 moze być za duży. Lepszy będzie 0.02 a dla tabel z duza ilością zmian nawet 0.002.

autovacuum_naptime parameter in Postgres specifies the minimum delay between autovacuum runs on any given database.Postgres wake up autovacume every autovacuum_naptime seconds. Generally, depending on just how high-churn your workloads are, it might be necessary to decrease this value, whereas a longer interval could be suited for environments that are not churning at such a high rate.

autovacuum_vacuum_cost_delay is the amount of time that the autovacuum process will halt processing when the autovacuum_vacuum_cost_limit is reached. In example a table reaches 10% bloat, meaning 10% of the tuples are dead. When the 10% threshold is reached, the autovacuum worker begins to work and starts accruing cost. When that cost reaches autovacuum_vacuum_cost_limit, it will pause for the duration specified by autovacuum_vacuum_cost_delay, and then continue working until it is complete.

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number_of_tuples
autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor * number_of_tuples

• High-update workloads: Lower autovacuum_vacuum_scale_factor (or raise autovacuum_vacuum_threshold) to vacuum more often, preventing bloating.
• Heavy-insert tables with few updates/deletes: Lower autovacuum_vacuum_insert_scale_factor (or set autovacuum_vacuum_insert_threshold = 0) so autovacuum can freeze tuples sooner, helping avoid wraparound delays.

ALTER TABLE your_table
  SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_insert_scale_factor = 0.05
  );

Be carefull — more frequent vacuums incur more I/O and CPU, so balance threshold and scale factor against your maintenance windows and system load.

PostgreSQL może uruchomić VACUUM nawet na tabeli, w której dominują tylko insert’y z powodu:

1. Frezowanie (tuple freezing) - zapobieganie wyczerpaniu identyfikatorów transakcji (wraparound)
2. Aktualizacja mapy widoczności (visibility map) ktora mówi silnikowi, które strony bloku danych zawierają wyłącznie wiersze już w pełni widoczne. Dzięki temu silnik może wykonywać index‐only scans – znacznie szybsze od standardowych odczytów.
3. Statystyki i planowanie zapytań - ANALYZE w ramach VACUUM może odświeżyć statystyki tabeli – także przyrostu liczby wierszy i rozkładu wartości kolumn.

SELECT relname, n_dead_tup FROM pg_stat_user_tables;  -- nie wyczyszczone rekordy

SELECT
       relname AS "table_name",
       pg_size_pretty(pg_table_size(C.oid)) AS "table_size"
FROM
       pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r')
ORDER BY pg_table_size(C.oid)
DESC LIMIT 1;

SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;

• pg_stat_progress_vacuum

# Install pgstattuple extension  
CREATE EXTENSION pgstattuple;  
# Identify dead tuples  
select * from pgstattuple('testings');

VACUUM (VERBOSE)

Jak zwroci xmin to

SELECT * FROM pg_stat_activity WHERE backend_xmin = '<xmin>';

There are three reasons why dead tuples cannot be removed:

• https://www.youtube.com/watch?v=D832gi8Qrv4

1. There is a long running transaction that has not been closed. You can find the bad boys with

    SELECT pid, datname, usename, state, backend_xmin
    FROM pg_stat_activity
    WHERE backend_xmin IS NOT NULL
    ORDER BY age(backend_xmin) DESC;

You can get rid of a transaction with [pg_cancel_backend() or pg_terminate_backend()](https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL).

VACUUM can only remove dead tuples which are long-dead, that is, dead to all possible uses. If you have long-lived transactions, they may prevent the recently-dead tuples from being removed.

Certainly a long running select or insert statement will do that. For isolation levels higher than read-committed, the whole transaction will retain the snapshot until it is down, so if some opens a repeatable read transaction and then goes on vacation without committing it, that would be a problem. Hung-up prepared transactions will as well (if you don't know what a prepared transaction is, then you probably aren't using them). Start logging the output of your VACUUM VERBOSE statements, so that you can find the information that covers the period during which the problem exists.

2. There are prepared transactions which have not been commited. You can find them with

    SELECT gid, prepared, owner, database, transaction
    FROM pg_prepared_xacts
    ORDER BY age(transaction) DESC;

User [COMMIT PREPARED](https://www.postgresql.org/docs/current/static/sql-commit-prepared.html) or [ROLLBACK PREPARED](https://www.postgresql.org/docs/current/static/sql-rollback-prepared.html) to close them.

3. There are replication slots which are not used. Find them with

    SELECT slot_name, slot_type, database, xmin, catalog_xmin
    FROM pg_replication_slots
    ORDER BY age(xmin),age(catalog_xmin) DESC;

Use [pg_drop_replication_slot()](https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-REPLICATION) to delete an unused replication slot.

Hot standby Replication slot

4. Long running backends - start transaction and forgot to end

SELECT pid,datname,usename,state,backend_xmin FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;