Post-mortem : un CREATE INDEX CONCURRENTLY qui a rempli le disque en production

Le contexte

Une table de consentement comptant environ 16 millions de lignes avait besoin d'un nouvel index pour accélérer des requêtes de plus en plus lentes en production. Pour éviter un verrou bloquant sur la table le temps de la création, le choix naturel s'est porté sur :

Sql

CREATE INDEX CONCURRENTLY idx_consentement_xxx ON consentement (colonne);

CONCURRENTLY évite de verrouiller les écritures pendant la création de l'index — en théorie, l'option la plus sûre en production.

Ce qui s'est mal passé

Sur une table de cette taille, la construction de l'index a consommé un volume important d'espace disque temporaire, jusqu'à saturer l'espace disponible sur le serveur. PostgreSQL a alors commencé à rejeter des écritures, faute d'espace.

Diagnostic

Premiers réflexes en cas de disque saturé sur PostgreSQL :

Sql

SELECT pg_size_pretty(pg_database_size('nom_db'));

Puis identification des objets volumineux et des index en cours de construction :

Sql

SELECT * FROM pg_stat_progress_create_index;

Cette vue système permet de voir en temps réel l'avancement d'un CREATE INDEX CONCURRENTLY et la phase en cours (scan, tri, construction), ce qui aide à comprendre si l'opération est en train de progresser ou bloquée.

La leçon principale

Sur une table volumineuse, CREATE INDEX CONCURRENTLY peut nécessiter un espace disque temporaire conséquent, potentiellement du même ordre de grandeur que la table elle-même selon la nature des données et du tri nécessaire. Ce n'est pas quelque chose d'intuitif quand on ne l'a pas déjà vécu.

Comment l'éviter la prochaine fois

Vérifier l'espace disque disponible avant toute opération de ce type sur une grosse table, avec une marge confortable
Surveiller pg_stat_progress_create_index pendant l'exécution plutôt que de lancer la commande et attendre passivement
Planifier sur une fenêtre de faible charge, même avec CONCURRENTLY, pour limiter les effets de bord
Avoir une alerte de supervision sur l'espace disque restant, pas seulement sur la charge CPU/mémoire

En résumé

CONCURRENTLY protège contre les verrous bloquants, pas contre la consommation de ressources. Sur des tables de plusieurs millions de lignes, l'espace disque temporaire mérite d'être vérifié en amont au même titre que n'importe quelle autre opération à fort volume.