Collecte de statistiques d'utilisation des index et des tables

Imagine que ta base de données, c'est un énorme entrepôt. Les index, c'est les catalogues et la liste qui t'aident à trouver vite ce qu'il te faut. Les tables, c'est les produits sur les étagères. Si un index est mal utilisé, c'est comme si le catalogue traînait dans un coin et que personne ne l'ouvrait jamais. Si une table est très utilisée mais mal structurée ou trop grosse, ça peut fatiguer ton entrepôt (ta base de données) et tout ralentir.

Tâches principales de l'analyse :

1. Évaluer l'efficacité d'utilisation des index. Par exemple, ton index super cher ne sert à rien ? Vire-le !
2. Déterminer la fréquence des opérations de lecture et d'écriture. Ça aide à piger quelles tables sont vraiment sollicitées.
3. Optimiser les requêtes. Les stats t'aident à voir où tu peux accélérer le traitement des données en ajoutant ou modifiant des index.

Vues pg_stat_user_indexes et pg_stat_user_tables

Dans PostgreSQL, t'as deux vues super utiles pour choper des stats : pg_stat_user_indexes et pg_stat_user_tables. On va les décortiquer un peu.

pg_stat_user_indexes : comment les index sont utilisés ?

Champs principaux :

• relname — nom de la table à laquelle l'index est lié.
• indexrelname — nom de l'index.
• idx_scan — combien de fois l'index a été utilisé pour chercher.
• idx_tup_read — nombre de lignes lues via l'index.
• idx_tup_fetch — nombre de lignes réellement retournées (après filtres).

Exemple de requête :

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS index_tuples_read, 
       idx_tup_fetch AS index_tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Ici, on :

• trie les données par nombre d'appels de l'index (idx_scan), pour voir quels index sont les plus populaires.
• si un index est quasi jamais utilisé (idx_scan = 0), pose-toi la question : il sert vraiment à quelque chose ?

Application pratique :

Tu déploies une nouvelle version de ton appli et t'as ajouté un nouvel index. Avec pg_stat_user_indexes, tu peux checker si ta requête utilise vraiment ce nouvel index, ou si PostgreSQL continue d'ignorer ta masterclass d'optimisation.

pg_stat_user_tables : voir les stats sur les tables

Champs principaux :

• relname — nom de la table.
• seq_scan — nombre de scans séquentiels de la table (sans index).
• seq_tup_read — nombre de lignes retournées lors des scans séquentiels.
• idx_scan — nombre de scans par index pour la table.
• n_tup_ins — nombre de lignes insérées
• n_tup_upd — nombre de lignes mises à jour.
• n_tup_del — nombre de lignes supprimées.

Exemple de requête :

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
ORDER BY sequential_scans DESC;

Qu'est-ce qu'on voit ici ?

• Les tables avec beaucoup de scans séquentiels (seq_scan) peuvent indiquer qu'il manque un index.
• Le nombre d'insertions, updates et suppressions aide à piger à quel point les données bougent dans la table.

Application pratique : Tu bosses sur la table users, qui stocke les infos de tous les utilisateurs de ton appli. Avec pg_stat_user_tables, tu remarques que les scans séquentiels (seq_scan) explosent. C'est un signal : il est temps de créer des index sur les colonnes les plus utilisées pour accélérer les requêtes.

Exemple : analyse des index et tables dans une vraie base

Imaginons qu'on a une base avec les tables orders (commandes) et products (produits). On veut piger comment les tables et index sont utilisés.

Analyse des index :

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS tuples_read, 
       idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY index_scans DESC;

Tu vois que l'index orders_customer_id_idx a été appelé 50 000 fois, alors que orders_date_idx seulement 5 fois. Peut-être que orders_date_idx ne sert à rien.

Analyse des tables :

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       seq_tup_read AS tuples_read, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products')
ORDER BY seq_scan DESC;

La table products se fait scanner séquentiellement tout le temps. Ça veut dire qu'il manque des index dans le catalogue produits.

Erreurs classiques et comment les éviter

Le piège classique des débutants, c'est d'ignorer les stats. Par exemple, t'ajoutes un nouvel index en te disant : « Ça y est, mes requêtes vont carburer », mais PostgreSQL ne l'utilise pas parce que les stats n'ont pas été mises à jour automatiquement. Après de gros changements dans les tables, pense à mettre à jour les stats à la main avec la commande ANALYZE.

Autre erreur fréquente : ajouter des index à tout-va. Rappelle-toi, chaque index prend de la place sur le disque et ralentit les insertions, updates et suppressions. Utilise les stats de pg_stat_user_indexes pour vérifier qu'un index est vraiment utile, et pas juste là pour décorer.

À quoi ça sert tout ça dans la vraie vie ?

En dev réel : si ta base rame, tu vas d'abord chercher les soucis côté tables et index.

En entretien : les questions sur l'optimisation des index, c'est du classique en entretien SQL. Tu sais expliquer pg_stat_user_indexes ? T'as déjà fait la moitié du taf.

En admin de base : le monitoring, c'est le pain quotidien du DBA. Sans stats sur les tables et index, tu peux rien améliorer.