Indexation PostgreSQL Approfondie : B-Tree, GIN, GiST et BRIN Expliqués

1. B-Tree : L’index polyvalent indispensable

B-tree est le type d’index par défaut de PostgreSQL et couvre l’immense majorité des besoins d’indexation réels : prédicats d’égalité et d’intervalle (=, <, <=, >, >=, BETWEEN), satisfaction d’ORDER BY sans tri séparé, et gestion efficace des doublons. Il maintient un arbre équilibré de pages où chaque feuille est à la même profondeur, donnant un coût de recherche en O(log n) qui varie à peine que la table passe d’un million à un milliard de lignes — cette prévisibilité est exactement pourquoi il doit être votre choix par défaut, sauf si le schéma de requête appelle spécifiquement autre chose.

Les index B-tree composites (multi-colonnes) suivent la correspondance par préfixe gauche : un index sur (a, b, c) peut servir efficacement des requêtes filtrant sur a seul, a+b, ou a+b+c, mais pas b seul ni c seul — la colonne de tête doit figurer dans le prédicat pour que l’index soit utilisable du tout pour cette recherche. L’ordre des colonnes dans un index composite doit généralement placer en premier la colonne de plus forte sélectivité ou la plus fréquemment filtrée seule, sauf si la combinaison WHERE + ORDER BY d’un schéma de requête précis dicte le contraire.

Les index partiels — `CREATE INDEX ... WHERE condition` — construisent un B-tree sur le seul sous-ensemble de lignes correspondant à la condition, nettement plus petit et plus rapide à parcourir qu’un index complet quand les requêtes filtrent systématiquement sur cette même condition (par exemple un index sur orders WHERE status = 'pending' quand 95% des commandes finissent 'completed' et sont rarement requêtées par statut ensuite). L’index est à la fois plus petit sur disque et moins coûteux à maintenir lors d’écritures sur des lignes hors de la condition partielle, puisqu’elles ne le touchent jamais.

Le compromis que personne n’évite est l’amplification d’écriture : chaque INSERT, UPDATE (touchant une colonne indexée) et DELETE doit aussi mettre à jour chaque index B-tree de cette table, et chaque mise à jour d’index est elle-même une opération B-tree à accès aléatoire. Une table avec huit index paie ce coût huit fois par écriture, ce qui fait de 'indexer par précaution' un véritable anti-pattern — auditez l’usage des index avec pg_stat_user_indexes et supprimez ceux dont le compteur idx_scan est proche de zéro.

2. GIN : Tableaux et recherche dans JSONB

Les index GIN inversent le mappage habituel : au lieu d’une entrée d’index par ligne, GIN construit une entrée par clé/élément distinct trouvé à l’intérieur des valeurs composites, chacune pointant vers la liste des lignes qui la contiennent — exactement comme l’index inversé d’un moteur de recherche associant des mots à des documents. Cela en fait le type d’index correct dès qu’une seule valeur de colonne contient plusieurs 'choses' recherchables : éléments de tableau, clés/valeurs JSONB, ou lexèmes d’un tsvector pour la recherche plein texte.

Pour JSONB, GIN supporte efficacement l’opérateur de confinement @> — `WHERE metadata @> '{"plan": "pro"}'` peut utiliser un index GIN pour sauter directement aux lignes correspondantes au lieu de parcourir et désérialiser chaque document JSONB. La classe d’opérateurs GIN par défaut (jsonb_ops) indexe chaque clé et valeur ; l’alternative jsonb_path_ops indexe à la place des combinaisons hachées chemin/valeur, produisant un index plus petit et plus rapide spécifiquement pour les requêtes de confinement, au prix de ne pas supporter les opérateurs d’existence de clé `?`, `?|`, `?&`.