Suppression légère

L'instruction DELETE légère supprime les lignes de la table [db.]table qui correspondent à l'expression expr. Elle est disponible uniquement pour la famille de moteurs de table *MergeTree.

DELETE FROM [db.]table [ON CLUSTER cluster] [IN PARTITION partition_expr] WHERE expr;

On l'appelle "lightweight DELETE" pour le distinguer de la commande ALTER TABLE … DELETE, qui est une opération lourde.

-- Deletes all rows from the `hits` table where the `Title` column contains the text `hello`
DELETE FROM hits WHERE Title LIKE '%hello%';

DELETE léger est implémenté sous la forme d'une mutation qui marque les lignes comme supprimées, sans toutefois les supprimer physiquement immédiatement.

Par défaut, les instructions DELETE attendent que le marquage des lignes comme supprimées soit terminé avant de renvoyer la réponse. Cela peut prendre beaucoup de temps si le volume de données est important. Vous pouvez aussi l'exécuter de manière asynchrone en arrière-plan à l'aide du paramètre lightweight_deletes_sync. S'il est désactivé, l'instruction DELETE renverra immédiatement, mais les données pourront encore rester visibles dans les requêtes jusqu'à la fin de la mutation en arrière-plan.

La mutation ne supprime pas physiquement les lignes qui ont été marquées comme supprimées ; cela ne se produira qu'au prochain merge. Par conséquent, il est possible que, pendant une durée indéterminée, les données ne soient pas réellement supprimées du stockage et soient seulement marquées comme supprimées.

Si vous devez garantir que vos données sont supprimées du stockage dans un délai prévisible, envisagez d'utiliser le paramètre de table min_age_to_force_merge_seconds. Vous pouvez aussi utiliser la commande ALTER TABLE … DELETE. Notez que la suppression de données à l'aide de ALTER TABLE ... DELETE peut consommer des ressources importantes, car elle recrée toutes les parts affectées.

Les suppressions massives peuvent dégrader les performances de ClickHouse. Si vous cherchez à supprimer toutes les lignes d’une table, envisagez d’utiliser la commande TRUNCATE TABLE.

Si vous prévoyez des suppressions fréquentes, envisagez d’utiliser une clé de partitionnement personnalisée. Vous pourrez ensuite utiliser la commande ALTER TABLE ... DROP PARTITION pour supprimer rapidement toutes les lignes associées à cette partition.

Par défaut, DELETE ne fonctionne pas sur les tables comportant des projections. En effet, des lignes d'une projection peuvent être affectées par une opération DELETE. Il existe toutefois un paramètre MergeTree, lightweight_mutation_projection_mode, pour modifier ce comportement.

La suppression de grands volumes de données avec l’instruction DELETE légère peut dégrader les performances des requêtes SELECT.

Les éléments suivants peuvent également dégrader les performances de DELETE léger :

• Une condition WHERE complexe dans une requête DELETE.
• Si la file d’attente des mutations contient déjà de nombreuses autres mutations, cela peut entraîner des problèmes de performances, car toutes les mutations d’une table sont exécutées séquentiellement.
• La table concernée contient un très grand nombre de parts de données.
• Une grande quantité de données stockées dans des parts compactes. Dans une part compacte, toutes les colonnes sont stockées dans un seul fichier.

DELETE requiert le privilège ALTER DELETE. Pour autoriser les instructions DELETE sur une table spécifique pour un utilisateur donné, exécutez la commande suivante :

GRANT ALTER DELETE ON db.table to username;

1. Un « masque » est appliqué aux lignes concernées

   Lorsqu'une requête DELETE FROM table ... est exécutée, ClickHouse enregistre un masque dans lequel chaque ligne est marquée soit comme « existante », soit comme « supprimée ». Ces lignes « supprimées » sont alors exclues des requêtes suivantes. Toutefois, les lignes ne sont réellement supprimées que plus tard, lors des fusions ultérieures. L'écriture de ce masque est bien plus légère que ce qu'implique une requête ALTER TABLE ... DELETE.

   Le masque est implémenté sous la forme d'une colonne système cachée _row_exists qui stocke True pour toutes les lignes visibles et False pour celles qui sont supprimées. Cette colonne n'est présente dans une part que si certaines de ses lignes ont été supprimées. Elle n'existe pas lorsqu'une part a toutes ses valeurs égales à True.

2. Les requêtes SELECT sont transformées pour inclure le masque

   Lorsqu'une colonne masquée est utilisée dans une requête, la requête SELECT ... FROM table WHERE condition est étendue en interne avec le prédicat sur _row_exists et devient :

   SELECT ... FROM table PREWHERE _row_exists WHERE condition

   Lors de l'exécution, la colonne _row_exists est lue pour déterminer quelles lignes ne doivent pas être renvoyées. S'il y a beaucoup de lignes supprimées, ClickHouse peut déterminer quels granules peuvent être entièrement ignorés lors de la lecture du reste des colonnes.

3. Les requêtes DELETE sont transformées en requêtes ALTER TABLE ... UPDATE

   DELETE FROM table WHERE condition est traduit en mutation ALTER TABLE table UPDATE _row_exists = 0 WHERE condition.

   En interne, cette mutation est exécutée en deux étapes :

   1. Une commande SELECT count() FROM table WHERE condition est exécutée pour chaque part afin de déterminer si elle est concernée.

   2. À partir des commandes ci-dessus, les parts concernées sont ensuite mutées, et des liens physiques sont créés pour les parts non concernées. Dans le cas des parts wide, la colonne _row_exists de chaque ligne est mise à jour, et les fichiers de toutes les autres colonnes sont liés physiquement. Pour les parts compact, toutes les colonnes sont réécrites, car elles sont toutes stockées ensemble dans un seul fichier.

   Comme le montrent les étapes ci-dessus, le DELETE léger utilisant la technique du masquage offre de meilleures performances que le ALTER TABLE ... DELETE traditionnel, car il ne réécrit pas les fichiers de toutes les colonnes pour les parts concernées.

• Blog : Gestion des mises à jour et des suppressions dans ClickHouse