Mise à jour en vrac/lot / upsert dans PostgreSQL

j'écris un enchancement Django-ORM qui tente de mettre en cache des modèles et de reporter leur sauvegarde jusqu'à la fin de la transaction. Tout est presque terminé, mais j'ai rencontré une difficulté inattendue dans la syntaxe SQL.

Je ne suis pas vraiment un DBA, mais d'après ce que j'ai compris, les bases de données ne fonctionnent pas vraiment efficacement pour de nombreuses petites requêtes. Quelques requêtes plus importantes sont beaucoup mieux. Par exemple, il est préférable d'utiliser des inserts de grande série (disons 100 lignes à la fois) au lieu de 100 one-liners.

maintenant, de ce que je peux voir, SQL ne fournit pas vraiment n'importe quelle déclaration pour effectuer une mise à jour de fournée sur une table. Le terme semble être déroutant donc, je vais expliquer ce que je veux dire par cela. J'ai un tableau de données arbitraires, chaque entrée décrivant une seule ligne dans une table. J'aimerais mettre à jour certaines lignes de la table, chacune utilisant les données de son entrée correspondante dans le tableau. L'idée est très similaire à une insertion de lot.

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

I came across this post:

pourquoi les inserts/mises à jour par lots sont-ils plus rapides? Comment fonctionnent les mises à jour par lots?

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

je pourrais également supprimer toutes les lignes qui nécessitent une mise à jour et les réinsérer en utilisant une insertion par lots, mais j'ai du mal à croire que cela fonctionnerait mieux.

je travaille avec PostgreSQL 8.4, donc certaines procédures stockées sont également possibles ici. Cependant, comme j'ai l'intention d'ouvrir le projet en fin de compte, toutes les idées portables ou des moyens de faire la même chose chose sur un RDBMS différent sont les bienvenus.

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

résultats des essais

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

ce sont les résultats:

  • toutes les opérations effectuées par L'intermédiaire de Django ORM-chaque passage ~2,45 secondes ,
  • les mêmes opérations, mais faites sans Django ORM-chaque passage ~1,48 secondes ,
  • insérer seulement les opérations, sans interroger la base de données pour les valeurs de séquence ~0.72 secondes ,
  • inscrire uniquement les opérations exécutées en blocs de 10 (100 blocs au total)) ~0,19 secondes ,
  • Seulement les opérations d'insertion, de l'exécution du bloc ~0,13 seconde .
  • inscrire opérations seulement, environ 250 énoncés par bloc, ~0,12 secondes .

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

Avertissement: je n'ai pas introduit d'indices en dehors des indices de clés primaires par défaut, donc les opérations d'insertion pourraient probablement courir plus vite à cause de cela.

35
demandé sur Community 2011-08-11 05:23:11

5 réponses

j'ai utilisé 3 stratégies pour le travail transactionnel par lots:

  1. générez des énoncés SQL à la volée, concaténez-les par des points-virgule, puis soumettez les énoncés en un seul tir. J'ai fait jusqu'à 100 inserts de cette façon, et c'était assez efficace (fait contre Postgres).
  2. JDBC a des capacités de mise en lots intégrées, si configurées. Si vous générez des transactions, vous pouvez vider vos déclarations JDBC de sorte qu'elles opèrent en un coup. Cette tactique exige moins d'appels de base de données, puisque les énoncés sont tous exécutés dans un lot.
  3. Hibernate prend également en charge la mise en lots JDBC dans le sens de l'exemple précédent, mais dans ce cas vous exécutez une méthode flush() contre L'hibernation Session , pas la connexion JDBC sous-jacente. Il accomplit la même chose que JDBC batching.

incidemment, Hibernate soutient également une stratégie de batching dans la collecte. Si vous annotez une collection avec @BatchSize , lors de la collecte des associations, Hibernate utilisera IN au lieu de = , conduisant à moins SELECT déclarations pour charger les collections.

12
répondu atrain 2011-08-11 02:35:02

vrac insérer

vous pouvez modifier l'insertion en vrac de trois colonnes par Ketema: 

INSERT INTO "table" (col1, col2, col3)
  VALUES (11, 12, 13) , (21, 22, 23) , (31, 32, 33);

il devient: 

INSERT INTO "table" (col1, col2, col3)
  VALUES (unnest(array[11,21,31]), 
          unnest(array[12,22,32]), 
          unnest(array[13,23,33]))

remplacement des valeurs par des espaces réservés: 

INSERT INTO "table" (col1, col2, col3)
  VALUES (unnest(?), unnest(?), unnest(?))

vous devez passer des tableaux ou des listes comme arguments à cette requête. Cela signifie que vous pouvez faire d'énormes inserts en vrac sans faire de concaténation de chaîne (et tous ses hazzles et dangers: injection sql et citant l'enfer).

mise à jour en vrac

PostgreSQL a ajouté L'extension de FROM à UPDATE. Vous pouvez l'utiliser de cette façon: 

update "table" 
  set value = data_table.new_value
  from 
    (select unnest(?) as key, unnest(?) as new_value) as data_table
  where "table".key = data_table.key;

le manuel manque une bonne explication, mais il y a un exemple sur la postgresql-admin mailing list . J'ai essayé d'élaborer sur elle: 

create table tmp
(
  id serial not null primary key,
  name text,
  age integer
);

insert into tmp (name,age) 
values ('keith', 43),('leslie', 40),('bexley', 19),('casey', 6);

update tmp set age = data_table.age
from
(select unnest(array['keith', 'leslie', 'bexley', 'casey']) as name, 
        unnest(array[44, 50, 10, 12]) as age) as data_table
where tmp.name = data_table.name;

Il y a aussi autres posts sur StackExchange expliquer UPDATE...FROM.. en utilisant une clause VALUES au lieu d'un sous-article. Ils pourraient en faciliter la lecture, mais sont limités à un nombre fixe de lignes.

37
répondu hagello 2017-05-23 12:34:12

les inserts de vrac peuvent être faits comme tels: 

INSERT INTO "table" ( col1, col2, col3)
  VALUES ( 1, 2, 3 ) , ( 3, 4, 5 ) , ( 6, 7, 8 );

insérera 3 lignes.

la mise à jour Multiple est définie par la norme SQL, mais n'est pas implémentée dans PostgreSQL.

citation:

" selon la norme, la syntaxe colonne-liste doit permettre une liste des colonnes à attribuer à partir d'une seule expression de valeur de ligne, telles que une sous-sélection:

mise à jour jeu de comptes (contact_last_name, contact_first_name) = (Sélectionnez last_name, first_name FROM salesmen Où les vendeurs.id = comptes.vente au détail); "

référence: http://www.postgresql.org/docs/9.0/static/sql-update.html

12
répondu Ketema 2011-08-11 16:29:22

il est assez rapide pour remplir json dans le jeu d'enregistrements (postgresql 9.3+) 

big_list_of_tuples = [
    (1, "123.45"),
    ...
    (100000, "678.90"),
]

connection.execute("""
    UPDATE mytable
    SET myvalue = Q.myvalue
    FROM (
        SELECT (value->>0)::integer AS id, (value->>1)::decimal AS myvalue 
        FROM json_array_elements(%s)
    ) Q
    WHERE mytable.id = Q.id
    """, 
    [json.dumps(big_list_of_tuples)]
)
1
répondu nogus 2016-12-07 16:24:39

éteignez autocommit et faites juste une commit à la fin. En SQL simple, cela signifie que l'émission commence au début et S'engage à la fin. Vous auriez besoin de créer une fonction afin de faire un upsert réel.

0
répondu aliasmrchips 2011-08-11 03:10:17