Index dans PostgreSQL : accélérer sans ralentir

Un index change cela. Au lieu de tout lire, PostgreSQL utilise l'index pour sauter directement vers les lignes pertinentes. La base de données parcourt la structure de l'index — généralement un B-tree — en quelques étapes au lieu de toute la table.

Mais un index n'est pas une accélération gratuite. Chaque fois que vous insérez, mettez à jour ou supprimez des données, PostgreSQL doit aussi mettre à jour l'index. Plus vous créez d'index, plus vous créez de surcharge d'écriture. Une table avec dix index et beaucoup de trafic `INSERT` et `UPDATE` peut devenir plus lente qu'avec aucun index. La documentation officielle le signale explicitement.

L'art n'est pas d'indexer tout, mais d'indexer ce que vos requêtes filtrent réellement. Un index sur une colonne qui n'apparaît jamais dans une clause `WHERE` ne coûte que de l'espace et du temps d'écriture.

Types d'index dans PostgreSQL

PostgreSQL prend en charge plusieurs types d'index, chacun avec sa propre structure interne et son cas d'utilisation. Le choix détermine quels opérateurs l'index peut accélérer et combien d'espace il occupe.

B-tree — le choix par défaut

Le B-tree (arbre équilibré) est le type le plus utilisé et le choix par défaut lorsque vous ne spécifiez pas de type. Il prend en charge les comparaisons avec `<`, `<=`, `=`, `>=` et `>`. Cela le rend adapté aux données numériques et textuelles sur lesquelles vous effectuez des requêtes d'égalité ou de plage.

CREATE INDEX idx_employees_name ON employees (name);

Cette requête crée un index B-tree sur la colonne `name` de la table `employees`. PostgreSQL utilise cet index pour des requêtes comme `WHERE name = 'Jansen'` ou `WHERE name > 'K'`.

Hash — rapide mais limité

L'index Hash ne prend en charge que les comparaisons d'égalité (`=`). Pour les correspondances exactes, il est plus rapide qu'un B-tree, mais il ne peut pas gérer les requêtes de plage. De plus, dans les anciennes versions, l'index Hash n'était pas résistant aux crashs ; cela a été amélioré depuis PostgreSQL 10, mais la limitation aux opérations `=` demeure.

CREATE INDEX idx_users_email_hash ON users USING hash (email);

GiST et SP-GiST — pour les données complexes

GiST (arbre de recherche généralisé) offre une infrastructure flexible pour indexer des données non traditionnelles comme des objets géométriques, du texte ou des documents. SP-GiST (GiST à partition spatiale) est une variante pour les données que l'on peut subdiviser en espaces non équilibrés, comme les arbres de préfixes ou les arbres radix.

Ces types sont pertinents lorsque vous travaillez avec PostGIS ou d'autres extensions qui permettent des requêtes spatiales. Pour des données relationnelles ordinaires, vous en avez rarement besoin.

GIN — pour les collections et le full-text

GIN (index inversé généralisé) s'utilise pour les tableaux, le JSON et les recherches en texte intégral. Il n'indexe pas la valeur elle-même, mais les éléments d'une collection. Une requête qui recherche un élément spécifique dans un champ JSON ou un mot dans un document peut ainsi utiliser l'index.

CREATE INDEX idx_documents_content ON documents USING gin (content);

BRIN — compact pour les grandes tables

BRIN (index de plage de blocs) est destiné aux très grandes tables où les données sont stockées dans un ordre logique. Au lieu d'indexer par ligne, BRIN indexe par bloc de plusieurs lignes. Cela occupe beaucoup moins d'espace qu'un B-tree et est efficace pour les requêtes de plage sur de grands ensembles de données physiquement triés.

CREATE INDEX idx_orders_created_at_brin ON orders USING brin (created_at);

Cet index fonctionne bien si les valeurs `created_at` dans la table sont approximativement dans l'ordre croissant. Si les données sont mélangées, BRIN doit lire trop de blocs et vous perdez l'avantage.

Bloom — via extension

Via l'extension `bloom`, vous pouvez créer un index de filtre de Bloom. Celui-ci vérifie rapidement si une valeur est potentiellement dans un ensemble — avec une petite probabilité de faux positifs. PostgreSQL doit encore vérifier les lignes réelles après un contrôle positif, mais peut exclure rapidement de nombreuses lignes.

CREATE EXTENSION bloom;
CREATE INDEX idx_orders_bloom ON orders USING bloom (customer_id, order_date, status);

Ce type est utile pour les requêtes qui filtrent sur plusieurs colonnes à la fois, où un seul index B-tree ne suffit pas.

Créer un index

La création se fait avec `CREATE INDEX`. Le choix des colonnes à indexer, du type à utiliser et de savoir si l'index doit être unique nécessite de la réflexion.

Pour les requêtes composites, vous créez un index composite sur plusieurs colonnes :

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

Cette index aide pour les requêtes qui filtrent sur les deux champs, comme `WHERE customer_id = 42 AND order_date > '2026-01-01'`. Notez que l’ordre des colonnes dans l’index doit correspondre à l’ordre dans lequel la requête filtre. Un index sur `(customer_id, order_date)` n’aide pas pour une requête qui filtre uniquement sur `order_date`.

Vous voulez un index fonctionnel parce que votre requête applique une fonction à la colonne :

CREATE INDEX idx_employees_upper_name ON employees (UPPER(name));

Sans cet index, PostgreSQL ne peut pas utiliser un index classique sur `name` lorsque votre requête contient `WHERE UPPER(name) = 'JAN'`. La base de données doit alors encore scanner toute la table.

Supprimer un index

Pour supprimer un index, on utilise `DROP INDEX`. C’est utile lorsqu’un index n’est plus nécessaire, qu’il performe mal ou qu’il provoque trop de surcharge en écriture. La suppression n’affecte pas les données elles-mêmes — seulement la vitesse de certaines requêtes.

DROP INDEX idx_employees_name;

Cette opération est permanente. Si vous voulez réutiliser l’index plus tard, vous devez le recréer.

Maintenance des index

Les index peuvent, avec le temps, se fragmenter ou même se corrompre. Deux outils aident à cela.

`reindexdb` est un outil en ligne de commande qui reconstruit les index. Cela peut être nécessaire si un index est corrompu ou si les performances se sont dégradées à cause de modifications fréquentes dans la table sous-jacente.

reindexdb -d mijn_database -t employees

Cette commande reconstruit tous les index de la table `employees` dans la base de données `mijn_database`. Dans la base elle-même, vous pouvez faire la même chose avec la commande SQL `REINDEX` :

REINDEX TABLE employees;

`pg_amcheck` détecte la corruption dans les index et autres objets de base de données, en particulier dans les index B-tree. Exécutez cet outil avant de lancer un `REINDEX` — vous saurez alors si la reconstruction est réellement nécessaire :

pg_amcheck -d mijn_database

Vérification

Vérifiez si un index est réellement utilisé avec `EXPLAIN` :

EXPLAIN SELECT * FROM employees WHERE name = 'Jansen';

Dans la sortie, vous verrez si PostgreSQL utilise un `Index Scan` ou un `Seq Scan`. Un `Seq Scan` signifie que l’index n’est pas utilisé — vérifiez alors si la forme de la requête correspond à la structure de l’index.

Pour voir quels index existent sur une table :

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'employees';

Problèmes fréquents

PostgreSQL n’utilise pas mon index. La forme de la requête doit correspondre à l’index. Une requête avec `WHERE UPPER(name) = 'JAN'` n’utilise pas un index classique sur `name` — pour cela, il faut un index fonctionnel. De même, une requête qui filtre sur une colonne qui n’est pas la première colonne d’un index composite ne peut pas utiliser l’index.

Mes performances en écriture se sont dégradées après l’ajout d’index. Chaque index doit être mis à jour lors des `INSERT`, `UPDATE` et `DELETE`. Supprimez les index qui n’accélèrent aucune requête, ou envisagez un index BRIN si vous travaillez avec de très grandes tables et que les performances en écriture sont plus importantes que la vitesse de lecture.

Un index est corrompu. Exécutez `pg_amcheck` pour confirmer la corruption, puis utilisez `REINDEX` ou `reindexdb` pour reconstruire l’index. Vérifiez ensuite si la corruption réapparaît — si c’est le cas, cela indique un problème avec le matériel ou le système de stockage.

Gérer les index de manière déclarative avec SQLAlchemy

Dans l’écosystème des outils ORM, SQLAlchemy 2.0 prend en compte les fonctionnalités d’index spécifiques à PostgreSQL. Via le langage de définition de schéma, vous pouvez définir des index de manière déclarative dans le code de votre application, sans écrire directement de SQL :

from sqlalchemy import Column, Integer, String, Index
from sqlalchemy.orm import declarative_base</p><p>Base = declarative_base()</p><p>class Employee(Base):
    __tablename__ = 'employees'
    __table_args__ = (
        Index('idx_employees_name', 'name'),
    )</p><p>id = Column(Integer, primary_key=True)
    name = Column(String)

SQLAlchemy traduit cette définition en DDL (Data Definition Language) approprié lorsque vous créez les tables. Cela est pratique lorsque vous souhaitez garder les index sous contrôle de version avec votre schéma. Cela coûte cependant une couche d’abstraction : vous devez connaître la manière de travailler de SQLAlchemy pour configurer des options avancées comme `USING gin` ou les index fonctionnels.

Le bon index n’est pas une question de créer et d’oublier. Le modèle de requête, l’ordre des données et le volume d’écriture déterminent si un index vaut encore la peine après un an — et seul `EXPLAIN` vous le dit honnêtement.