Bulk operations instead of per-row loops: round trips, transactions and COPY

What it is

A per-row loop sends one statement per row and waits for each answer. A bulk operation sends the whole set at once: a multi-row INSERT ... VALUES (...), (...), a single UPDATE ... FROM (VALUES ...), a WHERE id = ANY(...) read, or a COPY stream. The PostgreSQL guide on populating a database says to turn off autocommit and commit once at the end when using many inserts, to use COPY to load all the rows in one command because it is optimised for large numbers of rows and incurs significantly less overhead than a series of INSERTs, to use prepared statements when COPY is not possible, and to create indexes after loading a fresh table because indexing existing data is quicker than updating an index per row. psycopg's executemany runs the same command over a sequence of parameters and its documentation describes it as more efficient than separate queries while suggesting copy() for many inserts.

Why it matters

The per-row cost is round-trip latency plus parsing and planning plus, with autocommit, a commit that must be made durable. At a 1 ms round trip, one million rows spend at least 1,000 s (about 17 minutes) waiting on the network alone (arithmetic), before the database does any work. Bulk operations remove that term and let the database plan once.

How to apply

• Reads: one query with id = ANY(%s) or a join instead of a loop of point lookups; page large results with keyset cursors.
• Writes: multi-row INSERT, executemany, or COPY for loads; UPDATE ... FROM (VALUES ...) or UPDATE ... WHERE id = ANY(...) for bulk updates; DELETE ... WHERE id = ANY(...) for deletes.
• Batch the batches: one transaction per few thousand rows keeps locks short and the write-ahead log bounded while still amortising the commit.
• For a fresh table: load, then create indexes and constraints, then run ANALYZE as the guide recommends.
• Stream the input instead of materialising a list of a million dictionaries; executemany takes an iterable of parameter sets and copy() writes rows one at a time from any iterator.

Pitfalls

A bulk statement fails as a unit: one bad row aborts the batch, so decide in advance whether to fall back to row-by-row for the failed batch or to reject it. One enormous transaction holds locks for its entire duration and bloats the write-ahead log. Triggers and per-row constraints still fire per row. Bulk deletes on large tables block other writers; delete by primary-key range in batches. ORM bulk methods often skip model validation, signals and save hooks; read their caveats before relying on them.