Problèmes typiques avec les index

Même la bagnole la plus moderne va caler si tu mets du limonade à la place de l’essence. Pareil avec les index dans PostgreSQL. C’est super puissant, mais faut pas faire n’importe quoi. Allez, on mate ensemble quelques soucis classiques liés aux index.

Problème 1 : indexation excessive

Pour commencer, petit rappel de la leçon d’avant-avant. Quand t’as trop d’index sur une table, PostgreSQL doit tous les gérer pour les garder à jour. Ça impacte direct les opérations d’insertion, de mise à jour et de suppression. Parce que chaque index doit être mis à jour ET synchronisé !

Imaginons qu’on a une table students :

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    age INTEGER,
    grade INTEGER
);

Et tu décides de créer un index sur chaque colonne « au cas où » :

CREATE INDEX idx_students_name ON students(name);
CREATE INDEX idx_students_age ON students(age);
CREATE INDEX idx_students_grade ON students(grade);

Maintenant, imagine que tu insères 10 000 nouvelles lignes. PostgreSQL doit non seulement écrire les données dans la table, mais aussi mettre à jour les trois index. Si t’as beaucoup de données, la vitesse d’écriture chute et les perfs du système prennent cher.

Comment éviter ce souci ? Avant de créer un index, pose-toi deux questions :

1. À quelle fréquence cette colonne sert-elle pour la filtration (WHERE), le tri (ORDER BY) ou le groupement (GROUP BY) ?
2. Est-ce que la requête va vraiment utiliser cet index ou va quand même faire un scan complet de la table ?

Si la réponse aux deux questions c’est « rarement » ou « jamais », t’as pas besoin d’index.

Problème 2 : mauvais choix de colonnes pour l’index

Indexer des données avec peu de valeurs différentes, c’est comme essayer de verser du thé dans une tasse avec le couvercle collé : ça sert à rien. Si une colonne n’a que 2-3 valeurs uniques, PostgreSQL va sûrement faire un scan complet de la table au lieu d’utiliser l’index.

Imaginons une table courses :

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    level VARCHAR(10) -- Peut être seulement 'Débutant', 'Intermédiaire' ou 'Avancé'
);

Et tu crées un index sur la colonne level :

CREATE INDEX idx_courses_level ON courses(level);

Mais la requête :

SELECT * FROM courses WHERE level = 'Débutant';

peut ne pas utiliser l’index, parce que PostgreSQL va estimer qu’il vaut mieux scanner toute la table que de passer par l’index. C’est surtout vrai pour les petites tables et les données avec peu de variations.

Du coup, les index sont utiles sur les colonnes à forte cardinalité (donc avec plein de valeurs uniques). Pour les données peu variées, mieux vaut utiliser d’autres techniques d’optimisation, genre le partitionnement de table.

Problème 3 : index obsolètes

Parfois, on crée des index puis on oublie de les virer, même s’ils servent plus à rien. C’est comme les fichiers sur ton bureau : au début y’en a deux, trois, cinq… Et puis un jour tu passes 10 minutes à chercher le bon icône. Tu vois le délire ?

Par exemple, on crée un index pour une ancienne fonctionnalité, puis on change la logique des requêtes et on ajoute un nouvel index. L’ancien ne sert plus à personne, mais il reste là, prend de la place et ralentit les insertions.

Pour éviter ça, pense à checker et analyser tes index régulièrement. PostgreSQL te file une métrique pratique :

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS total_scans
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0;

Ici, idx_scan te dit combien de requêtes ont utilisé chaque index. Si la valeur est 0, c’est que l’index sert à rien et tu peux le supprimer :

DROP INDEX idx_courses_level;

Problème 4 : index sur des colonnes souvent modifiées

Si t’as un index sur une colonne que tu modifies tout le temps, PostgreSQL doit le reconstruire à chaque changement. Ça peut vraiment plomber les perfs.

Imagine une table avec les commandes :

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20), -- Peut changer plusieurs fois (genre "nouveau", "en cours", "terminé")
    total NUMERIC(10, 2)
);

Tu crées un index sur la colonne status pour accélérer le filtrage par statut :

CREATE INDEX idx_orders_status ON orders(status);

Mais si le statut change des dizaines de fois pour chaque ligne, l’index va flinguer les perfs.

Pour éviter ça, ne crée pas d’index sur les colonnes qui changent tout le temps. Si t’as vraiment besoin d’un index, pense aux index partiels :

CREATE INDEX idx_orders_status_partial
ON orders(status) 
WHERE status = 'en cours';

Comme ça, l’index ne sera mis à jour que pour les lignes avec cette valeur précise.

Problème 5 : contraintes UNIQUE sur des colonnes inutiles

Les index uniques (UNIQUE) sont créés automatiquement pour garantir l’unicité des données. Mais si t’as pas vraiment besoin d’unicité, ces index rajoutent juste de la charge pour rien.

Par exemple, on crée une table de logs :

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP UNIQUE
);

Si tu ajoutes des milliers de lignes chaque seconde, garantir l’unicité sur created_at va créer une grosse charge.

Pour que tout roule, mets des contraintes UNIQUE seulement là où c’est vraiment nécessaire. Dans notre exemple, si l’unicité sur created_at n’est pas obligatoire, remplace l’index par un index classique :

CREATE INDEX idx_logs_created_at ON logs(created_at);

Problème 6 : mauvais usage des index combinés

Les index combinés (multi-column indexes) sont utiles si tes requêtes filtrent ou trient sur plusieurs colonnes à la fois. Mais faut bien les créer, sinon ils servent à rien.

Par exemple, on a cet index :

CREATE INDEX idx_students_name_grade ON students(name, grade);

Il sera utilisé si la requête filtre ou trie sur les deux colonnes :

SELECT * FROM students WHERE name = 'Alice' AND grade = 90;

Mais la requête :

SELECT * FROM students WHERE grade = 90;

n’utilisera pas cet index, parce que name est la première colonne.

Pour éviter ce souci, crée des index combinés seulement dans l’ordre où ils sont le plus utilisés dans tes requêtes. Si tu dois filtrer sur une seule colonne, crée un index séparé.

Tips utiles

Surveille l’utilisation de tes index. PostgreSQL a la vue système pg_stat_user_indexes où tu peux voir quels index sont utilisés ou pas.

Optimise tes requêtes en même temps que les index. Une requête pourrie reste pourrie, même avec des index.

N’oublie pas de supprimer. Les index obsolètes prennent juste de la place et ralentissent les insertions.

Voilà, c’est tout les amis ! Les index, c’est puissant, mais n’oublie jamais qu’avec un grand pouvoir vient une grande responsabilité. Utilise-les avec la tête, et ta base de données va carburer comme une fusée SpaceX !