Czy SELECT usuwa martwe wiersze, jak robi to VACUUM?

9

Bawiłem się VACUUMi zauważyłem pewne nieoczekiwane zachowanie, w którym SELECTwstawianie wierszy z tabeli wydaje się zmniejszać VACUUMpóźniejszą pracę.

Dane testowe

Uwaga: autovacuum jest wyłączone

CREATE TABLE numbers (num bigint);
ALTER TABLE numbers SET (
  autovacuum_enabled = 'f',
  toast.autovacuum_enabled = 'f'
);

INSERT INTO numbers SELECT generate_series(1, 5000);

Próba 1

Teraz uruchamiamy aktualizację dla wszystkich wierszy,

UPDATE numbers SET num = 0;

A kiedy biegniemy VACUUM (VERBOSE) numbers;, dostajemy

INFO:  vacuuming "public.numbers"
INFO:  "numbers": removed 5000 row versions in 23 pages
INFO:  "numbers": found 5000 removable, 5000 nonremovable row versions in 45 out of 45 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 6585
There were 0 unused item pointers.

Próba 2

Teraz wydajemy inny UPDATE, ale tym razem dodajemy SELECTpóźniej,

UPDATE numbers SET num = 1;
SELECT * FROM numbers;

A kiedy biegniemy VACUUM (VERBOSE) numbers;, dostajemy

INFO:  vacuuming "public.numbers"
INFO:  "numbers": removed 56 row versions in 22 pages
INFO:  "numbers": found 56 removable, 5000 nonremovable row versions in 45 out of 45 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 6586
There were 56 unused item pointers.

Co dokładnie się tutaj dzieje? Dlaczego druga wersja, którą uruchamiam, po SELECTusunięciu martwych krotek ze stron, które odwiedza, zupełnie jak VACUUMrobi?

Używam Postgres 11.3 na macOS 10.14.5.

— rafbm
źródło

2

Jakiego klienta używasz do uruchamiania poleceń? Czy włączona jest funkcja automatycznego zatwierdzania?

— mustaccio

2

Mam zamiar usunąć pytanie „Czy stół VACUUM jest po prostu WYBIERZ * ze stołu pod maską?” (nie jest) Myślę, że to dobra kontynuacja, odpowiedź tutaj jest taka, że SELECT może usuwać martwe wiersze i ma to wspólne z VACUUM. Różnice między nimi będą bardzo wyczerpująca rozmowa na temat przejścia na XID i mnóstwo innych rzeczy. To pytanie jest w zasadzie „Co robi próżnia oprócz usuwania martwych rzędów”. (Co byłoby trochę niejasne)

— Evan Carroll

@mustaccio Zrobiłem te testy za pomocą skryptu Ruby przy użyciu ActiveRecord, który wykorzystuje klejnot PG pod maską. Uważam, że funkcja automatycznego zatwierdzania jest domyślnie włączona, ponieważ nie trzeba wydawać żadnego polecenia COMMIT, chyba że BEGIN zostanie użyte jawnie.

— rafbm

5

Od tego postu na / r / PostgreSQL do odpowiedzi Laurenz Albe wydaje się, że aktualizacje Heap Only Tuples (HOT) mogą być odpowiedzialne. Z opisu HOT aktualizacji wsrc/backend/access/heap/README.HOT

W efekcie odzyskiwanie przestrzeni następuje podczas pobierania krotki, gdy strona jest prawie pełna (<10% wolna) i można uzyskać blokadę czyszczenia bufora. Oznacza to, że UPDATE, DELETEi SELECTmoże uruchomić odzyskiwanie przestrzeni, ale często nie podczas, INSERT ... VALUESponieważ nie pobiera wiersza.

Cytat nie jest w oryginalnej odpowiedzi, ale reszta jest cytatem,

Aby wesprzeć lub obalić tę teorię, uruchom następujące zapytanie:
SELECT n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables
WHERE schemaname = 'public' AND relname = 'TABLE_NAME';
Jeśli n_tup_hot_updjest większa od zera, mamy przypadek.

— Evan Carroll
źródło

Teraz rozmawiamy. +1

— mustaccio

GORĄCE wydaje się być dobrym wytłumaczeniem. Jeśli I CREATE INDEX idx_numbers ON numbers USING btree (num), wyjście VACUUM zmieni się na INFO: "numbers": removed 5000 row versions in 45 pages. Należy jednak pamiętać, że w scenariuszu bez indeksu n_tup_hot_updzawsze wynosi 0, zarówno między AKTUALIZACJĄ a WYBOREM oraz między WYBOREM a VACUUM. Upewniłem się również, że biegam SELECT pg_sleep(10)między każdą instrukcją, aby statystyki były aktualne (widzę seq_scan: 2, jedną dla UPDATE i jedną dla SELECT).

— rafbm

Czy wybrana generuje WAL w tym przypadku? Miałem wrażenie, że wybrane wcale nie generują WAL. Jeśli tak, oznaczałoby to, że usunięcie martwych wierszy rozprzestrzenia się na wszystkich niewolników. Jeśli nie, oznacza to, że odkurzanie jest nadal konieczne w przypadku urządzenia slave. Oznaczałoby to również, że panowie i niewolnicy nie są trochę identyczni. Hmm, może muszę zrobić jakieś badania i opublikować pytanie i / lub odpowiedź lub dwa.

— Colin 't Hart

1

W szczególnym przypadku nieindeksowanej tabeli, tak, SELECT może wykonać tę samą pracę co VACUUM (jeśli chodzi o usuwanie martwych wierszy).

— jjanes
źródło

3

Czy możesz dodać wyjaśnienie?

— Laurenz Albe