Aller au contenu | Aller au menu | Aller à la recherche

Effacer un gros volume de données sans tout bloquer

Si vous possédez comme moi des tables contenant des dizaines de millions de données, et qui surtout pour certaines contiennent plus de 20 clefs étrangères, vouloir effacer quelques 4 millions de lignes peut engendrer quelques blocages sur votre production. Une solution consiste donc à effacer les données par blocs de tailles raisonnables pour ne pas bloquer l'accès en écriture à cette table.

Pour cela PostgreSQL possède une astuce qui permet de contourner l'absence de transaction dans une fonction plPgsql, qui est elle même une transaction.

DBLink Cette extension de PostgreSQL permet de se connecter à votre base de données depuis elle-même et de faire une transaction depuis cette connexion.

Ainsi, voilà un petit exemple de script qui va faire une boucle sur le volume de données à effacer, par lot de 100 records sans bloquer constamment l'accès en écriture à votre table, pour cela j'ai d'abord créé une table avec les identifiants uniques des données à effacer, avec un serial qui permettra de faire la boucle sur le volume de données :

DO 
$do$ 
DECLARE _counter int := 1; 
BEGIN 
	WHILE _counter < 3813142 
	LOOP 
		PERFORM dblink_connect('dblink_trans','dbname=mydb port=5432 user=postgres'); 
		PERFORM dblink('dblink_trans','DELETE FROM mytable WHERE iduser IN (SELECT iduser FROM tablewithidtodelete  where id between '|| _counter || ' and ' || _counter || ' + 100)'); 
		PERFORM dblink('dblink_trans','COMMIT;'); 
		PERFORM dblink_disconnect('dblink_trans'); 
		_counter := _counter + 100; 
		RAISE NOTICE 'Delete users from ID : %', _counter;
	END LOOP; 
END 
$do$;

Attention, pas moyen de travailler sur une table temporaire dans ce genre de cas de figure puisqu'avec une nouvelle connexion via DBLink, elle ne serait pas accessible.

Cela va prendre pas mal de temps, mais vous ne serez pas bloqué.

Une fois le principe intégré, voyons comment faire pour que cette requête soit plus rapide.

Le IN c'est bien, mais si vous avez un index sur la table pour laquelle vous voulez effacer des données, et en fonction de son volume, l'index ne sera peut-être pas utilisé.

Pour cela j'ai trouvé une astuce bien pratique qui permet une véritable optimisation :

DO 
$do$ 
DECLARE _counter int := 1; 
BEGIN 
	WHILE _counter < 3813142 
	LOOP 
		PERFORM dblink_connect('dblink_trans','dbname=mydb port=5432 user=postgres'); 
		PERFORM dblink('dblink_trans','WITH list_to_delete AS (SELECT unnest(array_agg(iduser)) AS iduser FROM tablewithidtodelete WHERE id BETWEEN '|| _counter || ' AND ' || _counter || ' + 100) DELETE FROM mytable USING list WHERE list.iduser = mytable.iduser'); 
		PERFORM dblink('dblink_trans','COMMIT;'); 
		PERFORM dblink_disconnect('dblink_trans'); 
		_counter := _counter + 100; 
		RAISE NOTICE 'Delete users from ID : %', _counter;
	END LOOP; 
END 
$do$;

Que se passe-t-il ?

Déjà nous utilisons une CTE qui permet de préparer les données des iduser comme nous le faisions dans le premier exemple. Mais cette fois nous allons les chercher pour les placer dans un tableau (ARRAY) grâce à la commande array_agg(), puis nous étendons le tableau en un ensemble de lignes de résultats, via la commande unnest(). Ceci permet alors de faire une jointure sur cette CTE avec le DELETE directement sur le champ concerné. Dès lors l'index de la table avec les données à effacer est utilisé et par conséquent la vitesse d'exécution est grandement améliorée.

Dans mon exemple, nous passons de 5 à 10 secondes sur une table de plus de 180 millions d'enregistrements, à moins de 100ms. Un résultats très appréciable.

La discussion continue ailleurs

URL de rétrolien : https://www.footcow.com/index.php?trackback/90

Fil des commentaires de ce billet