2 minute read

La plupart des index qui gonflent sont victimes d’un mélange de dead tuples non récupérés et d’un fillfactor inadapté.
Dans ce guide, on examine pourquoi ça arrive, comment le mesurer et quoi faire pour que ça ne se reproduise plus.

1. D’où vient le bloat ?

Cause Symptôme Pourquoi ça grossit ?
Dead tuples après UPDATE/DELETE L’index continue de pointer vers les anciennes versions de lignes. PostgreSQL ne nettoie pas l’index immédiatement (MVCC).
Fillfactor trop haut (100 %) Les pages deviennent pleines, chaque INSERT/UPDATE force des page splits. Le moteur crée de nouvelles pages plutôt que de réorganiser les existantes.
Autovacuum trop lent pg_stat_user_indexes.idx_scan ↑ mais vacuum_count ↓ Les pages mortes s’accumulent, le VACUUM n’a pas le temps de les réutiliser.
Reindex manquant après gros DELETE pg_class.reltuples chute, mais relpages reste haut Les pointeurs orphelins ne sont supprimés qu’avec un REINDEX.

2. Diagnostiquer la taille réelle de vos index

2.1 Vite fait, bien fait : la vue pg_indexes_size()

SELECT
    schemaname || '.' || relname  AS index,
    pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(relid) DESC
LIMIT 10;

2.2 Mesurer le bloat estimé

WITH bloat_idx AS (
  SELECT
    coalesce(nspname,'?') AS schema,
    idx.relname            AS index_name,
    pg_relation_size(idx.oid) AS index_bytes,
    pgstat.idx_scan,
    (pg_relation_size(idx.oid) - (stat.avg_leaf_density * stat.avg_leaf_blocks)::bigint) AS bloat_bytes,
    100 * (pg_relation_size(idx.oid) - (stat.avg_leaf_density * stat.avg_leaf_blocks)::bigint)
      / pg_relation_size(idx.oid) AS bloat_pct
  FROM pg_class idx
  JOIN pg_namespace ns             ON ns.oid   = idx.relnamespace
  JOIN pgstatindex(idx.oid)  AS stat ON true
  LEFT JOIN pg_stat_user_indexes pgstat ON pgstat.indexrelid = idx.oid
  WHERE idx.relkind = 'i'
)
SELECT *
FROM bloat_idx
ORDER BY bloat_pct DESC
LIMIT 20;

⚠️ pgstattuple doit être installé (CREATE EXTENSION pgstattuple).

3. Stratégies de réduction et prévention

3.1 Ajuster le fillfactor

  • Index très dynamiques (insert/update) : descendez à 90 % voire 80 %.
  • Vérifiez l’impact : pages ≈ 10 % plus nombreuses, mais beaucoup moins de page splits.
ALTER INDEX idx_orders_customer_id SET (fillfactor = 90);
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

3.2 Régler l’autovacuum

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,  -- 2 %
  autovacuum_vacuum_threshold    = 1000    -- + données = + fréquence
);

Astuce : surveillez log_autovacuum_min_duration pour confirmer que vos VACUUM tournent.

3.3 Nettoyage ponctuel : REINDEX CONCURRENTLY

REINDEX INDEX CONCURRENTLY idx_orders_customer_id;
  • Pas de verrou exclusif sur la table.
  • Double l’espace disque pendant l’opération – prévoyez la marge.

3.4 Archiver puis DROP / CREATE (cas extrême)

Quand le bloat dépasse 50 %, il est parfois plus rapide de :

DROP INDEX CONCURRENTLY idx_old;
CREATE INDEX CONCURRENTLY idx_new ON ... ;

4. Checklist “anti-bloat”

  • Extension pgstattuple installée pour mesurer précisément le bloat.
  • fillfactor réglé ≤ 90 % sur chaque index transactionnel.
  • Paramètres autovacuum personnalisés (scale_factor ↓, threshold ↑) pour les tables chaudes.
  • Job hebdomadaire REINDEX CONCURRENTLY des index dont bloat_pct > 20 %.
  • Tableau de bord Grafana/Prometheus et alerte quand bloat_pct dépasse 25 %.
  • Suivi des page splits via pg_stat_user_indexes pour ajuster le fillfactor.

-

5. Conclusion

Un index gonflé ralentit les requêtes (I/O supplémentaires) et les sauvegardes (plus de Go à archiver). En appliquant ces bonnes pratiques – fillfactor, autovacuum agressif, REINDEX périodique – vous conservez des structures compactes, plus rapides et moins coûteuses à maintenir.

Besoin d’un audit complet de vos index ? Contactez DBLINK pour un diagnostic personnalisé — en 48 h vous saurez exactement où agir.

Updated: