Opérations en masse plutôt que des boucles ligne par ligne : allers-retours, transactions et COPY

Ce que c'est

Une boucle ligne par ligne envoie une instruction par ligne et attend chaque réponse. Une opération en masse envoie tout l'ensemble d'un coup : un INSERT ... VALUES (...), (...) multi-lignes, un UPDATE ... FROM (VALUES ...) unique, une lecture WHERE id = ANY(...), ou un flux COPY. Le guide PostgreSQL sur le peuplement d'une base de données recommande de désactiver l'autocommit et de valider une seule fois à la fin lors de l'utilisation de nombreux inserts, d'utiliser COPY pour charger toutes les lignes en une seule commande car elle est optimisée pour de grands nombres de lignes et entraîne beaucoup moins de surcharge qu'une série d'INSERT, d'utiliser des instructions préparées lorsque COPY n'est pas possible, et de créer les index après le chargement d'une table neuve car indexer des données existantes est plus rapide que mettre à jour un index ligne par ligne. executemany de psycopg exécute la même commande sur une séquence de paramètres, et sa documentation la décrit comme plus efficace que des requêtes séparées, tout en suggérant copy() pour de nombreux inserts.

Pourquoi c'est important

Le coût par ligne est la latence de l'aller-retour plus l'analyse syntaxique et la planification plus, avec l'autocommit, une validation qui doit être rendue durable. À 1 ms d'aller-retour, un million de lignes passent au moins 1 000 s (environ 17 minutes) à attendre le seul réseau (calcul arithmétique), avant même que la base de données n'effectue le moindre travail. Les opérations en masse suppriment ce terme et laissent la base de données planifier une seule fois.

Comment l'appliquer

• Lectures : une seule requête avec id = ANY(%s) ou une jointure plutôt qu'une boucle de recherches ponctuelles ; paginer les résultats volumineux avec des curseurs par jeu de clés (keyset cursors).
• Écritures : INSERT multi-lignes, executemany, ou COPY pour les chargements ; UPDATE ... FROM (VALUES ...) ou UPDATE ... WHERE id = ANY(...) pour les mises à jour en masse ; DELETE ... WHERE id = ANY(...) pour les suppressions.
• Regrouper les lots par lots : une transaction toutes les quelques milliers de lignes garde les verrous courts et le journal d'écriture anticipée (write-ahead log) borné tout en amortissant la validation.
• Pour une table neuve : charger, puis créer les index et les contraintes, puis exécuter ANALYZE comme le recommande le guide.
• Diffuser l'entrée en flux plutôt que de matérialiser une liste d'un million de dictionnaires ; executemany accepte un itérable d'ensembles de paramètres et copy() écrit les lignes une à une à partir de n'importe quel itérateur.

Pièges

Une instruction en masse échoue comme un tout : une ligne défectueuse annule le lot, donc décider à l'avance s'il faut revenir au traitement ligne par ligne pour le lot échoué ou le rejeter. Une transaction unique et énorme retient les verrous pendant toute sa durée et fait gonfler le journal d'écriture anticipée. Les déclencheurs (triggers) et les contraintes par ligne se déclenchent quand même ligne par ligne. Les suppressions en masse sur de grandes tables bloquent les autres processus d'écriture ; supprimer par plage de clé primaire en lots. Les méthodes en masse des ORM sautent souvent la validation du modèle, les signaux et les hooks de sauvegarde ; lire leurs mises en garde avant de s'y fier.