Improving Million‑Row Insert Performance with Spring Boot ThreadPoolTaskExecutor

Introduction

The goal is to increase the efficiency of inserting millions of rows by using Spring Boot 2.1.1 with MyBatis‑Plus, Swagger, Lombok, PostgreSQL, and a multithreaded approach based on ThreadPoolTaskExecutor .

Implementation Details

Thread pool parameters are added to application-dev.properties :

# Asynchronous thread configuration
# Core thread count
async.executor.thread.core_pool_size = 30
# Maximum thread count
async.executor.thread.max_pool_size = 30
# Queue capacity
async.executor.thread.queue_capacity = 99988
# Thread name prefix
async.executor.thread.name.prefix = async-importDB-

A Spring bean for the executor is defined:

@Configuration
@EnableAsync
@Slf4j
public class ExecutorConfig {
    @Value("${async.executor.thread.core_pool_size}")
    private int corePoolSize;
    @Value("${async.executor.thread.max_pool_size}")
    private int maxPoolSize;
    @Value("${async.executor.thread.queue_capacity}")
    private int queueCapacity;
    @Value("${async.executor.thread.name.prefix}")
    private String namePrefix;

    @Bean(name = "asyncServiceExecutor")
    public Executor asyncServiceExecutor() {
        log.warn("start asyncServiceExecutor");
        ThreadPoolTaskExecutor executor = new VISIBLEThreadPoolTaskExecutor();
        executor.setCorePoolSize(corePoolSize);
        executor.setMaxPoolSize(maxPoolSize);
        executor.setQueueCapacity(queueCapacity);
        executor.setThreadNamePrefix(namePrefix);
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        executor.initialize();
        return executor;
    }
}

The asynchronous service that performs the batch insert:

@Service
@Slf4j
public class AsyncServiceImpl implements AsyncService {
    @Override
    @Async("asyncServiceExecutor")
    public void executeAsync(List
logOutputResults, LogOutputResultMapper logOutputResultMapper, CountDownLatch countDownLatch) {
        try {
            log.warn("start executeAsync");
            logOutputResultMapper.addLogOutputResultBatch(logOutputResults);
            log.warn("end executeAsync");
        } finally {
            countDownLatch.countDown(); // ensure latch is released even on exception
        }
    }
}

The main business method splits the data into sub‑lists of 100 rows, launches a thread for each sub‑list, and waits for all threads to finish:

@Override
public int testMultiThread() {
    List
logOutputResults = getTestData();
    List
> lists = ConvertHandler.splitList(logOutputResults, 100);
    CountDownLatch countDownLatch = new CountDownLatch(lists.size());
    for (List
listSub : lists) {
        asyncService.executeAsync(listSub, logOutputResultMapper, countDownLatch);
    }
    try {
        countDownLatch.await();
    } catch (Exception e) {
        log.error("Blocking exception:" + e.getMessage());
    }
    return logOutputResults.size();
}

Testing and Results

Using 30 concurrent threads, inserting 2,000,003 records took 1.67 minutes, while a single‑threaded run took 5.75 minutes. Additional tests with different thread counts showed that more threads do not always mean better performance; a practical rule of thumb is CPU cores × 2 + 2 threads.

Data integrity checks confirmed no duplicate or missing rows after the multithreaded import.

Conclusion

The multithreaded approach with ThreadPoolTaskExecutor significantly reduces bulk insert time, and the optimal thread count can be estimated based on CPU cores. This technique is applicable to any Spring Boot application that requires high‑throughput database writes.