SQL Academy · Leçon

Maintenance des index et gonflement

Diagnostiquez le gonflement des index, reconstruisez-les avec REINDEX CONCURRENTLY et supprimez les index inutilisés en toute sécurité.

Maintenance des index et gonflement est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Pourquoi les index gonflent

PostgreSQL utilise MVCC : UPDATE écrit une nouvelle version de la ligne, tandis que l’ancienne reste visible pour les transactions existantes. Des entrées d’index existent pour les deux versions. Au fil du temps :

Les tables faisant l’objet de nombreuses mises à jour accumulent des entrées d’index mortes
Les index deviennent plus volumineux que nécessaire
Les recherches ralentissent à mesure que l’arbre B s’approfondit

Diagnostiquer le gonflement

Les requêtes de vérification du gonflement ne sont pas triviales. Outils courants :

l’extension pgstattuple
les rapports de pg_repack
les requêtes de détection du gonflement de check_postgres ou des outils de supervision

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('orders_user_id_idx');

REINDEX

Reconstruit l’index. La forme classique acquiert un verrou ACCESS EXCLUSIVE — ce qui est problématique en production :

REINDEX INDEX orders_user_id_idx;       -- blocks writes!

REINDEX CONCURRENTLY (PG 12+)

Variante non bloquante : les lectures et les écritures continuent pendant la reconstruction :

REINDEX INDEX CONCURRENTLY orders_user_id_idx;

Index inutilisés

Un index inutilisé ralentit chaque écriture sans accélérer aucune lecture. Pour les trouver :

SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Supprimer les index inutilisés

Supprimez-les, mais vérifiez d’abord tous les environnements et toutes les périodes. Les index utilisés pour des rapports occasionnels semblent inutilisés la plupart du temps.

DROP INDEX CONCURRENTLY old_unused_idx;

Index en double

Il arrive que le même index soit créé à la fois par une contrainte et par un CREATE INDEX manuel. Vérifiez les doublons dans pg_indexes et supprimez l’index redondant :

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

Amplification des écritures par les index

Chaque INSERT/UPDATE/DELETE met à jour tous les index concernés. Trois index sur une table très sollicitée = un coût d’écriture multiplié par 3. Ajoutez uniquement les index réellement utiles.

Liste d’attente GIN

Les index GIN regroupent les mises à jour dans une liste d’attente. Videz-la manuellement ou laissez autovacuum s’en charger :

SELECT gin_clean_pending_list('events_data_gin');

VACUUM nettoie les entrées d’index

VACUUM (abordé dans la leçon sur MVCC) supprime les entrées d’index mortes des pages du tas. Sans autovacuum, les index grossissent indéfiniment.

Surveiller la taille des index

Suivez l’évolution de la taille des index :

SELECT pg_size_pretty(pg_indexes_size('orders')) AS index_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

pg_repack : réécrire les tables en ligne

En cas de gonflement important, pg_repack réécrit la table et les index en ligne, sans verrouillage complet de la table. Installez-le comme paquet OS et comme extension PostgreSQL.

Récapitulatif

Les index nécessitent une maintenance.

Le gonflement dû à MVCC est normal — gérez-le avec VACUUM et REINDEX CONCURRENTLY
Supprimez les index inutilisés
Évitez les doublons
Chaque nouvel index ralentit les écritures — procédez de manière réfléchie

Vérification rapide

Quelle commande PostgreSQL reconstruit un index WITHOUT bloquer les écritures ?

Questions Fréquemment Posées

La leçon « Maintenance des index et gonflement » est-elle gratuite ?

Oui — le texte complet de « Maintenance des index et gonflement » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Maintenance des index et gonflement » ?

Diagnostiquez le gonflement des index, reconstruisez-les avec REINDEX CONCURRENTLY et supprimez les index inutilisés en toute sécurité. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.

Combien de temps prend la leçon « Maintenance des index et gonflement » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?

Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

← Retour à SQL Academy