Modifications d'index sans interruption dans PostgreSQL : guide pratique

Pourquoi les modifications d'index provoquent des interruptions (et comment l'éviter)

Le travail sur les index semble anodin. Vous ajoutez "juste" une structure d'aide. Dans PostgreSQL, cependant, créer, supprimer ou remplacer un index peut nécessiter des verrous qui bloquent d'autres sessions. Si la table est sollicitée, ces attentes s'accumulent et l'application commence à sembler cassée.

Les interruptions ne ressemblent rarement à une notification de panne propre. Elles apparaissent souvent sous forme de pages qui se bloquent, de jobs en arrière-plan qui prennent du retard, et d'une file de requêtes qui attendent la base. Quelqu'un clique sur "Recherche" et obtient un timeout, tandis que les outils de support et les écrans d'administration semblent soudainement lents parce que des requêtes simples ne peuvent pas obtenir le verrou nécessaire.

« Lancez-le la nuit » échoue pour deux raisons courantes. Beaucoup de systèmes ne sont jamais vraiment calmes (utilisateurs partout dans le monde, jobs batch, ETL, sauvegardes). Et les opérations d'index peuvent prendre plus de temps que prévu parce qu'elles lisent beaucoup de données et concurrencent le CPU et le disque. Si la fenêtre se ferme au milieu de la construction, vous devez choisir entre attendre plus longtemps ou interrompre le travail.

Les modifications d'index sans interruption ne sont pas magiques. Elles reposent sur le choix de l'opération la moins bloquante, la mise en place de garde-fous (timeouts et vérifications disque) et la surveillance de la base pendant l'exécution.

Ce playbook se concentre sur des habitudes pratiques en production :

• Privilégier les constructions d'index concurrentes lorsque les lectures et écritures doivent continuer.
• Surveiller les verrous et la progression pour pouvoir réagir rapidement.
• Avoir un plan de rollback si le changement provoque des régressions ou prend trop de temps.

Ce qu'il ne couvre pas : la théorie profonde de conception d'index, l'optimisation large des requêtes, ou les refontes de schéma qui réécrivent beaucoup de données.

Le modèle simple de verrous derrière le travail d'index

PostgreSQL utilise des verrous pour garantir la cohérence lorsque de nombreuses sessions touchent la même table. Un verrou est simplement une règle qui dit qui peut lire ou écrire un objet maintenant, et qui doit attendre.

La plupart du temps, vous ne remarquez pas les verrous car PostgreSQL peut utiliser des modes légers qui permettent aux requêtes normales de s'exécuter. Le DDL est différent. Lorsque vous créez ou supprimez un index, PostgreSQL a besoin d'un contrôle suffisant sur la table pour garder le catalogue et les données cohérents. Plus il a besoin de contrôle, plus d'autres sessions peuvent être forcées d'attendre.

Construire un index vs utiliser un index

Utiliser un index est généralement peu coûteux en termes de verrous. Les requêtes SELECT, UPDATE et DELETE peuvent lire ou maintenir des index pendant que d'autres sessions font de même.

Construire un index est différent. PostgreSQL doit scanner la table, trier ou hasher des clés, et écrire une nouvelle structure sur le disque. Ce travail prend du temps, et le temps est ce qui transforme des "petits verrous" en "gros problèmes" en production.

Ce que CONCURRENTLY change (et ce qu'il ne change pas)

Un CREATE INDEX classique prend un verrou fort qui bloque les écritures pendant toute la durée. CREATE INDEX CONCURRENTLY est conçu pour laisser les lectures et écritures normales continuer pendant la construction de l'index.

Mais "concurrent" ne veut pas dire "sans verrou". Vous avez toujours de courtes fenêtres de verrou au début et à la fin, et la construction peut échouer ou attendre si quelque chose d'autre détient des verrous incompatibles.

Les résultats qui comptent le plus :

• Les constructions non concurrentes peuvent bloquer les inserts, updates et deletes sur la table.
• Les constructions concurrentes permettent généralement lectures et écritures, mais peuvent être ralenties ou bloquées par des transactions longues.
• Les étapes de finition nécessitent quand même de brefs verrous, donc les systèmes très sollicités peuvent voir de courtes attentes.

Choisir la bonne approche : concurrente ou non

Vous avez deux options principales lors d'un changement d'index : construire l'index normalement (rapide, mais bloquant), ou le construire avec CONCURRENTLY (généralement non bloquant pour le trafic applicatif, mais plus lent et plus sensible aux transactions longues).

Quand CONCURRENTLY est le bon choix

Utilisez CREATE INDEX CONCURRENTLY lorsque la table sert du trafic réel et que vous ne pouvez pas suspendre les écritures. C'est généralement le choix le plus sûr quand :

• La table est assez grosse pour qu'une construction normale puisse prendre des minutes ou des heures.
• La table reçoit des écritures régulières, pas seulement des lectures.
• Vous ne pouvez pas planifier une vraie fenêtre de maintenance.
• Vous devez construire d'abord, vérifier, puis supprimer l'ancien index plus tard.

Quand une construction normale est acceptable

Un CREATE INDEX classique peut convenir lorsque la table est petite, le trafic faible, ou si vous avez une fenêtre contrôlée. Il finit souvent plus vite et est plus simple à exécuter.

Considérez l'approche normale si la construction est constamment rapide en staging et que vous pouvez arrêter temporairement les écritures (même brièvement).

Si vous avez besoin d'unicité, décidez tôt. CREATE UNIQUE INDEX CONCURRENTLY fonctionne, mais échouera si des doublons existent. Dans beaucoup de systèmes de production, trouver et corriger les doublons est le vrai projet.

Vérifications préalables avant de toucher la production

La plupart des problèmes arrivent avant même que la commande ne démarre. Quelques vérifications vous aident à éviter deux grandes surprises : un blocage inattendu et une construction d'index qui dure bien plus longtemps (ou consomme plus d'espace) que prévu.

1. Assurez-vous de ne pas être dans une transaction. CREATE INDEX CONCURRENTLY échouera si vous l'exécutez après un BEGIN, et certains outils GUI encapsulent silencieusement les commandes dans une transaction. Si vous doutez, ouvrez une nouvelle session et lancez uniquement la commande d'index là.

2. Fixez des attentes pour le temps et le disque. Les constructions concurrentes prennent généralement plus de temps que les constructions normales et nécessitent de l'espace de travail supplémentaire pendant l'exécution. Prévoyez l'espace pour le nouvel index plus l'overhead temporaire et confirmez que vous avez une marge confortable de disque libre.

3. Définissez des timeouts qui correspondent à votre objectif. Vous voulez que la construction échoue rapidement si elle ne peut pas obtenir un verrou, mais vous ne voulez pas que la session meure en plein milieu de la construction à cause d'un statement_timeout agressif.

4. Capturez une baseline. Vous voulez une preuve que le changement a aidé et un moyen rapide de détecter des régressions. Enregistrez un snapshot avant : temps des requêtes lentes, un EXPLAIN (ANALYZE, BUFFERS) représentatif, et un aperçu rapide du CPU, IO, connexions et espace disque libre.

Paramètres de session sûrs que beaucoup d'équipes utilisent comme point de départ (ajustez selon vos règles) :

-- Run in the same session that will build the index
SET lock_timeout = '2s';
SET statement_timeout = '0';

Étape par étape : créer un index avec CONCURRENTLY

Utilisez CREATE INDEX CONCURRENTLY quand vous avez besoin que le trafic applicatif continue et que vous pouvez tolérer une durée de construction plus longue.

D'abord, décidez exactement ce que vous construisez :

• Soyez précis sur l'ordre des colonnes (ça compte).
• Envisagez si un index partiel suffit. Si la plupart des requêtes filtrent sur des lignes "actives", un index partiel peut être plus petit, plus rapide et moins coûteux à maintenir.

Un déroulé sûr ressemble à ceci : notez l'objectif et le nom de l'index, exécutez la construction en dehors de tout bloc transactionnel, surveillez jusqu'à la fin, puis vérifiez que le planner peut l'utiliser avant de supprimer quoi que ce soit.

-- Example: speed up searches by email for active users
CREATE INDEX CONCURRENTLY idx_users_active_email
ON public.users (email)
WHERE status = 'active';

-- Validate it exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Check the plan can use it
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM public.users
WHERE status = 'active' AND email = '[email protected]';

Pour les notes de progression (utile pour les audits), enregistrez l'heure de début, l'heure de fin et les attentes observées. Pendant l'exécution, vous pouvez interroger pg_stat_progress_create_index depuis une autre session.

La validation n'est pas simplement « l'index existe ». Confirmez que le planner peut le choisir, puis surveillez le temps réel des requêtes après le déploiement. Si le nouvel index n'est pas utilisé, ne vous précipitez pas pour supprimer l'ancien. Corrigez la requête ou la définition de l'index d'abord.

Étape par étape : remplacer ou supprimer des index sans blocage

Le schéma le plus sûr est d'abord d'ajouter, laisser le trafic bénéficier du nouvel index, puis seulement ensuite supprimer l'ancien. De cette façon, vous conservez un repli fonctionnel.

Remplacer un ancien index par un nouveau (ordre sûr)

1. Créez le nouvel index avec CREATE INDEX CONCURRENTLY.

2. Vérifiez qu'il est utilisé. Contrôlez EXPLAIN sur les requêtes lentes qui vous importent et surveillez l'usage des index dans le temps.

3. Ce n'est qu'après cela que vous supprimez l'ancien index concurremment. Si le risque est élevé, conservez les deux indexes pour un cycle business complet avant de supprimer quoi que ce soit.

Supprimer des index : quand CONCURRENTLY marche (et quand ce n'est pas le cas)

Pour un index normal que vous avez créé vous-même, DROP INDEX CONCURRENTLY est généralement le bon choix. Deux points d'attention : il ne peut pas s'exécuter à l'intérieur d'un bloc de transaction, et il nécessite toujours de brefs verrous au début et à la fin, donc il peut être retardé par des transactions longues.

Si l'index existe à cause d'une contrainte PRIMARY KEY ou UNIQUE, vous ne pouvez généralement pas le supprimer directement. Vous devez modifier la contrainte avec ALTER TABLE, ce qui peut prendre des verrous plus forts. Traitez cela comme une opération de maintenance planifiée séparée.

Renommer des index pour la clarté

Renommer (ALTER INDEX ... RENAME TO ...) est généralement rapide, mais évitez-le si des outils ou des migrations font référence aux noms d'index. Une habitude plus sûre est de choisir un nom clair dès le départ.

Si l'ancien index est encore nécessaire

Parfois, deux patterns de requêtes nécessitent deux index différents. Si des requêtes importantes dépendent encore de l'ancien index, conservez-le. Envisagez d'ajuster le nouveau (ordre des colonnes, condition partielle) plutôt que de forcer une suppression.

Surveiller les verrous et la progression pendant la construction

Même avec CREATE INDEX CONCURRENTLY, vous devez surveiller en temps réel. La plupart des incidents surprises viennent d'une des deux choses : une session bloquante à laquelle vous n'avez pas prêté attention, ou une transaction longue qui bloque la construction.

Repérer les sessions bloquées (qui bloque qui)

Commencez par trouver les sessions en attente de verrous :

SELECT
  a.pid,
  a.usename,
  a.application_name,
  a.state,
  a.wait_event_type,
  a.wait_event,
  now() - a.xact_start AS xact_age,
  left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY xact_age DESC;

Si vous avez besoin du bloqueur exact, suivez blocked_pid vers blocking_pid :

SELECT
  blocked.pid  AS blocked_pid,
  blocking.pid AS blocking_pid,
  now() - blocked.xact_start AS blocked_xact_age,
  left(blocked.query, 80)  AS blocked_query,
  left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Surveiller la progression et les signaux de « blocage »

PostgreSQL expose la progression de la construction d'index. Si vous ne voyez aucun mouvement pendant longtemps, cherchez une transaction longue (souvent une session idle qui garde un vieux snapshot).

SELECT
  pid,
  phase,
  lockers_total,
  lockers_done,
  blocks_total,
  blocks_done,
  tuples_total,
  tuples_done
FROM pg_stat_progress_create_index;

Surveillez aussi la pression système : IO disque, latence de réplication et augmentation des temps de requêtes. Les constructions concurrentes préservent mieux l'uptime, mais elles lisent beaucoup de données.

Règles simples qui fonctionnent bien en production :

• Attendez si la progression avance et que l'impact utilisateur est faible.
• Annulez et replanifiez si la construction est bloquée par une transaction longue que vous ne pouvez pas terminer en toute sécurité.
• Faites une pause pendant les pics de trafic si les IO nuisent aux requêtes clients.
• Terminez seulement en dernier recours, et seulement après avoir confirmé ce que fait la session.

Pour la communication d'équipe, gardez les mises à jour courtes : heure de début, phase actuelle, ce qui est bloqué (si pertinent) et quand vous revérifierez.

Plan de rollback : comment revenir en arrière en toute sécurité

Les changements d'index restent à faible risque si vous planifiez la sortie avant de commencer. Le rollback le plus sûr n'est souvent pas un undo spectaculaire. C'est simplement d'arrêter la nouvelle construction et de garder l'ancien index en place.

Façons communes dont le travail d'index échoue

La plupart des échecs en production sont prévisibles : la construction atteint un timeout, quelqu'un l'annule pendant un incident, le serveur manque d'espace disque, ou la construction concurrence suffisamment le trafic normal pour provoquer une hausse de latence côté utilisateur.

Avec CREATE INDEX CONCURRENTLY, annuler est généralement sans danger pour l'application car les requêtes continuent de fonctionner. Le compromis est le nettoyage : une construction concurrente annulée ou échouée peut laisser un index invalide.

Règles sûres d'annulation et de nettoyage

Annuler une construction concurrente ne se comporte pas comme le rollback d'une transaction normale. PostgreSQL peut laisser un index qui existe mais n'est pas valide pour le planner.

-- Cancel the session building the index (use the PID you identified)
SELECT pg_cancel_backend(\u003cpid\u003e);

-- If the index exists but is invalid, remove it without blocking writes
DROP INDEX CONCURRENTLY IF EXISTS your_index_name;

Avant de supprimer, confirmez ce que vous regardez :

SELECT
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'your_index_name';

Si indisvalid = false, il n'est pas utilisé et peut être supprimé en sécurité.

Checklist pratique de rollback lors du remplacement d'un index existant :

• Gardez l'ancien index jusqu'à ce que le nouveau soit complètement construit et valide.
• Si la nouvelle construction échoue ou est annulée, supprimez l'index invalide concurremment.
• Si vous avez déjà supprimé l'ancien index, recréez-le avec CREATE INDEX CONCURRENTLY pour restaurer l'état précédent.
• Si la pression disque a causé l'échec, libérez d'abord de l'espace, puis réessayez.
• Si des timeouts ont causé l'échec, planifiez une fenêtre plus calme plutôt que de forcer l'opération.

Exemple : vous lancez un nouvel index pour une recherche d'administration, il tourne pendant 20 minutes, puis des alertes disque se déclenchent. Annulez la construction, supprimez l'index invalide concurremment et conservez l'ancien index pour le trafic. Vous pouvez réessayer après libération d'espace, sans outage visible par les utilisateurs.

Erreurs courantes qui provoquent des interruptions surprises

La plupart des pannes autour des index ne viennent pas de PostgreSQL qui serait "lent". Elles surviennent parce qu'un petit détail transforme un changement sûr en opération bloquante.

1) Mettre une construction concurrente dans une transaction

CREATE INDEX CONCURRENTLY ne peut pas s'exécuter dans un bloc transactionnel. De nombreux outils de migration encapsulent par défaut chaque changement dans une seule transaction. Le résultat est soit une erreur nette (meilleur cas), soit un déploiement chaotique avec des retries.

Avant de lancer la migration, confirmez que votre outil peut exécuter une instruction hors d'une transaction externe, ou séparez la migration en une étape non transactionnelle spéciale.

2) Lancer pendant le pic de trafic

Les constructions d'index concurrentes réduisent les blocages, mais elles ajoutent quand même de la charge : lectures supplémentaires, écritures supplémentaires et plus de pression sur autovacuum. Lancer la construction pendant une fenêtre de déploiement où le trafic monte est une façon courante de créer un ralentissement qui ressemble à une panne.

Choisissez une période calme et traitez-la comme une maintenance de production normale.

3) Ignorer les transactions longues

Une seule transaction longue peut retenir la phase de nettoyage d'une construction concurrente. L'index peut sembler progresser, puis stagner près de la fin en attendant que les vieux snapshots disparaissent.

Adoptez l'habitude : vérifiez les transactions longues avant de démarrer, et à nouveau si la progression stagne.

4) Supprimer la mauvaise chose (ou casser une contrainte)

Les équipes suppriment parfois un index par nom de tête, ou retirent un index qui assure une contrainte d'unicité. Si vous supprimez le mauvais objet, vous pouvez perdre l'application d'une contrainte (unique) ou provoquer une régression de performance instantanée.

Checklist de sécurité rapide : vérifiez le nom de l'index dans le catalogue, confirmez s'il soutient une contrainte, revérifiez le schéma et la table, et séparez clairement "créer le nouveau" et "supprimer l'ancien". Ayez la commande de rollback prête avant de commencer.

Exemple réaliste : accélérer une recherche d'administration

Un point de douleur courant est une recherche d'administration qui semble instantanée en staging mais lente en production. Supposons une grande table tickets (dizaines de millions de lignes) derrière un panneau interne, et que les agents recherchent souvent "tickets ouverts pour un client, du plus récent au plus ancien."

La requête ressemble à ceci :

SELECT id, customer_id, subject, created_at
FROM tickets
WHERE customer_id = $1
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

Un index complet sur (customer_id, status, created_at) aide, mais il ajoute un overhead d'écriture pour chaque mise à jour de ticket, y compris les tickets fermés. Si la plupart des lignes ne sont pas open, un index partiel est souvent une meilleure option :

CREATE INDEX CONCURRENTLY tickets_open_by_customer_created_idx
ON tickets (customer_id, created_at DESC)
WHERE status = 'open';

Un calendrier sûr en production :

• Préflight : confirmez que la forme de la requête est stable et que la table a suffisamment d'espace disque libre pour une nouvelle construction d'index.
• Construction : exécutez CREATE INDEX CONCURRENTLY dans une session séparée avec des timeouts clairs.
• Validation : lancez ANALYZE tickets; et confirmez que le planner utilise le nouvel index.
• Nettoyage : une fois confiant, supprimez tout index plus ancien et redondant avec DROP INDEX CONCURRENTLY.

Ce à quoi ressemble le succès :

• La recherche admin passe de secondes à quelques dizaines de millisecondes pour la plupart des clients.
• Les lectures et écritures régulières continuent de fonctionner pendant la construction.
• Le CPU et les IO disque augmentent pendant la construction mais restent dans vos limites de sécurité normales.
• Vous pouvez montrer des chiffres avant/après clairs : temps de requête, lignes scannées et historique des verrous.

Checklist rapide et étapes suivantes

Le travail sur les index est plus sûr lorsque vous le traitez comme une petite release : préparez-vous, surveillez pendant l'exécution, puis vérifiez le résultat avant de nettoyer.

Avant de commencer :

• Définissez des timeouts pour qu'un verrou surprise ne reste pas bloqué indéfiniment.
• Confirmez suffisamment d'espace disque libre pour la nouvelle construction.
• Cherchez les transactions longues qui pourraient ralentir la construction.
• Choisissez une fenêtre de faible trafic et définissez ce qu'est "terminé".
• Rédigez votre plan de rollback maintenant.

Pendant l'exécution :

• Surveillez les blocages et les files d'attente de verrous.
• Suivez la progression avec pg_stat_progress_create_index.
• Surveillez les symptômes applicatifs : taux d'erreur, timeouts et endpoints lents liés à la table.
• Soyez prêt à annuler si les waits de verrou augmentent ou si les timeouts côté utilisateur grimpent.
• Consignez ce qui s'est passé : heure de début, heure de fin et alertes éventuelles.

Après la fin, confirmez que l'index est valide, exécutez les une ou deux requêtes clés pour constater l'amélioration de plan et de temps, puis seulement alors supprimez les anciens indexes de façon non bloquante.

Si vous faites cela souvent, transformez-le en étape de livraison reproductible : un petit runbook, une répétition en staging avec des données proches de la production, et un responsable clair qui surveille la construction.

Si vous construisez des outils internes ou des panneaux d'administration avec AppMaster (appmaster.io), il est utile de traiter les changements de base de données comme les constructions d'index : partie intégrante de la checklist de release de votre backend — mesurée, surveillée et avec un rollback exécutable rapidement.