Konflikty BufferPin w PostgreSQL: jak znaleźć i wyeliminować

# Konflikty BufferPin w PostgreSQL: diagnostyka i optymalizacja dla replik i autovacuumu

Konflikty przy zakrepleniu buforów (BufferPin) — jedna z ukrytych, ale krytycznych problemów wydajności PostgreSQL, szczególnie na replikach i przy zamrażaniu stron. Nie odzwierciedlają się w standardowych metrykach monitoringu, maskują się pod „zawieszenia” lub „niezrozumiały lag”, i wymagają głębokiego zrozumienia wewnętrznej budowy systemu zarządzania bazami danych do diagnostyki. W artykule omawiamy mechanizmy powstawania, objawy, metody wykrywania i praktyczne sposoby zmniejszenia wpływu.

Dlaczego konflikty BufferPin są niebezpieczne i niezauważalne

Główna trudność polega na tym, że oczekiwanie na BufferPin nie jest rejestrowane jako blokada w pg_locks i nie zawsze pojawia się w statystykach konfliktów. Proces czekający na zdjęcie pinu po prostu wisi w stanie wait_event = 'BufferPin', nie generując alertów. Szczególnie dotkliwie objawia się to na replikach:

• Proces startup przy odtwarzaniu rekordów WAL (np. HOT cleanup) musi uzyskać ekskluzywny dostęp do bufora — czyli poczekać, aż pincount = 0.
• Jeśli na replice wykonywany jest długi zapytanie (np. Seq Scan), to utrzymuje pin na bloku.
• Startup całkowicie się zatrzymuje do czasu zwolnienia bloku.
• Ponieważ nakładanie WAL odbywa się w jednym wątku, opóźnienie na jednym bloku prowadzi do wzrostu laga całej repliki.

Podobna sytuacja występuje z autovacuumem na hoście głównym i replice przy zamrażaniu stron (freeze). Autovacuum w trybie normalnym pomija zakreślone bloki, ale w agresywnym (przy freeze) — czeka. Jeśli takich bloków jest dużo, worker'y się zawieszają, narasta zadłużenie w czyszczeniu, co może doprowadzić do przepełnienia transakcyjnego (wraparound).

Ukryte objawy:

• Wzrost laga repliki bez widocznego obciążenia.
• Komunikaty w logach: automatic aggressive vacuum of table... lub skipped due to pins.
• Brak czekających w pg_locks, ale obecność BufferPin w pg_stat_activity.wait_event.

Jak odtworzyć i zdiagnozować problem

Do zrozumienia mechanizmu wystarczy prosty przykład. Stwórzmy tabelę i utkwimy wiersz kursorem w transakcji:

CREATE TABLE t AS SELECT 1 c;
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM t;
FETCH c;

Teraz sprawdźmy stan bufora za pomocą pg_buffercache:

select c.relname, bufferid, relforknumber, usagecount, pinning_backends
  from pg_buffercache b join pg_class c on b.relfilenode =
   pg_relation_filenode(c.oid)
   and c.relname = 't';

Wynik pokaże pinning_backends = 1 — nasza transakcja trzyma pin.

Uruchomimy agresywny VACUUM w drugiej sesji:

vacuum (freeze, verbose, skip_locked off) t;

VACUUM się zawiesi. Powtórne zapytanie do pg_buffercache pokaże pinning_backends = 2. Sprawdźmy oczekiwanie:

SELECT query, wait_event FROM pg_stat_activity WHERE query ILIKE 'vacuum%';
-- wait_event = 'BufferPin'

Jeśli otworzymy jeszcze kilka sesji z podobnymi kursorami, pinning_backends będzie rosło. VACUUM nie wznowi pracy, dopóki wszystkie transakcje się nie zakończą.

Praktyczne metody monitoringu i prewencji

Standardowe narzędzia nie pokazują problemu w czasie rzeczywistym. Ale można zbudować efektywny monitoring na bazie pg_buffercache i pg_stat_activity.

Podstawowe zapytanie do wykrywania gorących bloków:

SELECT 
  n.nspname AS schema_name,
  c.relname AS table_name,
  b.bufferid,
  b.pinning_backends,
  b.usagecount,
  b.isdirty,
  c.relpages
FROM pg_buffercache b 
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
  AND b.pinning_backends > 1
ORDER BY b.pinning_backends DESC
LIMIT 10;

Co ważne sprawdzać regularnie:

• Tabele z wysokim pinning_backends — kandydatki do optymalizacji.
• Obecność długich transakcji (pg_stat_activity.xact_start).
• Procesy oczekujące z wait_event = 'BufferPin'.

Sposoby zmniejszania konfliktów:

• Optymalizacja fillfactor — jeśli tabela zawiera mało wierszy, ale często jest aktualizowana, zmniejszenie fillfactor rozłoży wiersze na kilka bloków, zmniejszając konkurencję o jeden bufor.
• Wyodrębnienie dedykowanej repliki — replika bez zapytań użytkownika nie tworzy konfliktów pin, zapewniając stabilną synchronizację. Tak robi np. OpenAI.
• Konfiguracja max_standby_streaming_delay — zwiększenie wartości daje więcej czasu na wykonywanie zapytań na replice, ale rośnie ryzyko opóźnień.
• Ograniczenie czasu trwania transakcji — wprowadzenie timeoutów i automatycznego kończenia „wiszących” sesji.
• Wstępne czyszczenie przed freeze — uruchomienie ręcznego VACUUM FREEZE w okresach niskiego obciążenia.

Rozszerzenia i alternatywy do głębokiej analizy

W wystąpieniu Aleksandry Bondar wspomniano o niestandardowym rozszerzeniu do monitoringu gorących bloków. Używa ono callback-ów i okresowego zapisu do pamięci, by zminimalizować narzut. Jednak takie rozwiązania rzadko są akceptowane przez społeczność — wymagają modyfikacji jądra i rozwiązują wąsko specjalistyczne problemy.

Alternatywy bez zmian w jądrze:

• pg_wait_sampling — zbiera statystyki czekania, w tym BufferPin, poprzez próbkowanie.
• pg_buffercache + cron — regularny zbiór danych i porównanie z poprzednimi stanami.
• Logowanie autovacuumu — ustawienie log_autovacuum_min_duration = 0 do rejestrowania wszystkich przypadków pominięcia bloków.

Przykład konfiguracji logowania:

ALTER SYSTEM SET log_autovacuum_min_duration = 0;
SELECT pg_reload_conf();

Po tym w logach pojawią się wpisy w rodzaju:

LOG:  skipping block N of relation "public.t" --- block is pinned

To pozwala wykrywać problematyczne tabele nawet bez aktywnego monitoringu.

Co ważne

• Konflikty BufferPin — niewidoczna przyczyna wzrostu laga replik i zawieszeń autovacuumu.
• Diagnostyka możliwa tylko przez pg_buffercache i pg_stat_activity.wait_event.
• Fillfactor i dedykowane repliki — najskuteczniejsze metody zmniejszania konkurencji.
• Logowanie autovacuumu z zerowym progiem pomaga wykrywać problematyczne tabele w produkcji.
• Standardowe metryki (pg_stat_database_conflicts) nie obejmują większości przypadków — potrzebny jest niestandardowy monitoring.

— Editorial Team