Les index PostgreSQL expliqués : ce qu’ils sont, quand les ajouter, et quand ils nuisent

Si les requêtes lentes sont la maladie qui amène les clients chez moi, les index sont le médicament le plus souvent prescrit — et le plus souvent mal prescrit. J’ai audité des bases avec zéro index sur leurs plus grosses tables, et des bases avec quarante index sur une seule table, dont la moitié en double, tous taxant chaque écriture. Les deux extrêmes coûtent cher. Voici le guide que j’aimerais que chaque développeur lise avant de taper CREATE INDEX — comment les index fonctionnent vraiment, quels types comptent sous PostgreSQL, et les règles que j’utilise dans mon travail d’optimisation de bases de données.

Ce qu’est réellement un index (la version 30 secondes)

Un index est une structure de recherche triée, conservée à côté de votre table — l’analogie avec l’index d’un livre est véritablement exacte. Sans lui, trouver les lignes correspondant à une condition impose de lire toute la table (un scan séquentiel). Avec lui, la base parcourt un arbre équilibré directement jusqu’aux lignes correspondantes. L’échange : l’index prend de l’espace disque, et chaque insertion, mise à jour et suppression doit le maintenir. Les index achètent de la vitesse de lecture avec du coût d’écriture et du stockage. Tout le reste est le détail de quand cet échange vaut le coup.

Les types d’index qui comptent vraiment

B-tree (le défaut). Gère l’égalité et les intervalles sur des données triables — le cheval de trait pour WHERE user_id = 42, les dates, les prix, les statuts. Probablement 90 % des index que vous créerez seront des B-tree, et c’est très bien.

B-tree composite (plusieurs colonnes). Un index sur (status, created_at) sert les requêtes qui filtrent par statut, ou par statut et date, ou qui trient par date au sein d’un statut — grâce à la règle du préfixe le plus à gauche : les colonnes fonctionnent de gauche à droite, et un index qui commence par une colonne que votre requête ne filtre pas est un index qu’elle ne peut presque pas utiliser. L’ordre des colonnes est l’endroit le plus fréquent où je vois les index échouer : bonnes colonnes, mauvais ordre, aucun bénéfice.

Index partiels. N’indexer que les lignes que vous interrogez vraiment : WHERE status = 'pending' sur une table de commandes où 97 % des lignes sont anciennes et terminées. Minuscule, rapide, et d’une efficacité brutale pour les motifs « file de travail ».

Index GIN. Pour chercher à l’intérieur d’une valeur — documents JSONB, tableaux, recherche plein texte. Si vous interrogez WHERE metadata @> '{"plan": "pro"}' sans index GIN, la base lit toute la table pour regarder dans chaque document.

Index BRIN. Pour les tables énormes, naturellement ordonnées et alimentées en ajout seul (journaux, événements, séries temporelles). Un index BRIN est minuscule et peut transformer un scan désespéré d’une table d’un milliard de lignes en quelque chose d’exploitable — de niche, mais quand il convient, c’est de la magie.

Les règles que j’applique réellement

Indexez ce que vous filtrez, joignez et triez — d’après les requêtes réelles, pas l’imagination. Je tire les requêtes lentes/fréquentes principales de pg_stat_statements et je les laisse nommer les index. Indexer une colonne « parce qu’elle pourrait être cherchée un jour » est la façon dont les tables finissent par en porter quarante.

Les clés étrangères ne sont pas auto-indexées sous PostgreSQL. Cela surprend ceux qui viennent d’autres bases. Chaque orders.user_id que vous joignez ou filtrez a besoin de son index créé délibérément — les index de clés étrangères manquants sont le constat le plus fréquent de mes audits, et la cause du symptôme classique : rapide pour un utilisateur, mort dès que vous supprimez ou mettez à jour un parent dont il faut vérifier les enfants.

La sélectivité décide. Un index sur une colonne quasi-booléenne à deux valeurs (95 % la même) aide rarement les lectures de la valeur dominante — la base doit quand même visiter la plupart de la table. Un index composite ou partiel exprime généralement mieux la vraie question.

Chaque index est une taxe sur les écritures — auditez les inutilisés. pg_stat_user_indexes vous dit quels index ne sont jamais scannés. Les index morts continuent d’être maintenus à chaque écriture et d’occuper le cache. Les retirer est de la performance gratuite, mais vérifiez sur un trafic proche de la production : l’index utilisé une fois par mois par le rapport financier n’est pas mort, il dort.

Mesurez avec EXPLAIN ANALYZE, avant et après. Un index que vous n’avez pas vérifié est une rumeur. Le plan doit montrer l’index utilisé, les comptes de lignes qui s’effondrent, et le temps qui chute — la même discipline que j’applique pour trouver et corriger les requêtes lentes.

Un exemple miniature

Motif réel tiré d’un audit réel (détails modifiés) : une page de liste de commandes filtrée par status et triée par created_at, sur une table d’environ 2 millions de lignes. Plan : scan séquentiel, tri, 1 900 ms. La table avait déjà un index — sur (created_at) seul, que le planificateur a eu raison d’ignorer pour cette forme de requête. Un seul index composite sur (status, created_at) : la requête est devenue un scan d’index renvoyant les lignes déjà dans l’ordre. 11 ms. Aucun code modifié, aucun serveur augmenté — l’index correspondait enfin à la question posée.

C’est toute la leçon, en fait. Les index ne sont ni une décoration ni une assurance ; ce sont des réponses façonnées pour des questions précises. Façonnez-les pour les questions que votre application pose réellement, prouvez que chacun gagne sa place, et supprimez ceux qui ne le font pas.

Si vous préférez que cela soit fait avec les mesures prises en charge, c’est exactement à cela que sert mon service d’architecture et d’optimisation de bases de données — envoyez-moi votre page la plus lente et je vous dirai ce qu’il y a derrière.