Bulk-Operationen statt Schleifen pro Zeile: Roundtrips, Transaktionen und COPY

Worum es geht

Eine Schleife pro Zeile sendet ein Statement pro Zeile und wartet auf jede Antwort. Eine Bulk-Operation sendet die gesamte Menge auf einmal: ein mehrzeiliges INSERT ... VALUES (...), (...), ein einzelnes UPDATE ... FROM (VALUES ...), ein Lesevorgang mit WHERE id = ANY(...) oder ein COPY-Stream. Der PostgreSQL-Leitfaden zum Befüllen einer Datenbank rät, bei vielen Inserts das Autocommit auszuschalten und erst am Ende einmal zu committen, COPY zu verwenden, um alle Zeilen in einem Befehl zu laden, weil es für grosse Zeilenmengen optimiert ist und deutlich weniger Overhead als eine Reihe von INSERTs verursacht, Prepared Statements zu nutzen, wenn COPY nicht möglich ist, und Indizes erst nach dem Laden einer frischen Tabelle zu erstellen, weil das Indizieren vorhandener Daten schneller ist als das Aktualisieren eines Index pro Zeile. psycopgs executemany führt denselben Befehl über eine Folge von Parametern aus, und seine Dokumentation beschreibt es als effizienter als einzelne Abfragen, während sie für viele Inserts copy() vorschlägt.

Warum es wichtig ist

Die Kosten pro Zeile bestehen aus Roundtrip-Latenz plus Parsing und Planung plus, bei Autocommit, einem Commit, der dauerhaft gemacht werden muss. Bei einem Roundtrip von 1 ms verbringt eine Million Zeilen allein durch das Netzwerk mindestens 1000 s (etwa 17 Minuten) Wartezeit (Rechnung), bevor die Datenbank überhaupt Arbeit leistet. Bulk-Operationen entfernen diesen Term und lassen die Datenbank nur einmal planen.

So wird es angewendet

• Lesevorgänge: eine Abfrage mit id = ANY(%s) oder ein Join statt einer Schleife von Einzel-Lookups; grosse Ergebnisse mit Keyset-Cursorn paginieren.
• Schreibvorgänge: mehrzeiliges INSERT, executemany oder COPY zum Laden; UPDATE ... FROM (VALUES ...) oder UPDATE ... WHERE id = ANY(...) für Bulk-Updates; DELETE ... WHERE id = ANY(...) für Löschungen.
• Die Batches selbst batchen: eine Transaktion pro einigen tausend Zeilen hält Sperren kurz und das Write-Ahead-Log begrenzt, während der Commit trotzdem amortisiert wird.
• Für eine frische Tabelle: laden, dann Indizes und Constraints erstellen, dann ANALYZE ausführen, wie der Leitfaden empfiehlt.
• Die Eingabe streamen, statt eine Liste von einer Million Dictionaries zu materialisieren; executemany nimmt einen iterierbaren Satz von Parametern entgegen, und copy() schreibt Zeilen einzeln aus einem beliebigen Iterator.

Stolpersteine

Ein Bulk-Statement scheitert als Ganzes: eine fehlerhafte Zeile bricht den gesamten Batch ab; im Voraus entscheiden, ob für den fehlgeschlagenen Batch auf zeilenweise Verarbeitung zurückgefallen oder er verworfen wird. Eine einzige riesige Transaktion hält Sperren über ihre gesamte Dauer und lässt das Write-Ahead-Log anschwellen. Trigger und Constraints pro Zeile feuern weiterhin pro Zeile. Bulk-Löschungen auf grossen Tabellen blockieren andere Schreibende; nach Primärschlüsselbereich in Batches löschen. ORM-Bulk-Methoden überspringen oft Modellvalidierung, Signale und Save-Hooks; ihre Einschränkungen lesen, bevor man sich auf sie verlässt.