Batching database writes

Performance and scaling Mid-level Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Pick the fix

A nightly job inserts 2 million rows with one autocommitted INSERT each. Best change?

Options
Hint 1 of 1

What does each autocommit do?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A Load with COPY in batches inside transactions

Correct. Removes per-row round trips and commits.

Each autocommitted INSERT pays a round trip, a parse and a WAL flush. COPY streams many rows in one command, and committing in large batches turns 2 million flushes into a few hundred.

Why not the others?
  • B Add an index on every column. Slows inserts further.
  • C Run the job twice as often. Same total cost.
  • D Raise the connection pool size. The cost is per row, not connection count.

30-second answer

Each separate INSERT costs a network round trip, statement parsing, and, if autocommitted, a WAL flush at every commit. Batching removes most of that: wrap many inserts in one transaction, send multi-row INSERTs or executemany (psycopg 3 pipelines it), or use COPY, PostgreSQL's bulk-load path, which is the fastest for large loads. Keep batches moderate (thousands of rows) so locks and memory stay bounded.

Practise saying it · 25 s. No recording, just a timer

Batch writes.

Say your answer out loud, then check which of these points you covered:

  • Round trip + commit per row
  • One transaction
  • executemany / multi-row
  • COPY
  • Moderate batch size

Deep dive

psycopg 3's cursor.copy() streams rows with COPY FROM STDIN.

INSERT ... SELECT moves data inside the database without round trips.

Drop or defer secondary indexes for very large one-off loads, then rebuild.

Batching applies to reads too: one WHERE id = ANY(%s) beats many lookups.

Set-based insert in one statement Ran on PostgreSQL 17.11
SQL · PostgreSQL 17
CREATE TEMP TABLE events (id int, kind text);
INSERT INTO events
SELECT g, 'view' FROM generate_series(1, 10000) AS g;
SELECT count(*) FROM events;

Common mistakes

  • Autocommit around every row.
  • One giant transaction of millions of rows holding locks for minutes.
  • Building multi-row INSERT strings by hand without parameters.

Senior take & follow-ups

Name the per-row costs, then transactions, executemany, COPY, and sensible batch sizes.

Follow-up questions

Think of your answer first, then open the hint.

  1. Why is autocommit per row slow?

    Show hint

    Each commit waits for a WAL flush to disk.

  2. Fastest bulk load in PostgreSQL?

    Show hint

    COPY.

Related questions

Suggest a fix if something here is wrong or out of date.