Audit des index Postgres avec pg_stat : trouver les index lents et inutilisés

Ce qu'est (et n'est pas) un audit d'index Postgres

Un audit d'index Postgres est une remise en réalité basée sur ce que votre base de données fait réellement en production. Il répond à des questions pratiques : quelles requêtes sont lentes, quels index sont utilisés, quels index ne sont jamais touchés, et où passe le temps.

L'objectif n'est pas de collecter des index « agréables à avoir ». C'est d'accélérer les chemins les plus sollicités et de réduire les coûts évitables.

Les index semblent souvent corrects en dev parce que le trafic dev est propre : petites tables, requêtes prévisibles et quelques chemins heureux. La production est plus chaotique. Les tables grossissent, les filtres et tris varient, des cas limites apparaissent, et beaucoup d'utilisateurs frappent les mêmes endpoints en même temps. Une requête qui paraît « assez rapide » sur 10 000 lignes peut s'effondrer à 10 millions si elle commence à faire des scans séquentiels ou de gros tris.

Un bon audit vous aide à :

• Identifier des patterns de requêtes lentes sous charge réelle
• Confirmer quels index aident aujourd'hui
• Trouver des index qui coûtent en stockage et ralentissent les écritures sans bénéfice
• Décider quoi ajouter ou ajuster, et comment prouver que cela a aidé

« Statistiques réelles des requêtes » signifie le comportement mesuré sur la charge live : combien de fois une requête s'exécute, combien de temps elle prend, combien de lignes elle retourne, et si elle a des pics. Deviner à partir du seul schéma est risqué. Un schéma peut suggérer ce qui pourrait être indexé, mais il ne peut pas dire quels filtres les utilisateurs utilisent réellement ni quelles requêtes dominent le temps DB.

Il faut aussi garder les compromis clairs. Les index ne sont pas gratuits :

• Ils accélèrent les lectures (SELECT) quand ils correspondent à vos filtres, jointures et ordre de tri.
• Ils peuvent ralentir les écritures (INSERT/UPDATE/DELETE) parce que chaque changement doit mettre à jour les index.
• Ils augmentent le stockage et le travail de maintenance (vacuum, bloat, sauvegardes).

Donc un audit n'est pas « ajouter des index partout ». C'est « ajouter les bons, supprimer le gaspillage, et vérifier les résultats avec les statistiques ». On voit souvent ça dans des codebases générées par l'IA (par exemple par Cursor, Replit ou v0), où des requêtes sont livrées sans indexation soignée. L'app fonctionne en démo, puis le trafic de production expose rapidement les chemins lents.

Avant de commencer : assurez-vous que vos stats sont fiables

Un audit d'index n'est aussi bon que les statistiques qui le sous-tendent. Si votre base a redémarré, basculé ou si les stats ont été réinitialisées, vous pouvez finir par ajouter le mauvais index ou supprimer un index utile.

D'abord, confirmez que vous pouvez lire les vues dont vous avez besoin. La plupart des signaux viennent de vues systèmes comme pg_stat_*. Sur un Postgres managé vous n'êtes peut‑être pas superutilisateur, mais vous devriez en général pouvoir lire les vues pg_stat communes avec un rôle adapté à la supervision.

Avant de récupérer des chiffres, faites quelques vérifications rapides :

• Assurez-vous de pouvoir interroger pg_stat_database, pg_stat_user_tables et pg_stat_user_indexes sans erreur.
• Vérifiez si les stats ont été récemment réinitialisées (après un redémarrage, un basculement ou une réinitialisation manuelle).
• Confirmez que les requêtes de votre appli ne contournent pas majoritairement Postgres (une couche de cache peut masquer la vraie charge DB).
• Faites l'audit sur la même base qui sert le trafic réel.

Si vous pouvez l'utiliser, pg_stat_statements rend l'audit beaucoup plus fiable car il agrège le comportement réel des requêtes dans le temps (appels, temps total, temps moyen). Il doit être installé et activé à l'avance, et il ne suit que les requêtes après son démarrage.

Choisissez une fenêtre d'observation qui correspond à l'utilisation de l'app. Un bon défaut est quelques cycles métiers réels, y compris les heures de pointe.

Évitez une fenêtre déformée sauf si la déformation est le problème que vous essayez de résoudre. Ne faites pas d'audit juste après :

• Un déploiement majeur qui a changé les patterns de requêtes
• Un backfill ou une migration qui a inondé la base
• Une importation massive qui n'est pas du trafic normal
• Un incident où le système était dégradé

Un piège courant : une fonctionnalité récemment livrée déclenche accidentellement une boucle N+1. Vos stats crieront alors pour des index, alors que le vrai correctif est d'arrêter l'explosion de requêtes.

Les vues pg_stat sur lesquelles vous vous appuierez (carte rapide)

Un audit pratique repose sur un principe : faites confiance à ce que votre base a observé sous la charge réelle.

Ces vues couvrent la plupart de ce dont vous avez besoin :

• pg_stat_statements : un tableau des requêtes normalisées avec totaux et moyennes pour le temps, les appels et les lignes. C'est là que vous trouvez quels patterns de requêtes consomment le plus de latence.
• pg_stat_user_tables : comment chaque table est accédée. Le signal clé est si elle est principalement atteinte via des index ou via des scans séquentiels.
• pg_stat_user_indexes : à quelle fréquence chaque index est utilisé. Utile pour repérer des index qui semblent importants mais ont presque zéro utilisation.
• pg_statio_user_tables : si les lectures de table viennent du cache ou du disque. Beaucoup de lectures disque corrèlent souvent avec une lenteur visible par l'utilisateur.
• pg_statio_user_indexes : la même vue cache vs disque, mais pour les pages d'index.

Une idée reçue : les comptes d'utilisation et l'impact ne sont pas la même chose.

• Les comptes d'utilisation (par exemple « combien de fois un index a été scanné ») montrent la popularité, pas le temps économisé.
• Les signaux de temps et d'I/O (depuis pg_stat_statements et les vues pg_statio_*) montrent la douleur.

Un bon workflow : trouvez les formes de requêtes coûteuses dans pg_stat_statements, puis utilisez les stats de tables et d'index pour comprendre pourquoi ces requêtes sont lentes (scans de table, lectures disque, faible sélectivité, etc.).

Rappelez-vous aussi que les stats ne sont pas permanentes. Elles se réinitialisent au redémarrage, et on peut les réinitialiser manuellement. « Inutilisé » signifie parfois seulement « inutilisé depuis la dernière réinitialisation », donc assurez-vous que votre fenêtre couvre le trafic normal.

Étape par étape : réaliser un audit d'index simple en 30-60 minutes

Ceci est un audit rapide basé sur des signaux de trafic réel. Si vous avez hérité d'une appli où certaines pages rament ou expirent, ces instantanés vous aident à vous concentrer sur ce que les utilisateurs frappent réellement.

Étape 1 : Extraire vos requêtes les plus lentes (temps total et temps moyen).

-- Requires pg_stat_statements
-- Postgres 13+ uses *_exec_time columns
SELECT
  queryid,
  calls,
  total_exec_time AS total_ms,
  mean_exec_time  AS mean_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  left(query, 120) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

SELECT
  queryid,
  calls,
  total_exec_time AS total_ms,
  mean_exec_time  AS mean_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  left(query, 120) AS query_sample
FROM pg_stat_statements
WHERE calls >= 20
ORDER BY mean_exec_time DESC
LIMIT 20;

Utilisez la première requête pour trouver ce qui brûle le plus de temps total sur la journée. Utilisez la seconde pour trouver des statements lents qui peuvent provoquer des pics de latence utilisateur.

Étape 2 : Trouver les tables effectuant beaucoup de lectures séquentielles. Ce sont des candidats courants pour un index manquant ou une mauvaise forme de requête.

SELECT
  schemaname,
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  n_live_tup,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 20;

Étape 3 : Lister les index inutilisés ou à peine utilisés. Les gros index avec un faible idx_scan sont des suspects évidents.

SELECT
  schemaname,
  relname      AS table_name,
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 30;

Étape 4 : Repérer les tables à écritures lourdes où des index supplémentaires peuvent vous pénaliser. Chaque index supplémentaire augmente le travail des inserts et updates.

SELECT
  schemaname,
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_tup_hot_upd,
  (n_tup_ins + n_tup_upd + n_tup_del) AS total_writes
FROM pg_stat_user_tables
ORDER BY total_writes DESC
LIMIT 20;

Étape 5 : Sauvegardez des snapshots horodatés pour comparer avant / après. Stockez les résultats quelque part cohérent (CSV ou petite table) et prenez des notes sur ce qui a changé.

CREATE SCHEMA IF NOT EXISTS audit;
CREATE TABLE IF NOT EXISTS audit.slow_queries_snap (
  captured_at timestamptz NOT NULL,
  queryid     bigint,
  calls       bigint,
  total_ms    double precision,
  mean_ms     double precision,
  query_sample text
);

INSERT INTO audit.slow_queries_snap
SELECT
  now(), queryid, calls,
  total_exec_time, mean_exec_time,
  left(query, 200)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 50;

Comment repérer les index manquants avec les signaux de trafic réel

Un index manquant apparaît en général sous deux formes simultanément :

• Une grosse table est scannée de façon répétée
• Un petit ensemble de patterns de requêtes revient régulièrement comme coûteux

Commencez par les signaux au niveau des tables. Dans pg_stat_user_tables, un seq_scan élevé (surtout associé à un seq_tup_read élevé) sur une table volumineuse est un fort indice que des requêtes filtrent ou joignent sans index. Ce n'est pas une preuve, mais cela indique où regarder.

Puis reliez cela aux patterns de requêtes dans pg_stat_statements. Vous cherchez des accès répétés :

• Filtres WHERE communs (par ex. user_id, created_at, tenant_id)
• Clés de JOIN (les clés étrangères sont souvent en cause)
• Colonnes ORDER BY qui forcent un tri sur de grands ensembles
• Requêtes avec LIMIT qui lisent encore beaucoup parce qu'elles ne peuvent pas utiliser efficacement un index

Une règle simple : indexez d'abord les colonnes sélectives. Une colonne sélective a beaucoup de valeurs distinctes et réduit rapidement la recherche. WHERE user_id = 42 est souvent sélectif. WHERE status = 'active' souvent moins.

Les index multi-colonnes peuvent être la bonne solution, mais l'ordre compte car Postgres exploite surtout la partie gauche d'un index btree. Si votre requête fréquente est :

SELECT * FROM orders
WHERE user_id = $1 AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

Un index comme (user_id, created_at) correspond bien. L'inverse (created_at, user_id) aide souvent moins parce qu'il ne restreint pas à un utilisateur dès le départ.

Comment trouver les index inutilisés (et quand ne pas les supprimer)

Les index inutilisés sont sournois : ils ajoutent du coût aux écritures, prennent de la place disque et alourdissent le vacuum et les backups. Le but n'est pas de supprimer agressivement ; c'est d'enlever ceux qui n'aident jamais votre charge réelle.

Commencez par pg_stat_user_indexes. Un premier tri basique :

• idx_scan = 0 depuis la dernière réinitialisation des stats (candidat)
• Très peu de scans comparés au volume d'écritures de la table (candidat)

Puis faites une vérification contextuelle. Certains index existent pour la correction, pas pour la vitesse :

• Index soutenant un PRIMARY KEY ou une contrainte UNIQUE
• Index critiques pour les vérifications de foreign key
• Index utilisés pour des règles applicatives (par ex. « une souscription active par utilisateur »)
• Index nécessaires pour des jobs rares mais importants (rapports, exports, investigations)

Une erreur classique : vous voyez idx_scan = 0 pour orders_created_at_idx, vous le supprimez, et plus tard vous découvrez qu'une réconciliation mensuelle en dépendait. Si vous ne regardez qu'une semaine de stats, vous pouvez vous tromper.

Un processus prudent qui fonctionne :

1. Faites une courte liste de candidats (table, index, pourquoi vous pensez qu'il est inutilisé).
2. Re-vérifiez après un cycle métier complet (souvent 2-4 semaines).
3. Si ça semble toujours inutilisé, supprimez-le pendant une fenêtre planifiée et surveillez latence et temps d'écriture.
4. Conservez la définition de l'index pour pouvoir le recréer rapidement.

Trouver les plus gros coupables de latence via les stats de requête

Si vous voulez le plus grand gain rapidement, classez les requêtes réelles par le temps total qu'elles brûlent. pg_stat_statements vous aide à voir ce qui coûte sur la journée, pas seulement ce qui est lent une fois.

Deux patterns à comparer :

• Une requête très lente (2 secondes) qui s'exécute deux fois par jour
• Une requête « moyenne » (40 ms) qui s'exécute 10 000 fois par jour

Le second fait généralement plus de mal.

Voici un point de départ simple. Les noms de colonnes varient selon la version de Postgres (vous pouvez voir total_time/mean_time au lieu de total_exec_time/mean_exec_time) :

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Puis relancez en ordonnant par le temps moyen pour repérer les statements lents ponctuels :

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  query
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 20;

Pour chaque requête candidate, capturez quelques faits avant de changer les index :

• Le texte de la requête et son queryid
• Appels, temps total, temps moyen
• Lignes retournées (retourne-t-elle bien plus que ce dont l'appli a besoin ?)
• shared_blks_hit vs shared_blks_read (principalement en cache, ou beaucoup de lectures disque ?)
• Un rapide EXPLAIN (ANALYZE, BUFFERS) dans un environnement sûr

Les index ne sont pas toujours la réponse. Une latence élevée peut venir de jointures qui multiplient les lignes, d'une pagination manquante, de la récupération de gros blobs JSON, ou d'un trop grand travail fait dans SQL.

Valider et concevoir le bon index (sans improviser)

Après avoir repéré une requête lente, ne créez pas d'index immédiatement. Confirmez d'abord ce que la base fait réellement pour la forme de requête en production. Le moyen le plus rapide est de tester la requête exacte (avec des paramètres réalistes) en utilisant EXPLAIN (ANALYZE, BUFFERS).

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 42
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

Lisez-le comme une histoire :

• Seq Scan signifie que Postgres lit beaucoup la table et filtre après. C'est acceptable sur de petites tables, mais souvent lent sur de grosses.
• Index Scan ou Index Only Scan signifie que Postgres peut accéder directement aux lignes nécessaires.
• BUFFERS montre combien de travail s'est fait en mémoire vs disque, ce qui explique souvent pourquoi une requête est parfois rapide et parfois lente en période de pointe.

Quand vous concevez un index, faites-le correspondre à la façon dont la requête filtre et trie :

• Index mono-colonne quand une colonne fait la plupart du filtrage (par ex. user_id).
• Index composite quand vous filtrez sur plusieurs colonnes ensemble (par ex. (user_id, status)). L'ordre des colonnes compte.
• Index partiel quand vous interrogez toujours un sous-ensemble (par ex. status = 'open'). Index plus petit, coût d'écriture réduit.
• Inclure la colonne de tri quand cela évite un tri important (par ex. (user_id, status, created_at)).

Gardez les changements petits et réversibles :

1. Ajoutez un index ciblant une seule requête à haute latence.
2. Relancez EXPLAIN (ANALYZE, BUFFERS) pour confirmer que le plan s'est amélioré.
3. Mesurez à nouveau sur une fenêtre comparable.
4. Si le temps d'écriture ou de verrouillage s'empire, revenez en arrière.

Si vous avez hérité d'un code généré par l'IA, la requête elle-même est souvent le vrai problème (filtres manquants, récupération de trop de lignes, tris sur de grands ensembles). Corrigez la requête d'abord si c'est le cas.

Erreurs courantes qui font échouer un audit d'index

Les audits tournent mal quand vous considérez une seule statistique comme « la vérité » et que vous agissez trop vite.

Erreurs fréquentes :

• Supprimer un index qui protège la correction. Les index soutenant PRIMARY KEY ou UNIQUE sont importants même s'ils semblent « inutilisés ». Certains index sont aussi critiques pour les foreign keys.
• Poursuivre idx_scan sans regarder le temps et les lignes. Un faible nombre de scans n'est pas automatiquement « sûr à supprimer », et un nombre élevé de scans n'est pas automatiquement « à garder ».
• Ajouter des index qui se chevauchent pour chaque variante de requête. Ceci mène à des doublons proches, plus de coût d'écriture et plus de maintenance.
• Blâmer les index alors que le vrai problème est le bloat ou des stats obsolètes. Si autovacuum et analyze sont en retard, les plans peuvent se dégrader et les scans ralentir.
• Tester pendant les heures calmes et supposer que les pics s'amélioreront. Chaleur du cache, concurrence et pression d'écriture changent le comportement des plans.

Faites un seul changement à la fois, mesurez avant et après sous une charge similaire, et gardez un plan de rollback.

Checklist rapide et étapes suivantes

Un audit ne rapporte que si vous pouvez le répéter. Gardez un rapport simple que vous pouvez exécuter aujourd'hui, puis réexécutez après chaque changement pour prouver le gain (ou détecter une régression).

Votre rapport reproductible en 30 minutes

• Capturez les requêtes top par temps total et par temps moyen (gardez petit, par exemple 20 chacun).
• Notez les grandes tables avec des lectures séquentielles élevées.
• Gardez une courte liste d'index apparemment inutilisés, avec une raison en une ligne.
• Proposez jusqu'à trois changements (ajout, suppression ou ajustement) et liez chacun à une requête ou table spécifique.
• Relancez le même rapport après le déploiement et comparez temps, appels, lignes et scans.

Avant de supprimer quoi que ce soit, faites une vérification d'exception : soutient-il une clé primaire ou unique, supporte-t-il une foreign key, ou existe-t-il pour un job rare mais important ?

Quand demander un second avis

Si votre app a été générée par des outils comme Replit, Cursor ou v0, les problèmes de performance proviennent souvent d'un mélange d'index manquants, de requêtes inefficaces et de patterns dangereux. FixMyMess (fixmymess.ai) se concentre sur le diagnostic et la réparation des codebases générées par l'IA, y compris les problèmes de requêtes et de schéma qui ne se manifestent qu'avec la charge de production. Si vous êtes bloqué, un audit ciblé peut vous aider à choisir l'ensemble minimal de changements qui réduit réellement la latence.