Lors de la mise en œuvre d'un modèle de suppression douce utilisant un indicateur booléen, pourquoi l'ajout de cet indicateur à un index composite dégrade-t-il parfois les performances des requêtes de type Active Record par rapport à une stratégie d'index partiel, et comment l'estimation de sélectivité du planificateur de requêtes diffère-t-elle entre ces approches ?

Réponse à la question

Historique de la question

Les modèles de suppression douce ont émergé comme une alternative à la suppression dure pour des raisons de traçabilité et de récupération des données. Les premières implémentations utilisaient des indicateurs booléens simples ou des colonnes de date/heure, mais les développeurs ont rapidement rencontré une dégradation des performances lorsque ces indicateurs étaient intégrés dans des index B-tree standards. Le problème est devenu manifeste avec l'adoption généralisée des index partiels de PostgreSQL et des index filtrés de SQL Server au milieu des années 2000, qui permettaient d'indexer uniquement les enregistrements actifs. Comprendre l'estimation de sélectivité—comment le planificateur de requêtes prédit le pourcentage de lignes correspondant à une condition—est devenu crucial lors de la comparaison entre les index composites complets et les stratégies d'indexation partielle.

Le problème

Lorsqu'un indicateur de suppression douce (par exemple, is_deleted) est ajouté à un index composite tel que (is_deleted, user_id, created_at), l'optimiseur de la base de données peut mal calculer la sélectivité des lignes pour les requêtes filtrant WHERE is_deleted = false. Si 90 % des lignes sont actives, l'optimiseur peut opter pour un scan séquentiel plutôt qu'un scan d'index, ou inversement, si la distribution est déséquilibrée, il peut de manière inappropriée favoriser l'index. Les index partiels (WHERE is_deleted = false) ne stockent que les lignes actives, garantissant une haute sélectivité, mais les index composites standards stockent toutes les lignes, entraînant une inflation de l'index et des estimations de cardinalité ambiguës lorsque les statistiques ne reflètent pas avec précision la distribution de la suppression douce.

La solution

Implémentez des index partiels (dans PostgreSQL) ou des index filtrés (dans SQL Server) qui excluent entièrement les lignes supprimées, associés à des index séparés pour les données supprimées si nécessaire. Pour MySQL ou des bases de données manquant d'indexation partielle, utilisez un index composite avec l'indicateur de suppression douce comme colonne principale uniquement si l'ensemble de données actif est petit ; sinon, partitionnez la table par statut de suppression. Analysez explicitement les statistiques de la table après des suppressions en masse pour éviter des histogrammes obsolètes. Lors de la requête des enregistrements actifs, utilisez le prédicat exact de la définition de l'index partiel (WHERE is_deleted = false) pour garantir que l'optimiseur reconnaisse l'applicabilité de l'index.

Exemple de code

-- PostgreSQL : Index partiel pour les enregistrements actifs uniquement
CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE is_deleted = false;

-- SQL Server : Index filtré équivalent
CREATE INDEX IX_Active_Users_Email 
ON Users(Email) 
WHERE IsDeleted = 0;

-- Requête qui utilise l'index partiel
SELECT * FROM users 
WHERE email = 'alice@example.com' 
AND is_deleted = false;

Situation de la vie quotidienne

Une plateforme SaaS gérant 10 millions de dossiers d'utilisateurs a connu de graves ralentissements sur son tableau de bord administratif lors du filtrage des utilisateurs actifs par date de création. Au départ, ils utilisaient un index composite (is_deleted, created_at) sur PostgreSQL, pensant que cela accélérerait les requêtes WHERE is_deleted = false ORDER BY created_at. Cependant, à mesure que l'ensemble de données a dépassé 80 % de comptes historiques supprimés, les requêtes ont commencé à prendre 8 à 12 secondes car le planificateur sous-estimait le coût d'analyse de l'index surchargé.

Solution A : Maintenir l'index composite et forcer l'utilisation de l'index avec des indices. Cette approche a utilisé SET enable_seqscan = off ou des indices de plan de requête pour forcer l'utilisation de l'index. Bien que cela ait temporairement amélioré certaines requêtes, cela a créé une dette de maintenance et a souvent forcé des plans sous-optimaux pour d'autres modèles d'accès lorsque la distribution des données a changé. La solution n'a également pas résolu le problème fondamental de l'inflation de l'index causant une augmentation des coûts de stockage et de l'overhead VACUUM.

Solution B : Créer des index partiels distincts pour les enregistrements actifs et supprimés. La mise en œuvre de CREATE INDEX idx_active_created ON users(created_at) WHERE is_deleted = false a réduit la taille de l'index de 80% et a permis au planificateur d'estimer avec précision 2 millions de lignes actives contre 8 millions de supprimées. Les requêtes sont réduites à 40 ms, mais nécessitaient de refactorer toutes les requêtes de l'application pour s'assurer que le prédicat is_deleted = false restait explicite et non dissimulé dans des fonctions ou abstrait derrière des vues qui obscurcissaient la condition.

L'équipe a choisi la Solution B car elle offrait des performances durables sans maintenance des indices de requête. Le résultat a été une réduction de 95 % de la latence des requêtes et l'élimination des problèmes périodiques de gonflement VACUUM causés par l'index composite surdimensionné précédemment. Le suivi a confirmé des temps de réponse constants en moins d'une seconde pour les principaux cas d'utilisation du tableau de bord.

Ce que les candidats oublient souvent

Comment la présence de valeurs NULL dans une colonne de timestamp de suppression douce (utilisant NULL pour actif, timestamp pour supprimé) affecte-t-elle l'utilisation des index partiels par rapport aux méthodes d'indicateur booléen ?

Lors de l'utilisation d'un timestamp nullable deleted_at, les index partiels comme WHERE deleted_at IS NULL rencontrent des défis avec la gestion de la NULLabilité d'PostgreSQL. Contrairement aux indicateurs booléens où = false est explicite et sargable, les conditions IS NULL nécessitent que le planificateur reconnaisse l'applicabilité de l'index, ce qui peut échouer si la requête utilise des instructions paramétrées où le planificateur ne peut pas prouver que le paramètre sera NULL. De plus, les mises à jour définissant deleted_at = CURRENT_TIMESTAMP causent une inflation de l'index dans l'index partiel pour les enregistrements actifs à mesure que les lignes sont supprimées de celui-ci, tandis que les mises à jour d'indicateurs booléens inversent le bit mais restent dans un index composite complet. L'approche nullable nécessite des appels ANALYZE plus fréquents et une attention particulière aux facteurs de remplissage des index pour gérer le taux d'occupation élevé des changements de statut de suppression.

Pourquoi un index couvrant incluant des colonnes de suppression douce pourrait-il ralentir les écritures plus que prévu, même avec une faible fréquence de suppression ?

Les index couvrants (utilisant la clause INCLUDE dans PostgreSQL 11+ ou SQL Server) ajoutant is_deleted pour éviter les recherches dans la table dégradent en réalité les performances d'écriture, car chaque opération de suppression douce (une MISE À JOUR) doit modifier plusieurs structures d'index. Lorsqu'un utilisateur est supprimé en douceur, la base de données doit marquer l'ancienne entrée d'index comme morte dans l'index partiel actif, insérer une nouvelle entrée dans tout index d'enregistrements supprimés et mettre à jour les pointeurs de tas de l'index couvrant. Les candidats oublient souvent que les index partiels isolent ce changement—seuls les index partiels spécifiques pour les états actifs ou supprimés sont modifiés—tandis que les index couvrants sur la table principale doivent mettre à jour la structure de l'index primaire indépendamment du statut de suppression douce, créant une amplification des écritures qui affecte le débit des transactions.

Quand le planificateur de requêtes ignore-t-il un index partiel pour les données supprimées, même lorsque la requête filtre explicitement les enregistrements supprimés ?

Si l'index partiel est défini comme WHERE is_deleted = true pour les requêtes d'audit, mais que l'application utilise une déclaration préparée avec un paramètre $1 pour les requêtes actives et supprimées, PostgreSQL peut mettre en cache un plan générique qui ne reconnaît pas l'index partiel pour le cas spécifique de true. Cela se produit parce que les instructions préparées génèrent des plans avant que les valeurs des paramètres ne soient liées, et l'optimiseur ne peut pas prouver que $1 = true correspondra toujours au prédicat de l'index. Les candidats oublient que le SQL dynamique ou les indices de recompilation (OPTION (RECOMPILE) dans SQL Server, exécution avec des valeurs littérales dans PostgreSQL) sont nécessaires pour garantir que le planificateur voit la valeur concrète et la fait correspondre au prédicat de l'index partiel, plutôt que de s'appuyer sur des plans génériques qui défault vers des analyses séquentielles en raison de l'incertitude de la valeur du paramètre.