Chapitre 11.4 — Tuning & connexions

⏱️ TL;DR — Une base par défaut tient bien pour démarrer, mais deux choses la font vaciller sous charge : trop de connexions ouvertes (chaque connexion Postgres coûte de la RAM) et une mémoire mal répartie. La parade aux connexions : le pooling (un pool côté app/ORM, ou PgBouncer en façade) qui recycle un petit nombre de connexions au lieu d’en ouvrir des centaines. Côté mémoire, quelques paramètres comptent (shared_buffers, work_mem, effective_cache_size) — mais on n’invente pas de valeurs magiques : on part de PGTune et on ajuste en mesurant. Grande décision transverse : héberger la base sur le même VPS (simple, gratuit, mais concurrence de ressources) ou la prendre managée (backups/patchs/HA gérés, mais coût et données chez un tiers). Et pour piloter tout ça : surveiller les requêtes lentes.

🎯 Objectifs

• Comprendre pourquoi les connexions sont une ressource limitée, et ce qu’est le pooling.
• Situer le rôle d’un pool applicatif et de PgBouncer.
• Connaître les paramètres mémoire clés de Postgres — et la méthode pour les régler (pas des valeurs magiques).
• Arbitrer entre base auto-hébergée et base managée.
• Mettre en place la surveillance des requêtes lentes.

Les connexions : une ressource qui coûte cher

Chez PostgreSQL, chaque connexion est un processus côté serveur, qui réserve de la mémoire. Ce n’est pas gratuit : quelques centaines de connexions ouvertes peuvent, à elles seules, saturer la RAM d’un petit VPS. Le paramètre max_connections fixe le plafond (souvent 100 par défaut). Le réflexe naïf — « augmentons max_connections à 500 » — est un piège : tu multiplies la consommation mémoire et tu déplaces le problème.

Le vrai souci apparaît avec les apps modernes : un framework qui, sous le trafic, ouvre une connexion par requête épuise le plafond en un pic. La bonne réponse n’est pas plus de connexions, c’est de réutiliser un petit nombre de connexions. C’est le pooling.

⚠️ Piège — Gonfler max_connections pour « encaisser plus ». Chaque connexion consomme de la RAM (et un peu de work_mem, voir plus bas) ; en monter le plafond très haut sur un petit VPS invite l’OOM killer (Partie 1.3) à tuer Postgres au pire moment. La solution au « trop de connexions » est presque toujours un pool, pas un plafond plus haut.

Deux niveaux de pooling

• Le pool côté application/ORM. Ton ORM (Prisma, Drizzle, Doctrine, TypeORM…) gère un pool interne : il ouvre N connexions au démarrage et les recycle entre les requêtes. C’est le premier réflexe, souvent suffisant : tu fixes la taille du pool dans la config de l’app (une taille modeste, cohérente avec max_connections, pas énorme).
• PgBouncer, le pooler dédié. Pour des besoins plus lourds (beaucoup de processus applicatifs, du serverless qui ouvre/ferme sans cesse), on place PgBouncer entre l’app et Postgres. Léger, il maintient un petit lot de connexions réelles et multiplexe des milliers de connexions clientes dessus. On le cite ici en survol : sache qu’il existe et à quoi il sert ; sa mise en œuvre est un sujet à part.

💡 Réflexe — Commence par dimensionner le pool de ton ORM avant de sortir l’artillerie PgBouncer. Une règle de bon sens : la somme des tailles de pool de tous tes processus applicatifs doit rester sous max_connections, avec une marge pour l’admin. Si tu satures malgré un pool bien réglé, alors PgBouncer devient pertinent.

Les paramètres mémoire : la méthode, pas des chiffres

Trois paramètres de postgresql.conf pèsent le plus sur les performances. Comprends ce qu’ils font — mais pour les valeurs, suis la méthode, ne recopie pas des chiffres trouvés au hasard.

Le piège de work_mem mérite un mot : c’est de la mémoire par opération et par connexion. Une valeur généreuse × beaucoup de requêtes concurrentes × plusieurs tris chacune = explosion mémoire. C’est pour ça qu’on ne le règle pas isolément mais en lien avec max_connections.

🔒 Sécurité — Un mauvais tuning est aussi un risque de disponibilité : une base qui swappe ou se fait tuer par l’OOM, c’est ton service à terre et, potentiellement, des écritures perdues. La robustesse fait partie de la sécurité (la disponibilité est un des piliers, avec la confidentialité et l’intégrité). Régler la mémoire avec méthode, ce n’est pas du confort, c’est de la résilience.

📚 La doc — N’invente jamais de valeurs. Pars de PGTune (pgtune.leopard.in.ua), qui te propose un jeu de paramètres cohérent à partir de ta RAM, ton nombre de cœurs et ton type de charge. Vérifie chaque directive dans la doc officielle PostgreSQL, section « Server Configuration ». Puis mesure avant/après : le tuning se valide, il ne se devine pas.

🐚 Au terminal — Voir la valeur courante d’un paramètre, sans fouiller les fichiers :

sudo -u postgres psql -c "SHOW shared_buffers;"
sudo -u postgres psql -c "SHOW work_mem;"
sudo -u postgres psql -c "SHOW max_connections;"

Où héberger la base : même VPS ou managée ?

C’est une décision d’architecture qui revient tôt. Deux options, un arbitrage honnête.

Base sur le même VPS (l’approche par défaut de ce cours). Avantages : simplicité (une machine à opérer), coût nul en plus, latence minimale (l’app parle à la base en local, sans réseau), et contrôle total de tes données. Inconvénients : app et base se disputent CPU, RAM et disque (un gros build peut ralentir la base) ; et tu es responsable des sauvegardes, des mises à jour et de la reprise sur panne.

Base managée (fournie par un hébergeur : instance Postgres/MySQL clés en main). Avantages : backups automatiques, patchs de sécurité appliqués pour toi, souvent haute disponibilité (réplica, bascule automatique), et ressources isolées de l’app. Inconvénients : coût (nettement plus qu’un VPS), et surtout tes données vivent chez un tiers — un point qui a des implications de conformité (localisation, sous-traitance RGPD).

💡 Réflexe — Pour la majorité des projets qui démarrent, la base sur le même VPS est le bon choix : simple, économique, rapide, et parfaitement viable avec des sauvegardes sérieuses. On passe au managé quand la disponibilité devient critique, que l’équipe manque de temps pour l’ops, ou que la charge justifie d’isoler les ressources. Ce n’est pas « managé = mieux » : c’est un arbitrage coût / temps / contrôle.

Surveiller les requêtes lentes

On ne tune pas à l’aveugle : on regarde ce qui rame. Deux outils Postgres, en survol.

• log_min_duration_statement : un paramètre qui fait journaliser toute requête dépassant un seuil de durée. Réglé à une valeur raisonnable, il fait remonter dans les logs les requêtes trop lentes, avec leur texte — de quoi cibler quoi optimiser (souvent : un index manquant).
• pg_stat_statements : une extension qui agrège les statistiques de toutes les requêtes (nombre d’appels, temps total, temps moyen). Elle répond à « quelles requêtes coûtent le plus cher au global ». On l’active dans la config, puis on l’interroge comme une table. Cité ici en survol ; c’est l’outil de référence pour l’optimisation.

🧭 Sur FormaCampus — Postgres partage le VPS avec l’API Symfony. L’équipe garde la base auto-hébergée (coût maîtrisé, données chez elle pour le RGPD), avec un pool Doctrine de taille modeste et un max_connections cohérent — pas gonflé. Elle a réglé les paramètres mémoire via PGTune selon la RAM du VPS, puis mesuré. Et elle a activé log_min_duration_statement : c’est comme ça qu’ils ont repéré une requête de listing de cours sans index, corrigée en une migration. Le jour où le trafic explosera, ils envisageront une base managée — décision de coût, pas de principe.

✏️ Exercices

Exercice 1 — Diagnostique la saturation. En pic de trafic, ton app renvoie des erreurs « too many connections ». Un collègue propose de passer max_connections de 100 à 600. Pourquoi est-ce une mauvaise idée, et que fais-tu à la place ?

Exercice 2 — VPS ou managée ? Une petite association a un site à trafic faible, un budget très serré et une personne semi-technique. Base sur le VPS ou managée ? Justifie.

🧠 Quiz de révision

Chapitre suivant : Sauvegardes de base — le chapitre le plus important de tous : sauvegarder, automatiser, et surtout restaurer.