Checklist PostgreSQL : 20 points à vérifier pour la performance
10 min de lecture
Une checklist concrète en 20 points pour auditer la performance d'une instance PostgreSQL : index, vacuum, autovacuum, statistiques et charge de travail.
Index (points 1 à 5)
1. Toute requête fréquente avec une clause WHERE sélective dispose-t-elle d'un index adapté ? Un EXPLAIN ANALYZE révélant un Seq Scan sur une grande table est le premier signal à vérifier.
2. Les requêtes combinant un filtre et un ORDER BY bénéficient-elles d'un index composite couvrant les deux, plutôt que de deux index séparés que PostgreSQL ne peut pas toujours combiner efficacement ?
3. Des index inutilisés (jamais lus selon pg_stat_user_indexes.idx_scan) existent-ils encore ? Chaque index inutile ralentit les écritures (INSERT/UPDATE/DELETE) sans jamais accélérer une lecture.
4. Des index dupliqués ou redondants (même colonne de tête, préfixe identique) traînent-ils sur une même table, gonflant inutilement l'espace disque et le coût de maintenance ?
5. Les index sur les clés étrangères existent-ils bien ? PostgreSQL ne les crée jamais automatiquement, contrairement à d'autres SGBD — leur absence est une cause fréquente de lenteurs sur les jointures et les suppressions en cascade.
Vacuum & Autovacuum (points 6 à 10)
6. autovacuum est-il activé sur toutes les tables, y compris les tables à forte volumétrie d'écritures où il est parfois désactivé par erreur ou par une configuration héritée ?
7. Le bloat (espace occupé par des lignes mortes non encore récupérées) est-il surveillé sur les tables les plus sollicitées ? Un bloat important dégrade silencieusement les performances de lecture bien avant qu'un espace disque ne manque.
8. Les seuils autovacuum_vacuum_scale_factor et autovacuum_vacuum_threshold sont-ils adaptés à la taille réelle des tables, ou laissés aux valeurs par défaut inadaptées aux très grandes tables ?
9. Le vacuum freeze (protection contre le wraparound des identifiants de transaction) est-il suivi, pour éviter une intervention en urgence si la base approche la limite critique ?
10. Les tables soumises à des pics d'écriture ponctuels (imports batch, migrations) bénéficient-elles d'un VACUUM ANALYZE manuel après ces opérations, plutôt que d'attendre le prochain passage automatique ?
Statistiques & planificateur (points 11 à 14)
11. Les statistiques (pg_stats, alimentées par ANALYZE) sont-elles à jour sur les tables qui changent souvent de volume ? Des statistiques obsolètes faussent les estimations de cardinalité et donc le choix du plan d'exécution.
12. default_statistics_target est-il suffisant pour les colonnes à forte cardinalité utilisées dans des filtres complexes, ou faut-il l'augmenter spécifiquement sur ces colonnes ?
13. Les plans d'exécution des requêtes critiques sont-ils revérifiés après une migration de version majeure de PostgreSQL, le planificateur pouvant changer de comportement d'une version à l'autre ?
14. Des extended statistics (CREATE STATISTICS) sont-elles envisagées sur les colonnes corrélées, pour aider le planificateur à mieux estimer les filtres combinés ?
Configuration mémoire & connexions (points 15 à 17)
15. shared_buffers est-il dimensionné en cohérence avec la mémoire disponible sur le serveur, ni trop faible (cache hit ratio dégradé), ni excessif au point de priver le cache disque du système d'exploitation ?
16. work_mem est-il suffisant pour éviter que les tris et hash joins des requêtes courantes ne débordent sur le disque temporaire (visible dans EXPLAIN ANALYZE ou via temp_blks_written de pg_stat_statements) ?
17. max_connections est-il cohérent avec un pooler de connexions (PgBouncer ou équivalent) plutôt que laissé grand ouvert aux connexions directes de chaque instance applicative, ce qui gaspille de la mémoire par connexion inactive ?
Charge de travail & requêtes (points 18 à 20)
18. Les requêtes les plus coûteuses (pg_stat_statements, triées par temps cumulé) sont-elles revues périodiquement, plutôt que seulement au moment d'un incident ?
19. Les transactions longues (idle in transaction) sont-elles surveillées ? Elles retiennent des ressources et peuvent bloquer le vacuum sur les tables concernées, aggravant le bloat.
20. Un historique de snapshots (avant/après déploiement, avant/après montée en charge) est-il disponible pour comparer objectivement deux périodes, plutôt que de se fier à une impression subjective de ralentissement ?