En vrac/mise à jour par lot/upsert dans PostgreSQL

Je suis en train d'écrire un Django-ORM enchancement qui tente de cache de modèles et de reporter le modèle d'économie jusqu'à la fin de la transaction. Tout est presque terminé, mais je suis tombé sur une difficulté imprévue dans la syntaxe SQL.

Je ne suis pas beaucoup d'un DBA, mais ce que je comprends, les bases de données ne fonctionnent pas vraiment efficace pour de nombreuses petites requêtes. Peu plus les requêtes sont beaucoup mieux. Par exemple, il est préférable de les utiliser en grandes séries des inserts (disons 100 lignes à la fois) au lieu de 100 one-liners.

Maintenant, de ce que je peux voir, SQL n'a pas vraiment d'approvisionnement aucune déclaration à effectuer une mise à jour par lot sur une table. Le terme semble être confusion donc, je vais vous expliquer ce que je veux dire par là. J'ai un tableau de données arbitraires, chaque entrée décrivant une seule ligne dans une table. Je voudrais mettre à jour certaines lignes de la table, chacun à l'aide de données à partir de son entrée correspondante dans le tableau. L'idée est très similaire à une insertion de lot.

Par exemple: Mon tableau peuvent avoir deux colonnes "id" et "some_col". Maintenant, le tableau décrivant les données pour une mise à jour par lot se compose de trois entrées (1, 'first updated'), (2, 'second updated'), et (3, 'third updated'). Avant la mise à jour de la table contient des lignes: (1, 'first'), (2, 'second'), (3, 'third').

Je suis tombé sur ce post:

Pourquoi sont-lot insertions/mises à jour plus rapide? Comment faire les mises à jour par lot de travail?

qui semble faire ce que je veux, mais je ne peux pas vraiment comprendre la syntaxe à la fin.

Je pourrais aussi supprimer toutes les lignes qui nécessitent une mise à jour et réinsérez-les à l'aide d'un lot d'insertion, cependant j'ai du mal à croire que ce serait effectivement faire mieux.

Je travaille avec PostgreSQL 8.4, de sorte que certaines procédures stockées sont également possible ici. Cependant, comme j'ai l'intention de l'open source, le projet a finalement, plus portable des idées ou façons de faire la même chose sur un autre SGBD sont la plupart de bienvenue.

Question de suivi: Comment faire un " lot "d'insertion ou de mise à jour"/"upsert" déclaration?

Résultats de Test

Que j'ai effectué 100x fois 10 les opérations d'insertion, réparties sur 4 tables différentes (donc 1000 inserts au total). Je l'ai testé sur Django 1.3 avec un PostgreSQL 8.4 backend.

Ce sont les résultats:

  • Toutes les opérations effectuées par le biais de l'ORM de Django - chaque passage ~2.45 secondes,
  • Les mêmes opérations, mais sans l'ORM de Django - chaque passage ~1.48 secondes,
  • Seulement les opérations d'insertion, sans interroger la base de données pour les valeurs de séquence ~0.72 secondes,
  • Seulement les opérations d'insertion, exécuté dans des blocs de 10 (100 blocs au total) ~0.19 secondes,
  • Seulement les opérations d'insertion, un grand exécution bloc ~0,13 seconde.
  • Seulement les opérations d'insertion, environ 250 instructions par bloc, ~0,12 secondes.

Conclusion: exécuter autant d'opérations que possible en une seule connexion.execute(). Django lui-même introduit une surcharge importante.

Avertissement: je n'ai pas introduire des indices en dehors de défaut de la clé primaire d'indices, de sorte que les opérations d'insertion pourrait courir plus vite à cause de cela.

  • +1 parce que j'ai appris que "upsert" est un mot réel (c'est même sur wikipédia )
  • Pouvez-vous nous montrer les instructions que vous êtes en cours d'exécution? Je suis un peu confus par le terme "mise à jour par lot"? Ne pouvez-vous pas faire toutes les mises à jour avec une seule instruction de mise à JOUR? Avec 9.1, vous pouvez également faire la mise à JOUR et l'INSÉRER dans une seule opération à l'aide accessible en écriture d'expressions de table communes
InformationsquelleAutor julkiewicz | 2011-08-11