Batch Inserts and Updates

JDBC Batching

By default Hibernate sends each INSERT/UPDATE as its own round trip. With JDBC batching it groups up to batch_size statements of the same shape and sends them together.

Settings

application.properties

spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
# MySQL only: let the driver rewrite a batch into one multi-row INSERT
spring.datasource.url=jdbc:mysql://localhost:3306/shopease?rewriteBatchedStatements=true

order_inserts/order_updates sort statements by entity type so that batches are not broken when you save products and categories alternately.
Versioned entities batch fine in Hibernate 6 (hibernate.jdbc.batch_versioned_data is true by default).

The IDENTITY Trap

With GenerationType.IDENTITY (MySQL AUTO_INCREMENT) Hibernate must run each INSERT immediately to learn the id, so insert batching is silently disabled. Use a sequence with a pooled optimizer instead (on MySQL, a table-based or UUID id, or accept no insert batching).

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "product_seq")
    @SequenceGenerator(name = "product_seq", sequenceName = "product_seq", allocationSize = 50)
    private Long id;      // one sequence call reserves 50 ids -> inserts can be batched
}

Batch Size Effect

Example

Importing 10,000 products from a supplier CSV. Fewer round trips = much less time spent on the network:

public class Main {
    public static void main(String[] args) {
        int rows = 10_000;
        double latencyMs = 1.0;                  // one round trip to the database
        int[] batchSizes = {1, 10, 50, 100, 500};

        System.out.println("batch_size | round trips | network time");
        for (int size : batchSizes) {
            int trips = 0;
            int pending = 0;
            for (int i = 0; i < rows; i++) {
                pending++;                        // addBatch()
                if (pending == size) { trips++; pending = 0; }   // executeBatch()
            }
            if (pending > 0) trips++;
            System.out.printf("%10d | %11d | %6.0f ms%n", size, trips, trips * latencyMs);
        }
        System.out.println("gain from 50 -> 500 is small; 20-100 is the usual sweet spot");
    }
}

Output

batch_size | round trips | network time
         1 |       10000 |  10000 ms
        10 |        1000 |   1000 ms
        50 |         200 |    200 ms
       100 |         100 |    100 ms
       500 |          20 |     20 ms
gain from 50 -> 500 is small; 20-100 is the usual sweet spot

Flush and Clear in Chunks

Batching alone is not enough for big imports: every saved entity stays in the first-level cache, and dirty checking at each flush walks all of them. Memory and CPU grow with every row. Flush and clear every batch_size rows:

@Transactional
public void importProducts(List<ProductCsvRow> rows) {
    int batchSize = 50;
    for (int i = 0; i < rows.size(); i++) {
        em.persist(rows.get(i).toEntity());
        if ((i + 1) % batchSize == 0) {
            em.flush();     // send this batch of INSERTs
            em.clear();     // detach them: memory stays flat
        }
    }
}

Batch Log

Log with batching on:

Example output

DEBUG org.hibernate.engine.jdbc.batch.internal.BatchImpl : Executing JDBC batch (50 / 50) - com.shopease.product.Product#INSERT
DEBUG org.hibernate.engine.jdbc.batch.internal.BatchImpl : Executing JDBC batch (50 / 50) - com.shopease.product.Product#INSERT
...
Session Metrics { ... 200 JDBC batches executed; 10000 rows }

Tip: For millions of rows, skip the ORM: use JdbcTemplate.batchUpdate, the database's bulk loader (COPY in PostgreSQL, LOAD DATA in MySQL), or a Spring Batch job.

Exercise

Exercise: You set hibernate.jdbc.batch_size=50 but the log still shows one INSERT per round trip for OrderItem. List two likely causes.

Show answer

1. OrderItem uses @GeneratedValue(strategy = IDENTITY): Hibernate must execute each
   INSERT immediately to get the id, so insert batching is disabled.
   Fix: SEQUENCE with allocationSize (or a pre-generated UUID).
2. Inserts of different entities are interleaved (Order, OrderItem, Order, ...) and
   hibernate.order_inserts is false, so each batch has size 1.
   (Also check: MySQL needs rewriteBatchedStatements=true for a real multi-row insert.)