BufferPin konflikty v PostgreSQL: jak najít a odstranit

# Konflikty BufferPin v PostgreSQL: diagnostika a optimalizace pro repliky a autovacuum

Konflikty při pinování bufferů (BufferPin) — jeden z skrytých, ale kritických problémů výkonu PostgreSQL, zejména na replikách a při zmrazení stránek. Neodrážejí se ve standardních metrikách monitoringu, maskují se jako „závěsy“ nebo „nevysvětlitelný lag“ a vyžadují hluboké porozumění vnitřnímu fungování databázového systému pro diagnostiku. V článku rozebíráme mechanismy vzniku, symptomy, metody odhalení a praktické způsoby snížení jejich vlivu.

Proč jsou konflikty BufferPin nebezpečné a nenápadné

Hlavní složitost spočívá v tom, že čekání na BufferPin se nezaznamenává jako zámek v pg_locks a ne vždy se objeví ve statistikách konfliktů. Proces, který čeká na uvolnění pinu, prostě visí ve stavu wait_event = 'BufferPin', aniž by generoval upozornění. Nejvíce to bolí na replikách:

• Proces startup při aplikaci WAL záznamů (např. HOT cleanup) musí získat exkluzivní přístup k bufferu — tedy počkat, až pincount = 0.
• Pokud na replice běží dlouhý dotaz (např. Seq Scan), drží pin na bloku.
• Startup se úplně zastaví, dokud se blok neuvolní.
• Protože aplikace WAL probíhá v jednom vlákně, zpoždění na jednom bloku vede k růstu lagu celé repliky.

Podobná situace nastává s autovacuem na primárním serveru i replice při zmrazení stránek (freeze). Autovacuum v běžném režimu přeskakuje pinované bloky, ale v agresivním (při freeze) — čeká. Pokud je takových bloků hodně, pracovníci visí, hromadí se dluh v čištění, což může vést k přetečení transakcí (wraparound).

Skryté symptomy:

• Růst lagu repliky bez viditelné zátěže.
• Zprávy v logu: automatic aggressive vacuum of table... nebo skipped due to pins.
• Absence čekání v pg_locks, ale přítomnost BufferPin v pg_stat_activity.wait_event.

Jak reprodukovat a diagnostikovat problém

Pro pochopení mechanismu stačí jednoduchý příklad. Vytvoříme tabulku a zamkneme řádek kurzorem v transakci:

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

Nyní zkontrolujeme stav bufferu přes 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';

Výsledek ukáže pinning_backends = 1 — naše transakce drží pin.

Spustíme agresivní VACUUM ve druhé relaci:

vacuum (freeze, verbose, skip_locked off) t;

VACUUM zavěsne. Opakovaný dotaz na pg_buffercache ukáže pinning_backends = 2. Zkontrolujeme čekání:

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

Pokud otevřeme ještě několik relací s podobnými kurzorami, pinning_backends bude růst. VACUUM nepokračuje, dokud se všechny transakce neskončí.

Praktické metody monitoringu a prevence

Standardní nástroje problém v reálném čase neukazují. Lze však sestavit efektivní monitoring na bázi pg_buffercache a pg_stat_activity.

Základní dotaz pro odhalení horkých bloků:

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 je důležité pravidelně kontrolovat:

• Tabulky s vysokým pinning_backends — kandidáti na optimalizaci.
• Přítomnost dlouhých transakcí (pg_stat_activity.xact_start).
• Čekající procesy s wait_event = 'BufferPin'.

Způsoby snížení konfliktů:

• Optimalizace fillfactor — pokud tabulka obsahuje málo řádků, ale často se aktualizuje, snížení fillfactoru rozloží řádky do více bloků a sníží konkurenci o jeden buffer.
• Vydělení dedikované repliky — replika bez uživatelských dotazů nevytváří konflikty pinů a zajišťuje stabilní synchronizaci. Takto postupují např. v OpenAI.
• Nastavení max_standby_streaming_delay — zvětšení hodnoty dává více času na provedení dotazů na replice, ale roste riziko zaostávání.
• Omezení délky transakcí — zavedení timeoutů a automatického ukončení „visících“ relací.
• Předchozí čištění před freeze — spuštění ručního VACUUM FREEZE v obdobích nízké zátěže.

Rozšíření a alternativy pro hlubokou analýzu

V přednášce Alexandry Bondarové bylo zmíněno vlastní rozšíření pro monitoring horkých bloků. Využívá callback háčky a střídavý zápis do paměti pro minimalizaci overheadu. Taková řešení však komunita zřídka přijímá — vyžadují úpravu jádra a řeší úzce specifické úkoly.

Alternativy bez změny jádra:

• pg_wait_sampling — sbírá statistiku čekání včetně BufferPin prostřednictvím sampling.
• pg_buffercache + cron — pravidelný sběr dat a porovnání s předchozími stavy.
• Logování autovacua — nastavení log_autovacuum_min_duration = 0 pro zachycení všech případů přeskakování bloků.

Příklad nastavení logování:

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

Poté se v logu objeví záznamy typu:

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

To umožňuje odhalit problematické tabulky i bez aktivního monitoringu.

Co je důležité

• Konflikty BufferPin — neviditelná příčina růstu lagu replik a závěsů autovacua.
• Diagnostika je možná jen přes pg_buffercache a pg_stat_activity.wait_event.
• Fillfactor a dedikované repliky — nejekonomičtější metody snížení konkurence.
• Logování autovacua s nulovým prahem pomáhá odhalovat problematické tabulky v produkci.
• Standardní metriky (pg_stat_database_conflicts) nevystihují většinu případů — je potřeba vlastní monitoring.

— Editorial Team