PostgreSQL – Tuning avancé : indicateurs, paramètres & bonnes pratiques

Le tuning n’est pas un concours de “paramètres exotiques”. Le but n’est pas d’obtenir un EXPLAIN spectaculaire, mais de réduire la latence réelle et stabiliser la plateforme, en gardant une marge de manœuvre pour la croissance.

🎯 Ce que l’on cherche vraiment

• Des temps de réponse prévisibles sur les parcours critiques (p95, p99 acceptables).
• Une base qui encaisse les pics (campagnes, batchs, clôtures) sans partir en vrille.
• Une consommation CPU / I/O cohérente avec le dimensionnement matériel / cloud.
• Une maintenance maîtrisée (autovacuum, bloat, indexation) sans surprise en journée.

🧠 Principes de base

• Mesurer avant de modifier : toujours partir d’indicateurs, pas d’intuition.
• Un changement à la fois : pour pouvoir attribuer un effet à une cause.
• Commencer par le SQL : la plupart des problèmes viennent des requêtes et des index, pas du fichier de configuration.
• Favoriser les gains structurels : index, partitionnement, mécanismes de verrouillage, plutôt que des “magies” côté paramètres.

⚠️ Ce que le tuning n’est pas

• Un prétexte pour pousser le matériel au maximum en permanence.
• Une série de “recettes universelles” copiées sur un blog.
• Une activité ponctuelle “one shot” : en pratique, le tuning est un processus continu.

Une bonne posture de tuning consiste à se comporter en enquêteur : observer, formuler des hypothèses, tester, mesurer, documenter.

Première question : est-ce que PostgreSQL passe son temps à attendre le disque, ou bien est-il limité par autre chose (CPU, verrouillage, réseau...) ? Les vues pg_stat_database et pg_statio_* sont les premières à regarder.

📈 Hit ratio global & par base

L’indicateur classique est le hit ratio global :

• Sur un OLTP (beaucoup de petites requêtes), on vise en général > 98–99%.
• En-dessous, il faut regarder si le problème vient réellement de la taille de mémoire ou d’un schéma qui force à lire beaucoup de données inutiles.

📊 I/O par table et par index

Pour savoir quelles tables et quels index “chauffent”, on utilise :

• pg_statio_user_tables pour les lectures sur les tables ;
• pg_statio_user_indexes pour les index.

Une table avec beaucoup de lectures disque (blks_read élevés) peut être un candidat à une meilleure indexation, à un partitionnement, ou à une revue fonctionnelle.

🧊 Temp files & sorts

Les fichiers temporaires indiquent que des opérations (tri, hash, etc.) ne tiennent pas en mémoire (work_mem). Trop de temp files = latence et I/O supplémentaires.

Il ne s’agit pas forcément d’augmenter work_mem massivement, mais de comprendre quelles requêtes génèrent ces temp files et pourquoi.

En résumé : ces KPI indiquent “où la base souffre”. Ils ne disent pas encore comment la soigner, mais ils ciblent les zones à inspecter en priorité.

PostgreSQL repose sur le MVCC : lorsqu’on met à jour ou supprime une ligne, l’ancienne version reste physiquement présente jusqu’au passage du VACUUM. Si autovacuum ne suit pas, les tables “gonflent” (bloat), les index grossissent, et les performances se dégradent.

🧮 Rôle d’autovacuum

• Nettoyer les tuples morts (dead tuples) pour libérer de l’espace logique.
• Mettre à jour les statistiques d’optimiseur.
• Éviter que le XID wraparound (overflow de compteur de transactions) ne pose problème.

🧱 Symptômes d’un autovacuum insuffisant

• Tables et index beaucoup plus gros que nécessaire (bloat important).
• Plans d’exécution qui choisissent de mauvais index ou des séquentiels inattendus.
• Autovacuum qui se déclenche en pleine journée sur des tables énormes, avec un impact visible sur la latence.

🔍 Suivi & tuning d’autovacuum

• Surveiller les colonnes n_dead_tup & last_autovacuum dans pg_stat_user_tables.
• Ajuster les seuils (autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor) pour les grosses tables.
• Adapter la “force” d’autovacuum via autovacuum_vacuum_cost_limit / cost_delay pour ne pas saturer I/O.

🧨 Opérations lourdes : VACUUM FULL, REINDEX...

Quand le bloat est massif, on ne s’en sort pas avec de simples VACUUM. Il faut parfois recourir à  VACUUM FULL, REINDEX, CLUSTER, voire à des migrations par table temporaire.

• Ces opérations doivent être planifiées en fenêtres de maintenance.
• Sur les grosses tables critiques, privilégier des stratégies progressives (partitionnement, tables en parallèle, etc.).

Un tuning PostgreSQL sérieux inclut toujours un volet “hygiène des tables” : autovacuum bien réglé + planification des opérations lourdes.

Une grande partie du tuning se joue au niveau des requêtes. Lire un plan d’exécution permet de comprendre comment PostgreSQL exécute le SQL, quelles parties coûtent cher, et comment réécrire ou indexer intelligemment.

🔍 EXPLAIN, ANALYZE, BUFFERS

On ne se contente pas d’un EXPLAIN théorique : on utilise en pratique :

• ANALYZE exécute réellement la requête et affiche les temps mesurés.
• BUFFERS montre combien de blocs viennent du cache vs du disque.

🧩 Points d’attention classiques

• Joins qui explosent (nested loops sur de gros volumes, hash joins sans mémoire suffisante).
• Filtres appliqués trop tard dans le plan (WHERE mal positionné, sous-requêtes).
• Index non utilisés à cause d’expressions, de fonctions ou de types incompatibles.
• Estimations de cardinalité erronées (stats obsolètes ou histogrammes inadaptés).

🛠 Leviers de tuning SQL

• Réécrire les requêtes pour les rendre plus “lisibles” par l’optimiseur.
• Créer les bons index (simples, composés, partiels, sur expressions).
• Revoir le schéma (normalisation / dénormalisation ciblée).
• Limiter la quantité de données traitées (pagination, filtres, agrégations partielles).

L’idée n’est pas de “faire des requêtes jolies”, mais de maintenir un équilibre entre lisibilité fonctionnelle et performance globale.

On ne peut pas parler tuning PostgreSQL sans évoquer le fichier postgresql.conf. Mais la majorité des paramètres n’ont pas besoin d’être modifiés. On se concentre sur quelques familles clés.

💾 Mémoire

• shared_buffers : taille du cache de blocs Postgres. En général une fraction raisonnable de la RAM (pas 80%).
• work_mem : mémoire par opération de tri / hash. Mal réglé (trop bas ou trop haut) peut générer soit des temp files, soit des risques de dépassement mémoire.
• maintenance_work_mem : mémoire pour VACUUM, CREATE INDEX, etc.

🔌 Connexions & pooling

• max_connections : trop élevé = trop de processus, trop de contexte. On préfère souvent un pooler (PgBouncer) avec moins de connexions réelles.
• idle_in_transaction_session_timeout : limite les transactions oubliées.

📜 WAL & durabilité

• wal_level, synchronous_commit : arbitrage entre durabilité stricte et latence.
• checkpoint_timeout, max_wal_size : fréquence et taille des checkpoints, impact direct sur I/O.

🧹 Autovacuum & stats

• autovacuum_* : seuils, agressivité globale, limites de coût.
• default_statistics_target : qualité des stats vs coût de collecte.

L’idée est de partir d’une base saine (ex : générateurs de config raisonnables), puis d’ajuster à partir de mesures concrètes, pas l’inverse.

Certaines opérations sont, par nature, lourdes : gros ALTER TABLE, VACUUM FULL, REINDEX d’index massifs, migrations de schéma. L’enjeu est de les intégrer dans une stratégie de maintenance maîtrisée.

📅 Planifier au bon moment

• Identifier les vraies fenêtres de faible trafic (et les vérifier avec les métriques).
• Éviter les périodes de batchs métiers lourds.
• Communiquer clairement avec les métiers sur les impacts potentiels.

🧱 Stratégies pour limiter l’impact

• Utiliser autant que possible les opérations “concurrentes” (ex : CREATE INDEX CONCURRENTLY).
• Travailler par petits lots plutôt que tout d’un coup (purges, migrations de données...).
• Utiliser des scripts avec contrôle de charge (sleep, throttling, vérif de la latence).

L’objectif : entretenir la base sans transformer chaque opération en “nuit de tous les dangers”.

Quelques pièges que l’on retrouve régulièrement en audit :

🧱 Côté SQL / ORM

• Requêtes générées par l’ORM sans index adaptés (N+1, filtres sur des colonnes non indexées).
• Transactions “infinies” ouvertes côté application (idle in transaction).
• Usage abusif de SELECT * sur des tables larges.

⚙ Côté configuration

• max_connections très élevé sans pool de connexions.
• Paramètres copiés d’un blog “high performance” sans adaptation à la prod réelle.
• Autovacuum désactivé pour “gagner de la perf” (jusqu’au mur...).

📉 Côté exploitation

• Absence de monitoring dédié PostgreSQL (on ne voit les problèmes que quand l’appli explose).
• Runbooks inexistants : chaque incident est traité comme un cas unique, sans capitalisation.

Un bon tuning consiste aussi à supprimer ces anti-patterns, parfois avant même de toucher à un seul paramètre serveur.

Exemple de checklist utilisable en audit ou en revue régulière de la base. À adapter à ton contexte.

Cette checklist peut servir de support à une revue formelle avec les devs, les ops et les métiers, pour aligner tout le monde sur l’état de santé de la base.