Checklist de tuning Postgres en production : les réglages qui réduisent vraiment la latence

postgresql.conf, dans son état par défaut, est pensé pour ne jamais faire échouer une installation, pas pour absorber votre trafic. La valeur historique de shared_buffers, 128 Mo, permet à Postgres de démarrer sur une machine minimale sans réserver la moindre ressource critique. max_connections à 100 tient sur un petit serveur partagé. Aucune des deux n'a été choisie pour votre charge réelle.

Le problème n'est donc pas que Postgres soit mal réglé par défaut : c'est qu'il n'a jamais été réglé pour vous. Les sections qui suivent parcourent les réglages dans l'ordre où ils rapportent le plus, du plus fréquent goulot d'étranglement (les connexions) au plus lent à se manifester (le WAL).

La bonne approche inverse l'ordre habituel : dimensionner max_connections sur la concurrence réelle du serveur, puis absorber la concurrence côté application avec un pooler comme PgBouncer en pool_mode=transaction. Le pooler multiplexe des centaines de connexions clientes sur une poignée de connexions serveur réellement actives. Notre article dédié détaille le mécanisme du mode transaction et ses limites (prepared statements, LISTEN/NOTIFY), et un comparatif oppose PgBouncer, Supavisor et PgCat pour choisir l'implémentation.

max_connections est un paramètre de contexte "postmaster" : le changer exige un redémarrage complet du serveur, pas un simple rechargement. Sur les instances Postgres dédiées d'Aurabase (plans Pro et Enterprise, un cluster CNPG par projet), ce paramètre est configuré par palier de plan plutôt que laissé à sa valeur par défaut. Un redémarrage n'est pas une opération anodine à répéter en production, ce qui justifie ce choix par palier plutôt qu'une valeur fixe. La formule pour choisir sa propre valeur, avec ses limites, fait l'objet d'un article séparé : dimensionner max_connections.

Quatre réglages mémoire pèsent plus que tous les autres réunis : shared_buffers, effective_cache_size, work_mem et maintenance_work_mem. Les trois premiers déterminent combien de données Postgres garde en mémoire avant de retourner sur disque ; le quatrième détermine la vitesse d'un VACUUM ou d'une création d'index.

shared_buffers fixe le cache interne partagé par toutes les connexions. Le repère communément documenté par le projet PostgreSQL est d'environ 25% de la RAM disponible sur un serveur dédié à la base. Au-delà, les gains diminuent et le cache disque du système d'exploitation prend le relais. effective_cache_size n'alloue rien : c'est une estimation, donnée au planificateur de requêtes, de la mémoire totale disponible pour le cache (Postgres et OS combinés). Le sous-dimensionner pousse le planificateur vers des scans séquentiels alors qu'un index tiendrait largement en cache ; le repère courant se situe entre 50 et 75% de la RAM.

work_mem est le piège le plus fréquent. Ce n'est pas une limite globale. Chaque opération de tri ou de hachage dans une requête peut en consommer sa propre part, et une requête avec plusieurs jointures peut en réserver plusieurs fois. Une valeur trop généreuse combinée à un max_connections élevé peut épuiser la RAM du serveur sous charge concurrente, même si chaque requête prise isolément paraît raisonnable. maintenance_work_mem, à l'inverse, peut rester nettement plus généreux : il ne s'applique qu'aux opérations de maintenance (VACUUM, CREATE INDEX), rarement concurrentes entre elles.

Seul shared_buffers exige un redémarrage. Les trois autres se rechargent à chaud, y compris pour une session isolée : SET work_mem = '64MB'; le temps d'une seule requête gourmande, sans toucher au réglage global.

La valeur par défaut d'autovacuum_vacuum_scale_factor (20% de lignes mortes avant déclenchement) convient à une petite table, pas à une table de plusieurs millions de lignes à forte écriture. Sur une table de 10 millions de lignes, ces 20% représentent 2 millions de lignes mortes accumulées avant le premier passage. Abaissez ce seuil table par table plutôt que de changer la valeur globale de toute la base.

-- Abaisser le seuil sur une table à forte écriture, pas globalement
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000
);

L'Advisor intégré à Aurabase Studio vérifie cette configuration à chaque analyse de projet, au même titre que les tables sans clé primaire ou les clés étrangères non indexées. C'est un signal explicite plutôt qu'une dégradation silencieuse découverte trop tard.

-- Diagnostiquer une requête lente avant de toucher à la configuration
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = '…'
ORDER BY o.created_at DESC
LIMIT 20;

Un Seq Scan sur une table de plusieurs millions de lignes, là où un Index Scan était attendu, signale presque toujours un problème d'index. Trois causes reviennent le plus souvent : un index absent, un type de colonne incompatible avec l'index existant, ou des statistiques obsolètes après un import massif sans ANALYZE. Ajouter de la RAM ou augmenter work_mem masque parfois ce symptôme sur un petit volume de données ; le problème réapparaît dès que la table grossit.

Pour repérer ces requêtes sans les chercher une par une, pg_stat_statements agrège les statistiques d'exécution de toutes les requêtes du serveur. Un piège fréquent : l'extension doit d'abord être listée dans shared_preload_libraries, un paramètre de contexte "postmaster" qui exige un redémarrage. Sans cette étape, CREATE EXTENSION pg_stat_statements; réussit silencieusement mais ne collecte rien.

# Nécessite un redémarrage complet (paramètre "postmaster")
shared_preload_libraries = 'pg_stat_statements'
-- Une fois le serveur redémarré, dans chaque base à auditer :
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

C'est exactement l'erreur que retourne le backend d'Aurabase quand cette extension est absente : un message explicite plutôt qu'une liste vide silencieuse, qu'on pourrait confondre avec "aucune requête lente". L'Advisor de Studio va plus loin : il classe automatiquement toute requête avec un temps moyen supérieur à 150 ms en avertissement, et au-delà de 500 ms en critique. Ces seuils s'appuient sur les mêmes statistiques pg_stat_statements.

Un checkpoint force Postgres à écrire sur disque toutes les pages modifiées en mémoire depuis le précédent. Par défaut, cette écriture peut se concentrer sur une fenêtre de temps trop courte. Le résultat est un pic de latence disque perceptible côté application, le genre de ralentissement périodique difficile à relier à une requête précise.

checkpoint_completion_target contrôle l'étalement de cette écriture sur l'intervalle entre deux checkpoints. Un détail absent des checklists plus anciennes : PostgreSQL 14, sorti en 2021, a changé sa valeur par défaut de 0.5 à 0.9. Sur une instance en PostgreSQL 16, comme les clusters tenant dédiés d'Aurabase, ce réglage est donc déjà correct par défaut ; l'ajuster manuellement n'a de sens que sur une version antérieure à 14. Voir notre comparatif PostgreSQL 16 vs 17 vs 18 pour les autres changements de version qui affectent le tuning.

max_wal_size agit dans le même sens : une valeur trop basse déclenche des checkpoints plus fréquents qu'attendu, même quand checkpoint_timeout n'est pas encore atteint. L'augmenter réduit la fréquence des checkpoints, au prix d'un temps de récupération plus long après un crash puisqu'il y a plus de WAL à rejouer. Un compromis à trancher selon votre tolérance à l'indisponibilité, pas une valeur universelle.

Cette checklist n'est pas un audit ponctuel à cocher une fois avant la mise en production. Une base qui double de volumétrie ou un trafic qui triple rendent obsolètes les repères choisis au démarrage, souvent sans erreur explicite, juste une dégradation progressive de la latence p95.

Trois signaux méritent une surveillance continue. pg_stat_statements repère les requêtes qui se dégradent dans le temps. pg_stat_activity signale les requêtes bloquées ou anormalement longues en cours d'exécution, et le ratio connexions actives sur max_connections anticipe une saturation avant qu'elle ne produise des erreurs côté application.

L'onglet Observabilité de Studio couvre une partie de ce socle pour tout projet Aurabase, sans outil tiers à installer. Il liste les requêtes lentes, permet d'annuler ou de terminer une requête active par PID, et affiche un indicateur de saturation du pool qui passe en avertissement au-delà de 80% d'utilisation. Sur une instance auto-hébergée, cette même surveillance se construit à la main, avec pg_stat_statements activé et un outil de supervision externe branché dessus.

Les seuils cités ici, comme les 150 ms et les 80% de saturation du pool que surveille l'Advisor d'Aurabase Studio, sont un point de départ vérifié en code, pas une vérité universelle. Votre charge réelle reste le seul juge final. Pour dimensionner précisément max_connections plutôt que de suivre une règle générale, l'article dédié détaille la formule et ses limites.