Vider une table dans PostgreSQL

Quand on travaille sur un projet comportant une base de données volumineuse, il est important de connaître quelques petits trucs pour optimiser les requêtes.

Souvent, c'est une suite de petites optimisations qui permet de sauver du temps d'exécution au final. Par exemple, imaginez une table de statistiques qui est mise à jour à une certaine fréquence selon le résultat que compile une requête SQL. Un cronjob lance une routine SQL qui compile les résultats, les conserve dans une table temporaire, supprime toutes les entrées de la table de statistiques et les remplace par les nouveaux résultats compilés.

En temps normal, on aurait simplement exécuté la requête suivante pour vider la table :

DELETE FROM tablename;

Or, la suppression des enregistrements est plus longue car certaines vérifications sont effectuées et un log des actions est conservé.

En remplaçant DELETE par TRUNCATE, on passe par-dessus ces mesures de contrôle, ce qui permet d'accélérer substantiellement la vitesse d'exécution.

Par exemple, avec une table très simple comprenant une clé primaire auto-incrémentée et un champ texte, si on insère 1 million d'enregistrements aléatoires :

-- environ 27 secondes
INSERT INTO table_statistics (random_data)
SELECT md5(random()::varchar) as random_string
FROM generate_series(1,1000000)

Une suppression à l'aide de DELETE FROM table_statistics a pris approximativement 7.5 secondes pour vider la table. Alors qu'en utilisant TRUNCATE table_statistics, elle était complètement vide en 93 millisecondes.

Notez que contrairement à DELETE, il est impossible de combiner une clause WHERE avec TRUNCATE. Tronquer une table permet de la vider, donc c'est tout ou rien. L'implémentation de PostgreSQL permet aussi de vider plusieurs tables en séparant les noms par une virgule.

L'instruction TRUNCATE est disponible entre autre sous PostgreSQL, MySQL, SQL Server et Oracle et chacun des RDBMS a ses propres particularités.

Tags: PostgreSQL