Bulk / Batch Update / Upsert in PostgreSQL

Schreibe ich eine Django-ORM, sensible Erweiterung, die versucht, cache-Modelle und verschieben Sie das Modell speichern, bis zum Ende der Transaktion. Es ist alles fast fertig, allerdings stieß ich auf eine unerwartete Schwierigkeit in der SQL-syntax.

Ich bin nicht viel von einem DBA, sondern von dem, was ich verstehe, Datenbanken nicht wirklich effizient arbeiten für viele kleine queries. Paar größere Abfragen sind viel besser. Es ist zum Beispiel besser, um große batch-Einsätze (sagen wir 100 Zeilen auf einmal) anstatt 100 Einzeiler.

Nun, von dem, was ich sehen kann, ist SQL nicht wirklich irgendwelche Anweisung zum ausführen einer batch-update auf eine Tabelle. Der Begriff scheint verwirrend so, ich werde erklären, was ich damit meine. Ich habe ein array beliebige Daten, jeder Eintrag beschreibt eine einzelne Zeile in einer Tabelle. Ich möchte zu aktualisieren, bestimmte Zeilen in der Tabelle, die jeweils mit Daten aus den entsprechenden Eintrag im array. Die Idee ist sehr ähnlich wie eine Batchdatei einfügen.

Zum Beispiel: Meine Tabelle zwei Spalten "id" und "some_col". Jetzt ist das array zur Beschreibung der Daten für eine batch-update besteht aus drei Einträge (1, 'first updated'), (2, 'second updated'), und (3, 'third updated'). Vor dem aktualisieren der Tabelle Zeilen enthält: (1, 'first'), (2, 'second'), (3, 'third').

Ich stieß auf dieses post:

Warum sind batch-inserts/updates schneller? Wie kann der batch-updates funktionieren?

scheint das zu tun, was ich will, aber ich kann nicht wirklich herausfinden, die syntax am Ende.

Konnte ich auch löschen Sie alle Zeilen, die aktualisiert werden müssen, und legen Sie Sie unter Verwendung einer Batchdatei einfügen, allerdings finde ich es schwer zu glauben, dass dies tatsächlich besser.

Ich die Arbeit mit PostgreSQL-8.4, so dass einige gespeicherte Prozeduren sind auch hier möglich. Aber da ich Plane, open-source-Projekt schließlich nicht mehr tragbare Ideen oder Möglichkeiten zu tun, die gleiche Sache auf einem anderen RDBMS sind das meiste willkommen.

Follow-up-Frage: Wie man eine batch - "insert-oder update"/"upsert" - Anweisung?

Testergebnisse

Habe ich durchgeführt 100x mal 10 insert-Operationen, die sich über 4 verschiedene Tabellen (also 1000 inserts in der Summe). Getestet habe ich auf Django 1.3 mit einer PostgreSQL-8.4-backend.

Dies sind die Ergebnisse:

Alle Operationen durch Django ORM - jeder pass ~2.45 Sekunden,
Die gleichen Operationen, aber dies ohne Django ORM - jeder pass ~1.48 Sekunden,
Nur insert-Operationen, ohne Abfragen an die Datenbank, die für das sequence-Werte ~0.72 Sekunden,
Nur insert-Operationen, durchgeführt in Blöcken von 10 (100 Blöcke insgesamt) ~0.19 Sekunden,
Nur insert-Operationen, eine große Ausführung block ~0.13 Sekunden.
Nur insert-Operationen, über 250 Aussagen pro block, ~0,12 Sekunden.

Fazit: führen Sie so viele Operationen wie möglich in einer einzigen Verbindung.execute(). Django selbst stellt einen erheblichen Aufwand.

Disclaimer: ich habe nicht einführen, Indizes, abgesehen von Standard-Primärschlüssel-Indizes, so dass insert-Operationen könnte möglicherweise schneller ausgeführt werden, weil die.

Kommentar zu dem Problem - Öffnen

+1, da habe ich gelernt, dass "upsert" ist ein Wort, (es ist auch auf wikipedia ) Kommentarautor: SingleNegationElimination

Können Sie uns zeigen, den Aussagen, die Sie ausgeführt werden? Ich bin ein bisschen verwirrt durch den Begriff "batch-update"? Kannst du nicht alle updates mit einem einzigen UPDATE-Anweisung? Mit 9.1 Sie können auch UPDATE-und INSERT in einem Arbeitsgang mit beschreibbaren CTEs Kommentarautor: a_horse_with_no_name

InformationsquelleAutor der Frage julkiewicz | 2011-08-11

10

Ich habe 3 Strategien für batch-Transaktionen arbeiten:
1. Generieren Sie SQL-Anweisungen auf die Fliegen, verketten Sie diese durch ein Semikolon, und senden Sie dann die Anweisungen in einem Schuss. Ich habe getan, bis zu 100 Einsätze in dieser Art und Weise, und es war sehr effizient (gegen Postgres).
2. JDBC hat Dosierungs-Funktionen eingebaut, wenn konfiguriert. Beim generieren von Buchungen, können Sie Spülen Sie Ihre JDBC-Anweisungen, so dass Sie transact in einem Schuss. Diese Taktik erfordert weniger Datenbank-Aufrufe, wie die Anweisungen alle ausgeführt in einem batch.
3. Hibernate unterstützt auch JDBC-Gemenge entlang der Linien von der vorherigen Beispiel, aber Sie führen in diesem Fall eine flush() Methode gegen die Hibernate Session, nicht die zugrunde liegende JDBC-Verbindung. Es leistet das gleiche wie JDBC-Batchverarbeitung.
Übrigens, Hibernate unterstützt auch eine Stapelverarbeitung Strategie in die Sammlung Holen. Wenn Sie kommentieren eine Sammlung mit @BatchSize, beim abrufen von Verbänden, Hibernate verwenden IN statt =, was zu weniger SELECT Aussagen zu laden, bis die Sammlungen.

InformationsquelleAutor der Antwort atrain

Bulk insert

Können Sie die bulk insert von drei Spalten durch Ketema:

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

Wird es:

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

Ersetzen Sie die Werte durch Platzhalter:

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

Haben Sie das übergeben von arrays oder Listen als Argumente für diese Abfrage. Dies bedeutet, dass Sie tun können, riesige Massen-inserts, ohne dabei die string-Verkettung (und alle seine hazzles und Gefahren: sql-injection und Angabe der Hölle).

Bulk update

PostgreSQL Hinzugefügt hat, die AUS der extension zu AKTUALISIEREN. Sie können es verwenden, auf diese Weise:

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;

Handbuch fehlt eine gute Erklärung, aber es ist ein Beispiel für die postgresql-admin mailing list. Ich habe versucht, zu erläutern:

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;

Gibt es auch andere Beiträge auf StackExchange erklärt UPDATE...FROM.. mit einem VALUES - Klausel anstelle einer Unterabfrage. Sie können einfacher gelesen werden, sondern sind beschränkt auf eine Feste Anzahl von Zeilen.

InformationsquelleAutor der Antwort hagello

12

Großteil der Einsätze wie folgt durchgeführt:
```
INSERT INTO "table" ( col1, col2, col3)
  VALUES ( 1, 2, 3 ) , ( 3, 4, 5 ) , ( 6, 7, 8 );
```
Einfügen 3 Zeilen.

Mehreren Update ist definiert durch die SQL-standard, aber nicht implementiert PostgreSQL.

Zitat:

"Nach dem standard, die column-list syntax sollte es eine Liste
der Spalten zugeordnet werden, die aus einer einzigen Zeile-geschätzt-Ausdruck, wie
ein sub-select:

UPDATE Konten SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name VON Verkäufern
WO Vertriebsmitarbeiter.id = Konten.sales_id);"

Referenz: http://www.postgresql.org/docs/9.0/static/sql-update.html

InformationsquelleAutor der Antwort Ketema

es ist ziemlich schnell zu füllen json-in-recordset (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)]
)

InformationsquelleAutor der Antwort nogus

0

Schalten Sie autocommit und einfach ein commit am Ende. In plain SQL bedeutet dies die Erteilung, BEGINNEN am Anfang und BEGEHEN am Ende. Sie würden erstellen müssen, Funktion um eine tatsächliche upsert.

InformationsquelleAutor der Antwort aliasmrchips

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.