Quand une base ralentit, le premier réflexe est de demander une machine plus puissante. C’est parfois la bonne réponse, mais rarement la première à essayer : une requête mal servie reste lente sur un serveur deux fois plus gros. Voici cinq vérifications à faire avant.
1. Trouver les requêtes qui coûtent vraiment
L’extension pg_stat_statements cumule le temps passé par type de requête. On y découvre souvent que la lenteur ne vient pas de la grosse requête dont tout le monde se plaint, mais d’une petite requête exécutée des dizaines de milliers de fois par heure.
2. Lire le plan d’exécution
EXPLAIN (ANALYZE, BUFFERS) montre ce que PostgreSQL fait réellement : quelles tables il parcourt, dans quel ordre, et où passe le temps. Attention, ANALYZE exécute la requête : pour une modification de données, on l’encadre d’une transaction que l’on annule.
3. Vérifier les index — dans les deux sens
Un parcours complet d’une grande table pour en extraire quelques lignes signale presque toujours un index manquant. L’inverse existe aussi : chaque index ralentit les écritures, et une table chargée chaque nuit avec dix index dont huit ne servent jamais paie ce coût pour rien.
4. Contrôler les statistiques et le nettoyage
PostgreSQL choisit ses plans à partir de statistiques sur les tables, et récupère l’espace des lignes obsolètes par un nettoyage automatique. Après un gros chargement, des statistiques périmées conduisent à de mauvais plans : un ANALYZE en fin de traitement règle une part surprenante des lenteurs « inexplicables ».
5. Relire la configuration
Les valeurs par défaut de PostgreSQL sont volontairement prudentes, pensées pour démarrer sur n’importe quelle machine. La mémoire partagée et la mémoire de tri, notamment, méritent d’être ajustées au serveur réel et à l’usage : transactionnel ou décisionnel, les bons réglages ne sont pas les mêmes.
Et si cela ne suffit pas
Alors seulement se posent les questions d’architecture : partitionner les grandes tables, séparer la base de production de celle qui sert les rapports, ou construire un entrepôt dédié à l’analyse. Ces décisions se prennent mieux une fois les cinq points précédents écartés, parce que l’on sait alors ce que l’on cherche à résoudre.
Pour un diagnostic sur votre base, voyez la page Bases de données ou écrivez-moi.