Problèmes de sur-indexation

Les index, c’est clairement un super moyen de rendre ta base de données plus rapide, mais comme on dit, « le mieux est l’ennemi du bien ». Tous les index ne sont pas utiles, et en avoir trop peut faire plus de mal que de bien. Ça paraît bizarre, mais c’est vrai. On va voir ça ensemble.

Imagine une grande bibliothèque où tu as plusieurs catalogues pour trouver des livres — par auteur, par genre, par année de publication. Chaque catalogue t’aide à trouver un livre plus vite. Mais si tu as trop de catalogues — genre un pour chaque mot du titre ou chaque détail — au lieu d’aider, ça devient le bazar : tu passes plus de temps à chercher, ça prend de la place, et le bibliothécaire doit tout le temps mettre à jour tous ces catalogues.

Dans une base de données, les index fonctionnent pareil : ils t’aident à trouver les données rapidement, mais s’il y en a trop, les mettre à jour à chaque ajout ou modif devient galère. Et ça prend de la place sur le disque aussi. En plus, quand il y a trop d’index, le système peut juste ne plus savoir lequel utiliser.

Donc, comme avec les catalogues dans une bibliothèque, il ne faut pas abuser avec les index — mieux vaut en avoir quelques-uns bien choisis et efficaces que des dizaines qui ne servent à rien.

Allez, on joue aux "détectives PostgreSQL". Imagine que tu ajoutes trois index sur une seule colonne. Tu t’es dit que ça allait booster les perfs. Mais imagine :

• Si ta table est une grosse liste d’étudiants, et qu’il y a trois index, chaque ajout d’étudiant va déclencher trois updates d’index. Pas vraiment un "boost", non ?
• Et si tu as 10 tables comme ça, toutes blindées d’index ? Les perfs de toute la base vont s’effondrer.

Comment savoir si tu as un problème de sur-indexation ?

La première chose à faire pour checker si t’as un souci, c’est de regarder les index existants. Dans PostgreSQL, tu peux faire ça avec la commande :

\d nom_de_table

Cette commande te montre la table, ses colonnes et les index associés. Si tu vois qu’il y a une tonne d’index sur une seule table, c’est déjà un signal d’alarme.

Un autre outil utile, c’est la vue système pg_stat_user_indexes. Elle te montre à quel point les index sont utilisés, ce qui permet de repérer ceux qui sont juste là pour décorer :

SELECT
    relname AS nom_table,
    indexrelname AS nom_index,
    idx_scan AS scans_index
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0;

Si idx_scan vaut 0, ça veut dire que l’index n’a jamais servi dans une requête. Cet index-là, c’est clairement un candidat à la suppression.

Exemple de sur-indexation

Imaginons une table avec des utilisateurs :

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

Et on a trois index :

-- Index sur email
CREATE INDEX idx_users_email ON users (email);

-- Index sur username
CREATE INDEX idx_users_username ON users (username);

-- Index sur created_at
CREATE INDEX idx_users_created_at ON users (created_at);

Regardons maintenant les requêtes typiques qu’on fait :

1. Recherche d’un utilisateur par email.
2. Recherche d’un utilisateur par username.
3. Tri des utilisateurs par created_at.

On dirait que les index sont utiles. Mais voilà le piège : si ces requêtes sont rares (genre une fois par semaine), créer des index ne sert à rien. Pire, si certains de ces index ne servent jamais, ils ne font que ralentir les insert et updates.

Pour illustrer : imaginons qu’on a ces données dans la table users :

Si les requêtes sur username ne sont quasiment jamais faites, l’index idx_users_username n’est jamais utilisé (idx_scan = 0) et peut être supprimé pour optimiser.

Donc un index, c’est un super outil, mais il faut l’utiliser intelligemment. Mieux vaut avoir quelques index utiles et bien utilisés que plein d’index inutiles.

Comment éviter la sur-indexation

1. Analyse des index utilisés. Comme on l’a déjà dit, vérifie les stats d’utilisation des index avec pg_stat_user_indexes. Si un index est quasi jamais utilisé, tu peux sûrement le virer :

DROP INDEX IF EXISTS nom_index;

2. Crée des index seulement pour les requêtes fréquentes. Avant d’ajouter un index, pose-toi ces questions :

• Est-ce que cette colonne est souvent dans un WHERE, ORDER BY, GROUP BY ?
• Est-ce que la table contient beaucoup de données ?
• Est-ce que la requête est vraiment trop lente sans index ?

Si tu réponds "non" à au moins une de ces questions, l’index est sûrement inutile.

3. Utilise des index composés. Si tu utilises souvent plusieurs colonnes dans une même requête, au lieu de créer un index pour chaque colonne, fais un index composé :

CREATE INDEX idx_users_email_username ON users (email, username);

Ça accélère les requêtes qui filtrent sur email et username en même temps.

4. Revois régulièrement tes index existants. Quand ta base grossit, tes requêtes changent. Ce qui était utile il y a un an ne l’est peut-être plus aujourd’hui. Pense à checker tes index de temps en temps et à supprimer ceux qui ne servent plus.

Minimiser les index : un exemple

Revenons à notre table users. Au lieu de trois index séparés, on peut optimiser comme ça :

• Supprimer l’index sur created_at si le tri sur cette colonne est rare.
• Au lieu de deux index séparés sur email et username, créer un index composé :

CREATE INDEX idx_users_email_username ON users (email, username);

Conclusion : c’est quoi le secret de l’équilibre ?

Comme souvent en dev, le minimalisme, c’est la clé : "Moins, c’est mieux". Pas besoin d’indexer chaque colonne juste parce que tu peux. Réfléchis à pourquoi tu veux un index et si ça va vraiment booster tes requêtes. Sois pragmatique et rappelle-toi qu’un bon dev, ce n’est pas celui qui balance des index partout, mais celui qui comprend leur impact et les utilise à bon escient.

Maintenant, avec cet outil en main, tu peux éviter la cata de la sur-indexation et rendre ta base aussi rapide qu’un guépard, pas aussi lente qu’une tortue qui traîne des index inutiles.