Conflits BufferPin dans PostgreSQL : comment les trouver et les corriger

# Conflits BufferPin dans PostgreSQL : Diagnostic et optimisation pour les répliques et Autovacuum

Les conflits BufferPin lors de l'épinglage des buffers — l'un des problèmes de performance cachés mais critiques dans PostgreSQL, particulièrement sur les répliques et pendant le gel des pages. Ils n'apparaissent pas dans les métriques de surveillance standard, se font passer pour des « blocages » ou des « retards inexpliqués », et exigent une compréhension approfondie des mécanismes internes du SGBD pour être diagnostiqués. Cet article explique comment ils surviennent, leurs symptômes, les méthodes de détection et des solutions pratiques pour atténuer leur impact.

Pourquoi les conflits BufferPin sont dangereux et difficiles à repérer

Le principal défi, c'est que les attentes BufferPin ne sont pas enregistrées comme des verrous dans pg_locks et n'apparaissent pas toujours dans les statistiques de conflits. Le processus en attente de libération d'un pin reste simplement bloqué dans l'état wait_event = 'BufferPin', sans déclencher d'alertes. Cela frappe particulièrement fort sur les répliques :

• Le processus startup lors de la relecture des enregistrements WAL (par exemple, le nettoyage HOT) a besoin d'un accès exclusif au buffer — c'est-à-dire qu'il doit attendre que pincount = 0.
• Si une requête longue (par exemple, Seq Scan) s'exécute sur la réplique, elle retient le pin sur le bloc.
• Startup s'arrête complètement jusqu'à ce que le bloc soit libéré.
• Comme la relecture WAL se fait dans un seul thread, un délai sur un bloc provoque une accumulation de retard sur l'ensemble de la réplique.

Une situation similaire se produit avec autovacuum sur le primaire et les répliques pendant le gel des pages (freeze). Autovacuum ignore les blocs épinglés en mode normal, mais en mode agressif (pendant le freeze) — il attend. S'il y a beaucoup de tels blocs, les workers se bloquent, un backlog de nettoyage s'accumule, et cela peut mener à un dépassement d'identifiants de transaction.

Symptômes cachés :

• Croissance du retard de la réplique sans charge visible.
• Messages de log comme : automatic aggressive vacuum of table... ou skipped due to pins.
• Aucune attente visible dans pg_locks, mais BufferPin présent dans pg_stat_activity.wait_event.

Comment reproduire et diagnostiquer le problème

Un exemple simple suffit à comprendre le mécanisme. Créez une table et épinglez une ligne avec un curseur dans une transaction :

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

Vérifiez maintenant l'état du buffer via 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';

Le résultat affichera pinning_backends = 1 — notre transaction retient le pin.

Lancez un VACUUM agressif dans une seconde session :

vacuum (freeze, verbose, skip_locked off) t;

VACUUM se bloquera. Une requête répétée sur pg_buffercache montrera pinning_backends = 2. Vérifiez l'attente :

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

Si vous ouvrez quelques sessions supplémentaires avec des curseurs similaires, pinning_backends continuera d'augmenter. VACUUM n'avancera pas tant que toutes les transactions ne seront pas terminées.

Méthodes de surveillance et de prévention pratiques

Les outils standards ne montrent pas le problème en temps réel. Mais vous pouvez mettre en place une surveillance efficace basée sur pg_buffercache et pg_stat_activity.

Requête de base pour détecter les blocs chauds :

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;

À vérifier régulièrement :

• Les tables avec un pinning_backends élevé — candidates idéales pour l'optimisation.
• Présence de transactions longues (pg_stat_activity.xact_start).
• Processus en attente avec wait_event = 'BufferPin'.

Façons de réduire les conflits :

• Optimisation du fillfactor — si la table a peu de lignes mais subit des mises à jour fréquentes, baisser le fillfactor répartit les lignes sur plusieurs blocs, réduisant la contention sur un seul buffer.
• Réplique dédiée — une réplique sans requêtes utilisateur évite entièrement les conflits de pin, assurant une synchronisation stable. C'est comme ça qu'ils procèdent, par exemple, chez OpenAI.
• Ajustement de max_standby_streaming_delay — augmenter la valeur donne plus de temps aux requêtes sur la réplique, mais accroît le risque de retard.
• Limitation de la durée des transactions — implémentez des timeouts et une terminaison automatique des sessions bloquées.
• Nettoyage préventif avant freeze — lancez un VACUUM FREEZE manuel pendant les périodes de faible charge.

Extensions et alternatives pour une analyse approfondie

La conférence d'Alexandra Bondar mentionnait une extension personnalisée pour surveiller les blocs chauds. Elle utilise des hooks de callback et des écritures alternées en mémoire pour minimiser la surcharge. Cependant, de telles solutions sont rarement acceptées par la communauté — elles nécessitent des modifications du cœur et traitent des problèmes très spécifiques.

Alternatives sans changements du cœur :

• pg_wait_sampling — collecte les statistiques d'attente, y compris BufferPin, par échantillonnage.
• pg_buffercache + cron — collecte périodique de données et comparaison avec les états précédents.
• Logging autovacuum — réglez log_autovacuum_min_duration = 0 pour capturer tous les cas de blocs ignorés.

Exemple de configuration de logging :

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

Après cela, les logs incluront des entrées comme :

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

Cela permet d'identifier les tables problématiques même sans surveillance active.

Points clés à retenir

• Les conflits BufferPin sont une cause invisible de croissance du retard des répliques et de blocages d'autovacuum.
• Le diagnostic n'est possible qu'avec pg_buffercache et pg_stat_activity.wait_event.
• Le fillfactor et les répliques dédiées sont les moyens les plus efficaces pour réduire la contention.
• Le logging autovacuum avec un seuil à zéro aide à repérer les tables problématiques en production.
• Les métriques standard (pg_stat_database_conflicts) manquent la plupart des cas — une surveillance personnalisée est essentielle.

— Editorial Team