Batch operations

Inserting five thousand rows one statement at a time is five thousand round trips. The rows are trivial, the database is idle, and the job takes minutes — because almost none of the time is spent inserting anything.

Batching replaces the round trips, and it is the single largest win available in any code that writes many rows.

#The measurement, and its honest caveat

try (PreparedStatement ps = c.prepareStatement("insert into event values (?, ?)")) {
    for (int i = 0; i < N; i++) {
        ps.setInt(1, i);
        ps.setString(2, "payload-" + i);
        ps.addBatch();
        if (i % 500 == 499) ps.executeBatch();   // flush in chunks
    }
    ps.executeBatch();                            // the remainder
}

### 5000 inserts, one executeUpdate each :     48 ms
### 5000 inserts, addBatch in 500s       :     21 ms
### speedup                              : 2.3x
### rows now in the table                : 5000

Now the caveat, because 2.3× dramatically understates it. That ran against an in-memory H2 in the same process — there is no network in that measurement, and the network is what batching removes. Against a Postgres one millisecond away, five thousand individual inserts spend five seconds purely waiting, and the same batched loop finishes in a fraction of it. Reported speedups of 10× to 50× over a real connection are ordinary.

Which is the useful way to think about it: batching does not make the database faster. It stops you waiting. The gain is proportional to your round-trip time, so it is smallest on a laptop and largest in the environment you actually care about.

#Flush in chunks, not at the end

addBatch() accumulates in the driver's memory. Batching all five thousand and calling executeBatch() once builds one enormous packet and holds every parameter set in the heap.

Chunks of 500 to 1000 is the usual advice, and the reason is not superstition: it bounds memory, it keeps a single failure from invalidating the whole job, and past a certain size the round-trip saving is already taken. Going from 1 to 500 is where the win is; going from 500 to 5000 buys almost nothing and costs memory.

#Batching and transactions are separate decisions

They are easy to conflate and they do different things:

• Batching controls how many round trips you make.
• The transaction controls what is atomic.

With autocommit on, each executeBatch() commits — so a job that fails halfway leaves half the rows. Usually you want one transaction around the whole thing, or one per chunk with a way to resume:

c.setAutoCommit(false);
// ... batches ...
c.commit();

One transaction over a million rows is its own problem, though: it holds locks for the duration and generates a lot of undo or WAL. For a genuinely large import, a transaction per chunk with a recorded high-water mark beats both extremes.

#When one statement fails

executeBatch() returns an int[] of update counts, and on failure throws BatchUpdateException — which carries the counts for the statements that did succeed, so you can tell how far it got.

The part that catches people: what the driver does with the rest is not guaranteed. Some stop at the failure; others continue and report the failed ones with Statement.EXECUTE_FAILED. If your recovery depends on knowing, test it against your actual driver rather than assuming, and remember getNextException() — the useful message in a batch failure is frequently further down the chain than the first one.

#Rewriting, which is the other half on some drivers

addBatch sends many statements in one round trip. Some drivers can do better and send one statement with many value tuples:

insert into event values (1,'a'), (2,'b'), (3,'c'), ...

• PostgreSQL — reWriteBatchedInserts=true in the JDBC URL. It is off by default and is frequently a large additional win for inserts.
• MySQL — rewriteBatchedStatements=true, same idea.

Both are connection-string settings, both apply only to inserts, and both are worth checking before concluding that batching "did not help much".

#From JPA

Hibernate batches too, and it is off by default, which surprises people who assume saveAll is doing something clever:

spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

Two things stop it working even when configured, and both come straight from the JPA course:

• GenerationType.IDENTITY disables insert batching entirely. Hibernate must know the id after each insert, so it cannot defer them. A sequence generator with an allocation size can.
• The persistence context keeps every entity you touch. Inserting a hundred thousand rows through entities means a hundred thousand managed objects and their snapshots. flush() and clear() periodically — or do not use entities for a bulk import, which is usually the better answer.