B-tree vs GIN vs GiST : guide pratique pour PostgreSQL

Ce que vous choisissez vraiment quand vous choisissez un index

La plupart des problèmes d’indexation PostgreSQL commencent de la même façon : une vue de liste semble rapide à 1 000 lignes, puis devient lente à 1 000 000. Ou une boîte de recherche qui marchait en test devient une pause d’une seconde en production. Quand cela arrive, on a envie de demander « Quel index est le meilleur ? » Une meilleure question est : « Que demande cet écran à la base de données ? »

La même table peut nécessiter différents types d’index parce que différents écrans la lisent différemment. Une vue filtre par un status unique et trie par created_at. Une autre fait de la recherche en texte intégral. Une autre vérifie si un champ JSON contient une clé. Une autre trouve des éléments près d’un point sur une carte. Ce sont des schémas d’accès différents, donc un seul type d’index ne gagnera pas partout.

C’est ce que vous choisissez quand vous choisissez un index : comment l’application accède aux données. Faites-vous surtout des égalités, des plages et des tris ? Cherchez-vous à l’intérieur de documents ou de tableaux ? Demandez-vous « qu’est-ce qui est proche de cette position » ou « qu’est-ce qui chevauche cette plage » ? La réponse détermine si B-tree, GIN ou GiST est le bon choix.

B-tree, GIN et GiST en langage courant

Choisir un index dépend moins du type de colonne que de ce que vos requêtes font. PostgreSQL choisit des index en fonction des opérateurs comme =, <, @>, ou @@, pas en fonction du fait qu’une colonne soit « text » ou « json ». C’est pourquoi le même champ peut nécessiter des index différents selon les écrans.

B-tree : rapide pour les recherches ordonnées

B-tree est le choix par défaut et le plus courant. Il brille lorsque vous filtrez par valeur exacte, par une plage, ou que vous avez besoin des résultats dans un ordre précis.

Un exemple typique est une liste d’administration filtrée par status et triée par created_at. Un index B-tree sur (status, created_at) peut aider à la fois le filtre et le tri. B-tree est aussi l’outil habituel pour l’unicité (contraintes unique).

GIN : rapide quand chaque ligne contient beaucoup de clés recherchables

GIN est conçu pour les questions « est-ce que cette ligne contient ce terme/valeur ? », où une ligne peut correspondre à beaucoup de clés. Exemples courants : recherche en texte intégral (un document contient des mots) et appartenance JSONB/tableau (JSON contient une clé/valeur).

Pensez à une fiche client avec un objet JSONB preferences, et un écran qui filtre les utilisateurs dont preferences contient {\"newsletter\": true}. C’est une recherche de type GIN.

GiST : flexible pour les plages, la géo et la similarité

GiST est un cadre général utilisé par des types de données qui ne rentrent pas dans un simple ordre. Il convient naturellement aux plages (chevauchement, contenance), aux requêtes géométriques/géographiques (près, dans) et à certaines recherches de similarité.

Quand vous hésitez entre B-tree vs GIN vs GiST, commencez par écrire les opérateurs que vos écrans les plus sollicités utilisent. Le bon index devient souvent évident après cela.

Tableau de décision pour les écrans courants (filtres, recherche, JSON, géo)

La plupart des applications n’ont besoin que de quelques schémas d’index. L’astuce consiste à faire correspondre le comportement de l’écran aux opérateurs que vos requêtes utilisent.

Deux index peuvent coexister quand les endpoints diffèrent. Par exemple, une liste admin peut nécessiter un B-tree sur (tenant_id, created_at) pour un tri rapide, tandis qu’une page de recherche a besoin d’un GIN pour @@. Gardez les deux seulement si ces formes de requêtes sont courantes.

Si vous doutez, regardez d’abord l’opérateur. Les index aident quand la base peut les utiliser pour sauter de larges parties de la table.

Filtres et tri : où B-tree gagne généralement

Pour la plupart des écrans quotidiens, B-tree est le choix ennuyeux qui fonctionne. Si votre requête ressemble à « prendre les lignes où une colonne vaut une valeur, peut-être les trier, puis afficher la page 1 », B-tree est généralement la première chose à essayer.

Les filtres d’égalité sont le cas classique. Des colonnes comme status, user_id, account_id, type ou tenant_id apparaissent constamment dans les dashboards et panneaux d’administration. Un index B-tree peut sauter directement aux valeurs correspondantes.

Les filtres par plage conviennent aussi bien à B-tree. Quand vous filtrez par temps ou plages numériques, la structure ordonnée aide : created_at >= ..., price BETWEEN ..., id > .... Si votre UI propose « 7 derniers jours » ou « 50 $ à 100 $ », B-tree fait exactement ce que vous voulez.

Le tri et la pagination sont les situations où B-tree peut vous faire économiser le plus. Si l’ordre de l’index correspond à votre ORDER BY, PostgreSQL peut souvent renvoyer des lignes déjà triées au lieu de trier un grand ensemble en mémoire.

-- Un écran courant : "Mes tickets ouverts, du plus récent au plus ancien"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

Les index composites suivent une règle simple : PostgreSQL ne peut utiliser efficacement que la partie gauche de l’index. Pensez « gauche à droite ». Avec (user_id, status, created_at), les requêtes qui filtrent par user_id (et éventuellement status) en profitent. Une requête qui filtre seulement par status n’en profitera généralement pas.

Les index partiels sont un très bon compromis quand votre écran ne s’intéresse qu’à une tranche de données. Tranches courantes : « seulement les lignes actives », « pas soft-deleted », ou « activité récente ». Ils gardent l’index plus petit et plus rapide.

Colonnes à haute cardinalité et coût des index supplémentaires

Les colonnes à haute cardinalité ont beaucoup de valeurs uniques, comme user_id, order_id, email, ou created_at au niveau seconde. Les index excellent souvent ici parce qu’un filtre peut rapidement réduire le résultat à une toute petite partie de la table.

Les colonnes à faible cardinalité sont l’inverse : booléens et petits enums comme is_active, status IN ('open','closed'), ou plan IN ('free','pro'). Un index sur celles-ci déçoit souvent car chaque valeur correspond à un gros morceau de lignes. PostgreSQL peut correctement choisir un sequential scan puisque sauter via l’index implique quand même de lire beaucoup de pages de table.

Un autre coût subtil est la récupération des lignes. Même si un index trouve rapidement les IDs correspondants, la base peut devoir visiter la table pour lire les autres colonnes. Si votre requête ne nécessite que quelques champs, un index couvrant peut aider, mais il rend aussi l’index plus grand et plus coûteux à maintenir.

Chaque index supplémentaire a un prix à l’écriture. Les inserts doivent écrire dans chaque index. Les updates qui modifient des colonnes indexées doivent aussi mettre à jour ces entrées. Ajouter des index « au cas où » peut ralentir l’ensemble de l’app, pas seulement un écran.

Conseils pratiques :

• Commencez par 1 à 2 index principaux par table chargée, basés sur des filtres et tris réels.
• Favorisez les colonnes à haute cardinalité utilisées dans WHERE et ORDER BY.
• Méfiez-vous d’indexer les booléens et petits enums sauf s’ils se combinent à une autre colonne sélective.
• Ajoutez un nouvel index seulement quand vous pouvez nommer la requête exacte qu’il accélérera.

Exemple : une liste de tickets filtrée par assignee_id (haute cardinalité) profite d’un index, tandis que is_archived = false seul en profite rarement.

Écrans de recherche : texte intégral, préfixes et "contains"

Les boîtes de recherche semblent simples, mais les utilisateurs en attendent beaucoup : plusieurs mots, formes différentes d’un mot, et un classement raisonnable. Dans PostgreSQL, c’est généralement la recherche en texte intégral : on stocke un tsvector (texte préparé) et on l’interroge avec un tsquery (ce que l’utilisateur a tapé, parsé en termes).

Pour la recherche en texte intégral, GIN est le choix courant car il est rapide pour répondre à « est-ce que ce document contient ces termes ? » sur de nombreuses lignes. Le compromis est des écritures plus lourdes : insérer et mettre à jour des lignes coûte plus.

GiST peut aussi fonctionner pour la recherche en texte intégral. Il est souvent plus petit et moins coûteux à mettre à jour, mais en lecture il est généralement plus lent que GIN. Si vos données changent constamment (par exemple, tables de type événement), cet équilibre lecture/écriture peut compter.

La recherche par préfixe n’est pas de la recherche en texte intégral

La recherche par préfixe signifie « commence par », comme chercher des clients par préfixe d’email. Ce n’est pas ce pour quoi la recherche en texte intégral est conçue. Pour les préfixes, un index B-tree peut aider (souvent avec la bonne classe d’opérateurs) car il correspond à l’ordre des chaînes.

Pour les recherches « contient » comme ILIKE '%error%', B-tree aide rarement. C’est là que l’index trigram ou une autre approche de recherche devient pertinent.

Quand les utilisateurs veulent filtres + recherche en texte

La plupart des écrans réels combinent recherche et filtres : status, assignee, plage de dates, tenant, etc. Une configuration pratique est :

• Un index GIN (ou parfois GiST) pour la colonne tsvector.
• Des index B-tree pour les filtres les plus sélectifs (par exemple account_id, status, created_at).
• Une règle stricte de « garder le minimum », car trop d’index ralentissent les écritures.

Exemple : un écran de tickets qui recherche « refund delayed » et filtre sur status = 'open' et un account_id précis. Le texte intégral vous donne les lignes pertinentes, tandis que B-tree aide PostgreSQL à restreindre rapidement au bon compte et statut.

Champs JSONB : choisir entre GIN et index B-tree ciblés

JSONB est pratique pour les données flexibles, mais il peut ralentir les requêtes si vous le traitez comme une colonne normale. La décision centrale est simple : cherchez-vous « n’importe où dans ce JSON », ou filtrez-vous sur quelques chemins spécifiques répétitivement ?

Pour les requêtes de contenance comme metadata @> '{"plan":"pro"}', un index GIN est souvent le premier choix. Il est conçu pour « est-ce que ce document contient cette forme ? » et prend aussi en charge les vérifications d’existence de clé comme ?, ?|, et ?&.

Si votre appli filtre surtout par une ou deux clés JSON, un index d’expression B-tree ciblé est souvent plus rapide et plus petit. Il aide aussi quand vous avez besoin de trier ou de faire des comparaisons numériques sur des valeurs extraites.

-- Support large pour la contenance et les vérifications de clés
CREATE INDEX ON customers USING GIN (metadata);

-- Filtres ciblés et tri sur un chemin JSON
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

Règles pratiques :

• Utilisez GIN quand les utilisateurs recherchent plusieurs clés, tags ou structures imbriquées.
• Utilisez des index d’expression B-tree quand les utilisateurs filtrent sur des chemins spécifiques et répétitifs.
• Indexez ce qui apparaît dans de vrais écrans, pas tout.
• Si les performances dépendent de quelques clés JSON utilisées tout le temps, envisagez de les promouvoir en colonnes réelles.

Exemple : un écran de support peut filtrer les tickets par metadata->>'priority' et trier par created_at. Indexez le chemin JSON de priorité et la colonne created_at. Évitez un GIN large sauf si les utilisateurs cherchent aussi des tags ou des attributs imbriqués.

Requêtes géo et plages : où GiST s’impose

Les écrans géo et de plages sont là où GiST devient souvent le choix évident. GiST est conçu pour « est-ce que cette chose chevauche, contient, ou est proche de cette chose ? » plutôt que « cette valeur égale-t-elle cette valeur ? »

Les données géo signifient souvent des points (position d’un magasin), des lignes (un itinéraire) ou des polygones (zone de livraison). Écrans courants : « magasins près de moi », « jobs dans un rayon de 10 km », « afficher les éléments dans ce rectangle de carte », ou « cette adresse est-elle dans notre zone de service ? » Un index GiST (souvent via PostGIS geometry/geography) accélère ces opérateurs spatiaux pour que la base puisse sauter la plupart des lignes au lieu d’examiner chaque forme.

Les plages fonctionnent de la même façon. PostgreSQL propose des types range comme daterange et int4range, et la question typique est le chevauchement : « cette réservation entre en conflit ? » ou « montrer les abonnements actifs pendant cette semaine ». GiST gère efficacement les opérateurs de chevauchement et de contenance, d’où son usage courant dans les calendriers et la gestion de disponibilité.

B-tree peut quand même compter sur des écrans géo. Beaucoup de pages filtrent d’abord par tenant, status ou temps, puis appliquent une condition spatiale, puis trient. Par exemple : « seulement les livraisons de ma société, des 7 derniers jours, par proximité ». GiST s’occupe du spatial, B-tree des filtres sélectifs et du tri.

Comment choisir un index étape par étape

Le choix d’un index porte surtout sur l’opérateur, pas sur le nom de la colonne. La même colonne peut nécessiter des index différents selon que vous utilisez =, >, LIKE 'prefix%', recherche en texte intégral, contenance JSON ou distance géographique.

Lisez la requête comme une checklist : WHERE décide quelles lignes qualifient, JOIN comment les tables se connectent, ORDER BY l’ordre de sortie, et LIMIT combien de lignes vous avez réellement besoin. Le meilleur index est souvent celui qui aide à trouver les 20 premières lignes rapidement.

Un processus simple qui marche pour la plupart des écrans :

1. Notez les opérateurs exacts que votre écran utilise (exemple : status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. Commencez par un index qui correspond au filtre le plus sélectif ou au tri par défaut. Si l’écran trie par created_at DESC, commencez par là.
3. Ajoutez un index composite seulement quand vous voyez les mêmes filtres réunis souvent. Mettez d’abord les colonnes d’égalité, puis les colonnes de plage, puis la clé de tri.
4. Utilisez un index partiel quand vous filtrez toujours une sous-partie (exemple : status = 'open'). Utilisez un index d’expression quand vous interrogez une valeur calculée (exemple : lower(email) pour recherche insensible à la casse).
5. Validez avec EXPLAIN ANALYZE. Gardez l’index s’il réduit significativement le temps d’exécution et les lignes lues.

Exemple concret : un dashboard de support filtre les tickets par status et trie par les plus récents. Un B-tree sur (status, created_at DESC) est un bon premier essai. Si le même écran filtre aussi sur un flag JSONB comme meta @> '{"vip": true}', c’est un opérateur différent qui nécessite en général un index axé JSON.

Erreurs courantes qui font perdre du temps (et ralentissent les écritures)

Une façon classique d’être déçu est de choisir le « bon » type d’index pour le mauvais opérateur. PostgreSQL ne peut utiliser un index que si la requête correspond à ce pour quoi l’index a été construit. Si votre appli utilise ILIKE '%term%', un B-tree simple sur cette colonne texte restera inutilisé et vous scannerez toujours la table.

Autre piège : construire d’énormes index multi-colonnes « au cas où ». Ils semblent sûrs, mais ils sont coûteux à maintenir et ne correspondent souvent pas aux schémas réels de requête. Si les colonnes les plus à gauche ne sont pas utilisées dans le filtre, le reste de l’index peut ne rien apporter.

Les colonnes peu sélectives sont aussi faciles à sur-indexer. Un B-tree sur un booléen comme is_active ou un statut à quelques valeurs peut être presque inutile à moins d’en faire un index partiel correspondant exactement à ce que vous interrogez.

JSONB a ses propres pièges. Un large index GIN est excellent pour des filtres flexibles, mais beaucoup de vérifications de chemins JSON sont plus rapides avec un index d’expression sur la valeur extraite. Si votre écran interroge toujours payload->>'customer_id', indexer cette expression est souvent plus petit et plus rapide que d’indexer tout le document.

Enfin, chaque index supplémentaire pèse sur les écritures. Sur des tables souvent mises à jour (tickets, commandes), chaque insert et update doit mettre à jour tous les index.

Avant d’ajouter un index, posez-vous :

• L’index correspond-il exactement à l’opérateur utilisé par la requête ?
• Peut-on remplacer un large index multi-colonnes par un ou deux index ciblés ?
• L’index devrait-il être partiel pour éviter le bruit des colonnes peu sélectives ?
• Pour JSONB, un index d’expression ne conviendrait-il pas mieux ?
• La table est-elle assez write-heavy pour que le coût d’un index l’emporte sur le gain en lecture ?

Vérifications rapides avant d’ajouter (ou de garder) un index

Avant de créer un nouvel index, soyez précis sur ce que l’app fait réellement. Un index « sympa à avoir » se transforme souvent en écritures plus lentes et en stockage supplémentaire sans réel bénéfice.

Commencez par vos trois écrans (ou endpoints) principaux et notez la forme exacte des requêtes : filtres, ordre, et ce que tape l’utilisateur. Beaucoup de « problèmes d’index » sont en réalité des « problèmes de requêtes floues », surtout quand on débat B-tree vs GIN vs GiST sans nommer l’opérateur.

Une checklist simple :

• Choisissez 3 écrans réels et listez leurs patterns WHERE et ORDER BY exacts (incluant direction et gestion des NULL).
• Confirmez le type d’opérateur : égalité (=), plage (>, BETWEEN), préfixe, contenance, chevauchement, ou distance.
• Choisissez un index par pattern d’écran courant, testez-le, et ne gardez que ceux qui réduisent significativement le temps ou les lectures.
• Si la table est très écrite, soyez strict : les index supplémentaires multiplient le coût d’écriture et peuvent accroître la pression du vacuum.
• Réévaluez après chaque changement de fonctionnalité. Un nouveau filtre, un nouveau tri par défaut, ou le passage de « starts with » à « contains » peut rendre l’ancien index obsolète.

Exemple : un dashboard ajoute un tri par défaut last_activity DESC. Si vous n’aviez indexé que status, le filtre peut rester rapide, mais le tri forcera du travail supplémentaire.

Exemple : mapper des écrans réels aux bons index

Un tableau de décision n’aide que si vous pouvez le relier aux écrans réels que vous publiez. Voici trois écrans courants et comment ils se traduisent souvent en choix d’index.

Une manière pratique d’appliquer ceci : partez de l’UI :

• Listez chaque contrôle qui réduit les résultats (filtres).
• Notez l’ordre par défaut.
• Soyez précis sur le comportement de recherche (full-text vs starts-with vs contains).
• Repérez les champs « spéciaux » (JSONB, géo, plages).

Étapes suivantes : intégrer l’indexation à votre processus de développement

De bons index suivent vos écrans : les filtres que les gens cliquent, l’ordre qu’ils attendent, et la boîte de recherche qu’ils utilisent réellement. Traitez l’indexation comme une habitude pendant le développement et vous éviterez la plupart des surprises de performance plus tard.

Rendez cela reproductible : identifiez 1 à 3 requêtes que chaque écran exécute, ajoutez l’index le plus petit qui les couvre, testez avec des données réalistes, puis supprimez ce qui ne fait pas ses preuves.

Si vous construisez un outil interne ou un portail client, planifiez les besoins d’index tôt car ces apps s’enrichissent souvent de nouveaux filtres et listes. Si vous développez avec AppMaster (appmaster.io), traitez chaque configuration de filtre et de tri d’écran comme un contrat de requête concret, puis n’ajoutez que les index qui correspondent à ces clics réels.