Les index spécialisés – GIN, GiST, BRIN, SP-GiST et Bloom

Bonne lecture et bon apprentissage !
Junior TSAFACK – 20/08/2026
⏱️ Temps de lecture estimé : 12 minutes

---

Dans le cours précédent, nous avons exploré les index B-tree et Hash, qui couvrent la majorité des cas d’usage. Mais PostgreSQL propose d’autres types d’index pour des besoins plus spécifiques : données complexes (tableaux, JSON), géospatiales, très grandes tables, structures non équilibrées, etc. Ce cours vous présente ces index spécialisés et vous aide à choisir le bon outil pour chaque situation.

---

Vue d’ensemble des index spécialisés

Type d’index	Idéal pour	Opérateurs typiques	Taille	Maintenance
GIN	Tableaux, JSONB, recherche plein texte	@>, <@, &&, @@	Grande	Élevée
GiST	Données géospatiales, plages, recherche plein texte	<<, &<, &>, &&, @>	Moyenne	Moyenne
BRIN	Très grandes tables avec données ordonnées	=, <, <=, >, >=, BETWEEN	Très petite	Faible
SP-GiST	Données non uniformes, arbres, préfixes	Spécifique à l’opérateur	Variable	Variable
Bloom	Requêtes d’égalité sur de nombreuses colonnes	= uniquement	Petite	Faible

---

1. L’index GIN (Generalized Inverted Index)

Principe de fonctionnement

GIN est un index inversé : il associe chaque élément d’une valeur composite (ex : un mot dans un texte, une clé dans un JSON, un élément dans un tableau) aux lignes qui le contiennent.

Analogies :

• C’est comme l’index d’un livre : vous cherchez un mot, l’index vous donne les pages où il apparaît.
• Pour un tableau de tags, GIN stocke chaque tag avec la liste des lignes qui le contiennent.

Cas d’usage

GIN est particulièrement efficace pour :

• Tableaux : recherche d’éléments dans un tableau (@>, &&).
• JSONB : vérification de présence de clés ou de valeurs (@>, ?).
• Recherche plein texte (to_tsvector).
• Recherche floue avec pg_trgm (trigrammes).

Exemples pratiques

1. Index sur un tableau

-- Table d'articles avec des tags
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    titre TEXT,
    tags TEXT[]
);

-- Créer un index GIN sur le tableau de tags
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- Requête qui utilise l'index : articles contenant le tag 'postgresql'
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];

2. Index sur JSONB

-- Table avec des données JSONB
CREATE TABLE produits (
    id SERIAL PRIMARY KEY,
    nom TEXT,
    caracteristiques JSONB
);

-- Index GIN sur la colonne JSONB
CREATE INDEX idx_produits_carac ON produits USING GIN (caracteristiques);

-- Requête : produits avec une caractéristique spécifique
SELECT * FROM produits WHERE caracteristiques @> '{"couleur": "rouge"}';

💡 Bon à savoir : L’opérateur @> signifie « contient ». Pour JSONB, caracteristiques @> '{"couleur": "rouge"}' vérifie que le JSON contient la paire clé/valeur.

3. Index pour la recherche plein texte

-- Table de documents
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    contenu TEXT
);

-- Index GIN sur le vecteur de recherche plein texte
CREATE INDEX idx_documents_tsv ON documents USING GIN (to_tsvector('french', contenu));

-- Requête : documents contenant le mot 'sécurité'
SELECT * FROM documents
WHERE to_tsvector('french', contenu) @@ to_tsvector('french', 'sécurité');

4. Index GIN avec l’extension pg_trgm (recherche approximative)

-- Activer l'extension
CREATE EXTENSION pg_trgm;

-- Index GIN sur les trigrammes
CREATE INDEX idx_clients_nom_trgm ON clients USING GIN (nom gin_trgm_ops);

-- Recherche approximative (similarité)
SELECT * FROM clients WHERE nom % 'Dupont';  -- % = similarité
SELECT * FROM clients WHERE nom ILIKE '%dup%';  -- aussi accéléré

Bonnes pratiques GIN

• Utilisez GIN pour les données complexes : tableaux, JSONB, texte.
• Surveillez les coûts de maintenance : GIN a un coût élevé en écriture.
• Utilisez CREATE INDEX CONCURRENTLY sur les grandes tables pour éviter les blocages.
• Évitez GIN sur les tables avec des mises à jour fréquentes.
• L’extension btree_gin permet d’utiliser GIN avec des types simples (INT, TEXT) pour des index multi-colonnes.

---

2. L’index GiST (Generalized Search Tree)

Principe de fonctionnement

GiST est un cadre d’indexation extensible qui permet de supporter une grande variété de types de données et d’opérateurs. Il utilise une structure d’arbre équilibré similaire au B-tree, mais généralisée pour accepter des données complexes comme des points, des polygones, des plages, etc..

Cas d’usage

GiST est idéal pour :

• Données géospatiales (PostGIS) : points, lignes, polygones.
• Plages (tsrange, int4range, etc.) : recherche de chevauchement.
• Recherche plein texte (alternative à GIN).
• Recherche de plus proches voisins (k-NN).

Exemples pratiques

1. Index géospatial (avec PostGIS)

-- Activer PostGIS
CREATE EXTENSION postgis;

-- Table de points d'intérêt
CREATE TABLE points_interet (
    id SERIAL PRIMARY KEY,
    nom TEXT,
    geom GEOMETRY(Point, 4326)
);

-- Index GiST sur la colonne géométrique
CREATE INDEX idx_points_geom ON points_interet USING GIST (geom);

-- Requête : points dans un rayon de 5 km autour d'une coordonnée
SELECT * FROM points_interet
WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(2.35, 48.86), 4326), 5000);

2. Index sur les plages (range types)

-- Table de réservations avec une plage de dates
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    chambre_id INTEGER,
    periode TSRANGE
);

-- Index GiST sur la plage
CREATE INDEX idx_reservations_periode ON reservations USING GIST (periode);

-- Requête : réservations qui chevauchent une période donnée
SELECT * FROM reservations
WHERE periode && '[2024-12-01, 2024-12-10)'::tsrange;

3. Index GiST pour la recherche plein texte

-- Index GiST sur le vecteur de recherche (alternative à GIN)
CREATE INDEX idx_documents_gist ON documents USING GIST (to_tsvector('french', contenu));

-- Requête
SELECT * FROM documents
WHERE to_tsvector('french', contenu) @@ to_tsvector('french', 'sécurité');

4. Recherche de plus proches voisins (k-NN)

-- Table de villes avec coordonnées
CREATE TABLE villes (
    id SERIAL PRIMARY KEY,
    nom TEXT,
    coord POINT
);

-- Index GiST sur les coordonnées
CREATE INDEX idx_villes_coord ON villes USING GIST (coord);

-- Trouver les 5 villes les plus proches d'un point
SELECT *, coord <-> '(2.35, 48.86)'::point AS distance
FROM villes
ORDER BY coord <-> '(2.35, 48.86)'::point
LIMIT 5;

💡 Bon à savoir : L’opérateur <-> calcule la distance. GiST est le seul type d’index qui supporte efficacement ce type de requête.

Bonnes pratiques GiST

• Choisissez GiST pour les données complexes : spatiales, plages, texte.
• Vérifiez que vos opérateurs sont supportés.
• Surveillez les coûts de maintenance : GiST est plus coûteux que B-tree.
• Analysez la distribution des données : GiST est plus efficace avec des données bien réparties.

---

3. L’index BRIN (Block Range Index)

Principe de fonctionnement

BRIN est un index léger qui stocke des résumés (min, max, etc.) pour chaque bloc de données (par défaut 128 pages).

Analogies :

• C’est comme les pages d’un annuaire : au lieu d’indexer chaque nom, vous indexez chaque page en indiquant la première et la dernière lettre de la page.
• Pour trouver un nom commençant par « M », vous sautez les pages qui contiennent « A-E » et « F-L ».

Avantages :

• Très petit : quelques kilo-octets pour une table de plusieurs téraoctets.
• Maintenance faible : pas de mise à jour à chaque insertion (si les données sont ordonnées).

Inconvénients :

• Moins précis : peut retourner des faux positifs (recheck nécessaire).
• Inefficace si les données ne sont pas naturellement ordonnées.

Cas d’usage

BRIN est idéal pour :

• Très grandes tables (milliards de lignes).
• Données naturellement ordonnées : timestamps, séquences, dates.
• Requêtes par plage : WHERE date BETWEEN '2024-01-01' AND '2024-01-31'.

Exemples pratiques

1. Index BRIN basique

-- Table de logs avec un timestamp
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    message TEXT
);

-- Insérer des données ordonnées (par exemple, des logs en temps réel)
-- ...

-- Index BRIN sur le timestamp
CREATE INDEX idx_logs_timestamp_brin ON logs USING BRIN (timestamp);

-- Requête par plage (très efficace avec BRIN)
SELECT * FROM logs
WHERE timestamp BETWEEN '2024-01-01' AND '2024-01-31';

2. Index BRIN sur plusieurs colonnes

-- Index BRIN multi-colonnes
CREATE INDEX idx_logs_brin ON logs USING BRIN (timestamp, level);

-- Requête sur les deux colonnes
SELECT * FROM logs
WHERE timestamp BETWEEN '2024-01-01' AND '2024-01-31'
AND level = 'ERROR';

💡 Bon à savoir : Les index BRIN multi-colonnes peuvent être utilisés avec n’importe quelle sous-ensemble des colonnes.

3. Index BRIN avec paramètre pages_per_range

-- pages_per_range = 32 (plus précis, mais plus grand)
CREATE INDEX idx_logs_brin_custom ON logs USING BRIN (timestamp)
WITH (pages_per_range = 32);

-- pages_per_range = 256 (moins précis, mais plus petit)
CREATE INDEX idx_logs_brin_large ON logs USING BRIN (timestamp)
WITH (pages_per_range = 256);

La valeur par défaut est 128. Plus la valeur est petite, plus l’index est précis mais plus il est volumineux.

4. Comparaison B-tree vs BRIN

-- Table de 10 millions de lignes avec des dates ordonnées
CREATE TABLE mesures (
    id SERIAL PRIMARY KEY,
    date_mesure DATE,
    valeur NUMERIC
);

-- Index B-tree (taille : ~214 Mo)
CREATE INDEX idx_mesures_btree ON mesures (date_mesure);

-- Index BRIN (taille : ~240 Ko, soit 900x plus petit !)
CREATE INDEX idx_mesures_brin ON mesures USING BRIN (date_mesure);

Bonnes pratiques BRIN

• Utilisez BRIN sur les très grandes tables (plus de 10 millions de lignes).
• Les données doivent être naturellement ordonnées (timestamps, séquences).
• Ajustez pages_per_range pour équilibrer précision et taille.
• Surveillez les performances : BRIN peut se dégrader si les données deviennent désordonnées.
• Pensez à REINDEX périodiquement si les performances diminuent.

---

4. L’index SP‑GiST (Space‑Partitioned GiST)

Principe de fonctionnement

SP‑GiST est une variante de GiST qui supporte des structures d’arbre non équilibrées, comme les quadtrees, les k‑d trees et les radix trees. Elle est optimisée pour les données qui ne se prêtent pas bien aux arbres équilibrés.

Cas d’usage

SP‑GiST est idéal pour :

• Données géospatiales avec des distributions non uniformes.
• Recherche par préfixe (ex : mots commençant par une chaîne).
• Données multidimensionnelles (points, vecteurs).
• Structures hiérarchiques (ltree).

Exemples pratiques

1. Index SP-GiST sur des points géographiques

-- Table de points d'intérêt
CREATE TABLE poi (
    id SERIAL PRIMARY KEY,
    nom TEXT,
    location POINT
);

-- Index SP-GiST sur les points
CREATE INDEX idx_poi_location_spgist ON poi USING SPGIST (location);

-- Recherche des plus proches voisins
SELECT *, location <-> '(2.35, 48.86)'::point AS distance
FROM poi
ORDER BY location <-> '(2.35, 48.86)'::point
LIMIT 10;

2. Index SP-GiST pour la recherche par préfixe

-- Table de produits
CREATE TABLE produits (
    id SERIAL PRIMARY KEY,
    code TEXT,
    description TEXT
);

-- Index SP-GiST pour la recherche par préfixe
CREATE EXTENSION IF NOT EXISTS spgist;
CREATE INDEX idx_produits_code_spgist ON produits USING SPGIST (code spgist_text_ops);

-- Requête : produits dont le code commence par 'ABC'
SELECT * FROM produits WHERE code ~ '^ABC';

3. Index SP-GiST sur ltree (structures hiérarchiques)

-- Activer l'extension ltree
CREATE EXTENSION ltree;

-- Table de catégories avec chemin hiérarchique
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    chemin LTREE
);

-- Index SP-GiST sur le chemin
CREATE INDEX idx_categories_chemin_spgist ON categories USING SPGIST (chemin spgist_path_ops);

-- Requête : catégories sous la branche 'informatique/logiciels'
SELECT * FROM categories WHERE chemin ~ 'informatique.logiciels.*';

SP‑GiST vs GiST

Critère	GiST	SP‑GiST
Structure	Arbre équilibré	Arbre non équilibré (partitionné)
Idéal pour	Données bien réparties	Données non uniformes, préfixes
Performance	Bonne en général	Excellente pour certains cas spécifiques

---

5. L’index Bloom (filtre de Bloom)

Principe de fonctionnement

L’index Bloom utilise un filtre de Bloom : une structure de données probabiliste qui permet de tester rapidement si un élément n’est pas dans un ensemble.

Caractéristiques :

• Faux positifs possibles : l’index peut dire qu’une ligne existe alors qu’elle n’existe pas.
• Pas de faux négatifs : si l’index dit qu’une ligne n’existe pas, c’est certain.
• Très petit et très rapide pour les requêtes d’égalité.

Cas d’usage

Bloom est idéal pour :

• Tables avec de nombreuses colonnes.
• Requêtes d’égalité sur des combinaisons arbitraires de colonnes.
• Quand un index B-tree unique serait trop volumineux.

Exemple pratique

-- Activer l'extension bloom
CREATE EXTENSION bloom;

-- Table avec de nombreuses colonnes
CREATE TABLE tbloom AS
SELECT
    (random() * 1000000)::int AS i1,
    (random() * 1000000)::int AS i2,
    (random() * 1000000)::int AS i3,
    (random() * 1000000)::int AS i4,
    (random() * 1000000)::int AS i5,
    (random() * 1000000)::int AS i6
FROM generate_series(1, 10000000);

-- Index Bloom sur toutes les colonnes
CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6)
WITH (length = 80, col1 = 2, col2 = 2, col3 = 2, col4 = 2, col5 = 2, col6 = 2);

-- Requête d'égalité sur plusieurs colonnes
EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;

Comparaison avec B-tree :

Index	Taille	Performance
Bloom	~24 Mo	Très rapide pour les requêtes multi-colonnes
B-tree (6 colonnes)	~386 Mo	Plus lent, nécessite un scan de l’index

Paramètres Bloom

Paramètre	Description	Défaut
length	Taille de la signature en bits (multiple de 16)	80
col1 … col32	Nombre de bits pour chaque colonne	2

💡 Bon à savoir : Les index Bloom ne supportent que l’opérateur =. Ils ne sont pas utiles pour les requêtes par plage ou les tris.

---

Synthèse : Quel index choisir ?

Scénario	Index recommandé
Recherches par égalité, plage, tri	B-tree
Recherches par égalité uniquement, très grande table	Hash
Tableaux, JSONB, recherche plein texte	GIN
Données géospatiales, plages, plus proches voisins	GiST
Très grande table, données ordonnées, requêtes par plage	BRIN
Données non uniformes, préfixes, structures hiérarchiques	SP-GiST
Requêtes d’égalité sur de nombreuses colonnes	Bloom

---

Tableau récapitulatif des commandes

Action	Commande
Créer un index GIN	CREATE INDEX idx ON table USING GIN (col);
Créer un index GiST	CREATE INDEX idx ON table USING GIST (col);
Créer un index BRIN	CREATE INDEX idx ON table USING BRIN (col);
Créer un index SP-GiST	CREATE INDEX idx ON table USING SPGIST (col);
Créer un index Bloom	CREATE INDEX idx ON table USING bloom (col1, col2, ...);
Activer une extension	CREATE EXTENSION nom_extension;

---

Prochain chapitre

Vous maîtrisez maintenant tous les types d’index de PostgreSQL. Dans le prochain cours, nous aborderons les vues, un outil puissant pour simplifier vos requêtes et gérer les droits d’accès.

👉 Cours 7 : Les vues – classiques et matérialisées

---

Junior TSAFACK – 20/08/2026