Indexation des données JSONB : utilisation des index GIN et BTREE

L’indexation dans PostgreSQL, c’est un moyen de retrouver rapidement des données dans ta base. Imagine que les données de ta table sont des bouquins : l’indexation, c’est comme le catalogue de la bibliothèque qui te permet de trouver vite le bon livre par titre ou auteur. Avec JSONB, c’est un peu plus tricky, parce que les données sont stockées dans un format structuré, pas juste en lignes et colonnes classiques.

Quand tes données JSONB commencent à gonfler façon “Harry Potter sans les images”, la recherche dedans peut devenir super lente. Genre, si tu veux trouver toutes les commandes où la clé "status" vaut "delivered", PostgreSQL doit scanner toutes les lignes pour faire la recherche. Avoue, c’est le genre de taf que t’as pas envie de faire à la main, non ?

Eh ben, les index GIN et BTREE, c’est nos super-héros qui débarquent pour nous sauver des attentes interminables !

Types d’index pour JSONB

GIN (Generalized Inverted Index)

L’index GIN est spécialement conçu pour gérer les données structurées, genre les arrays et objets, donc parfait pour JSONB. Il permet d’indexer non pas tout l’objet d’un coup, mais chaque clé et valeur à l’intérieur. Ça veut dire qu’avec GIN, tu peux retrouver vite les lignes qui contiennent certaines clés, valeurs ou combinaisons.

Imagine une colonne JSONB avec ces données :

{"nom": "Alice", "âge": 25, "ville": "Berlin"}

L’index GIN crée une structure interne où les clés "nom", "âge" et "ville" sont reliées à leurs valeurs. Donc, quand tu cherches "nom": "Alice", PostgreSQL sait déjà où regarder — il ne va pas fouiller toute la table.

BTREE

L’index BTREE est plus classique. Il crée une structure ordonnée qui permet de retrouver vite les données par valeur précise. Pour JSONB, tu peux utiliser BTREE si tu cherches une correspondance exacte ou si tu as une clé fixe (genre tu veux comparer toute la valeur JSONB d’un coup).

Si ta colonne contient des objets JSONB comme :

{"nom": "Bob", "âge": 30}

L’index BTREE peut servir si tu veux retrouver les lignes où tout l’objet est strictement égal.

{"nom": "Bob", "âge": 30}

Créer un index pour JSONB

Voyons d’abord comment créer un index GIN. Tout ce qu’il te faut, c’est la commande magique CREATE INDEX. Voilà à quoi ça ressemble :

-- On crée un index GIN pour une colonne JSONB
CREATE INDEX idx_jsonb_data ON orders USING GIN (data);

Où :

• idx_jsonb_data — c’est le nom de l’index.
• orders — le nom de la table.
• data — la colonne avec les données JSONB.

Après avoir créé cet index, les requêtes qui cherchent des clés ou valeurs dans le JSONB seront bien plus rapides.

Imaginons qu’on a une table orders avec une colonne data qui contient du JSONB :

Requête sans index :

-- Trouvons toutes les commandes avec le statut "livré"
SELECT * FROM orders WHERE data @> '{"status": "livré"}';

Si la table est grosse, cette requête peut prendre un bail. Mais avec l’index GIN, ça va beaucoup plus vite.

Comment créer un index BTREE

Pour créer un index BTREE, il faut changer un peu d’approche. La plupart du temps, pour utiliser BTREE avec JSONB, tu dois préciser que tu veux indexer juste une partie de l’objet. Exemple :

-- On crée un index BTREE pour une clé précise
CREATE INDEX idx_jsonb_total ON orders ((data->>'total'));

Regarde bien (data->>'total'). Ça extrait la valeur de la clé total du JSONB, et c’est cette valeur qui est indexée. Maintenant, si tu cherches les commandes où total = 100, PostgreSQL va utiliser cet index.

Petit exemple avec les mêmes données :

Requête :

-- Trouver toutes les commandes où total = 100
SELECT * FROM orders WHERE data->>'total' = '100';

Avec l’index BTREE sur data->>'total', cette requête sera bien plus rapide.

Comparaison GIN vs BTREE

Si tu as des structures JSONB complexes et que tu utilises souvent des opérateurs comme @> ou ?, prends GIN. Si tu cherches des valeurs ou clés précises et fixes, BTREE peut être le meilleur choix.

Pièges et erreurs classiques avec l’indexation JSONB

Travailler avec l’indexation JSONB, c’est puissant, mais y’a quelques pièges à éviter.

1. Pas d’index là où il en faut. Si tu utilises souvent des données JSONB dans tes filtres (WHERE), mais que t’as pas créé d’index, tes requêtes vont ramer.
2. Trop d’indexation. Si tu crées un index pour chaque clé possible du JSONB, ça va ralentir les insertions et updates.
3. Mauvais choix de type d’index. Si tes requêtes sont complexes et utilisent des opérateurs genre @> ou ?, mais que t’as mis un index BTREE, tu verras pas de gain de perf.
4. Manque de connaissance sur les chemins. Si tu accèdes souvent à des valeurs imbriquées, mais que t’as pas créé d’index pour ce chemin précis (genre data->>'some_key'), ta requête sera toujours lente.

En résumé : quand utiliser quel index

• Utilise GIN si tu as des arrays ou objets complexes, où tu fais souvent des recherches sur les clés et valeurs.
• Utilise BTREE si tu cherches une correspondance exacte ou que tu accèdes souvent à des clés précises.