Linux, Base de données Postgresql, développement, Internet, emailing et déliverabilité.

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é.

Partager

  • Favories


Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.

La discussion continue ailleurs

URL de rétrolien : http://www.footcow.com/index.php/trackback/109

Fil des commentaires de ce billet