Connection Pooling and Batch

Opening a DB connection takes TCP + TLS + authentication: often 20–100 ms. A connection pool keeps open connections and lends them out; close() returns the connection to the pool instead of closing it. HikariCP is the fastest and is Spring Boot's default.

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/shopease");
config.setUsername("shop");
config.setPassword(System.getenv("DB_PASS"));
config.setMaximumPoolSize(10);           // rule of thumb: (cores * 2) + disks
config.setConnectionTimeout(3000);       // ms to wait for a free connection
DataSource ds = new HikariDataSource(config);

try (Connection con = ds.getConnection()) {   // borrowed from the pool
    ...
}                                             // returned to the pool

spring.datasource.url=jdbc:mysql://localhost:3306/shopease
spring.datasource.username=shop
spring.datasource.password=${DB_PASS}
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.connection-timeout=3000

Sending 10,000 inserts one by one means 10,000 network round trips. addBatch() + executeBatch() sends them in groups. For MySQL also add rewriteBatchedStatements=true to the URL.

String sql = "INSERT INTO order_item (order_id, product_id, qty, price) VALUES (?, ?, ?, ?)";
try (Connection con = ds.getConnection(); PreparedStatement ps = con.prepareStatement(sql)) {
    con.setAutoCommit(false);
    int count = 0;
    for (OrderItem item : items) {
        ps.setLong(1, item.orderId());
        ps.setLong(2, item.productId());
        ps.setInt(3, item.qty());
        ps.setBigDecimal(4, item.price());
        ps.addBatch();
        if (++count % 1000 == 0) ps.executeBatch();   // flush every 1000 rows
    }
    ps.executeBatch();
    con.commit();
}

one-by-one insert vs batch insert

single inserts, auto-commit : 41.8 s
batch of 1000, one commit   :  0.9 s