skip to content

How would you choose the JDBC batch size and shape the transactions for a nightly job that writes several million rows through Hibernate?

level: principalimportance: nice to knowfreq 30%

answer

  1. measure on the real network path; latency decides
  2. fix blockers first: identity id, missing ordering
  3. sweep 10/30/50/100/500 — flattens after ~50
  4. chunked restartable commits + flush/clear
  5. past a few million rows: COPY / native loader

basics

~20 s

Measure rather than guess. Start around 30 to 50, raise it while throughput improves, and stop when gains flatten. Chunk the work into bounded transactions, flush and clear per chunk, avoid identity keys, and check whether the driver rewrites batches before tuning further.

solid answer

~60 s

Treat batch size as one variable in a system, not a magic number. **Start**: 30–50 with statement ordering enabled and a sequence-based identifier using an allocation size. Measure rows per second over a realistic sample. **Sweep**: try 10 / 50 / 100 / 500. Gains flatten quickly — beyond roughly 50–100 you are usually latency-bound no longer, and the remaining cost is the database doing real work. Wide rows, large text or binary columns, and many indexes all lower the useful ceiling. **Bound the transaction**: commit per chunk (tens of thousands of rows) rather than once at the end. One giant transaction holds locks and undo/redo for its whole duration, delays vacuum or purge, inflates replication lag, and turns a failure at 95% into a total restart. Chunking gives restartability if chunks are idempotent or checkpointed. **Bound memory**: `flush()` + `clear()` per batch so the persistence context does not accumulate entities and snapshots. **Check the transport**: driver rewrite flags can matter as much as batch size. **Know the exit**: past a certain volume, a bulk loader such as `COPY` beats any ORM path, and the honest answer is to leave the ORM for that job.

code

java · 16 lines
java
int batchSize = 50, chunkSize = 20_000;
for (List<Row> chunk : partition(rows, chunkSize)) {
    tx.begin();
    int i = 0;
    for (Row row : chunk) {
        em.persist(toEntity(row));
        if (++i % batchSize == 0) {
            em.flush();
            em.clear();
        }
    }
    em.flush();
    em.clear();
    tx.commit();
    checkpoint.record(chunk.lastId());
}

go deeper

for a junior

Say that batch size should be measured, that a moderate value like 50 is typical, and that the loop should flush and clear periodically.

for a middle

Add the prerequisites — sequence identifiers, statement ordering — and why gains flatten after a point.

for a senior

Discuss transaction chunking against lock duration, replication and restartability, plus driver rewrite flags and index maintenance.

for a principal

Weigh the whole write path: where the bottleneck actually is, atomicity versus operability, impact on co-tenant traffic, and when to abandon the ORM for a bulk loader.

## Why there is no single right number Batching converts network round trips into fewer, larger ones. Its value therefore depends on how much of your elapsed time *was* round trips. On a 0.2 ms local socket, batching saves little; across an availability zone at 2 ms, it can dominate. The correct batch size is whatever makes the remaining bottleneck something other than latency — which means it must be measured on the real network path with realistic rows, not chosen from a blog post. ## A method 1. **Establish a baseline.** Fixed input sample, fixed hardware, measure rows per second and peak heap. Verify batching is actually happening with a proxying datasource before tuning anything — a large fraction of "tuning" sessions turn out to be fixing an identity-generated key. 2. **Remove the blockers first.** Identity identifiers disable insert batching entirely; missing `hibernate.order_inserts`/`order_updates` cap batches at one in mixed workloads. Neither is a tuning question; both are prerequisites. 3. **Sweep the size.** 10, 30, 50, 100, 500. Plot throughput. Expect a steep climb to roughly 30–50 and a flat curve after; large values mainly increase memory held per statement and lengthen the window between commits. 4. **Watch the second axis.** Note heap and GC alongside throughput. A batch size that is 5% faster and doubles allocation pressure is not a win on a shared JVM. ## Transaction shape matters more than the last 20% of batch size A single transaction wrapping millions of rows is the more common mistake: - **Locks and contention.** Row locks are held until commit; concurrent workloads suffer for the whole run. - **Undo/redo and vacuum.** Long transactions pin old row versions, delaying cleanup on MVCC engines and bloating the tables the job just wrote. - **Replication lag.** A giant commit arrives at replicas as one lump; read replicas can fall far behind at exactly the wrong moment. - **Failure cost.** A failure near the end throws away all the work and repeats the entire run. Chunked commits — tens of thousands of rows per transaction — fix all four. The price is that the job is no longer atomic, so it must be **restartable**: idempotent writes keyed by a natural business key, or a checkpoint recording the last committed chunk. Deciding that trade is the actual senior judgement in this question; the batch size is the easy part. ## Memory Inside a chunk, `flush()` and `clear()` at the batch boundary keep the persistence context small. Without `clear()`, entities and their loaded-state snapshots accumulate, heap climbs, and each flush dirty-checks a growing set — throughput decays over the run, which is a characteristic curve worth recognising. ## Transport and schema levers with comparable payoff - **Driver rewriting.** MySQL `rewriteBatchedStatements=true`, PostgreSQL `reWriteBatchedInserts=true` — these can beat any batch-size adjustment because they collapse a batch into one server-side statement. - **Identifier allocation.** A pooled sequence with `allocationSize` of 50–100 removes a round trip per row. - **Indexes and triggers.** Every secondary index is maintained per row. For a very large load, dropping and rebuilding non-essential indexes afterwards can dominate every other effect. - **Second-level cache.** Writes through a cached region add put/invalidate work per entity; a bulk job usually should not populate it. ## Knowing when to leave the ORM Hibernate's write path exists to maintain object state, versioning, cascades and callbacks. A bulk load needs none of that. Past a few million rows, a native bulk path — PostgreSQL `COPY`, a vendor loader, or a single `insert … select` executed in the database — is often an order of magnitude faster than any ORM configuration, because it avoids per-row object work altogether. A principal-level answer says this out loud instead of tuning `batch_size` toward a ceiling that the wrong tool imposes. ## Operability Whatever the numbers, make the job observable and controllable: log rows per second and chunk progress, make the chunk size and batch size configurable without redeployment, add a rate limit if the job shares the database with online traffic (a nightly import that saturates I/O is an outage in disguise), and schedule with awareness of replication and backup windows. Being able to slow the job down deliberately is worth more in production than the last few percent of throughput.

  • Why not simply set the batch size to 10,000 and commit once at the end?
    Throughput stops improving well before that, while the downsides keep growing: more memory held per prepared statement, row locks and old row versions retained for the whole run, replication lag on a single enormous commit, and a failure at 95% that discards everything. Chunked commits with a moderate batch size give nearly all the throughput with a far better failure and contention profile.
  • Chunked commits mean the job is no longer atomic. How do you make that safe?
    Make it restartable. Either write idempotently — keyed by a business identifier so re-processing a chunk is a no-op or an upsert — or record a checkpoint after each committed chunk so a restart resumes from the last completed one. Also decide what a partially applied load means for readers, and gate visibility with a status flag or a staging table if partial data would be wrong to expose.

saying these in an interview costs you the question

  • Quoting a batch size as universally correct without measuring
  • Tuning batch size while an identity-generated key disables insert batching entirely
  • Wrapping millions of rows in one transaction for atomicity without weighing lock, vacuum and replication cost
  • Omitting clear() and blaming the slowdown on the database
  • Never considering a native bulk-load path for very large volumes

context

resolving links…