PostgreSQL 19 : la fin des lectures obsolètes sur répliques avec WAIT FOR LSN

Dans les architectures d'entreprise modernes, l'utilisation de répliques secondaires en lecture seule (standby nodes, des serveurs de base de données secondaires synchronisés pour alléger le principal) pose un défi récurrent : l'anomalie de la lecture de ses propres écritures (read-your-own-writes). Lorsqu'un frontend dynamique ou une API envoie une mutation puis rafraîchit l'état dans la foulée, la requête de lecture atterrit souvent sur une réplique avant que la modification ne soit appliquée. Sur un banc de test mesuré avec 1 000 requêtes concurrentes, 99,2 % des lectures simples sur réplique échouent à renvoyer la donnée fraîchement inscrite. Les palliatifs habituels — forcer la lecture sur le nœud primaire, introduire des temporisations arbitraires (sleep) ou gérer des marqueurs dans Redis — s'avèrent inefficaces, coûteux ou imprécis.

Ce problème ne provient pas du délai de transit réseau (write_lag ou flush_lag), mais du retard de relecture (replay_lag). Le journal d'écriture séquentiel de PostgreSQL, appelé WAL (Write-Ahead Logging, le registre qui enregistre chaque modification avant son écriture sur disque), est expédié rapidement au standby. En revanche, le processus de restauration du standby applique ces enregistrements un par un de manière mono-filaire. Cette relecture entre en compétition directe avec les requêtes de consultation pour l'accès aux ressources CPU et I/O. La configuration classique synchronous_commit = on ne résout rien : elle garantit seulement que le WAL est écrit sur le disque du standby, pas qu'il a été rejoué par le moteur de base de données.

Des performances mesurées à la milliseconde

Pour combler ce manque, PostgreSQL 19 intègre la commande native WAIT FOR LSN. L'LSN (Log Sequence Number) est l'identifiant positionnel exact à 64 bits d'un enregistrement dans le journal WAL. L'application ordonne au standby de bloquer la transaction en cours jusqu'à ce qu'il ait rejoué la position LSN spécifiée.

Les mesures comparatives sur un serveur PostgreSQL 19 mettent en évidence l'efficacité de cette méthode pour 1 000 requêtes d'écriture suivies de leur lecture :

• Lecture directe sur réplique sans attente : 992 lectures obsolètes sur 1 000 (latence p50 de 1,8 ms).
• Bascule forcée sur le primaire : 0 lecture obsolète (latence p50 de 1,9 ms).
• Pause applicative (sleep) de 50 ms avant lecture : 0 lecture obsolète (latence p50 dégradée à 53,9 ms).
• Utilisation de WAIT FOR LSN : 0 lecture obsolète avec 100 % des requêtes traitées par la réplique (latence p50 de 2,8 ms, p95 à 4,2 ms et p99 à 6,4 ms).

Implémentation en quatre étapes dans vos services

L'intégration applicative de ce mécanisme s'articule autour d'un workflow strict en quatre temps :

1. Après le commit sur le primaire : récupérer la position WAL validée via SELECT pg_current_wal_flush_lsn(). Attention : effectuer cette requête avant le commit (pg_current_wal_insert_lsn) renvoie un LSN antérieur à l'enregistrement COMMIT du WAL, ce qui rend la donnée invisible sur le standby au moment du contrôle.
2. Transmettre l'LSN : faire circuler ce jeton texte (ex: 0/554D1B78) dans la session utilisateur, le jeton JWT ou le header de réponse API.
3. Attendre sur la réplique : exécuter la commande d'attente au sommet du bloc de transaction de lecture.
4. Traiter le résultat : adapter le routage si le délai imparti (timeout) est dépassé.

-- Étape 3 : Attente sur le standby avant la lecture métier
WAIT FOR LSN '0/554D1B78' WITH (
  MODE 'standby_replay',
  TIMEOUT '50ms',
  NO_THROW
);

L'option NO_THROW évite le déclenchement d'une exception SQL en convertissant le résultat en un tableau de statut exploitable. Si le statut renvoie success, la requête lit la donnée garantie sur le standby. S'il renvoie timeout ou not in recovery (cas où le standby vient d'être promu nœud primaire), l'application peut basculer la lecture sur le primaire ou renvoyer un statut dégradé à l'utilisateur.

Impact architectural et précaution sur les pools de connexions

Il faut dimensionner le TIMEOUT comme un budget de routage applicatif et non comme un simple paramètre de réglage de base de données.

Si le délai TIMEOUT est fixé de manière trop généreuse (par exemple 500 ms) et qu'une dégradation globale survient sur le cluster, des centaines de sessions de lecture restent bloquées en attente sur la réplique. Ce phénomène consomme l'intégralité du pool de connexions (connection pool) au pire moment. Lorsque ces requêtes tombent simultanément en timeout, elles basculent toutes en même temps sur le serveur primaire, créant un effet de ruée (thundering herd) capable d'effondrer la base principale. Il est donc recommandé d'associer WAIT FOR LSN à des timeouts courts (20 à 50 ms) et d'isoler ce mécanisme derrière un disjoncteur (circuit breaker).