Création d’index ( CREATE INDEX) et paramètres d’indexation ( UNIQUE, CONCURRENTLY)

On a déjà parlé plusieurs fois du fait que les index accélèrent les sélections et évitent à la base de tout parcourir à chaque fois. Maintenant, il est temps de voir comment ils sont créés, quels paramètres on peut utiliser avec la commande CREATE INDEX et dans quels cas il vaut mieux utiliser des options comme UNIQUE ou CONCURRENTLY. Tout ça est super important si tu veux pas juste utiliser les index, mais vraiment les gérer comme un pro.

Syntaxe de CREATE INDEX

Pour créer un index, tu utilises la commande CREATE INDEX. Voilà la syntaxe de base :

CREATE INDEX index_name
ON table_name (column_name);

• index_name — Le nom de l’index. C’est mieux si ça reflète à quoi sert l’index, par exemple idx_users_email pour un index sur la colonne email dans la table users.
• table_name — Le nom de la table pour laquelle tu crées l’index.
• column_name — La colonne qui va être indexée.

Un exemple simple. Disons qu’on a une table users :

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    age INT
);

On veut accélérer la recherche des utilisateurs par le champ email. On crée l’index :

CREATE INDEX idx_users_email
ON users (email);

Maintenant, quand tu fais des requêtes du genre :

SELECT * FROM users WHERE email = 'example@example.com';

PostgreSQL va utiliser l’index idx_users_email pour trouver la ligne qu’il te faut super vite.

Index uniques (UNIQUE)

Un index unique, c’est la garantie que les valeurs dans la colonne (ou les colonnes) indiquée(s) seront toutes différentes. Si tu essaies d’insérer une valeur déjà présente, PostgreSQL va te bloquer.

Les index uniques sont souvent utilisés pour des clés comme email, username ou d’autres identifiants qui doivent pas se répéter.

Syntaxe pour créer un index unique

La syntaxe pour créer un index unique ressemble beaucoup à celle d’un index normal, sauf qu’on ajoute le mot-clé UNIQUE :

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

Imaginons que dans notre table users, le champ email doit être unique, pour éviter deux utilisateurs avec la même adresse. Voilà comment on fait :

CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);

Maintenant, si tu essaies de faire, par exemple :

INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 30);
INSERT INTO users (name, email, age) VALUES ('Jane', 'john@example.com', 25);

PostgreSQL va te sortir une erreur, parce que email doit être unique.

Création d’index avec le paramètre CONCURRENTLY

Imagine que tu bosses avec une énorme table en prod, sur laquelle il y a tout le temps des opérations (genre des insertions de nouvelles données). Créer un index en mode standard (CREATE INDEX) bloque cette table, donc plus personne ne peut insérer, mettre à jour ou supprimer des données. C’est la cata pour un système en production. Pour éviter ça, tu peux créer un index "en asynchrone" avec le paramètre CONCURRENTLY.

Syntaxe

CREATE INDEX CONCURRENTLY index_name
ON table_name (column_name);

Le mot-clé CONCURRENTLY dit à PostgreSQL de créer l’index en parallèle, sans bloquer la table.

Imaginons qu’on a une table orders avec des millions de lignes et qu’elle se remplit tout le temps avec de nouvelles commandes :

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL
);

Tu veux créer un index pour accélérer la recherche par order_date, mais sans bloquer la table :

CREATE INDEX CONCURRENTLY idx_orders_order_date
ON orders (order_date);

Maintenant la base va créer l’index sans bloquer la table, et tes utilisateurs ne verront même pas la différence.

Parmi les particularités de CONCURRENTLY :

1. L’index est créé plus lentement qu’en mode normal, parce que PostgreSQL fait ça en plusieurs étapes.
2. Si l’index est créé avec des erreurs (genre à cause de doublons), il faudra le supprimer à la main et le recréer.

Paramètres d’indexation supplémentaires

PostgreSQL permet d’ajouter des paramètres en plus quand tu crées des index. Par exemple, tu peux indexer plusieurs colonnes en même temps. C’est utile si tu fais souvent des requêtes avec des filtres sur plusieurs champs.

CREATE INDEX idx_users_name_email
ON users (name, email);

Maintenant, les requêtes avec WHERE name = 'John' AND email = 'john@example.com' seront plus rapides.

Deux index sur une colonne chacun, ce n’est pas pareil qu’un index sur deux colonnes ! Un index multi-colonnes accélère surtout les recherches où le WHERE utilise toutes ces colonnes.

Exemples d’erreurs et comment les résoudre

Quand tu crées des index, tu peux tomber sur plusieurs types d’erreurs. Voilà les plus courantes :

Erreur d’insertion de doublons lors de la création d’un index unique. Si la table a déjà des lignes en double, PostgreSQL ne pourra pas créer l’index unique. Dans ce cas, il faut d’abord supprimer ou corriger les doublons.

DELETE FROM users
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(email) > 1
);

Erreur de verrouillage lors de la création d’index. Si tu utilises la création d’index normale sur une base en prod, les clients peuvent voir des ralentissements ou des plantages. Utilise le paramètre CONCURRENTLY pour éviter ce souci.

Imagine maintenant que tu bosses dans une boîte et qu’on te confie l’optimisation d’une base avec des millions de lignes. Tu peux utiliser les index pour trouver les goulots d’étranglement et booster l’expérience utilisateur. Par exemple, en ajoutant le bon index, tu peux passer d’une requête qui prend 10 secondes à quelques millisecondes. Avoue que c’est stylé, non ?