Ne comptez plus sur IF NOT EXISTS pour la création d’index simultanée dans PostgreSQL !

Technologie

En tant que développeur, vous avez probablement rencontré des situations où la création d’un index dans PostgreSQL échoue en raison de délais d’attente de verrouillage. Dans de tels cas, il peut être tentant d’utiliser la clause IF NOT EXISTS comme solution rapide. Cependant, cette méthode peut engendrer des problèmes subtils et difficiles à déboguer dans des environnements de production.

Comprendre la gestion de la création d’index concurrente par PostgreSQL

Lorsque nous lançons CREATE INDEX CONCURRENTLY, PostgreSQL commence par créer une entrée pour l’index dans les catalogues système (notamment dans pg_index) et la marque comme invalid. Cela se produit avant le début du processus de construction de l’index. Si un délai d’attente de verrouillage se produit pendant la construction de l’index (par exemple, si un processus de nettoyage est en cours), l’opération est annulée. Cependant, l’entrée de l’index dans pg_index peut déjà exister. PostgreSQL ne supprime pas automatiquement l’index partiellement créé lorsque l’opération est annulée en raison d’un délai d’attente de verrouillage. Cela est intentionnel, car cela permet une récupération potentielle ou une intervention manuelle. Lorsque vous essayez de créer à nouveau l’index, PostgreSQL détecte l’entrée existante dans pg_index et renvoie une erreur indiquant que l’index existe déjà, même s’il est dans un état invalide.

L’usage de IF NOT EXISTS

Dans ces situations, il est très tentant d’encapsuler vos créations d’index dans IF NOT EXISTS. Cependant, c’est ici que les problèmes commencent. Lorsque vous utilisez IF NOT EXISTS et relancez la création de votre index, la tâche peut se terminer silencieusement tout en laissant derrière elle un index invalide. Cela se produit parce que PostgreSQL ignore discrètement la création de l’index si un index existe déjà, même s’il est marqué comme invalide.

Pour aggraver les choses, si vous n’êtes pas attentif et utilisez un cadre comme Ruby on Rails ou un système similaire qui exécute des migrations de schéma, ces migrations peuvent être silencieusement marquées comme complètes, laissant derrière elles des index invalides. Vous ne découvrirez peut-être cela que plus tard, lorsque les performances des requêtes se dégradent, car PostgreSQL ne pourra pas utiliser un index invalide.

Démonstration rapide

Pour reproduire ce problème, vous pouvez utiliser le script SQL suivant :

-- Créer une table de test
CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);

-- Insérer des données d'exemple
INSERT INTO test_table (data)
SELECT 'Data ' || generate_series(1, 1000000);

-- Fonction pour simuler une requête longue
CREATE OR REPLACE FUNCTION simulate_long_query() RETURNS void AS $$
BEGIN
    PERFORM pg_sleep(30);  -- Dormir pendant 30 secondes
END;
$$ LANGUAGE plpgsql;

-- Définir un court délai d'attente de verrouillage
SET lock_timeout = '5s';

-- Dans la session 1 :
BEGIN;
SELECT simulate_long_query();

-- Dans la session 2 (exécutez ceci pendant que la session 1 est toujours en cours) :
CREATE INDEX CONCURRENTLY idx_test_data ON test_table (data);

-- Cela va probablement expirer en raison du verrou détenu par la session 1

-- Après le délai d'attente dans la session 2, essayez de créer l'index à nouveau avec IF NOT EXISTS :
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_test_data ON test_table (data);

-- Vérifiez l'état de l'index
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indisvalid = false;

Une meilleure approche : Supprimer et recréer

Au lieu de compter sur IF NOT EXISTS, une approche plus sûre consiste à supprimer tout index existant (valide ou invalide) puis à le recréer. Voici un exemple de méthode d’assistance pour une application Ruby on Rails, mais le concept s’applique également :

def safe_add_index_with_retry(table, columns, options = {})
    index_name = options[:name] || index_name(table, columns)
    add_index(table, columns, **options)
rescue ActiveRecord::LockWaitTimeout
    begin
        execute("DROP INDEX CONCURRENTLY IF EXISTS #{index_name}")
    rescue ActiveRecord::StatementInvalid => e
        Rails.logger.error("Échec de la suppression de l'index : #{e.message}")
        raise
    end

    retries = 0
    begin
        add_index(table, columns, **options)
    rescue ActiveRecord::LockWaitTimeout => e
        raise e unless retries < 5
        retries += 1
        sleep(2**retries)
        retry
    end
end

Cette méthode :

1. Tente de créer l'index.
2. Si cela échoue en raison d'un délai d'attente de verrouillage, elle essaie de supprimer tout index existant.
3. Ensuite, elle réessaie la création de l'index avec un délai exponentiel.

Bon codage !