Index concurrents (CREATE INDEX CONCURRENTLY)
Créez des index sans bloquer les écritures avec CREATE INDEX CONCURRENTLY et reprenez les créations interrompues.
Index concurrents (CREATE INDEX CONCURRENTLY) est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 2 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.
Le problème de CREATE INDEX
Un CREATE INDEX classique prend un verrou SHARE — il bloque les écritures (mais pas les lectures) pendant toute la construction. Sur une table d’un milliard de lignes, cela représente plusieurs minutes d’interruption des écritures.
CONCURRENTLY : la solution
CREATE INDEX CONCURRENTLY construit l’index sans bloquer les écritures :
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);Fonctionnement
Deux parcours de la table :
- Premier parcours : construire l’index à partir des lignes actuelles
- Second parcours : prendre en compte les lignes modifiées pendant le premier parcours
Il faut également attendre brièvement toute transaction concurrente ayant commencé avant la construction.
Impossible dans une transaction
CONCURRENTLY valide deux fois les changements — il ne peut donc pas être exécuté dans un bloc BEGIN/COMMIT explicite :
BEGIN;
CREATE INDEX CONCURRENTLY ...; -- ERROR
COMMIT;Lent, mais en ligne
CONCURRENTLY prend plus de temps qu’un CREATE INDEX classique (deux parcours et attentes de verrouillage). L’avantage : aucune interruption de service.
Récupération après un échec
Si CONCURRENTLY échoue (interruption, erreur système), l’index reste dans un état INVALID :
SELECT relname FROM pg_class
WHERE relkind = 'i' AND NOT pg_index.indisvalid;
-- Fix: drop and re-create:
DROP INDEX CONCURRENTLY my_idx;
CREATE INDEX CONCURRENTLY my_idx ON ...;REINDEX CONCURRENTLY
Reconstruire sans bloquer, depuis PG 12 :
REINDEX INDEX CONCURRENTLY orders_user_id_idx;
REINDEX TABLE CONCURRENTLY orders;DROP INDEX CONCURRENTLY
Également sans blocage :
DROP INDEX CONCURRENTLY old_idx;Index uniques
Les index UNIQUE peuvent également être construits avec CONCURRENTLY. C’est utile pour ajouter des contraintes UNIQUE en ligne :
CREATE UNIQUE INDEX CONCURRENTLY users_email_uidx ON users(email);
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_uidx;Index de clés étrangères
Modèle standard pour ajouter une FK à une grande table :
-- 1. Build the index online:
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders(user_id);
-- 2. Add the FK with NOT VALID (instant):
ALTER TABLE orders ADD CONSTRAINT orders_user_fk
FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
-- 3. Validate in the background:
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;Coût de la construction d’un index concurrent
Deux parcours complets de la table, plus la gestion des informations intermédiaires. Cela consomme du CPU et des entrées-sorties. Dans la mesure du possible, exécutez cette opération pendant les périodes de faible trafic.
Surveiller la progression
PG 12+ expose la progression dans pg_stat_progress_create_index :
SELECT phase,
blocks_done, blocks_total,
tuples_done, tuples_total
FROM pg_stat_progress_create_index;Récapitulatif
CONCURRENTLY est la méthode adaptée à la production pour gérer les index.
- CREATE/DROP/REINDEX CONCURRENTLY ne bloquent pas les écritures
- Impossible de les exécuter dans une transaction
- Les échecs laissent des index INVALID — supprimez-les
- Surveillez la progression avec pg_stat_progress_create_index
Vérification rapide
Pourquoi CREATE INDEX CONCURRENTLY ne peut-il pas être exécuté dans un bloc BEGIN/COMMIT explicite ?
Questions Fréquemment Posées
La leçon « Index concurrents (CREATE INDEX CONCURRENTLY) » est-elle gratuite ?
Oui — le texte complet de « Index concurrents (CREATE INDEX CONCURRENTLY) » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.
Qu'est-ce que j'apprendrai dans « Index concurrents (CREATE INDEX CONCURRENTLY) » ?
Créez des index sans bloquer les écritures avec CREATE INDEX CONCURRENTLY et reprenez les créations interrompues. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.
Dois-je avoir de l'expérience pour commencer SQL Academy ?
Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 2 sur 4.
Combien de temps prend la leçon « Index concurrents (CREATE INDEX CONCURRENTLY) » ?
La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.
Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?
Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.
Toutes les leçons de ce cours
- Migrations en ligne : pourquoi ALTER TABLE verrouille
- Index concurrents (CREATE INDEX CONCURRENTLY)
- Renommer des colonnes sans interruption
- Outils : Flyway, Liquibase, Sqitch