Plus rapide n’est pas toujours mieux : choisir la bonne stratégie d’insertion PostgreSQL en Python (+ benchmarks)
démontre qu’il est parfaitement possible d’insérer 2 millions d’enregistrements par seconde dans Postgres. Au lieu de rechercher des micro-benchmarks, dans cet article, nous prendrons du recul pour poser une question plus importante : quelles abstractions correspondent réellement à notre charge de travail ?
Nous examinerons 5 façons d’insérer des données dans Postgres à l’aide de Python. L’objectif n’est pas seulement d’examiner les vitesses d’insertion et de désigner un gagnant, mais de comprendre les compromis entre abstraction, sécurité, commodité et performance.
Au final vous comprendrez :
- les forces et les faiblesses des inserts ORM, Core et au niveau du pilote
- quand la performance compte vraiment
- comment choisir le bon outil sans trop d’ingénierie
Pourquoi les insertions rapides sont importantes
Les charges de travail d’insertion de gros volumes apparaissent partout :
- charger des millions d’enregistrements
- synchronisation des données à partir d’API externes
- remplissage des tables d’analyse
- ingérer des événements ou des journaux dans les entrepôts
Les petites inefficacités s’aggravent rapidement. Transformer une tâche d’insertion de 3 minutes en une tâche de 10 secondes peut réduire la charge du système, libérer les travailleurs et améliorer le débit global.
Cela dit, plus rapide ne signifie pas automatiquement meilleur. Lorsque la charge de travail est faible, sacrifier la clarté et la sécurité pour des gains marginaux est rarement payant.
Compréhension quand la performance est importante et pourquoi est le véritable objectif.
Avec quel outil utilisons-nous pour insérer ?
Pour communiquer avec notre base de données Postgres, nous avons besoin d’un pilote de base de données. Dans notre cas, c’est psycopg3 avec SQLAlchemy superposé. Voici une distinction rapide :
Psycopg3 (le pilote)
psycopg3 est un pilote PostgreSQL de bas niveau pour Python. Il s’agit d’une abstraction très fine avec une surcharge minimale qui communique directement avec Postgres.
Le compromis est la responsabilité : vous écrivez du SQL vous-même, gérez le bain et gérez explicitement l’exactitude.
SQLAlchimie
SQLAlchemy se trouve au-dessus des pilotes de base de données comme psycopg3 et fournit deux couches :
1) Noyau SQLAlchemy
Il s’agit de la couche d’abstraction et d’exécution SQL. Il est indépendant de la base de données, ce qui signifie que vous écrivez des expressions Python et Core les traduira en SQL dans le dialecte de base de données correct (PostgreSQL / SQL Server / SQLite) et liera les paramètres en toute sécurité.
2) ORM SQLAlchimie
ORM est construit sur Core et résume encore plus. Il mappe les classes Python aux tables, suit l’état des objets et gère les relations. L’ORM est hautement productif et sûrmais toute cette comptabilité introduit des frais généraux, en particulier pour les opérations en gros.
En bref:
Tous les trois existent sur un spectre. D’un côté il y a ORMce qui vous décharge de beaucoup de travail et offre beaucoup de sécurité au prix de frais généraux. De l’autre côté, il y a le Conducteur est très simple mais offre un débit maximal. Cœur se situe en plein milieu et vous offre un bon équilibre entre sécurité, performances et contrôle.
Dit simplement :
- ORM vous aide à utiliser le Cœur plus facilement
- Cœur vous aide à utiliser le Conducteur plus sûr et indépendant de la base de données
La référence
Pour que l’indice de référence reste équitable :
- chaque méthode reçoit les données sous la forme pour laquelle elle a été conçue
(Objets ORM pour ORM, dictionnaires pour Core, tuples pour le Driver) - seul le temps passé à déplacer les données de Python vers Postgres est mesuré
- aucune méthode n’est pénalisée pour les travaux de conversion
- La base de données existe dans le même environnement que notre script Python ; cela empêche notre référence de commencer à être gênée par la vitesse de téléchargement, par exemple
Le but n’est pas de « trouver l’insert le plus rapide » mais de comprendre ce que chaque méthode fait bien.
1) Plus vite, c’est toujours mieux ?
Quoi de mieux ? Une Ferrari ou une Jeep ?
Cela dépend du problème que vous essayez de résoudre.
Si vous traversez une forêt, optez pour la Jeep. Si vous voulez être le premier à franchir la ligne d’arrivée, la Ferrari est une meilleure alternative.
La même chose s’applique à l’insertion. Réduire 300 millisecondes sur un insert de 10 secondes ne justifie peut-être pas une complexité et un risque supplémentaires. Dans d’autres cas, ce gain en vaut vraiment la peine.
Dans certains cas, la méthode la plus rapide sur papier est la le plus lent lorsque vous comptabilisez :
- coût d’entretien
- garanties d’exactitude
- charge cognitive
2) Quel est votre point de départ ?
La bonne stratégie d’insertion moins sur le nombre de lignes et plus sur l’apparence déjà de vos données
L’ORM, le Core et le driver ne sont pas des outils concurrents. Ils sont optimisés à différentes fins :
| Méthode | But |
ORM (add_all) |
Logique métier, exactitude, petits lots |
ORM(bulk_save_object) |
Objets ORM à grande échelle |
Cœur (execute) |
Données structurées, abstraction de la lumière |
Conducteur (executemany) |
Lignes brutes, débit élevé |
Conducteur (COPY) |
Ingestion groupée, ETL, charges de travail Firehose |
Un ORM excelle dans les applications lourdes CRUD où la clarté et la sécurité sont les plus importantes. Pensez aux sites Web et aux API. Les performances sont généralement « assez bonnes » et la clarté compte davantage.
Cœur brille dans les situations où vous souhaitez contrôler sans écrire du SQL brut. Pensez à l’ingestion de données, aux tâches par lots, aux pipelines d’analyse et aux services sensibles aux performances comme les tâches ETL.
Vous savez exactement quel SQL vous voulez mais vous ne souhaitez pas gérer vous-même les connexions ou les différences de dialecte.
Le Conducteur est optimisé pour un débit maximal ; des écritures extrêmement volumineuses, comme l’écriture de millions de lignes pour des ensembles de formation ML, des chargements groupés, la maintenance ou les migrations de bases de données ou des services d’ingestion à faible latence.
Le pilote minimise la surcharge d’extraction et de python et vous offre le débit le plus élevé. L’inconvénient est que vous devez écrire manuellement du SQL, ce qui facilite les erreurs.
3) Ne confondez pas les abstractions
L’ORM n’est pas lent. COPIER n’est pas magique
Des problèmes de performances apparaissent lorsque nous forçons les données à travers une abstraction pour laquelle elles n’ont pas été conçues :
- Utilisation de Core avec des objets SQLAlchemy ORM – > lente en raison de la surcharge de conversion
- Utiliser ORM avec des tuples – > maladroit et fragile
- ORM en vrac dans le processus ETL – > surcharge gaspillée
Parfois, passer à un niveau inférieur peut en fait réduire performance.
Quand choisir lequel ?
Règle générale :
| Couche | Utilisez-le quand… |
| ORM | Vous construisez une application (exactitude et productivité) |
| Cœur | Vous déplacez ou transformez des données (équilibre entre sécurité et rapidité) |
| Conducteur | Vous repoussez les limites de performance (puissance brute et pleine responsabilité) |
Conclusion
Dans les systèmes de données et d’IA, les performances sont rarement limitées par la base de données. Elle est limitée par la façon dont notre code s’aligne sur la forme des données et les abstractions que nous choisissons.
ORM, Cœur et ConducteurLes API de niveau supérieur forment un spectre allant de la sécurité de haut niveau à la puissance de bas niveau. Tous sont d’excellents outils lorsqu’ils sont utilisés dans le contexte pour lequel ils ont été conçus.
Le véritable défi n’est pas de savoir lequel est à jeun, mais de sélectionner l’outil adapté à votre situation.
J’espère que cet article était aussi clair que je le souhaitais, mais si ce n’est pas le cas, faites-moi savoir ce que je peux faire pour clarifier davantage. En attendant, consultez mon d’autres articles sur toutes sortes de sujets liés à la programmation.
Bon codage !
-Mike
Ps : tu aimes ce que je fais ? Suis-moi!
