One round trip beats a thousand, and your batch API probably is not batching

A nightly job loads 100,000 rows from a file into Postgres. It takes 41 minutes. The table has three indexes, the rows are small, the database is on a machine doing almost nothing, and 41 minutes is roughly 40 rows per second, which is a number so bad it does not look like a performance problem. It looks like something is broken.

Nothing is broken. The job is issuing 100,000 separate statements, each in its own transaction, across a network link with 0.4ms of latency, and the arithmetic on that is exactly what you would predict.

Rewritten to use COPY, the same load takes 11 seconds.

The work is not where you think

For a small insert, the row is not the expensive part. The overhead around it is.

Every statement pays a network round trip out and back. The server parses the SQL text, plans it, and executes it. The transaction machinery opens, records and commits. Then the result comes back and the client moves to the next row.

per row, roughly:
  network round trip      0.4ms   (same AZ; more across zones)
  parse and plan          0.05ms
  execute                 0.02ms   <- the only part doing your work
  commit and flush        0.5ms    (with autocommit on)

The row insert is 20 microseconds of a roughly one millisecond operation. Everything else is ceremony, and batching is how you stop paying the ceremony once per row.

This is the same accounting as an N+1 query pattern, where four hundred fast queries cost more than one slow one. Reads and writes have the same shape: when the per-operation overhead exceeds the per-operation work, the operation count is the thing to optimise.

The batch API that does not batch

Here is the part that genuinely annoys me, because the code looks right.

PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO events (id, type, payload) VALUES (?, ?, ?)");

for (Event e : events) {
    ps.setLong(1, e.getId());
    ps.setString(2, e.getType());
    ps.setString(3, e.getPayload());
    ps.addBatch();
}
ps.executeBatch();

That is textbook JDBC batching. Against MySQL with default connection settings, it sends 100,000 separate INSERT statements. The driver accepts them into a batch, then loops and executes them individually, because rewriting them into a multi-value statement is opt in.

jdbc:mysql://host/db?rewriteBatchedStatements=true

Without that parameter, addBatch is a client side convenience with no effect on the wire. With it, the driver combines rows into INSERT INTO events VALUES (...), (...), (...) and the round trip count collapses.

Postgres has the same shape with a different name:

jdbc:postgresql://host/db?reWriteBatchedInserts=true

Both default to off. Both produce code that passes review, passes tests, and performs like it never batched. I have seen this exact flag be the entire content of a performance fix more than once, and there is nothing in the API that hints at it.

Worth setting a batch size rather than accumulating everything, because the driver builds the whole statement in memory:

int batchSize = 1_000;
int count = 0;

conn.setAutoCommit(false);
for (Event e : events) {
    // bind parameters
    ps.addBatch();
    if (++count % batchSize == 0) {
        ps.executeBatch();
    }
}
ps.executeBatch();
conn.commit();

Somewhere around 500 to 5,000 rows per batch is the usual sweet spot. Past that the statement gets large enough that memory and parse time start eating the gain.

Autocommit turns every row into a barrier

The setAutoCommit(false) line above is doing more work than it appears to.

With autocommit on, each insert is its own transaction. Each transaction commits. Each commit is a durability barrier, which means waiting for the storage device to confirm the write-ahead log record is safe.

That barrier is not CPU work you can parallelise away. It is a wait on the device, and it caps throughput at roughly one over the flush latency regardless of everything else. A hundred thousand autocommitted inserts is a hundred thousand flushes.

Wrapping the load in one transaction means one barrier for the whole thing. The write-ahead log records still get written, they just get flushed together.

flowchart TB
    subgraph auto["autocommit on"]
        A1["row 1"] --> F1["flush"]
        F1 --> A2["row 2"] --> F2["flush"]
        F2 --> A3["... 100,000 flushes"]
    end

    subgraph txn["explicit transaction"]
        B1["row 1"] --> B2["row 2"] --> B3["... 100,000 rows"]
        B3 --> BF["one flush at commit"]
    end

    style A3 stroke:#ef4444,stroke-width:3px,color:#fff
    style BF stroke:#4ade80,stroke-width:3px,color:#fff

There is a limit to how far to take this. One transaction around 100 million rows holds locks for the duration, keeps every row version alive for vacuum, and rolls back the entire load on a single failure. Chunking into transactions of a few tens of thousands gives you most of the amortisation with a recovery point that is not the beginning.

The ladder, and where COPY sits

Each rung removes a different overhead, which is why the gains multiply rather than add.

COPY is a different protocol path rather than a faster statement. There is no SQL to parse per row, no plan, no per-statement bookkeeping. The client streams rows and the server writes them.

CopyManager copy = new PGConnection(conn).getCopyAPI();
copy.copyIn("COPY events (id, type, payload) FROM STDIN WITH (FORMAT csv)", reader);

For genuine bulk loading this is the correct tool and the difference is not subtle. The constraint is that it is a load path, not a general write path: no ON CONFLICT handling in older versions, no returning generated keys, and errors abort the whole stream. Staging into an unlogged table with COPY and then merging with a single INSERT ... SELECT ... ON CONFLICT gets you both.

The same principle on the read side

Reads have the identical failure and it hides better, because reading in a loop looks like normal code.

// 500 round trips
for (Long id : ids) {
    orders.add(orderRepository.findById(id));
}

// One
List<Order> orders = orderRepository.findAllByIdIn(ids);

Keep the IN list bounded. A few thousand values is fine; a hundred thousand produces a statement large enough to be slow to parse and, on Postgres, can blow past the bind parameter limit. Chunking the ids into pages of a thousand and issuing a handful of queries is almost always the right shape.

What I would check

Run the load with rewriteBatchedStatements or reWriteBatchedInserts explicitly set and compare, because if the timing changes then the code was never batching and everything else you tune is noise on top of that.

Count round trips rather than reasoning about them. Postgres log_statement = 'all' for a single run, or the query count metrics from the connection pool, will tell you in one number whether the batch reached the wire.

Check autocommit on any code path that writes more than a handful of rows. It is on by default in most drivers and it is the single largest multiplier in the table above.

The thing that makes this worth writing down is that all four of these controls are invisible at the call site. The loop looks the same whether it issues one round trip or a hundred thousand, the batch API looks identical whether or not the driver honours it, and autocommit is a connection property set somewhere else entirely. The code cannot tell you which version you have. Only the wire can.