Mettre à l’Échelle PostgreSQL : Réplicas de Lecture, PgBouncer et Pooling

1. Pourquoi PostgreSQL exige un pool de connexions

Chaque connexion PostgreSQL est adossée à un processus OS dédié (le backend), pas à un thread léger. Le fork de ce processus, l’initialisation de ses structures mémoire locales et son rattachement à shared_buffers coûtent un temps réel et de la RAM — typiquement plusieurs mégaoctets de mémoire résidente par connexion inactive, avant même d’avoir exécuté la moindre requête. Un serveur applicatif ouvrant des centaines de connexions par instance, multiplié par un parc de serveurs applicatifs scalés horizontalement, peut épuiser max_connections ou, pire, la RAM réelle de la machine bien avant que la base ne sature son CPU ou ses I/O.

Le mode de défaillance est contre-intuitif : ajouter des connexions au-delà d’un certain seuil dégrade le débit au lieu de l’améliorer, car les backends se disputent les mêmes cœurs CPU et les mêmes verrous de shared_buffers. La documentation de PostgreSQL et l’expérience opérationnelle convergent vers un nombre optimal de connexions proche de (2 × nombre de cœurs) + le nombre de disques effectifs pour les connexions exécutant réellement des requêtes — bien en deçà des centaines ou milliers qu’une application ouvre naïvement.

Le pooling résout cela en découplant le nombre de connexions logiques côté application du nombre de processus backend réellement maintenus par PostgreSQL. Un pooler comme PgBouncer se place entre l’application et PostgreSQL, multiplexant de nombreuses connexions clientes sur un ensemble bien plus restreint de connexions serveur réelles, gardant le nombre de processus de la base stable quel que soit le nombre d’instances applicatives ou de threads ouvrant des connexions en amont.

Cela compte encore plus, pas moins, à mesure que vous montez en charge — dès que vous introduisez des réplicas de lecture et plusieurs services applicatifs, le nombre naïf de connexions se multiplie par chaque service × chaque réplica, et sans pooling vous atteignez les limites de max_connections bien avant une charge significative, ce qui vous force soit à surdimensionner max_connections (au risque d’un OOM lors d’un vrai pic de trafic), soit à subir des erreurs de connexion lors de déploiements routiniers.

2. Séparation Lecture / Écriture (Read Replicas)

La réplication en streaming de PostgreSQL expédie le write-ahead log (WAL) d’un primaire vers un ou plusieurs serveurs standby, qui le rejouent pour rester synchronisés. Étant asynchrone par défaut, un réplica accuse toujours un certain retard sur le primaire — généralement quelques millisecondes en charge normale, mais pouvant grimper à plusieurs secondes lors de rafales d’écriture, de transactions longues sur le réplica, ou d’à-coups réseau. Toute architecture routant des lectures vers des réplicas doit être conçue en tenant compte de ce lag comme un fait de premier plan, pas un cas limite.

La répartition pratique est : les écritures (INSERT/UPDATE/DELETE, et toute lecture devant voir sa propre écriture précédente) vont au primaire ; les lectures tolérant une donnée légèrement obsolète vont aux réplicas. Cela fonctionne bien pour les tableaux de bord, les index de recherche, les requêtes de reporting et la plupart des endpoints d’API de type GET, mais échoue pour les schémas read-your-writes — un utilisateur soumettant un formulaire et étant immédiatement redirigé vers une page relisant ce qu’il vient d’écrire est le bug classique, où le réplica n’a pas encore rattrapé son retard et l’utilisateur voit une donnée obsolète ou absente.