# Index de base de données

## Ce que c'est

Un index de base de données est une structure distincte, stockée à côté d'une table, qui permet à la base d'atteindre directement les lignes correspondant à une condition, au lieu de lire la table de la première à la dernière ligne. Dans l'usage courant et la plupart de la documentation PostgreSQL, on dit simplement index. La documentation le compare à l'index en fin de livre : le lecteur cherchant un sujet y trouve les pages pertinentes et s'y rend, au lieu de feuilleter tout le volume.

Prenez une table de commandes et la condition `WHERE customer_id = 42`. Sans index sur `customer_id`, PostgreSQL a une façon de répondre : un parcours séquentiel, lisant chaque ligne pour garder celles qui correspondent. Le coût est le même, que le client ait deux commandes ou deux mille, et augmente avec la table. Avec un index sur la colonne, PostgreSQL traverse quelques niveaux d'un arbre de recherche jusqu'aux entrées de 42 et ne récupère que ces lignes. Un index se crée en une seule instruction, `DROP INDEX` le supprime, et les deux opérations sont possibles à tout moment.

Une fois l'index créé, PostgreSQL le synchronise avec la table à chaque écriture. Le planificateur décide requête par requête si son usage coûte moins qu'un parcours. Rien dans le SQL ne nomme l'index ; le même peut donc accélérer un `SELECT`, un `UPDATE` ou `DELETE` avec condition de recherche, ainsi qu'une jointure.

PostgreSQL propose plusieurs types, chacun conçu pour une forme de question :

- **B-tree** est le type construit par `CREATE INDEX` sans autre indication. Il répond aux comparaisons d'égalité et de plage (`<`, `<=`, `=`, `>=`, `>`), à `BETWEEN`, `IN`, `IS NULL` et à un `LIKE` dont le motif est ancré au début. Ses entrées restent ordonnées ; il peut donc aussi renvoyer les lignes déjà triées.
- **Hash** stocke un code de hachage de 32 bits par valeur et ne répond qu'à l'égalité.
- **GiST** et **SP-GiST** sont des cadres d'indexation pour des données comme la géométrie, où l'on demande « chevauche », « contient » ou « le plus proche », plutôt que « égal ».
- **GIN** est un index inversé pour des valeurs composées de plusieurs parties, comme les tableaux. Il trouve les lignes dont la valeur contient un élément donné.
- **BRIN** ne garde qu'un résumé par plage de blocs de table. Il convient à une colonne dont les valeurs suivent l'ordre physique des lignes, comme souvent un horodatage d'insertion.

Au-delà du type, la forme de l'index peut s'adapter à la question. Un index unique refuse les valeurs en double. Un index multicolonne couvre plusieurs colonnes, jusqu'à 32. Un index partiel a une clause `WHERE` et ne contient que les lignes qui la satisfont. Un index d'expression stocke une valeur calculée comme `lower(email)`. Une liste `INCLUDE` contient des colonnes supplémentaires sans les utiliser pour la recherche ; une requête peut ainsi recevoir sa réponse du seul index.

## Pourquoi c'est important

L'écart produit par un index grandit avec la table. Sur une petite table, le parcours coûte peu, et le planificateur le préfère souvent à raison : lire quelques pages dans l'ordre coûte moins que traverser un arbre pour les atteindre. Sur des dizaines de millions de lignes, l'écart entre parcours et recherche ciblée sépare une page qui se charge d'une page dont le chargement dépasse le délai d'attente. Une requête rapide au lancement peut devenir lente un an plus tard sans changement de code, simplement parce que sa table a grandi.

Les index ne sont pas gratuits ; le coût se paie lors des écritures. Chaque insertion ajoute une entrée à chaque index de la table. Seule une mise à jour de type heap-only tuple (HOT) peut éviter ces ajouts : la nouvelle version de la ligne reste sur la même page, et les entrées d'index existantes y mènent encore. PostgreSQL ne l'autorise que si la mise à jour ne touche aucune colonne utilisée par les index de la table, à l'exception des BRIN, qui ne portent qu'un résumé. Chaque index réduit donc les cas où cette optimisation est possible ; la documentation conseille de supprimer ceux qui servent rarement ou jamais. Une table ayant une douzaine d'index paie une douzaine de fois à chaque insertion.

Construire l'index a aussi un coût. Un `CREATE INDEX` ordinaire laisse les autres sessions lire la table, mais bloque leurs écritures jusqu'à la fin de la construction. La documentation prévient que sur une très grande table cela peut prendre de nombreuses heures.

Enfin, la valeur d'un index dépend de la décision du planificateur de l'utiliser, fondée sur les statistiques recueillies par `ANALYZE`. La question n'est pas « y a-t-il un index ? », mais « les requêtes importantes l'utilisent-elles ? ». `EXPLAIN` permet de le savoir.

## À quoi cela ressemble en pratique

La page de compte d'une boutique en ligne montre les vingt dernières commandes d'un client :

```sql
SELECT id, created_at, total
FROM   orders
WHERE  customer_id = 42
ORDER  BY created_at DESC
LIMIT  20;
```

Avec seulement une clé primaire sur `id`, PostgreSQL doit lire tout `orders`, garder les lignes du client, les trier, puis ne conserver que les vingt premières. `EXPLAIN` le montre : un `Seq Scan` sur `orders`, avec la condition comme `Filter` et un tri au-dessus. `EXPLAIN ANALYZE` exécute aussi la requête et affiche les nombres de lignes et durées réels à côté des estimations.

L'index adapté place la colonne d'égalité en premier et celle du tri en second :

```sql
CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at);
```

Relancez `EXPLAIN` : le parcours devient un parcours d'index utilisant `orders_customer_created_idx`, et `customer_id = 42` apparaît comme `Index Cond`, et non plus comme filtre. Le tri disparaît aussi. Un B-tree garde les entrées du client 42 ordonnées par `created_at` ; PostgreSQL peut les lire à rebours pour commencer par les plus récentes. Avec un `LIMIT`, il s'arrête après vingt entrées sans toucher au reste.

L'ordre des colonnes compte. Le même index sert une requête sur sa première colonne, `customer_id`, seule. Mais un rapport sur toutes les commandes d'hier, filtrant seulement `created_at`, en tire bien moins : dans un B-tree multicolonne, l'égalité sur les premières colonnes puis une plage sur la suivante réduisent la partie lue. PostgreSQL 18 sait faire un skip scan, répétant la recherche pour chaque valeur distincte de la première colonne. Le planificateur ne le choisit toutefois en général que lorsque la première colonne a peu de valeurs distinctes, et les identifiants clients en ont beaucoup. Si le rapport compte, il reçoit son index sur `created_at`. À l'inverse, `(created_at, customer_id)` disperserait les commandes du client 42 dans tout l'index.

La note sur
[l'optimisation de la base avant de la répartir](/fr/notes/optimise-before-you-shard/)
place les index en tête des corrections classées par rendement, une fois que `EXPLAIN (ANALYZE, BUFFERS)` a montré les parcours d'une requête très sollicitée. Son exemple est un index partiel, `units_unsold`, ne gardant que les unités encore à vendre : il rétrécit donc au fil de la vente au lieu de grossir. La note compte aussi le prix. Dans sa table, les 2 000 demandes de réservation modifiant `reserved`, `reserved_at` et `order_id` étaient toutes des mises à jour HOT. Parmi les 2 000 mises à jour de `sold`, colonne lue par l'index partiel, aucune ne l'était. Elle conseille de supprimer les index dont `idx_scan` dans `pg_stat_user_indexes` reste à zéro.

L'étude de cas sur
[un meilleur choix de clé](/fr/portfolio/quicker-answers-from-a-better-choice-of-key/)
montre pourquoi les valeurs entrant dans un B-tree comptent autant que les colonnes. Les UUID aléatoires dispersent les insertions dans l'index, ce qui peut le fragmenter. La plateforme a retenu comme choix standard des UUID v7 ordonnés dans le temps pour les clés primaires des entités, avec un horodatage dans les premiers bits pour transmettre cet ordre aux nouveaux identifiants. Réduire la fragmentation et accélérer les requêtes étaient des objectifs de conception. Le résultat consigné est la convention de clés configurée ; l'étude n'établit aucun benchmark de charge avant et après.

## Où les problèmes surviennent

**L'ajout d'index arrête les écritures.** Un `CREATE INDEX` ordinaire sur une table active bloque insertions, mises à jour et suppressions pendant toute sa construction ; un déploiement courant devient une panne. Utilisez `CREATE INDEX CONCURRENTLY` : il parcourt la table deux fois et attend les transactions susceptibles d'utiliser l'index. Cela prend nettement plus longtemps, mais les écritures continuent. Il ne peut s'exécuter dans un bloc de transaction ; un outil de migration qui enveloppe chaque étape dans une transaction doit donc lancer cette instruction seule. Une seule construction concurrente peut s'exécuter à la fois sur une table.

**Une construction concurrente échoue et laisse un index invalide.** Un interblocage, ou un doublon pendant la construction d'un index unique, fait échouer `CREATE INDEX CONCURRENTLY`. L'index commencé reste toutefois dans le catalogue, marqué invalide. Les requêtes l'ignorent, mais chaque écriture paie encore son entretien, et un index unique invalide peut continuer à imposer l'unicité. Le `\d` de psql affiche `INVALID` après sa définition. Supprimez-le et relancez la construction concurrente, ou reconstruisez-le avec `REINDEX INDEX CONCURRENTLY`.

**La requête cache la colonne dans une fonction.** Un index sur `email` ne sert pas à `WHERE lower(email) = 'ana@example.com'`, car il contient `email`, pas `lower(email)`. Indexez l'expression de la requête :

```sql
CREATE INDEX users_lower_email_idx ON users (lower(email));
```

L'expression se calcule à chaque insertion et chaque mise à jour non HOT. La documentation recommande donc ces index quand la vitesse de lecture compte plus que celle d'écriture.

**Les index inutilisés s'accumulent.** Chaque ajout « au cas où » coûte à chaque écriture et peut empêcher les mises à jour HOT. `pg_stat_user_indexes` compte les parcours commencés sur chaque index dans `idx_scan` et enregistre l'horodatage du dernier dans `last_idx_scan` :

```sql
SELECT relname, indexrelname, idx_scan, last_idx_scan
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0
ORDER  BY relname;
```

Vérifiez avant de supprimer. Un index derrière une clé primaire ou une contrainte unique fait son travail même si aucune requête ne le lit. Un compteur à zéro signifie peu sur un serveur qui vient de commencer à recevoir du trafic.

**Le planificateur ignore un bon index.** Après un chargement massif, les statistiques sont en retard sur les données, les estimations sont fausses et le parcours gagne sur le papier. Lancez `ANALYZE` ; la documentation qualifie d'« effort perdu » l'évaluation de l'usage des index sans lui. Testez aussi sur des volumes réalistes : sur cent lignes tenant sur une page disque, aucun index ne bat la lecture de cette page. Pour savoir si l'index est utilisable, définissez `enable_seqscan = off` dans une session de test, puis relancez `EXPLAIN`. Si le plan parcourt toujours la table, la condition ne correspond pas à l'index.

## Pour aller plus loin

- [Index : introduction](https://www.postgresql.org/docs/current/indexes-intro.html) — rôle, création et suppression d'un index, et surcoût de chaque écriture.
- [Types d'index](https://www.postgresql.org/docs/current/indexes-types.html) — B-tree, Hash, GiST, SP-GiST, GIN et BRIN, et les opérateurs auxquels chacun répond.
- [Index multicolonnes](https://www.postgresql.org/docs/current/indexes-multicolumn.html) — l'effet de l'ordre des colonnes sur la réduction de recherche dans un B-tree et les conditions du skip scan.
- [CREATE INDEX](https://www.postgresql.org/docs/current/sql-createindex.html) — la syntaxe complète, dont `CONCURRENTLY`, `INCLUDE`, les index partiels et la récupération après construction invalide.
- [Utiliser EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html) — lire un plan : types de parcours, coûts estimés et valeurs réelles d'`EXPLAIN ANALYZE`.
- [Examiner l'utilisation des index](https://www.postgresql.org/docs/current/indexes-examine.html) — les raisons de commencer par `ANALYZE` et les données réelles, et le test de l'utilisabilité d'un index.

<https://engineer.company/fr/glossary/database-index/>
