Indexes PostgreSQL pour applications SaaS : B-tree vs GIN vs GiST

Quel problème les index résolvent dans de vrais écrans SaaS

Un index change la façon dont PostgreSQL trouve les lignes. Sans index, la base doit souvent lire une grande partie de la table (un sequential scan) puis en jeter la plupart. Avec le bon index, elle peut sauter directement aux lignes correspondantes (une recherche par index), puis ne récupérer que ce dont elle a besoin.

Vous le remarquez vite en SaaS parce que les écrans quotidiens font beaucoup de requêtes. Un seul clic peut déclencher plusieurs lectures : la page de liste, un total, quelques cartes de dashboard, et une boîte de recherche. Quand une table passe de milliers à millions de lignes, la même requête qui semblait instantanée commence à ralentir.

Un exemple typique est une page Orders filtrée par statut et date, triée par les plus récentes et paginée. Si PostgreSQL doit scanner toute la table orders pour trouver les commandes payées des 30 derniers jours, chaque chargement de page effectue du travail supplémentaire. Un bon index transforme cela en un saut rapide vers la bonne portion de données.

Les index ne sont pas gratuits. Chacun accélère des lectures pour des requêtes spécifiques, mais ralentit aussi les écritures (INSERT/UPDATE/DELETE doivent mettre à jour les index) et utilise plus d'espace disque (et augmente la pression sur le cache). C'est pour ça qu'il faut partir des motifs réels de requête, pas des types d'index.

Une règle simple qui évite du travail inutile : n'ajoutez un index que si vous pouvez pointer vers une requête précise et fréquente qu'il accélérera. Si vous construisez des écrans avec un constructeur piloté par chat comme Koder.ai, conservez le SQL derrière vos pages de liste et tableaux de bord et utilisez‑le comme liste de souhaits pour les index.

B-tree vs GIN vs GiST en termes simples

La plupart des confusions sur les index disparaissent quand vous arrêtez de penser en fonctionnalités (JSON, recherche, tableaux) et que vous pensez en forme de requête : que fait la clause WHERE, et comment attendez‑vous les résultats triés ?

B-tree : rapide pour le tri et les comparaisons

Utilisez B-tree quand votre requête ressemble à des comparaisons normales et que vous tenez au tri. C'est le cheval de trait pour l'égalité, les intervalles et les jointures.

Exemples de formes : filtrer par tenant_id = ?, status = 'active', created_at >= ?, joindre users.id = orders.user_id, ou afficher « plus récentes d'abord » avec ORDER BY created_at DESC.

GIN et GiST : quand une ligne contient de nombreuses valeurs cherchables

GIN (Generalized Inverted Index) convient quand une colonne contient beaucoup d'éléments et que vous demandez « contient‑il X ? ». C'est courant avec les clés JSONB, les éléments de tableau et les vecteurs full‑text.

Formes d'exemple : metadata @> {'plan':'pro'} sur JSONB, tags @> ARRAY['urgent'], ou to_tsvector(body) @@ plainto_tsquery('reset password').

GiST (Generalized Search Tree) convient aux questions de distance ou de recouvrement, où les valeurs se comportent comme des intervalles ou des formes. On l'utilise souvent pour les types range, les données géométriques et certaines recherches de « correspondance la plus proche ».

Formes d'exemple : fenêtres temporelles qui se chevauchent avec des colonnes range, certaines recherches par similarité (par ex. avec des opérateurs trigram), ou des requêtes spatiales (si vous utilisez PostGIS).

Une manière pratique de choisir :

• Si vous filtrez ou triez par des colonnes normales, commencez par B-tree.
• Si vous vérifiez la contenance ou l'appartenance, regardez GIN.
• Si vous demandez « à quel point ? » ou « est‑ce que ça se chevauche ? », GiST est souvent le bon choix.
• Si une requête est rare ou si la table est petite, vous n'avez peut‑être pas besoin d'un nouvel index.
• Si vous ne savez pas décrire la forme de la requête, mesurez d'abord (EXPLAIN) avant d'ajouter quoi que ce soit.

Les index accélèrent les lectures, mais coûtent en temps d'écriture et en disque. En SaaS, cet arbitrage compte surtout sur les tables chaudes comme events, sessions et activity logs.

Schémas B-tree pour filtres, tri et pagination

La plupart des écrans de liste SaaS ont la même forme : une frontière tenant, quelques filtres et un tri prévisible. Les index B-tree sont le choix par défaut ici, et ils sont généralement les moins coûteux à maintenir.

Un schéma courant est WHERE tenant_id = ? plus des filtres comme status = ?, user_id = ?, et une plage temporelle comme created_at >= ?. Pour des index B-tree composites, mettez d'abord les filtres d'égalité (colonnes comparées avec =), puis ajoutez la colonne par laquelle vous triez.

Règles qui fonctionnent bien dans la plupart des applications :

• Commencez par tenant_id si chaque requête est scoped par tenant.
• Mettez ensuite les filtres = (souvent status, user_id).
• Placez la colonne ORDER BY en dernier (souvent created_at ou id).
• Utilisez INCLUDE pour couvrir les pages de liste sans élargir la clé.
• Préférez la pagination par seek (keyset) plutôt que OFFSET quand les pages deviennent profondes.

Un exemple réaliste : une page Tickets montrant les éléments les plus récents en premier, filtrée par statut.

-- Query
SELECT id, status, created_at, title
FROM tickets
WHERE tenant_id = $1
  AND status = $2
ORDER BY created_at DESC
LIMIT 50;

-- Index
CREATE INDEX tickets_tenant_status_created_at_idx
ON tickets (tenant_id, status, created_at DESC)
INCLUDE (title);

Cet index supporte à la fois le filtre et le tri, donc Postgres peut éviter de trier un grand jeu de résultats. La partie INCLUDE (title) aide la page de liste à toucher moins de pages de table, tout en gardant les clés d'index centrées sur le filtrage et le tri.

Pour les plages temporelles, la même idée s'applique :

SELECT id, created_at
FROM events
WHERE tenant_id = $1
  AND created_at >= $2
  AND created_at <  $3
ORDER BY created_at DESC
LIMIT 100;

CREATE INDEX events_tenant_created_at_idx
ON events (tenant_id, created_at DESC);

La pagination est souvent l'endroit où de nombreuses apps SaaS ralentissent. La pagination par OFFSET (OFFSET 50000) force la base à parcourir beaucoup de lignes. La pagination par seek reste rapide en utilisant la dernière clé vue :

SELECT id, created_at
FROM tickets
WHERE tenant_id = $1
  AND created_at < $2
ORDER BY created_at DESC
LIMIT 50;

Avec le bon index B-tree, cela reste rapide même quand la table grossit.

Indexation adaptée aux tenants sans sur‑indexer

La plupart des apps SaaS sont multi‑tenant : chaque requête doit rester dans un tenant. Si vos index n'incluent pas tenant_id, Postgres peut quand même trouver des lignes rapidement, mais il parcourt souvent bien plus d'entrées d'index que nécessaire. Les index aware‑tenant gardent les données de chaque tenant regroupées dans l'index, de sorte que les écrans courants restent rapides et prévisibles.

Une règle simple : mettez tenant_id en premier dans l'index quand la requête filtre toujours par tenant. Ensuite ajoutez la colonne sur laquelle vous filtrez ou triez le plus.

Les index à fort impact, souvent ennuyeux mais efficaces, ressemblent à :

• (tenant_id, created_at) pour les listes des éléments récents et la pagination par curseur
• (tenant_id, status) pour les filtres par statut (Open, Paid, Failed)
• (tenant_id, user_id) pour les écrans « éléments possédés par cet utilisateur »
• (tenant_id, updated_at) pour les vues admin « récemment modifiés »
• (tenant_id, external_id) pour les recherches venant de webhooks ou d'import

Le sur‑indexage arrive quand vous ajoutez un nouvel index pour chaque écran légèrement différent. Avant d'en créer un autre, vérifiez si un index composite existant couvre déjà les colonnes les plus à gauche dont vous avez besoin. Par exemple, si vous avez (tenant_id, created_at), vous n'avez généralement pas aussi besoin de (tenant_id, created_at, id) sauf si vous filtrez vraiment sur id après ces colonnes.

Les index partiels peuvent réduire la taille et le coût d'écriture quand la plupart des lignes ne sont pas pertinentes. Ils fonctionnent bien avec les suppressions logiques et les données « actives seulement », par exemple : indexer seulement où deleted_at IS NULL, ou seulement où status = 'active'.

Chaque index supplémentaire alourdit les écritures. Les inserts doivent mettre à jour chaque index, et les updates peuvent toucher plusieurs index même quand vous ne changez qu'une colonne. Si votre app ingère beaucoup d'événements (y compris des apps construites rapidement avec Koder.ai), gardez les index centrés sur les quelques formes de requête que les utilisateurs touchent chaque jour.

Indexer JSONB : GIN et index d'expression ciblés

JSONB est pratique quand votre app a besoin de champs flexibles comme des feature flags, des attributs utilisateur ou des paramètres par tenant. Le piège est que les différents opérateurs JSONB se comportent différemment, donc le meilleur index dépend de la manière dont vous interrogez.

Deux formes importent le plus :

• Contenance : « ce JSON contient‑il ces paires clé‑valeur ? » avec @>.
• Extraction par chemin : « quelle est la valeur de ce champ précis ? » avec -> / ->> (souvent comparé avec =).

Quand un index GIN est le bon choix

Si vous filtrez fréquemment avec @>, un index GIN sur la colonne JSONB paie généralement.

-- Query shape: containment
SELECT id
FROM accounts
WHERE tenant_id = $1
  AND metadata @> '{"region":"eu","plan":"pro"}';

-- Index
CREATE INDEX accounts_metadata_gin
ON accounts
USING GIN (metadata);

Si la structure JSON est prévisible et que vous utilisez surtout @> sur des clés de premier niveau, jsonb_path_ops peut être plus petit et plus rapide, mais il supporte moins d'opérateurs.

Quand un index d'expression est mieux

Si votre UI filtre souvent sur un seul champ (comme plan), extraire ce champ et l'indexer est souvent plus rapide et moins cher qu'un large GIN.

SELECT id
FROM accounts
WHERE tenant_id = $1
  AND metadata->> 'plan' = 'pro';

CREATE INDEX accounts_plan_expr
ON accounts ((metadata->> 'plan'));

Une règle pratique : gardez JSONB pour les attributs flexibles et rarement filtrés, mais promotez les champs stables et fortement utilisés (plan, status, created_at) en vraies colonnes. Si vous itérez vite sur une app générée, c'est souvent un ajustement de schéma facile une fois que vous voyez quels filtres apparaissent sur chaque page.

Exemple : si vous stockez {"tags":["beta","finance"],"region":"us"} en JSONB, utilisez GIN quand vous filtrez par paquets d'attributs (@>), et ajoutez des index d'expression pour les quelques clés qui pilotent la plupart des vues de liste (plan, region).

Indexer les tableaux : quand GIN brille

Les tableaux sont tentants car ils sont faciles à stocker et à lire. Une colonne users.roles text[] ou projects.labels text[] peut bien marcher quand vous posez surtout la question : cette ligne contient‑elle une valeur ? C'est exactement là où un index GIN aide.

GIN est le choix par défaut pour les requêtes d'appartenance sur des tableaux. Il décompose le tableau en éléments individuels et construit une recherche rapide vers les lignes qui les contiennent.

Formes de requête sur tableaux qui profitent souvent :

• Contient une valeur ou un ensemble : @> (array contains)
• Chevauchement avec un ensemble : && (le tableau partage des éléments)
• Parfois : = ANY(...), mais @> est souvent plus prévisible

Un exemple typique pour filtrer les utilisateurs par rôle :

-- Find users who have the "admin" role
SELECT id, email
FROM users
WHERE roles @> ARRAY['admin'];

CREATE INDEX users_roles_gin ON users USING GIN (roles);

Et filtrer des projets par un ensemble d'étiquettes (doit contenir les deux labels) :

SELECT id, name
FROM projects
WHERE labels @> ARRAY['billing', 'urgent'];

CREATE INDEX projects_labels_gin ON projects USING GIN (labels);

Où les gens sont surpris : certains motifs n'utilisent pas l'index comme attendu. Si vous transformez le tableau en chaîne (array_to_string(labels, ',')) puis faites un LIKE, l'index GIN n'aidera pas. De même, si vous avez besoin de « commence par » ou de correspondances floues à l'intérieur des labels, vous êtes dans le champ de la recherche textuelle, pas de l'appartenance tableau.

Les tableaux deviennent aussi difficiles à maintenir quand ils se transforment en mini‑base de données : mises à jour fréquentes, besoin de métadonnées par élément (qui a ajouté le label, quand, pourquoi), ou analyses par label. À ce stade, une table de jointure comme project_labels(project_id, label) est en général plus simple à valider, interroger et faire évoluer.

Indexation pour la recherche : full‑text et correspondances floues (GIN et GiST)

Pour les boîtes de recherche, deux motifs reviennent souvent : la recherche full‑text (trouver des enregistrements sur un sujet) et la recherche floue (gérer fautes de frappe, noms partiels, et patterns ILIKE). Le bon index fait la différence entre « instantané » et « timeouts à 10k utilisateurs ».

Recherche full‑text : tsvector + GIN

Utilisez la recherche full‑text quand les utilisateurs tapent de vrais mots et que vous voulez des résultats classés par pertinence, comme rechercher des tickets par sujet et description. L'installation habituelle est de stocker un tsvector (souvent en colonne générée) et de l'indexer avec GIN. On recherche avec @@ et un tsquery.

-- Tickets: full-text search on subject + body
ALTER TABLE tickets
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
  to_tsvector('simple', coalesce(subject,'') || ' ' || coalesce(body,''))
) STORED;

CREATE INDEX tickets_search_vec_gin
ON tickets USING GIN (search_vec);

-- Query
SELECT id, subject
FROM tickets
WHERE search_vec @@ plainto_tsquery('simple', 'invoice failed');

-- Customers: fuzzy name search using trigrams
CREATE INDEX customers_name_trgm
ON customers USING GIN (name gin_trgm_ops);

SELECT id, name
FROM customers
WHERE name ILIKE '%jon smth%';

Ce qu'il faut stocker dans le vecteur : seulement les champs que vous recherchez vraiment. Si vous incluez tout (notes, logs internes), vous payez en taille d'index et en coût d'écriture.

Correspondances floues : trigrammes avec GIN ou GiST

Utilisez la similarité trigramme quand les utilisateurs cherchent des noms, des emails ou de courtes phrases et que vous avez besoin de correspondances partielles ou de tolérance aux fautes. Les trigrammes aident pour ILIKE '%term%' et les opérateurs de similarité. GIN est généralement plus rapide pour « est‑ce que ça correspond ? » ; GiST peut être mieux quand vous vous souciez aussi du tri par similarité.

Règles empiriques :

• Utilisez GIN + tsvector pour la recherche textuelle basée sur la pertinence.
• Utilisez les trigrammes pour les recherches ILIKE et tolérantes aux fautes sur les noms.

Pièges à surveiller :

• Les wildcards en début sans trigrammes (ILIKE '%abc') forcent des scans.
• Les termes de recherche très courts (1–2 caractères) exploitent mal les trigrammes.
• Les stop words et la racinisation (stemming) peuvent surprendre dans les résultats full‑text, choisissez une configuration qui correspond à la langue de votre produit.

Si vous livrez des écrans de recherche rapidement, traitez l'index comme une partie de la fonctionnalité : UX de recherche et choix d'index doivent être conçus ensemble.

Étape par étape : de la requête lente au bon index

Commencez par la requête exacte que votre app exécute, pas une supposition. Un « écran lent » est généralement une instruction SQL avec un WHERE et un ORDER BY très spécifiques. Copiez‑la depuis les logs, la sortie debug de votre ORM ou le mécanisme de capture de requêtes que vous utilisez.

Un workflow qui tient dans les apps réelles :

• Capturez le SQL complet, y compris WHERE, ORDER BY et LIMIT.
• Lancez EXPLAIN (ANALYZE, BUFFERS) sur la même requête.
• Concentrez‑vous sur les opérateurs qui font le travail (=, >=, LIKE, @>, @@), pas seulement sur les noms de colonnes.
• Ajoutez le plus petit index qui correspond à ces opérateurs.
• Relancez EXPLAIN (ANALYZE, BUFFERS) avec un volume de données réaliste.

Voici un exemple concret. Une page Customers filtre par tenant et status, trie par les plus récents et pagine :

SELECT id, created_at, email
FROM customers
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT 50;

Si EXPLAIN montre un sequential scan et un tri, un index B-tree qui matche le filtre et le tri le corrige souvent :

CREATE INDEX ON customers (tenant_id, status, created_at DESC);

Si la lenteur vient d'un filtrage JSONB comme metadata @> '{"plan":"pro"}', cela pointe vers GIN. Si c'est une recherche full‑text comme to_tsvector(...) @@ plainto_tsquery(...), c'est aussi un cas pour un index GIN‑backed. Si c'est un opérateur de « plus proche » ou d'overlap, GiST est souvent le bon choix.

Après avoir ajouté l'index, mesurez l'arbitrage. Vérifiez la taille de l'index, le temps d'insertion et de mise à jour, et si cela aide les quelques requêtes lentes ou seulement un cas marginal. Dans les projets qui avancent vite (y compris ceux construits avec Koder.ai), cette vérification évite d'empiler des index inutilisés.

Erreurs courantes d'indexation qui font perdre du temps et de l'argent

La plupart des problèmes d'index ne concernent pas le choix B-tree vs GIN vs GiST. Ils viennent de la création d'un index qui a l'air juste, mais qui ne correspond pas à la manière dont l'app interroge la table.

Erreurs fréquentes :

• Index jamais utilisés. La requête utilise un opérateur différent de celui supporté par l'index, ou un index composite a le mauvais ordre de colonnes. Si votre WHERE commence par tenant_id et created_at, mais que l'index commence par created_at, le planner peut l'ignorer.
• Indexer des colonnes à faible cardinalité seules. Un index seul sur status, is_active ou un booléen est souvent peu utile car il correspond à trop de lignes. Associez‑le à une colonne sélective (comme tenant_id ou created_at) ou évitez‑le.
• Index qui se chevauchent et gonflent la base tout en ralentissant les écritures. Des index similaires sur la même table peuvent doubler l'espace et ralentir inserts/updates.
• Pièges de pagination. Indexer juste pour supporter OFFSET est courant. Si vous utilisez la pagination par clé, il vous faut un index qui correspond au tri et au filtre « dernière clé vue ».
• Statistiques obsolètes et table encombrée. Si autovacuum n'arrive pas à suivre ou si ANALYZE n'a pas été lancé récemment, le planner peut choisir de mauvais plans même quand le bon index existe.

Un exemple concret : votre écran Invoices filtre par tenant_id et status, puis trie par created_at DESC. Un index seulement sur status aidera peu. Un meilleur choix est un index composite commençant par tenant_id, puis status, puis created_at (filtre d'abord, tri en dernier). Ce changement unique bat souvent l'ajout de trois index séparés.

Considérez chaque index comme un coût. Il doit gagner sa place dans les requêtes réelles, pas seulement en théorie.

Checklist rapide avant de déployer des changements d'index

Les changements d'index se déploient facilement mais sont pénibles à annuler s'ils augmentent le coût d'écriture ou verrouillent une table chargée. Avant de merger, traitez‑les comme une petite release.

Commencez par décider ce que vous optimisez. Tirez deux courts classements depuis vos logs ou monitoring : les requêtes qui s'exécutent le plus souvent, et celles avec la latence la plus élevée. Pour chacune, notez la forme exacte : colonnes de filtre, ordre de tri, jointures et opérateurs utilisés (=, range, IN, ILIKE, opérateurs JSONB, contains pour tableaux). Cela évite les suppositions et vous aide à choisir le bon type d'index.

Checklist avant déploiement :

• Confirmez que la requête est bien scoped par tenant si c'est le cas attendu.
• Faites correspondre l'index à l'opérateur : B-tree pour égalité/plage/tri, GIN pour appartenance (tableaux, JSONB, full text), GiST pour overlap ou cas de distance.
• Préférez un index composite qui matche le filtre commun + tri plutôt que plusieurs index mono‑colonne.
• Gardez l'index étroit : n'incluez que les colonnes réellement utilisées par la requête.
• Planifiez le rollout : la création d'index va‑t‑elle bloquer des écritures et faut‑il la faire hors pics ?

Après ajout de l'index, vérifiez qu'il aide dans le plan réel. Lancez EXPLAIN (ANALYZE, BUFFERS) sur la requête exacte et comparez avant/après. Puis observez le comportement en production pendant une journée :

• La latence de lecture a‑t‑elle diminué pour les écrans ciblés ?
• La latence d'écriture a‑t‑elle augmenté (inserts/updates) ?
• Le CPU ou l'espace disque ont‑ils bondi à cause du nouvel index ?
• L'index est‑il réellement utilisé ou est‑il un poids mort ?

Si vous construisez avec Koder.ai, conservez le SQL généré pour une ou deux pages lentes à côté du changement, ainsi l'index correspondra exactement à ce que l'app exécute.

Exemple : indexer un workflow SaaS type + étapes suivantes

Imaginez un écran admin courant : une liste Users avec scoping tenant, quelques filtres, tri par dernière activité et une boîte de recherche. C'est là que les index cessent d'être théoriques et commencent à faire gagner du temps réel.

Trois formes de requête que vous verrez généralement :

-- 1) List page with tenant + status filter + sort
SELECT id, email, last_active_at
FROM users
WHERE tenant_id = $1 AND status = $2
ORDER BY last_active_at DESC
LIMIT 50;

-- 2) Search box (full-text)
SELECT id, email
FROM users
WHERE tenant_id = $1
  AND to_tsvector('simple', coalesce(name,'') || ' ' || coalesce(email,'')) @@ plainto_tsquery($2)
ORDER BY last_active_at DESC
LIMIT 50;

-- 3) Filter on JSON metadata (plan, flags)
SELECT id
FROM users
WHERE tenant_id = $1
  AND metadata @> '{"plan":"pro"}'::jsonb;

Un jeu d'index petit mais intentionnel pour cet écran :

• B-tree composite pour la liste : (tenant_id, status, last_active_at DESC).
• GIN pour la recherche : une colonne tsvector générée avec un index GIN.
• Index JSONB basé sur l'usage : GIN (metadata) quand vous utilisez souvent @>, ou une expression B-tree comme ((metadata->> 'plan')) quand vous filtrez surtout sur une clé.

Les besoins mixtes sont normaux. Si une page fait filtres + recherche + JSON, évitez de tout compacter dans un méga‑index. Gardez le B-tree pour le tri/pagination, puis ajoutez un index spécialisé (souvent GIN) pour la partie la plus coûteuse.

Étapes suivantes : choisissez un écran lent, notez ses 2–3 formes de requête principales, et révisez chaque index par objectif (filtre, tri, recherche, JSON). Si un index ne correspond pas clairement à une requête réelle, retirez‑le du plan. Si vous itérez rapidement sur Koder.ai, faire cette revue au fur et à mesure que vous ajoutez des écrans prévient la prolifération d'index pendant que votre schéma change.